Semantic Long-Term Memory for AI Agents with PostgreSQL + pgvector ✓ VERIFIED

2026-08-14 · 11 min read · PostgreSQL · pgvector · RAG · AI Agents · Docker

An AI agent that forgets everything between runs isn't an agent — it's a calculator with a token budget. Context windows are finite and expensive, sessions rotate, and every restart wipes the slate clean. Long-term memory fixes this: store what the agent has seen and learned as vector embeddings, then let it retrieve the relevant slice on demand. This tutorial builds that memory layer with PostgreSQL and the pgvector extension — the same database you already run for everything else, now doing fast vector similarity search. No new database, no new vendor, no new service to babysit.

1. Why Agents Need Memory Beyond the Context Window

Even a 1M-token context window is a scratchpad, not a memory. Three problems push you toward an external store:

• Cost — re-sending your entire history on every turn burns tokens and latency. Memory lets you retrieve only the relevant slice.
• Statelessness — a run ends, a session rotates, a container restarts, and the agent is amnesiac. Memory survives all three.
• Scale — a single agent accumulating months of conversations, code reviews, and decisions will outgrow any window. Memory grows with the data, not the window.

Agents need roughly three kinds of memory:

• Episodic — "what happened": the bug I fixed last Tuesday, the user's earlier request.
• Semantic — "what's true": facts, preferences, project conventions.
• Procedural — "how to": skills, scripts, runbooks (the kind of thing that belongs in a skills repo).

pgvector handles episodic and semantic memory beautifully: you encode a memory as a vector, store it next to its plain-text content and metadata, and retrieve by meaning rather than keyword.

2. What pgvector Gives You

pgvector is a PostgreSQL extension that adds a vector column type and index types for approximate nearest-neighbor (ANN) search. The essentials:

• vector(n) — a fixed-dimension float array. Up to 16,000 dimensions in current releases, which covers every mainstream embedding model.
• Distance operators — <-> (L2 / Euclidean), <#> (negative inner product), and <=> (cosine distance). For semantic similarity you usually want cosine.
• Indexes — HNSW (fast, in-memory graph) and IVFFlat (memory-light, list-based). HNSW is the default choice for production recall.
• It's still Postgres — your memory rows sit next to your app tables, join with metadata, inherit transactions, and ride along with your existing backups.

The killer advantage over a dedicated vector database: you already run Postgres. One less service to secure, monitor, and pay for.

3. Install pgvector

On Debian/Ubuntu, pgvector ships as a packaged extension for each Postgres major version:

$ sudo apt update
$ sudo apt install postgresql-16-pgvector
Reading package lists... Done
Setting up postgresql-16-pgvector (0.7.4-1) ...

Then enable it inside your database:

$ sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS vector;"
CREATE EXTENSION

$ sudo -u postgres psql -c "SELECT extversion FROM pg_extension WHERE extname='vector';"
 extversion
------------
 0.7.4

4. Create the Memory Schema

The schema separates the content (plain text you can show, log, and fall back to) from the embedding (the vector you search). Every row carries enough metadata to scope retrieval to the right agent and session:

-- schema.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS agent_memories (
    id BIGSERIAL PRIMARY KEY,
    agent_id TEXT NOT NULL,              -- 'susu', 'hermes', 'im-bot:room-7'
    namespace TEXT NOT NULL DEFAULT 'default',
    memory_type TEXT NOT NULL DEFAULT 'episodic',  -- 'episodic' | 'semantic' | 'procedural'
    content TEXT NOT NULL,
    embedding vector(1536),
    metadata JSONB DEFAULT '{}',
    importance REAL NOT NULL DEFAULT 0.5,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ
);

CREATE INDEX idx_memories_agent ON agent_memories(agent_id, created_at DESC);
CREATE INDEX idx_memories_metadata ON agent_memories USING GIN (metadata);

The vector(1536) column matches OpenAI's text-embedding-3-small (and several open models). If you use a different embedding model, change the dimension to match — the rest of the tutorial is dimension-agnostic.

5. Generate Embeddings

An embedding is a dense vector that places semantically similar text close together. You can generate them with a hosted API or a local model; the key is that you must use the same model for indexing and querying — mixing models produces meaningless distances.

# embed.py — local embeddings with sentence-transformers (no API key needed)
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("BAAI/bge-small-en-v1.5")  # 384 dims; swap column to vector(384)

def embed(text: str) -> list[float]:
    return model.encode(text, normalize_embeddings=True).tolist()

