How to build agent memory with pgvector

Most agents forget everything between calls because their state lives only in the prompt. Building agent memory pgvector lets you persist episodic and semantic knowledge in a Postgres instance you already run, with cosine similarity search out of the box. This tutorial walks through a working implementation: from extension install to a minimal retrieval-augmented agent loop you can drop into a service.

Prerequisites

• Docker for local Postgres with the vector extension.
• Python 3.10+ and pip install psycopg2-binary openai.
• An OpenAI-compatible embeddings endpoint. The standard text-embedding-3-small returns 1536-dim vectors; we’ll match that dimension in the schema.
• Basic familiarity with SQL and Python.

If you already run Postgres 16, you can skip the container and just enable the extension.

1. Start Postgres with pgvector

Use the official pgvector image to avoid compiling from source:

docker run --name agent-mem \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -d pgvector/pgvector:pg16

Within a few seconds, docker logs agent-mem should show:

database system is ready to accept connections

Keep the container running for the rest of the steps.

2. Create the memory schema

The vector type is provided by the extension. Dimension must be fixed per column. We’ll use 1536 to match the embedding model.

import psycopg2

conn = psycopg2.connect(
    host="localhost", port=5432, dbname="postgres",
    user="postgres", password="secret"
)
cur = conn.cursor()
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
cur.execute("""
CREATE TABLE IF NOT EXISTS agent_memory (
    id BIGSERIAL PRIMARY KEY,
    agent_id TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMPTZ DEFAULT now()
);
""")
conn.commit()
cur.close()
conn.close()

No output is printed, but in psql you can verify:

\d agent_memory

Expected columns: id, agent_id, content, embedding vector(1536), created_at.

3. Generate and store embeddings

We need a function to turn text into a vector. If you route embeddings through n4n.ai, its OpenAI-compatible endpoint fronts 240+ models and handles provider fallback, so a rate limit won’t break your memory writes. The client code is identical otherwise.

from openai import OpenAI

client = OpenAI()  # or OpenAI(base_url="https://api.n4n.ai/v1", api_key="sk-...")

def embed(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-small",
        input=text
    )
    return resp.data[0].embedding

def store_memory(agent_id: str, content: str):
    vec = embed(content)
    conn = psycopg2.connect(host="localhost", port=5432, dbname="postgres",
                            user="postgres", password="secret")
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO agent_memory (agent_id, content, embedding) VALUES (%s, %s, %s)",
        (agent_id, content, vec)
    )
    conn.commit()
    cur.close()
    conn.close()

store_memory("research-bot", "User prefers concise answers with code snippets.")
store_memory("research-bot", "Project uses Postgres 16 and pgvector for all vector needs.")

Check the row count:

conn = psycopg2.connect(host="localhost", port=5432, dbname="postgres",
                        user="postgres", password="secret")
cur = conn.cursor()
cur.execute("SELECT count(*) FROM agent_memory;")
print(cur.fetchone())
cur.close(); conn.close()

Expected output:

(2,)

4. Retrieve by similarity

The core of agent memory pgvector is nearest-neighbor lookup. The <=> operator computes cosine distance; 1 - distance is similarity.

def recall_memories(agent_id: str, query: str, k: int = 3):
    vec = embed(query)
    conn = psycopg2.connect(host="localhost", port=5432, dbname="postgres",
                            user="postgres", password="secret")
    cur = conn.cursor()
    cur.execute("""
        SELECT content, 1 - (embedding <=> %s) AS similarity
        FROM agent_memory
        WHERE agent_id = %s
        ORDER BY embedding <=> %s
        LIMIT %s
    """, (vec, agent_id, vec, k))
    rows = cur.fetchall()
    cur.close(); conn.close()
    return rows

print(recall_memories("research-bot", "How should I format responses?"))

Typical output:

[('User prefers concise answers with code snippets.', 0.82), ('Project uses Postgres 16 and pgvector for all vector needs.', 0.71)]

Scores depend on the embedding model; treat values above 0.7 as strong topical matches for text-embedding-3-small.

5. Wire into a minimal agent loop

A real agent retrieves context before answering and stores new facts when told. Below we add a chat completion call using the recalled memories as system context.

from openai import OpenAI
llm = OpenAI()

def agent_respond(agent_id: str, user_msg: str) -> str:
    ctx = recall_memories(agent_id, user_msg, k=3)
    memory_block = "\n".join(f"- {c} (sim={s:.2f})" for c, s in ctx)
    sys_prompt = (
        "You are a focused assistant. Use the provided memory to stay consistent.\n"
        f"Memory:\n{memory_block}"
    )
    resp = llm.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {"role": "system", "content": sys_prompt},
            {"role": "user", "content": user_msg}
        ]
    )
    answer = resp.choices[0].message.content
    if "remember" in user_msg.lower():
        store_memory(agent_id, user_msg)
    return answer

print(agent_respond("research-bot", "What style should I use?"))
print(agent_respond("research-bot", "Remember I dislike verbose intros."))
print(recall_memories("research-bot", "style preference"))

The first call pulls the concise-answer memory. The second stores a new fact. The third confirms retrieval. This agent memory pgvector loop is enough to keep an agent stateful across sessions.

6. Indexing and production notes

Sequential scan is fine under ~10k rows. Beyond that, create an HNSW index for cosine:

CREATE INDEX ON agent_memory
USING hnsw (embedding vector_cosine_ops);

For single-agent workloads, a filtered index per agent_id keeps multi-tenant tables fast:

CREATE INDEX ON agent_memory
USING hnsw (embedding vector_cosine_ops)
WHERE agent_id = 'research-bot';

Cache embeddings client-side for repeated queries. If your gateway honors provider cache-control hints, set cache-control: max-age=3600 on embedding requests to avoid recomputing identical strings. Per-token metering at the gateway helps track memory-write costs separately from generation.

The agent memory pgvector design gives you ACID writes and joins with relational metadata (user_id, session) without standing up a separate vector store.

7. Temporal blending

Embeddings ignore recency. Add a time decay to surface fresh facts:

SELECT content,
       1 - (embedding <=> %s) AS similarity
FROM agent_memory
WHERE agent_id = %s
ORDER BY (embedding <=> %s) + EXTRACT(EPOCH FROM (now() - created_at))/1e7
LIMIT %s

Tune the divisor so a day-old memory adds ~0.01 to distance. This pattern extends the agent memory pgvector base into a pragmatic memory system without external services.

8. Cleanup and limits

Delete stale memories with standard SQL:

DELETE FROM agent_memory WHERE created_at < now() - interval '30 days';

Vector search is approximate under HNSW; for exact recall on small tables drop the index. The approach does not model hierarchical memory or forgetting curves—those are application-level concerns you can layer on the same table.