PostgreSQL Basics

Agents need somewhere to put things. A conversation history that persists between sessions. A user profile that survives a restart. A log of every tool call so you can debug a failure three days later. PostgreSQL is a relational database that handles all of this, and it is the backing store for nearly every agent in this portal. This lesson covers installing it, connecting from Python, and the handful of SQL statements you will actually write.

What you'll learn

• PostgreSQL stores structured data in tables, with a schema that enforces what each column can contain
• Connect from Python with psycopg2 -- a few lines of code gets you querying from your agent
• The extension pgvector adds vector search for semantic memory. We use it heavily in the RAG course -- this lesson gives you the foundations

The problem

You build an agent that remembers conversation context while it runs. You store it in a Python dictionary. It works great until you restart the process and every conversation is gone. You try writing to a JSON file. That works until two requests hit at the same time and one overwrites the other's data. You try SQLite, which solves the concurrency problem, but then you need to scale to multiple agent processes and SQLite's single-writer model becomes a bottleneck.

PostgreSQL handles concurrent reads and writes safely, persists data to disk, and scales from a single process on your laptop to a production cluster. It is the database that the rest of the AI industry has standardized on for agent memory, and it is what we use throughout this portal.

Options & when to use each

Approach	What it is	When to use it
In-memory dict or list	Python data structures, gone on restart	Prototypes, one-off scripts where persistence does not matter
JSON/YAML files	Flat files on disk, no concurrency safety	Single-user CLI tools, configuration files
SQLite	File-based relational database, single writer	Small projects, mobile apps, embedded use cases. Works with Python's built-in sqlite3 module
PostgreSQL	Client-server relational database, concurrent readers and writers	Multi-user applications, agent backends, anything that needs to grow beyond a single process

Build it

Install PostgreSQL

On Debian/Ubuntu (including WSL):

sudo apt update
sudo apt install postgresql postgresql-contrib

On macOS with Homebrew:

brew install postgresql@16
brew services start postgresql@16

After installation, PostgreSQL is running as a system service. Confirm it:

sudo systemctl status postgresql
# Look for "active (running)"

Create a database and user

PostgreSQL creates a default postgres superuser during installation. Use it to set up a dedicated user and database for your project:

# Switch to the postgres user and open the psql shell
sudo -u postgres psql

Inside the psql shell:

-- Create a user with a password
CREATE USER agent_user WITH PASSWORD 'choose-a-strong-password-here';

-- Create a database owned by that user
CREATE DATABASE agent_memory OWNER agent_user;

-- Exit psql
\q

Connect from Python

Install the PostgreSQL adapter:

uv add psycopg2-binary python-dotenv

Store your credentials in a .env file (do not commit this to git):

DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=agent_memory
DB_USER=agent_user
DB_PASSWORD=choose-a-strong-password-here

Connect and query:

import os
from dotenv import load_dotenv
import psycopg2

load_dotenv()

conn = psycopg2.connect(
    host=os.getenv("DB_HOST", "127.0.0.1"),
    port=os.getenv("DB_PORT", "5432"),
    dbname=os.getenv("DB_NAME", "agent_memory"),
    user=os.getenv("DB_USER", "agent_user"),
    password=os.getenv("DB_PASSWORD"),
)

# Create a cursor to execute SQL
cur = conn.cursor()

# Create a table
cur.execute("""
    CREATE TABLE IF NOT EXISTS conversations (
        id SERIAL PRIMARY KEY,
        user_id VARCHAR(100) NOT NULL,
        message TEXT NOT NULL,
        role VARCHAR(20) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    );
""")

# Insert a row
cur.execute(
    "INSERT INTO conversations (user_id, message, role) VALUES (%s, %s, %s)",
    ("user_42", "What is the weather in Tokyo?", "user"),
)

# Commit the transaction
conn.commit()

# Query rows
cur.execute(
    "SELECT message, role, created_at FROM conversations WHERE user_id = %s ORDER BY created_at DESC LIMIT 10",
    ("user_42",),
)
rows = cur.fetchall()
for message, role, created_at in rows:
    print(f"[{created_at}] {role}: {message}")

# Clean up
cur.close()
conn.close()

Core SQL you will actually use

The four statements that cover most agent work:

-- CREATE TABLE: define the shape of your data
CREATE TABLE sessions (
    id SERIAL PRIMARY KEY,
    session_token VARCHAR(64) UNIQUE NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- INSERT: add rows
INSERT INTO sessions (session_token, user_id) VALUES ('abc123', 'user_42');

-- SELECT: read rows
SELECT * FROM sessions WHERE user_id = 'user_42';

-- UPDATE: modify rows
UPDATE sessions SET metadata = '{"theme": "dark"}' WHERE session_token = 'abc123';

pgvector: a preview

PostgreSQL has an extension called pgvector that adds a vector column type and similarity search operators. This is how agents store and search embedding vectors for semantic memory. You will use this heavily in the RAG course. For now, know that it exists and that the setup is one line:

CREATE EXTENSION IF NOT EXISTS vector;

See the RAG & Knowledge Retrieval course for building full vector search pipelines on top of PostgreSQL.

What goes wrong

Mistake	How you notice it	The fix
Forgot to commit the transaction	INSERT has no effect after the script exits	Call conn.commit() after writes. psycopg2 does not auto-commit by default. Or set conn.autocommit = True at connection time
Connection refused	psycopg2.OperationalError: connection refused	PostgreSQL is not running. sudo systemctl start postgresql. If it fails to start, check logs with sudo journalctl -u postgresql
Authentication failed	psycopg2.OperationalError: FATAL: password authentication failed	The password in your .env does not match what you set with CREATE USER. Reset it: sudo -u postgres psql -c "ALTER USER agent_user PASSWORD 'new-password';"
Hardcoded credentials in code	Password visible in source, committed to git	Always load credentials from environment variables or a .env file. Add .env to .gitignore
Used SELECT * in production code	Query breaks when someone adds a column	List the columns you need explicitly: SELECT id, message, created_at FROM conversations. It is clearer and safer
No indexes on query columns	Queries get slower as the table grows	Add an index: CREATE INDEX idx_conversations_user_id ON conversations(user_id);. Indexes speed up reads at the cost of slightly slower writes

Confirm it worked

After setting up PostgreSQL and running the Python connection code:

# 1. Check PostgreSQL is running
sudo systemctl status postgresql
# Look for "active (running)"

# 2. Connect manually and verify the table exists
sudo -u postgres psql -d agent_memory -c "\dt"
# Should list the "conversations" table

# 3. Verify your row was inserted
sudo -u postgres psql -d agent_memory -c "SELECT * FROM conversations;"
# Should show the row your Python script inserted

# 4. Verify Python can connect and query
python -c "
import psycopg2, os
from dotenv import load_dotenv
load_dotenv()
conn = psycopg2.connect(
    host=os.getenv('DB_HOST', '127.0.0.1'),
    dbname=os.getenv('DB_NAME', 'agent_memory'),
    user=os.getenv('DB_USER', 'agent_user'),
    password=os.getenv('DB_PASSWORD'),
)
cur = conn.cursor()
cur.execute('SELECT COUNT(*) FROM conversations;')
print(f'Rows in conversations: {cur.fetchone()[0]}')
cur.close()
conn.close()
"
# Should print a count >= 1

Next: Docker & Local Services