IdempotencyGENERALDATABASE-SPECIFICSCALE-SPECIFIC

Idempotency Storage

Where keys live, how long they last, what scopes them, what is stored against them — and the atomic insert that makes concurrent use safe.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

What do I actually store for an idempotency key, for how long, and scoped to what?

The requirement

Our payments API promises safe retries for 24 hours. We need a store that honours that, does not grow forever, and does not let one customer read another's confirmation.

The obvious build

A table of key -> response, written when the request completes. Add a row per request, look it up on the way in, and clean it up eventually.

Why it breaks

Keyed on key alone, so two tenants who both generate the string retry-1 collide — and the second receives the first's stored payment confirmation (Multi-Tenancy).

How it breaks in production

Keyed on key alone, so two tenants who both generate the string retry-1 collide — and the second receives the first's stored payment confirmation (Multi-Tenancy).
Written when the request completes, so it cannot protect against a concurrent retry: there is nothing in the table while the first request is running.
"Cleaned up eventually" means never. The table becomes the largest in the database, and its index stops fitting in memory, which changes the cost of every insert on the hot path.
Storing the response body means storing full payment confirmations, so the key table quietly becomes a second copy of your most sensitive data with no access controls of its own.
A TTL shorter than the client's retry horizon reopens the duplicate window silently — the mechanism appears to work and stops working for exactly the late retries it was built for.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

The record is not key -> response. It is (principal, endpoint, key) -> {state, fingerprint, response, created_at}, and every part of that earns its place.
Scope is what makes lookups safe. The principal prevents cross-tenant collision and disclosure. The endpoint prevents one key legitimately used for two different operations from colliding — though whether to include it is a real design choice, discussed below.
State is what makes concurrency expressible. A row that exists but is in_progress is the signal that another attempt is running (The Idempotency Key Flow).
Fingerprint — a hash of the request body — is what detects a client reusing one key for two intents. Hash it rather than storing it, because the body is as sensitive as the response.
Response is what makes the retry return the same answer. Store the status code and the body; a replay that returns a different shape from the original forces clients to handle two cases for one outcome.
Expiry bounds the store. The window is a promise to clients, so it must be at least as long as the retry behaviour you have documented and they have implemented against.
The atomic insert is what makes the whole thing safe under concurrency. A store without a uniqueness guarantee on the scoped key cannot implement idempotency at all, whatever else it offers.

The record, field by field

DATABASE-SPECIFICPartial indexes (WHERE state = ...) are a Postgres feature; MySQL has no direct equivalent, and the usual substitute is a separate small table of in-flight claims or a composite index on (state, created_at) that is larger but works. ON CONFLICT ... RETURNING likewise has no exact MySQL analogue — the portable pattern there is to attempt the insert and catch the duplicate-key error.

The schema is small and every column is answering a specific failure. It is worth reading as a list of things that go wrong when the column is missing rather than as a table definition.

The unique constraint is the load-bearing part. It is not an integrity nicety layered on top of application logic — it *is* the concurrency control, and the application logic is a thin wrapper that reads the outcome of the insert.

A key store that survives concurrency and time

1CREATE TABLE idempotency_keys (
2  id             bigserial PRIMARY KEY,
3  principal_id   uuid        NOT NULL,   -- scope: no cross-tenant collision
4  endpoint       text        NOT NULL,   -- scope: see the decision below
5  key            text        NOT NULL,   -- client-supplied, length-bounded
6  fingerprint    bytea       NOT NULL,   -- sha256(request body), not the body
7  state          text        NOT NULL,   -- in_progress | completed | failed
8  response_status smallint,              -- replay must be byte-identical
9  response_body  jsonb,                  -- size-capped at the application
10  correlation_id text,                   -- links a replay to the original
11  created_at     timestamptz NOT NULL DEFAULT now(),
12  expires_at     timestamptz NOT NULL,
13 
14  CONSTRAINT idem_unique UNIQUE (principal_id, endpoint, key)
15);
16 
17-- Expiry must be an index scan, not a full scan, and deletion must be
18-- chunked so it never holds locks across the whole table.
19CREATE INDEX idem_expiry ON idempotency_keys (expires_at);
20 
21-- Finding crashed attempts: state + age. Partial index keeps it tiny,
22-- because in_progress rows are a vanishing fraction of the table.
23CREATE INDEX idem_stale ON idempotency_keys (created_at)
24  WHERE state = 'in_progress';
25 
26-- The claim. One statement: no window between deciding and acting.
27INSERT INTO idempotency_keys
28  (principal_id, endpoint, key, fingerprint, state, expires_at)
29VALUES ($1, $2, $3, $4, 'in_progress', now() + interval '24 hours')
30ON CONFLICT (principal_id, endpoint, key) DO NOTHING
31RETURNING id;
32-- NULL returned -> someone else holds the claim; read the row and dispatch.

The partial index on in_progress is the detail people omit. Without it the stale-claim sweeper scans the whole table on every run, and that table is the largest one you have.

Scope: per what, exactly?

Scoping by principal is not a choice; without it the store is a cross-tenant information leak. Scoping by endpoint is a genuine choice, and it changes what a key means.

