The strategic case for PostgreSQL as the production default for agent memory is straightforward. The implementation mistake starts right after that decision, when teams put all agent state into one oversized document and call it a schema.
If you want durable agent memory in production, separate operational truth from flexible payloads. PostgreSQL works best when sessions, messages, tool runs, and memory summaries have clear ownership in the schema instead of being buried in one giant blob.
This tutorial is the practical next step to our comparison of Redis vs PostgreSQL for agent memory and session state. The goal here is not academic database purity. It is helping teams design a schema that is easy to operate, easy to debug, and still flexible enough for fast-moving agent workflows.
Why naive memory schemas fail in production
The common first version of agent persistence is simple: one table, one primary key, one JSON column holding everything about the conversation, memory, tool state, and maybe some retrieval metadata on top. It looks convenient because the agent runtime can write one object without much modelling effort.
That convenience usually disappears once the product becomes real. Teams start needing to answer questions such as which sessions are active, which tool call failed, what changed between one memory summary and the next, and what state must be preserved for resume and retry. A single-document design makes all of those harder.
The problem is not JSON itself. The problem is refusing to distinguish durable workflow truth from flexible payload data. When those concerns are mixed, reads get heavy, auditability gets weak, and partial updates become messy.
The core tables to create first
A better production starting point is to split the model into a few clear responsibilities. Most teams do not need an elaborate schema on day one. They do need one that reflects how the system actually behaves.
agent_sessions
This table is the control plane for each conversation or workflow instance. It should hold the durable session identity, tenant linkage, lifecycle status, timestamps, and a small set of summary fields useful for operations.
session_messages
This table stores the chronological message history. Keep one row per message or state transition that must be reconstructable later. It is far easier to inspect, replay, and debug than trying to mutate one monolithic transcript object repeatedly.
tool_runs
Tool execution deserves its own table. If a tool invocation fails, retries, or returns structured output, that should not be hidden in a chat transcript. Operationally, tool runs behave more like jobs than like messages.
memory_snapshots
Long-running agents often need a durable summary or compressed memory state that evolves over time. Keep that separate from raw messages. It lets you inspect how memory changed without rebuilding everything from the transcript on every read.
session_events
This optional table becomes valuable earlier than teams expect. It records transitions such as handoff triggered, memory rebuilt, retry scheduled, escalation opened, or human review requested. That is excellent for observability and post-incident analysis.
When to use JSONB and when not to
JSONB is useful, but it should be placed deliberately. Use it for flexible payloads that are inconvenient to normalize fully and where field shape may evolve over time. Good examples include raw tool inputs, model outputs, memory summary attributes, or session annotations that are not central query dimensions.
Do not use JSONB as an excuse to avoid modelling the fields you already know you must filter, join, sort, or audit. Status, tenant, timestamps, sequence numbers, tool names, error states, and version markers should usually be first-class columns. If the field is operationally important, promote it.
How to keep writes simple and reads useful
Many teams over-optimize for write convenience because the agent runtime is generating state quickly. That is understandable, but production systems are not judged only by write throughput. They are judged by how easily the team can answer questions during incidents, customer escalations, and product changes.
A good compromise is to keep the write path append-friendly: insert a session row once, append messages as rows, append tool runs as rows, and write memory snapshots as versioned records instead of overwriting them blindly.
create table agent_sessions (
id uuid primary key,
tenant_id uuid not null,
agent_key text not null,
status text not null,
session_metadata jsonb,
started_at timestamptz not null default now(),
last_activity_at timestamptz,
ended_at timestamptz
);
create table session_messages (
id bigserial primary key,
session_id uuid not null references agent_sessions(id),
sequence_number int not null,
role text not null,
content_text text,
content_json jsonb,
created_at timestamptz not null default now(),
unique (session_id, sequence_number)
);This is not the final schema for every system. It is a strong production starting point because it balances flexible payloads with durable operational structure.
Where pgvector fits and where it does not
Teams often mix up two different problems: durable session state and semantic retrieval. pgvector can be useful if you want embeddings inside PostgreSQL for retrieval, ranking, or memory lookups. That does not mean your primary session truth should become a vector problem.
The cleaner approach is to keep vectorized memory or retrieval artifacts as a separate concern. Session truth tells you what happened. Retrieval infrastructure helps you find what may be relevant. Those are related, but they are not identical.
Common schema mistakes
- storing all state in one JSON object because it feels fast on day one,
- using JSONB for fields the operations team must filter constantly,
- overwriting memory summaries instead of versioning them,
- burying tool-run failures inside message logs,
- and mixing retrieval structures directly into core session truth.
The winning pattern is not maximum normalization. It is clear operational boundaries. PostgreSQL becomes powerful for agent memory when the schema reflects how the workflow really needs to be operated.
References
Talk with Alongside
If your team is moving from AI prototype to production system, the data model behind session state and agent memory becomes an engineering decision with product, cloud, and security consequences. Alongside helps teams design delivery-ready architectures that are easier to scale, govern, and operate under real constraints.