Idempotency Keys for Safe Cross-Shard Retries

Every reliable cross-shard mechanism retries, and every retry is a duplicate waiting to happen. Idempotency keys are what turn at-least-once delivery into exactly-once effect. This guide implements them properly: caller-generated, shard-enforced, response-storing, and expiring on a schedule. It supplies the prerequisite property assumed throughout Distributed Transactions & Cross-Shard Consistency, part of Cross-Partition Querying & Aggregation Strategies.

Prerequisites

Step 1 — Generate the key once, at the origin

The key identifies a business operation, not an attempt:

# client side — the key is created with the intent, not with the request
def request_transfer(src, dst, amount):
    op = Operation.objects.create(          # durable before any network call
        kind="transfer", src=src, dst=dst, amount=amount,
        idempotency_key=str(uuid.uuid4()),
    )
    return send_with_retries(op)            # every retry reuses op.idempotency_key

The key must survive a client restart. A UUID generated inside the retry loop is a new key on every attempt and provides no protection whatsoever — the most common way this pattern is implemented incorrectly.

Operational note: Deriving the key deterministically from the operation’s content (a hash of source, destination, amount and a client-supplied reference) is an alternative, but it collides for genuinely repeated operations — two identical transfers a minute apart are not the same operation. Prefer an explicit id.

DBA tip: Put the key in the request as a header or a first-class field rather than inside a JSON payload. Middleware, proxies and logs can all see it, which makes tracing a duplicate far easier.

Step 2 — Enforce it with a unique constraint on the shard

The check and the effect must be in the same transaction, which means the same database:

CREATE TABLE idempotency_record (
    key           text        NOT NULL,
    scope         text        NOT NULL,          -- e.g. 'transfer'
    request_hash  text        NOT NULL,          -- guards against key reuse with different args
    response      jsonb,
    state         text        NOT NULL CHECK (state IN ('in_progress','completed')),
    created_at    timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (scope, key)
) PARTITION BY RANGE (created_at);

CREATE TABLE idempotency_record_2026_08 PARTITION OF idempotency_record
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
BEGIN;
  INSERT INTO idempotency_record (key, scope, request_hash, state)
  VALUES ($1, 'transfer', $2, 'in_progress')
  ON CONFLICT (scope, key) DO NOTHING
  RETURNING key;
  -- zero rows returned → this key has been seen; fall through to the replay path

  INSERT INTO ledger_entries (id, account_id, amount, kind)
  VALUES ($3, $4, $5, 'transfer_out');

  UPDATE idempotency_record
     SET state = 'completed', response = $6
   WHERE scope = 'transfer' AND key = $1;
COMMIT;
Three outcomes of an idempotency check, and what each returns A request arrives with a key. If the key is new, the effect is applied and the response stored in the same transaction. If the key exists and is completed, the stored response is replayed without touching the data. If the key exists and is still in progress, the caller receives a conflict telling it to retry shortly, because the first attempt has not yet committed. requestcarries the key key seenbefore? no → apply the effect insert key + write + store response, one transaction returns 201 with the new resource yes, completed → replay return the stored response verbatim no rows are written; the effect happened once yes, in progress → 409 with Retry-After the first attempt has not committed yet the caller retries in a moment and hits the replay path The third branch is the one naive implementations omit, and it is where concurrent retries produce duplicates: two attempts arrive together, both find no key, and both proceed. The unique constraint is what makes one of them lose.

Operational note: ON CONFLICT DO NOTHING returning zero rows is the signal to take the replay path. Do not implement it as a SELECT followed by an INSERT — that is the race the constraint exists to close.

DBA tip: Partition idempotency_record by created_at from day one. It grows with request volume and is pure overhead after expiry, which makes it the ideal candidate for detach-and-drop retention.

Step 3 — Store and replay the response

A retry must get the same answer, not merely avoid a duplicate:

Two retries arriving together: one wins the insert, one replays Two attempts of the same operation arrive within a millisecond of each other. Both find no existing key. The unique constraint lets exactly one insert succeed; the other receives a conflict, waits for the winner to commit and then replays its stored response. Without the constraint both would proceed and the effect would apply twice. attempt A attempt B INSERT key effect applied COMMIT · response stored INSERT key conflict — key exists replays the stored response Attempt B must wait for A to commit before it can read the stored response — which is why the in-progress state returns a retryable 409 rather than blocking: a short client-side retry is cheaper than a held connection. The database, not the application, decides the winner. That is the whole reason the check belongs in a unique constraint. 
def handle(request):
    key   = request.headers["Idempotency-Key"]
    rhash = hash_request(request.body)

    row = claim_key(key, scope="transfer", request_hash=rhash)
    if row.claimed:
        response = perform_transfer(request.body, key)     # same transaction as the key row
        return response
    if row.state == "in_progress":
        return Response(409, {"error": "in_progress"}, headers={"Retry-After": "1"})
    if row.request_hash != rhash:
        return Response(422, {"error": "idempotency_key_reused_with_different_body"})
    return Response(row.response["status"], row.response["body"])   # replay

The request_hash check matters: a client that reuses a key with a different body is a bug, and silently replaying the first response hides it. Returning an explicit error surfaces it during development rather than during reconciliation.

Operational note: Store the full response including its status code. A replayed 201 Created that returns 200 OK breaks clients that branch on the status.

SRE tip: Log the replay rate as a metric. A sudden rise means a client is retrying aggressively — usually because a timeout somewhere is shorter than the operation’s real latency.

Step 4 — Expire keys with the retention job

-- keys expire with their partition; nothing is deleted row by row
ALTER TABLE idempotency_record DETACH PARTITION idempotency_record_2026_06;
DROP TABLE idempotency_record_2026_06;
Key retention must outlast the slowest retry in the system A synchronous client retries for seconds, a background job for hours, and a dead-letter queue can be replayed manually days later. The key retention period must exceed the longest of these, plus a margin, or a late replay finds no key and applies the effect a second time. synchronous client retry background job redelivery dead-letter replay by hand idempotency key retention ~30 s up to 6 h up to 7 days 30 days — comfortably longer than every retry path above Set retention from the slowest replay path you actually operate, then double it. Expiring keys is cheap; a duplicate payment is not.

Operational note: Alert if the retention job falls behind. An idempotency table that outgrows expectations is usually a symptom of a retry storm rather than of growth.

SRE tip: When a dead-letter queue is replayed manually months later, check the key retention first. If keys have expired, the replay is not idempotent any more and needs a different reconciliation.

Verification

Prove the property under concurrency, not just sequentially:

# fire the same key twenty times in parallel; exactly one effect must result
for i in $(seq 1 20); do
  curl -sS -X POST https://api.internal/transfers \
    -H 'Idempotency-Key: 9f1c2d64-0a1e-4f7c-9d2b-3d8e5c1a77f0' \
    -H 'Content-Type: application/json' \
    -d '{"src":8842,"dst":1190,"amount":2500}' &
done; wait
SELECT count(*) FROM ledger_entries
WHERE  kind = 'transfer_out' AND account_id = 8842
  AND  created_at > now() - interval '5 minutes';
 count
-------
     1

Exactly one ledger entry, one completed idempotency record, and the other nineteen responses either replaying the stored body or returning 409 while the first was in flight.

Failure mode table

Failure mode Root cause SRE mitigation
Duplicates under concurrent retries the check was a SELECT followed by an INSERT rather than a unique constraint enforce with ON CONFLICT on a primary key covering scope and key, inside the same transaction as the effect
A late replay applies the effect twice the key expired before the slowest retry path could fire set retention from the dead-letter replay window, not from the synchronous timeout; alert when the retention job runs early
Client reuses a key for a different request key generated per session or per user rather than per operation compare request_hash and return 422 rather than replaying; surface the error in client tests

FAQ

Who should generate the idempotency key?

The caller, once, before the first attempt — and the same value must be reused for every retry of that logical operation. A key generated by the server, or regenerated per attempt, provides no protection at all: the whole point is that two attempts of the same operation carry the same key, which only the originator can guarantee.

Does the key belong on the shard or in a central store?

On the shard that performs the write, enforced by a unique constraint in the same transaction as the effect. A central store is a second system that can disagree with the shard, and the window between checking it and writing is exactly where duplicates appear. Enforcing locally makes the guarantee atomic with the change it protects.

How long should idempotency records be kept?

Longer than the longest retry window any client could use, which in practice means at least as long as the queue’s maximum redelivery age plus a margin — commonly 7 to 30 days. Expiring too early re-opens the duplication window for a late retry; keeping them forever turns the table into one of the largest in the schema, so it should itself be partitioned by time and dropped by the retention job.

Back to Distributed Transactions Cross Shard Consistency