Include the endpoint and a key becomes "this attempt at this operation". A client that reuses one key across POST /payments and POST /refunds is fine, and each is deduplicated independently. Exclude it and a key becomes "this attempt at this intent, whatever calls it takes" — which lets one key cover a multi-step flow and means a client reusing a key across two different operations gets the first one's response for the second.

Neither is wrong. What is wrong is not deciding, because clients will assume the opposite of whatever you built and the failure is silent.

What should the key be scoped to?

What does one key identify in your API?

Principal only

when A key represents one user intent that may span more than one call.

cost A client reusing a key on a different endpoint silently receives the wrong stored response. Requires a fingerprint check to catch it.

Principal + endpoint

when The common case: each key covers one operation.

cost A client that intended one key for a multi-step flow finds each step deduplicated separately, which is usually what they wanted anyway.

Principal + endpoint + resource

when Very high key volume where narrowing the index measurably helps.

cost The resource must be extractable before the handler runs, which couples the key store to routing.

Global (key only)

when Single-tenant internal service with one trusted caller.

cost A cross-tenant disclosure the day it becomes multi-tenant, which is not visible in any test.

Expiry is a promise, not a cleanup job

The retention window is the length of time you guarantee a retry is safe. Clients build retry queues against that number: a mobile app that stores failed payments and retries them when connectivity returns is depending on it being longer than the user's commute.

Which means the number belongs in your public documentation and is chosen from client behaviour, then implemented — not chosen from disk pressure and discovered by clients. Shortening it is a breaking change that produces no errors, only duplicate charges.

The implementation has its own trap. DELETE FROM idempotency_keys WHERE expires_at < now() on a table with tens of millions of rows is a long-running statement holding locks and generating enormous write-ahead traffic. Chunked deletes bounded by a limit, or time-based partitioning where expiry is a partition drop, keep cleanup from becoming its own incident (Deadlocks in Application Code).

Storage failures and what they actually cause

Trigger	Symptom	Cause	Response
Cache eviction under memory pressure	Duplicate charges during peak traffic only	Key store is an LRU cache; keys evicted exactly when load is highest	Use a store with no eviction, or a database table. Alert on any eviction (When Not to Cache).
TTL shorter than client retry horizon	Duplicates from mobile clients after connectivity gaps	Window chosen from storage cost, not from the contract	Set retention from the documented promise; publish the number.
Unbounded table growth	Claim insert p99 degrades across all protected endpoints	Unique index no longer fits in memory	Expiry with an index; partition by day at high volume.
Mass delete of expired rows	Lock contention and replication lag during cleanup	One unbounded `DELETE` over millions of rows	Chunked deletes, or partition drops (Deadlocks in Application Code).
Claim in Redis, effect in Postgres	Key says completed, payment does not exist	Two stores, no shared transaction	Co-locate the claim with the effect, or reconcile explicitly (The Dual Write Problem).
Key not scoped to principal	Tenant B receives tenant A's payment confirmation	Uniqueness on `key` alone	Unique on `(principal_id, ...)`; treat a foreign key as absent.
Stored response body unbounded	Key table dominated by one endpoint's payloads	No size cap on what is stored	Cap the size; store a reference above the cap.

How to build it

Most important first.

Enforce uniqueness on the full scope — UNIQUE (principal_id, idempotency_key) — and claim with a single INSERT ... ON CONFLICT DO NOTHING. Never SELECT first (Duplicate Detection).
Scope by principal always. Scope by endpoint if your clients might reasonably reuse a key across operations; do not if you want one key to cover a multi-step intent. Document whichever you chose, because clients will assume the other.
Store the status code, the response body and the response content type. That is what a replay needs to be indistinguishable from the original.
Set expiry from your documented retry window plus a margin, and publish the number. expires_at as a column with an index, or a native TTL if the store has one.
Prefer the primary database when the effect is also in that database, so the claim and the effect share a transaction. Prefer Redis when they do not, and accept that the claim and the effect can then diverge (Local vs Distributed Cache).
Cap the stored response size. A large export response does not belong in the key table; store a reference and re-derive.
Delete by partition or by an indexed range scan, not by an unbounded DELETE ... WHERE expires_at < now() that locks its way through a huge table (Schema Migrations from the Application Side).

What can go wrong

Failure modes

Keys stored in an in-process cache, which works in every test and fails the moment a retry reaches a second instance (Stateless Services).
Redis used as the key store with maxmemory-policy allkeys-lru, so keys are evicted under memory pressure — exactly when traffic is high and retries are frequent.
Expiry implemented but not indexed, so the cleanup job full-scans the largest table in the database on every run.
The claim in Redis and the effect in Postgres, with a crash in between: the key says done, the payment does not exist, and every retry replays a success that never happened (The Dual Write Problem).
Response bodies stored without a size cap, so one endpoint returning a large payload turns the key table into the storage hot spot.
A retention job that deletes rows still inside the promised window because someone tuned it for disk usage rather than for the contract.
Scoping by endpoint when clients use one key across a multi-call flow, so their retry of step two is treated as a new intent.

