Why agent memory keys must be content-addressed: a Postgres idempotency story

You’re building an agent that remembers. Every conversation, every tool call, every observation gets stored in a Postgres table called agent_memories. The schema looks sane: an auto-increment id, a session_id, a role (user/assistant/tool), content text, and a timestamp. You insert rows, query them, feed them back as context. It works—until it doesn’t.

The duplicate memory problem

Agents retry. Networks glitch. The user hits “send” twice. Your orchestration layer, in its infinite wisdom, calls the same memory insertion twice. With a serial primary key, Postgres happily creates two rows with identical session_id, role, and content but different ids. Now your agent sees the same memory twice. Context grows stale. Token budgets bloat. The agent starts repeating itself.

You could add a unique constraint on (session_id, content). That works for exact duplicates, but what about semantically similar memories? “The user’s name is Alice” and “Alice is the user’s name” are different strings. A unique constraint won’t catch them. And if you ever need to update a memory (e.g., correct a fact), you now have to search by content, which is slow and ambiguous.

Enter content-addressed keys

A content-addressed key is a hash of the memory’s payload. Instead of a random or sequential ID, you compute sha256(normalized_payload) and use that as the primary key. The payload includes everything that defines the memory: session_id, role, content, and possibly a scope or type. Normalization ensures that byte-for-byte identical payloads produce the same hash.

Here’s a minimal Postgres implementation:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE agent_memories (
    memory_id BYTEA PRIMARY KEY,  -- SHA-256 hash
    session_id UUID NOT NULL,
    role TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Insert with idempotency: ON CONFLICT DO NOTHING
INSERT INTO agent_memories (memory_id, session_id, role, content)
VALUES (
    sha256( row('session-uuid', 'user', 'Alice likes dogs')::TEXT ),
    'session-uuid',
    'user',
    'Alice likes dogs'
)
ON CONFLICT (memory_id) DO NOTHING;

The sha256( row(...)::TEXT ) call serializes the tuple into a deterministic string. Any retry of the exact same insert hits ON CONFLICT DO NOTHING and silently succeeds without duplicating the row.

Why this matters for agents

Agents are inherently non-deterministic. LLM outputs vary, tool calls may be retried, and network failures cause duplicate requests. A content-addressed key turns the database into a deduplication layer. The agent can emit the same memory a hundred times; the database only stores one copy.

This pattern also enables upserts for mutable memories. If a memory’s content evolves (e.g., “Alice likes dogs” → “Alice loves golden retrievers”), you can update by the same key:

INSERT INTO agent_memories (memory_id, session_id, role, content, updated_at)
VALUES (
    sha256( row('session-uuid', 'user', 'Alice loves golden retrievers')::TEXT ),
    'session-uuid',
    'user',
    'Alice loves golden retrievers',
    now()
)
ON CONFLICT (memory_id) DO UPDATE SET
    content = EXCLUDED.content,
    updated_at = EXCLUDED.updated_at;

But careful: this changes the key semantics. Now the key is tied to the content, so updating content changes the key. If you want to track mutable facts, use a separate immutable fact ID (like a UUID) and store revisions with content-addressed versions. That’s a more advanced pattern for fact-checking and provenance.

Normalization: the devil in the details

Content-addressed keys are only as good as the normalization. Two semantically identical payloads must produce the same hash. That means:

• Whitespace: trim and canonicalize whitespace (e.g., collapse multiple spaces).
• Encoding: always use UTF-8.
• Field order: define a canonical order for the tuple. JSON keys must be sorted.
• Null handling: decide how to represent missing fields (e.g., empty string vs. NULL).

For JSON payloads, use jsonb and let Postgres handle normalization:

INSERT INTO agent_memories (memory_id, session_id, role, content)
VALUES (
    sha256( jsonb_build_object(
        'session_id', 'session-uuid',
        'role', 'user',
        'content', 'Alice likes dogs',
        'scope', 'chat'
    )::TEXT::BYTEA ),
    ...
);

jsonb normalizes key ordering and whitespace. But beware: jsonb::TEXT uses a canonical representation that may change between Postgres versions. For rock-solid determinism, pre-hash in application code with a stable serialization library.

Performance considerations

Using a 32-byte BYTEA primary key has trade-offs:

• Index size: SHA-256 hashes are 32 bytes vs. 4-8 bytes for a serial. Indexes are larger, but still manageable for millions of rows.
• Insert speed: Random hash values cause index page splits. Sequential IDs are faster for inserts. Mitigate by using a smaller hash (e.g., 64-bit) or a hash index instead of B-tree. Postgres hash indexes are write-optimized and can be a good fit.
• Lookup speed: Exact-match lookups by hash are O(1) with a hash index. Range scans are rare for content-addressed keys.

Benchmark your workload. For high-throughput agent memory systems, consider partitioning by session_id and using local hash indexes.

Real-world example: replacing UUIDs with hashes

Teams typically observe that a memory service originally using UUID v4 primary keys accumulates duplicates due to retries in the agent orchestration layer. A common pattern is to add a unique constraint on (session_id, content) which catches exact duplicates but misses near-duplicates (e.g., trailing newlines). The constraint also makes updates painful.

Switching to content-addressed keys involves:

ALTER TABLE agent_memories DROP CONSTRAINT agent_memories_pkey;
ALTER TABLE agent_memories ADD COLUMN memory_id BYTEA;
UPDATE agent_memories SET memory_id = sha256( row(session_id, role, content)::TEXT );
ALTER TABLE agent_memories ADD PRIMARY KEY (memory_id);
ALTER TABLE agent_memories DROP COLUMN id;

Insert throughput typically drops due to hash computation, but duplicate insertions go to zero. The agent no longer repeats itself.

When NOT to use content-addressed keys

• Mutable entities: User profiles, session metadata—things that change often and need a stable identifier. Use UUIDs.
• High-frequency inserts: If you’re writing millions of rows per second, the hash computation and random I/O may be too costly. Consider a hybrid: content-addressed key for dedup, plus a secondary sequential ID for ordering.
• Semantic deduplication: Content-addressed keys only catch byte-identical memories. For near-duplicate detection (e.g., “Alice likes dogs” vs. “Alice loves canines”), you need embedding similarity + clustering, not hashing.

The bottom line

Agent memory is append-heavy and retry-prone. Content-addressed keys give you idempotent inserts with zero application-level deduplication logic. The cost is a slightly larger primary key and a normalization layer. For any system where the same data can arrive multiple times, it’s a net win.

Next time you reach for a serial primary key on a memory table, ask yourself: “Can this insert be called twice?” If yes, hash it.

Damir Radulić writes about self-hosted AI infrastructure at The Sovereign Stack.