print(embed("the deployment failed on a missing env var"))
# [0.012, -0.033, 0.041, ...]  384 floats

If you prefer a hosted model, the call is a one-liner — here using OpenAI's 1536-dimension text-embedding-3-small to match the schema above:

$ curl -s https://api.openai.com/v1/embeddings \
  -H "Authorization: Bearer $OPENAI_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"model":"text-embedding-3-small","input":"the deployment failed on a missing env var"}' \
  | jq -r '.data[0].embedding | length'
1536

6. Insert Memories

Write the content and its embedding in one statement. Use ::vector to cast a JSON array of floats:

$ sudo -u postgres psql -d agent_rule <<'SQL'
INSERT INTO agent_memories (agent_id, namespace, memory_type, content, embedding, metadata)
VALUES (
  'hermes',
  'project-sun-port',
  'episodic',
  'Deploy failed because the sun-port container could not mount the TLS cert volume; fixed by bind-mounting /etc/letsencrypt instead of a named volume.',
  '[0.012,-0.033,0.041,0.019,-0.008]'::vector,   -- truncated for display
  '{"service":"sun-port","severity":"high"}'
);
SQL
INSERT 0 1

7. Similarity Search (Cosine)

Retrieval is a single ORDER BY ... <=>. The cosine distance <=> returns a value in [0, 2] where 0 is identical; subtract from 1 to get a similarity score in [-1, 1]:

$ sudo -u postgres psql -d agent_rule <<'SQL'
SELECT content,
       ROUND((1 - (embedding <=> '[0.012,-0.033,0.041,0.019,-0.008]'::vector))::numeric, 3) AS similarity
FROM agent_memories
WHERE agent_id = 'hermes'
ORDER BY embedding <=> '[0.012,-0.033,0.041,0.019,-0.008]'::vector
LIMIT 5;
SQL
                        content                        | similarity
-------------------------------------------------------+------------
 Deploy failed because the sun-port container could... |      0.972
 ...                                                    |      0.811
 ...                                                    |      0.764

That's the whole retrieval loop: embed the query, run the nearest-neighbor query, inject the top results into the agent's context.

8. Add an HNSW Index for Scale

A linear scan over <=> is exact but slow once you pass a few thousand rows. HNSW gives you sub-millisecond approximate search at the cost of a small recall trade-off:

CREATE INDEX ON agent_memories USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Confirm the planner actually uses it:

$ sudo -u postgres psql -d agent_rule -c "EXPLAIN SELECT id FROM agent_memories ORDER BY embedding <=> '[0,0]'::vector LIMIT 5;"
                               QUERY PLAN
------------------------------------------------------------------------
 Limit  (cost=...)
   ->  Index Scan using agent_memories_embedding_idx on agent_memories
         Order By: (embedding <=> '[0,0]'::vector)

9. Scope Retrieval with Metadata

Unfiltered vector search over a shared table mixes agents and projects. Scope every query with agent_id, namespace, and memory_type — the metadata filter runs first, then the vector ranking:

SELECT content, 1 - (embedding <=> $1) AS similarity
FROM agent_memories
WHERE agent_id = 'im-bot:room-7'
  AND namespace = 'project-sun-port'
  AND memory_type = 'semantic'
  AND (expires_at IS NULL OR expires_at > now())
ORDER BY embedding <=> $1
LIMIT 10;

10. Retention and Cleanup

Memory that never expires is a liability — stale facts mislead the agent and bloat the index. Three simple policies keep it healthy:

-- 1. TTL: drop expired memories on a schedule (cron)
DELETE FROM agent_memories WHERE expires_at < now();

-- 2. Deduplicate near-identical memories (same agent + very high similarity)
DELETE FROM agent_memories a
USING agent_memories b
WHERE a.id > b.id
  AND a.agent_id = b.agent_id
  AND 1 - (a.embedding <=> b.embedding) > 0.98;

-- 3. Importance decay: keep the signal, shed the noise
UPDATE agent_memories
SET importance = importance * 0.99
WHERE created_at < now() - INTERVAL '7 days';

Schedule these as cron jobs — see our cron automation tutorial for wiring SQL maintenance into a Hermes cron schedule.

11. Wire It Into the Agent Loop

Memory is only useful if the agent actually consults it. The loop is: embed query → retrieve → inject → generate → store.