What can race

Two concurrent requests with the same key — closed only by the atomic insert against the unique constraint, which is why the constraint is the design and not a safeguard (Duplicate Detection).
Expiry racing a late retry: the sweeper deletes the record microseconds before the retry arrives, and the retry executes as a first attempt.
A cache eviction racing a retry, with the same outcome and no trace of why.
The claim committing in Redis while the business transaction rolls back in Postgres, leaving a key that promises an effect that does not exist (The Dual Write Problem).
Two requests with the same key on different endpoints when the scope includes the endpoint — legitimate, and easily mistaken for a bug in the client.

Security

Without principal scoping, an attacker who guesses or observes a key retrieves the stored response — someone else's payment confirmation, complete with amounts and identifiers (Object-Level Authorization).
Stored responses inherit the sensitivity of the endpoint. The key table needs the same access controls, encryption and retention rules as the payments table, and usually does not get them.
Attacker-supplied keys are attacker-controlled storage. Cap key length, cap keys per principal per window, and expire aggressively (Resource Limits).
Store a fingerprint hash rather than the request body, so the table does not accumulate card metadata it does not need.
Re-authorize on replay: the stored response was authorized when it was computed, and the caller's permissions may have changed since.

Misreads

"Redis has TTL, so it is the obvious choice." It is a good choice when the claim and the effect are already separate. When they could share a transaction, moving the claim out of the database creates the inconsistency you were trying to avoid (When Not to Cache).
"Store the response so we can replay it" — and then storing it outside the transaction that produced it, which reopens the crash window.
"Expiry is a cleanup detail." It is the boundary of your safety promise, and it belongs in the API documentation.
"Scope by key alone, keys are UUIDs." Clients send whatever they want, including 1, test, and the same string every time in their integration environment.
"The key table is metadata." It contains request fingerprints and response bodies for your most sensitive endpoints. It is data.

Operating it

How you see it in production

Row count and total bytes of the key store as a first-class metric. It should be roughly flat at request_rate x retention; a rising trend means expiry is not working.
Age of the oldest non-expired row, which catches a stalled cleanup job before it becomes a storage incident.
Count of claims, replays, conflicts and fingerprint mismatches — the same counter as the flow lesson, read here as storage load.
Eviction count if the store is a cache. Any non-zero eviction rate on an idempotency store means the mechanism has silently stopped working for some requests.
p99 latency of the claim insert. It is on the hot path of every protected write, and it is the first thing to degrade when the index outgrows memory.

What changes at 10x and 100x

Steady-state size is request rate x retention window, and nothing else. A 24-hour window at 1000 protected writes per second is on the order of 86 million rows — a number worth computing before choosing a store.
At 10x, the (principal, key) index dominates the table's cost, and inserts are the workload — this is an append-heavy table with a uniqueness check, which is the pattern most sensitive to index size (Should I Add an Index?).
At 100x, partition by creation day so expiry is a partition drop rather than a mass delete, and consider a dedicated store with native TTL.
Response storage scales with payload size, not request count, so one verbose endpoint can dominate the table even at low volume.

What this costs

The primary database gives you a shared transaction with the business write and puts a high-write, high-churn table in the middle of your most important database.
Redis gives you native TTL, fast atomic claims via SET NX, and no transactional relationship to the effect — so a crash between the two leaves them inconsistent, and you must decide which way that fails.
Longer retention keeps late retries safe and grows storage linearly. There is no setting that is both cheap and generous; pick from the client contract, not from disk pressure.
Storing full responses makes replay exact and duplicates sensitive data. Storing a reference keeps the table small and makes replay depend on the referenced data still existing.
Scoping by endpoint prevents key collisions across operations and prevents one key covering a multi-request intent. Both are defensible; only one is what your clients expect.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALScope, state, fingerprint, response and expiry are needed regardless of the store; only their implementation differs.
DATABASE-SPECIFICPostgres: UNIQUE (principal_id, key) plus INSERT ... ON CONFLICT DO NOTHING RETURNING claims and reports in one round trip, and the row can share the business transaction. MySQL: INSERT IGNORE also suppresses unrelated errors such as data truncation, so prefer an explicit duplicate-key catch, and note that ON DUPLICATE KEY UPDATE reports 0 affected rows when nothing changed and 2 when a row is updated. Redis: SET k v NX PX ttl is atomic and gives free expiry, but has no relationship to your database transaction.
SCALE-SPECIFICA plain table with an index and a nightly delete is entirely adequate up to the point where the unique index no longer fits in memory. Partitioning, and possibly a dedicated store, become worth their operational cost past that — and where that point sits depends on your row size and available memory, not on a fixed request rate.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designidempotency-keys idempotency

Databaseshould-i-add-an-index transactions-and-acid isolation-levels

Securitysecrets-management

Performancehot-keys cache-performance-signals

Cloudmanaged-databases

Concurrencycompare-and-swap shared-mutable-state local-lock-not-distributed

Domains that do not exist yet

— Distributed Systems — why a deduplication window is always finite, and what "safe to retry for 24 hours" actually promises.