DataTalks.Club

Articles Slack Events Podcast Wiki Books Courses

Course Wiki

Storing Data in PostgreSQL — LLM Zoomcamp Module 5

--- video_url: 'https://www.youtube.com/watch?v=iXRu_AbMtuU&list=PL3MmuxUbc_hLZFNgSad56pDBKK8KO0XIv' --- # Storing Data in PostgreSQL

Related Pages

llmz-module-05

LLM Zoomcamp › Module 5: Monitoring › Storing Data in PostgreSQL

Notes

video_url: “https://www.youtube.com/watch?v=iXRu_AbMtuU&list=PL3MmuxUbc_hLZFNgSad56pDBKK8KO0XIv”

Storing Data in PostgreSQL

The metrics disappear when we close the app, so we need somewhere to keep them. We store every conversation in PostgreSQL, which we run only for monitoring. No other part of the system touches this database. We pick Postgres for two reasons: it handles structured data well, and Grafana connects to it easily later on.

Starting PostgreSQL with Docker

First we create a Docker network. Postgres and Grafana both run in containers, and Grafana reaches Postgres by name, so they need to share a network.

Create the network:

docker network create monitoring

Start PostgreSQL with a volume for data persistence and connect it to the network:

docker run -it \
    --name course-assistant-pg \
    --network monitoring \
    -e POSTGRES_USER=user \
    -e POSTGRES_PASSWORD=password \
    -e POSTGRES_DB=course_assistant \
    -p 5432:5432 \
    -v pgdata:/var/lib/postgresql/data \
    postgres:17

If the commands feel opaque, paste them into ChatGPT and ask it to walk you through each flag. The first module of our data-engineering-zoomcamp goes deeper into Docker if you want the longer version.

These are long commands we’ll run again and again, so they go in the Makefile from lesson 02. The postgres target depends on network, so make postgres creates the network first and then starts the container.

Add these targets:

network:
	docker network create monitoring

postgres: network
	docker run -it \
		--name course-assistant-pg \
		--network monitoring \
		-e POSTGRES_USER=user \
		-e POSTGRES_PASSWORD=password \
		-e POSTGRES_DB=course_assistant \
		-p 5432:5432 \
		-v pgdata:/var/lib/postgresql/data \
		postgres:17

Now we can just run:

make postgres

To reach Postgres from Python, we install the psycopg driver:

uv add "psycopg[binary]"

Initializing the database

The table stores everything from our LLMCallRecord, plus the question and the course. I call it conversations, which isn’t the best name. I carried it over from materials I recorded a couple of years ago. Something like llm_call_records would describe it better, but the name stuck. Name yours however you like.

Two fields are worth a word. We store the course because the same assistant can serve more than one course. Right now everything is llm-zoomcamp, but keeping the column means we don’t have to reshape the table when we add others. The timestamp is timezone-aware (TIMESTAMP WITH TIME ZONE) on purpose. Without the time zone, Grafana won’t line the data up correctly on its time axis later.

The SQL to create the table:

CREATE TABLE conversations (
    id SERIAL PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    course TEXT NOT NULL,
    model TEXT NOT NULL,
    instructions TEXT NOT NULL,
    prompt TEXT NOT NULL,
    prompt_tokens INTEGER NOT NULL,
    completion_tokens INTEGER NOT NULL,
    total_tokens INTEGER NOT NULL,
    response_time FLOAT NOT NULL,
    cost FLOAT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL
)

We can run this via psql or any other tool, but let’s create a Python script.

Create db_init.py.

Imports:

import os
import psycopg
from datetime import datetime
DB_TIMEZONE = datetime.now().astimezone().tzinfo
print(f"Using timezone: {DB_TIMEZONE}")

A helper to connect to the database.

It uses environment variables with defaults matching the Docker container we just started:

def get_db_connection():
    return psycopg.connect(
        host=os.getenv("POSTGRES_HOST", "localhost"),
        dbname=os.getenv("POSTGRES_DB", "course_assistant"),
        user=os.getenv("POSTGRES_USER", "user"),
        password=os.getenv("POSTGRES_PASSWORD", "password"),
    )

The init function creates the table.

Key concepts

Sources

DataTalks.Club. Hosted on GitHub Pages. Built with Rustkyll. We use cookies.