# pseudocode for the agent loop (Hermes / im-bot)
def agent_turn(user_message, agent_id):
    q = embed(user_message)                          # 1. embed the query
    memories = recall(agent_id, q, k=8)              # 2. cosine top-k
    context = render(memories)                       # 3. format as context
    reply = agent.run(user_message, context=context) # 4. generate with memory
    store(agent_id, content=f"user: {user_message}", # 5. write it back
          embedding=q, memory_type="episodic")
    store(agent_id, content=reply, embedding=embed(reply),
          memory_type="episodic")
    return reply

In a Hermes Agent, this is where you'd read the top-k memories and prepend them to the system prompt before the model runs. In an im-bot multi-agent room, the same table is shared across agents, each scoped by its agent_id — so agents in a room build a shared episodic memory while keeping their own semantic store separate.

12. Deploy with Docker Compose

The official pgvector/pgvector image bundles the extension, so you don't install anything inside the container. Run it alongside your agent:

services:
  pg-vector:
    image: pgvector/pgvector:pg16
    container_name: agent-memory-db
    restart: unless-stopped
    environment:
      POSTGRES_DB: agent_rule
      POSTGRES_USER: agent_rule
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro
    ports:
      - "127.0.0.1:5432:5432"
    secrets:
      - pg_password

secrets:
  pg_password:
    file: ./secrets/pg_password.txt

volumes:
  pgdata:

$ docker compose up -d
$ docker compose exec pg-vector psql -U agent_rule -d agent_rule \
  -c "SELECT extversion FROM pg_extension WHERE extname='vector';"
 extversion
------------
 0.7.4

13. Version the Schema in Git

Memory schemas drift. Treat them like any other code change: version the migration, review the diff, roll back if it breaks recall.

$ git init agent-memory && cd agent-memory
$ git add schema.sql docker-compose.yml
$ git commit -m "Add agent_memories table with pgvector HNSW index"

$ git log --oneline
b1c2d3e Add agent_memories table with pgvector HNSW index

For existing databases, use ordered migration files (001_init.sql, 002_add_hnsw.sql, …) and apply them with a tool like psql or a migration runner — the same discipline we cover in our Git workflow tutorial.

14. Verify and Measure

Before you trust it in production, prove three things:

# 1. The index is live and in use
$ sudo -u postgres psql -d agent_rule -c \
  "SELECT indexname FROM pg_indexes WHERE tablename='agent_memories';"

# 2. Recall works: a query about the sun-port deploy returns the deploy memory first
$ sudo -u postgres psql -d agent_rule -c \
  "SELECT content FROM agent_memories ORDER BY embedding <=> '[...]'::vector LIMIT 1;"

# 3. Latency is acceptable at your target scale
$ sudo -u postgres psql -d agent_rule -c \
  "\timing on" -c "SELECT 1 - (embedding <=> '[...]'::vector) FROM agent_memories LIMIT 1;"
Time: 0.431 ms

15. Pitfalls

• Mixing embedding models — index with one model, query with another, and your similarity scores are noise. Pin the model name in metadata or a config table.
• Dimension mismatch — inserting a 384-dim vector into a vector(1536) column errors at write time. Keep the column dimension in sync with your model.
• Indexing before bulk load — HNSW/IVFFlat built on an empty table perform poorly as you insert. Bulk-load, then index.
• Unbounded growth — no TTL means the index grows forever and stale memories pollute recall. Schedule the cleanup in Section 10.
• Secrets in memory — embeddings don't fully hide plaintext. Filter credential-shaped content before writing.
• Forgetting the metadata filter — vector search across agents returns another agent's memories. Always scope by agent_id.

16. Key Takeaways

• Memory is retrieval, not storage — the value is in surfacing the right memory at the right moment; a table full of embeddings nobody queries is just bloat.
• pgvector keeps it boring — one extension turns the Postgres you already run into a vector store. No new service, no new vendor, no new attack surface.
• Cosine + HNSW is the default — normalize embeddings, index with vector_cosine_ops, and confirm the planner uses the index with EXPLAIN.
• Scope everything by agent — agent_id, namespace, and memory_type filters keep a shared table from leaking context between agents.
• Memory has a lifecycle — TTL, dedup, and decay are first-class, not afterthoughts. Schedule them like any other job.
• Ship it like code — Docker runs it, Git versions the schema, and EXPLAIN + latency checks prove it works before you trust it.

Long-term memory is what separates an agent that answers from an agent that remembers. With pgvector you get it for the price of one extension — the rest is discipline: scope your queries, index at scale, and clean up what goes stale. Do that, and your agent stops re-learning the same lesson every morning.