Give Your Agent a Memory: Postgres, pgvector, and Key Value as a Three-Tier Context Store

Stateless models, stateful agents

Large language models are stateless. Every inference call receives a context window and returns a completion, with no memory of prior interactions. An AI agent, by contrast, must behave as a stateful system. It needs to recall what a user said three turns ago, retrieve relevant knowledge from past sessions, and persist durable facts across weeks of interaction. This gap between stateless models and stateful behavior is a storage problem, and no single database solves it well.

This article is an architectural explainer with an operational bent. It walks through a three-tier memory pattern for agents, explains why each tier maps to a specific storage type, and then covers the orchestration and failure modes you hit when you run it. If you need the ReAct and tool-use foundation that this memory sits underneath, start with building an agent with LangChain and Claude or OpenAI and return here to wire in context retrieval.

A three-tier memory architecture maps distinct memory types to matching storage characteristics:

• Short-term memory maps to Render Key Value for ephemeral session context with low-latency key-based reads.
• Semantic memory maps to Render Postgres with the pgvector extension for similarity search over embeddings.
• Long-term memory maps to Postgres relational tables for structured, durable, transactionally consistent facts.

Each tier exists because it solves a retrieval problem the other two handle poorly. The sections below take them in that order, then show how they combine per turn.

Tier 1: short-term memory with Key Value

Short-term memory is your agent's working context: the last N conversation turns, in-flight task state, and session-scoped variables. Its defining characteristics are high read frequency, low durability requirements, and bounded lifetime. Your agent accesses a session's context on every turn, but that context becomes worthless once the session ends.

These characteristics match a key-value store, not a relational database. Render Key Value is compatible with virtually all Redis clients, and newly created instances run Valkey 8. It provides fast key-based lookups, TTL-based expiration, and configurable eviction policies through the maxmemory policy setting, which automatically discards stale entries under memory pressure. A relational query planner adds latency and complexity that this access pattern never uses. You always know the exact key, typically a session ID, so exact-match retrieval is sufficient.

This simplified example shows the general shape of how your agent might store recent conversation turns in a key-value store:

For production, add connection pooling, retry logic, and appropriate key namespacing. This example demonstrates the concept and needs adaptation for your specific agent architecture.

Tier 2: semantic memory with pgvector

Semantic memory answers a fundamentally different question. Short-term memory asks "what happened in this session?" Semantic memory asks "what have I stored that is relevant to the current input?" even when no exact keyword or key matches.

The mechanism is embedding-based similarity search. An embedding is a fixed-dimension numeric vector, commonly 384 to 3072 dimensions depending on the model, that encodes the semantic meaning of a piece of text. Texts with similar meanings produce vectors that sit close together in vector space. pgvector is a Postgres extension that adds a vector column type plus distance operators, turning a standard Postgres instance into a vector database. Render Postgres supports pgvector on databases running PostgreSQL 13 or later, so you enable it with CREATE EXTENSION vector;. For the reasoning behind using pgvector instead of running a separate vector database, see why managed PostgreSQL and pgvector can replace a dedicated vector store.

The retrieval semantics differ from every other tier. Instead of exact-match lookup like Key Value or predicate filtering with SQL WHERE clauses, a vector query returns the k nearest neighbors by distance. A user asking "how do I reset my credentials?" can retrieve a stored memory about "password recovery flow" despite zero shared keywords. This is what lets an agent recall rather than merely look up.

This minimal example illustrates the shape of a pgvector query for finding semantically similar context. It assumes pg is an existing Postgres client and toVectorLiteral is a helper you supply:

For production, add index tuning with ivfflat or hnsw, batching, and embedding versioning. Adapt this to your agent's embedding pipeline and schema. For a concrete build that uses Postgres and pgvector as an agent's memory in practice, see how the Hacker News AI agent with Inngest and Render is assembled.

Tier 3: long-term memory with Postgres

Long-term memory is your agent's durable record: user profiles, extracted facts, preferences, and audit trails. Its defining characteristics are the inverse of short-term memory: low write frequency, indefinite retention, and strict correctness requirements.

Relational Postgres is the source of truth for this tier because it provides properties the other tiers do not: ACID transactions, uniqueness and referential constraints, typed schemas, and point-in-time recovery on paid Render Postgres plans. A fact like "user prefers metric units" must not silently disappear under an eviction policy or get retrieved probabilistically by similarity. It must be exactly and durably queryable.

This minimal example demonstrates the shape of durable agent memory in Postgres. It assumes pg is an existing Postgres client:

For production, add constraints, an indexing strategy, and migration handling. Schema design must reflect your agent's actual fact model.

Orchestrating the three tiers

A typical per-turn retrieval sequence looks like this:

1. Fetch recent turns from Key Value.
2. Embed the user's input and query pgvector for the top-k relevant memories.
3. Load durable facts from Postgres.
4. Assemble all three into the prompt.

This sequence is orchestration logic rather than a fixed formula. Some turns skip semantic recall entirely, and write paths differ per tier. On Render, services in the same region can reach all three stores over the private network using each datastore's internal URL. Inject connection strings through environment variables and manage them independently per environment.

If your agent runs long or multi-step jobs that depend on this memory, treat orchestration as a durability problem too. Render Workflows covers how to structure long-running processes that survive restarts and retries while keeping persistent state consistent.

Production considerations

Once the pattern works, the operational concerns shift to latency, scaling, and observability per tier. Each store scales on a different axis. Key Value scales with memory and read throughput, pgvector with index build cost and query fan-out, and relational Postgres with write correctness and connection limits. Track query latency for each tier separately so a slow vector scan does not hide behind an otherwise healthy p99. For a broader treatment of deploying agents with auto-scaling and monitoring, see the guide on how to deploy an AI agent on Render with auto-scaling and monitoring.

Common mistakes and troubleshooting

• Treating all memory as equally hot. Routing every lookup through pgvector adds embedding latency to queries that Key Value answers with a fast in-memory read. Map your access patterns to tiers deliberately.
• Missing TTLs on Key Value entries. Sessions without expiration accumulate stale context and eventually trigger eviction of live data. Set explicit TTLs and choose an appropriate eviction policy.
• Unindexed vector columns. Sequential scans over embeddings degrade linearly with row count. Add an approximate index (ivfflat or hnsw) before scale forces it.
• Oversized embeddings. Higher dimensionality increases storage and distance-computation cost. Evaluate whether your recall quality justifies the dimension count.

This tiering pattern generalizes to any agent system. Classify each memory type by lifetime, access pattern, and correctness requirements, then choose storage to match.

Frequently asked questions