AI agent long-term memory on PostgreSQL

Every call to a language model is stateless: nothing is written back, and the only "memory" available is what you put into the prompt. Once an application is expected to know who the user is, what they preferred last week, and which facts have changed since, the application has to own that state itself. That state layer is what "agent memory" means in practice — a database problem with an LLM-assisted write path, not a model feature.

Memory is not RAG

RAG and agent memory are often conflated because both end with "retrieve relevant text into the prompt". The difference is the write path:

A memory store must therefore support targeted UPDATE and DELETE, time-scoped validity, and conflict handling — not just nearest-neighbor search. A read-only vector index of conversation logs is RAG over chat history, not memory.

Three shapes of a memory layer

1. Dedicated memory frameworks — SDKs and services (Mem0, Cognee, and others) that own extraction, storage, and retrieval behind an add/search API. Fastest to prototype; the schema and retrieval policy live inside the framework.
2. A vector database alone — embeddings plus metadata filters. Simple, but user profiles, relationships between entities, and exact-match lookup all have to be rebuilt elsewhere.
3. PostgreSQL directly — one system holds embeddings (pgvector), structured profiles (jsonb), keyword search (full-text search), entity relationships (tables, or SQL/PGQ graphs in PostgreSQL 19), plus transactions and row-level security. The write policy lives in your code and SQL instead of a framework.

These are not exclusive: the frameworks in the first row typically persist into stores from the other two. The real decision is where the schema of record lives and who controls the write policy.

PostgreSQL as the memory base

A workable memory schema separates the fact from its embedding and keeps update history explicit instead of overwriting in place:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE agent_memory (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  user_id text NOT NULL,
  kind text NOT NULL CHECK (kind IN ('profile', 'preference', 'episode', 'fact')),
  content text NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}',
  embedding vector(1536),
  search_vector tsvector GENERATED ALWAYS AS
    (to_tsvector('simple', content)) STORED,
  valid_from timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz,
  superseded_by bigint REFERENCES agent_memory(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON agent_memory USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON agent_memory USING gin (search_vector);

• pgvector stores embeddings next to the facts they describe; index choice and filtered-scan behavior follow the same rules as RAG pipeline and pgvector setup.
• jsonb holds the structured part of a profile (timezone, language, plan tier) that must be filterable and updatable field by field, not re-embedded on every change.
• Full-text search covers exact names, IDs, and error strings that embeddings handle poorly; combine both candidate sets and fuse, exactly as in hybrid RAG retrieval.
• Entity relationships are ordinary tables: entity plus an edge table with PRIMARY KEY (src, dst, relation). On PostgreSQL 19 they can additionally be declared as a property graph and queried with GRAPH_TABLE — see Graph queries with SQL/PGQ. On earlier versions the same tables are queried with joins or WITH RECURSIVE.

Retrieval is one SQL statement with the tenant and permission filters pushed into the database, per the pattern in RAG pipeline. Nothing here requires a memory-specific server.

Candidate frameworks

Capability statements below were checked against the official repositories and documentation in 2026-08. Benchmark figures published by vendors are vendor-reported, not independently verified here.

Both frameworks can sit on top of PostgreSQL for part of their storage, so "framework vs PostgreSQL" is usually "framework's write policy on PostgreSQL" vs "your write policy on PostgreSQL", not two different databases.

Production concerns

Write consistency

The dangerous write is the model-extracted one. Constrain it: memories go through a fixed schema with CHECK constraints, the extracting model gets a narrow role that cannot touch business tables (Safe SQL for agents), and a correction is an INSERT plus superseded_by link rather than an in-place rewrite, so "what did we believe when the agent answered" stays auditable. If a memory is derived from a business transaction, write both in one transaction or record the source version explicitly.

Forgetting and TTL

Memory without expiry grows into noise that retrieval then amplifies. Give facts expires_at where the domain allows it, sweep expired rows on a schedule, and treat user-initiated deletion as a hard DELETE (plus embedding rows) rather than a flag. pgvector indexes do not make deleted rows disappear from backups — align PITR retention with your deletion policy.

Multi-tenant isolation with RLS

Memory is per-user data with prompt-injection blast radius: a poisoned memory written in one tenant must not be retrievable in another. Enforce isolation in the database, not the retrieval code:

ALTER TABLE agent_memory ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON agent_memory
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Set app.tenant_id per request from the authenticated identity. This keeps the guarantee intact when agents or MCP tools issue queries you did not hand-write.

Evaluating memory quality

Retrieval recall is necessary but not sufficient: a memory layer can also fail by extracting wrong facts, keeping contradictions, or surfacing stale state. Keep a versioned set of conversation traces with the memories they should produce and the answers retrieval should return, measure extraction accuracy and retrieval recall separately, and re-run on every prompt, model, or schema change. The harness for this is the same as in Agent evals; framework-published benchmarks are vendor-reported numbers and should not substitute for a suite built from your own traffic.

AI prompt: draft a memory schema

Help me design an agent long-term memory schema on PostgreSQL 19.

1. What my agent needs to remember: (e.g. user preferences, past decisions, open issues)
2. Scale: (users, memories per user, writes per day)
3. Please:
 - Separate facts (text + jsonb attributes) from embeddings (pgvector) and entity relations (edge table)
 - Use INSERT + superseded_by for corrections instead of in-place UPDATE
 - Add expires_at where forgetting should be automatic
 - Include RLS policy for tenant_id isolation
 - Write the hybrid retrieval query (tenant filter + full-text + vector candidates)
4. Flag anything that should stay in business tables instead of memory.

Related

• RAG pipeline — hybrid retrieval, indexing, and permission filtering that the read path reuses
• pgvector setup — installing and verifying the vector extension
• Context contract — what the model is allowed to see, and how to say "insufficient context"
• Safe SQL for agents — roles and statement limits for model-issued writes
• Agent evals — evaluation harness for memory quality
• Graph queries with SQL/PGQ — entity relationships on PostgreSQL 19