Idempotency Key API Design: Safe Retries on Postgres

A client times out on POST /charges, retries, and the customer is charged twice, because the server can't tell a retry from a new order. By the end of this post you'll have a Postgres-backed idempotency layer, and a concurrent test that shows two parallel requests with the same key execute once. I ran everything below against a throwaway Postgres container, and the output is pasted as I got it.

Why retries double-charge

A timeout doesn't tell the client whether the server did the work. The request may have died before reaching you, or it may have succeeded and the response was lost. A client can't distinguish the two, so a well-behaved one retries, and a non-idempotent POST runs twice.

Here's the naive handler (it inserts a charge row after a 0.3s pg_sleep standing in for the payment provider), hit by two parallel requests:

naive, 2 parallel      -> [201, 201] charges: 2

Two requests, two charges. No amount of client-side care fixes this; the server has to recognise the repeat.

The Idempotency-Key header and what it should return

The convention: the client generates a unique key per logical operation, sends it in an Idempotency-Key header, and reuses the same key on every retry of that operation.

Two documents are worth reading:

• Stripe's docs (Idempotent requests) describe the de facto behaviour. Keys are up to 255 characters, V4 UUIDs are suggested, and Stripe saves "the status code and body of the first request," including 500 errors. Keys can be pruned once they're at least 24 hours old, and incoming parameters are compared with the original, with an error if they differ. Only POST needs keys; GET and DELETE are idempotent by definition.
• The IETF draft (draft-ietf-httpapi-idempotency-key-header) is the standardisation attempt. As of when I read it (2026-10-11) it's revision 07, dated 2025-10-15, and its datatracker status is "Expired" with no intended RFC status. So treat it as guidance, not a standard. Per the text, the header value is a Structured Field String (so Idempotency-Key: "8e03978e-40d5-43e8-bc93-6894a57f9324", quoted), and servers SHOULD answer:
  • 400 when a required key is missing,
  • 422 when the key is reused with a different payload,
  • 409 when the original request is still being processed.

I follow the draft's status codes below because they're sensible, and they differ from Stripe in one place: Stripe doesn't store a result for requests that conflict with a concurrent one, and tells you to retry them.

Storage design

One table, one unique constraint:

CREATE TABLE idempotency_keys (
  key           text PRIMARY KEY,          -- the unique constraint does the real work
  fingerprint   text NOT NULL,             -- hash of method + path + canonical body
  response_code int,
  response_body jsonb,
  created_at    timestamptz NOT NULL DEFAULT now()
);

• key: the primary key. This is what makes "exactly one winner" a database guarantee rather than an application hope.
• fingerprint: SHA-256 of method path plus the JSON body with sorted keys. It's how you catch a client reusing a key for a different request. (The draft makes fingerprints optional, a MAY. I'd treat it as required: without it, a reused key silently returns the wrong response.)
• response_code / response_body: what you replay on a retry.
• created_at: drives expiry.

In production you'd probably also scope the key by user or API credential (PRIMARY KEY (account_id, key)), so one customer's key can't collide with another's. I left that out of the test.

The handler

The important decision: the key claim, the business write, and the stored response all go in one transaction. Either all of it commits or none of it does.

def idempotent_handler(key, body, on_busy="409"):
    if not key:
        return 400, {"error": "Idempotency-Key header required"}
    fp = fingerprint("POST", "/charges", body)
    with psycopg.connect(DSN) as conn, conn.cursor() as cur:
        if on_busy == "409":
            cur.execute("SELECT pg_try_advisory_xact_lock(hashtext(%s))", (key,))
            if not cur.fetchone()[0]:
                return 409, {"error": "request with this key is in progress"}
        cur.execute("""INSERT INTO idempotency_keys (key, fingerprint) VALUES (%s, %s)
                       ON CONFLICT (key) DO NOTHING RETURNING key""", (key, fp))
        if cur.fetchone() is None:  # someone already claimed and committed this key
            cur.execute("SELECT fingerprint, response_code, response_body "
                        "FROM idempotency_keys WHERE key=%s", (key,))
            old_fp, code, resp = cur.fetchone()
            if old_fp != fp:
                return 422, {"error": "Idempotency-Key reused with a different request"}
            return code, resp  # replay
        resp = do_charge(cur, body)
        cur.execute("UPDATE idempotency_keys SET response_code=201, response_body=%s WHERE key=%s",
                    (Jsonb(resp), key))
        return 201, resp  # commit on leaving the with-block

Setup was docker run -d -e POSTGRES_PASSWORD=pw -p 55432:5432 postgres:17-alpine (PostgreSQL 17.11), Python 3.12.10 and psycopg 3.3.6. INSERT ... ON CONFLICT DO NOTHING RETURNING key is the claim: per the PostgreSQL docs, skipped rows aren't returned, so None means the key already exists.

Concurrent duplicates: two ways to handle them

When two requests with the same key arrive together, you have two defensible behaviours, and I tested both.

Mode "wait" (just the unique constraint, no advisory lock): the second INSERT hits an uncommitted row with the same key and blocks until the first transaction finishes. Once it commits, the insert is skipped and the second request replays the stored response. Both clients get a 201 with the same body.

Mode "409" (the advisory lock above): the loser doesn't wait, it gets 409 immediately and the client retries later. This matches the draft's wording for in-flight requests.

The test fires N threads through a barrier so they start together:

naive, 2 parallel      -> [201, 201] charges: 2
idempotent[409], 2 par -> [201, 409] charges: 1 0.60s
idempotent[409], 20 par-> {409: 19, 201: 1} charges: 1
idempotent[wait], 2 par -> [201, 201] charges: 1 0.48s
idempotent[wait], 20 par-> {201: 20} charges: 1

That's the proof: one row in charges in every idempotent case, whether 2 or 20 requests raced. (The timings include opening connections and the 0.3s simulated provider call, so they're only useful as "the waiter didn't take much longer than the winner.")

Which one should you pick?

• Wait is friendlier to clients: they never see a 409 they have to handle. The cost is that every duplicate holds a connection while the first request runs, so a slow upstream plus an aggressive retrying client can tie up your pool. I only tested 20 threads against 1 key on one machine; I haven't measured pool exhaustion under load, so I can't give you a number for where it hurts.
• 409 keeps connections free and pushes the retry loop to the client. Use it if your downstream call can take seconds.

Same key, different body

first       (201, {'charge_id': 1, 'amount_cents': 5000})
retry       (201, {'charge_id': 1, 'amount_cents': 5000})
diff body   (422, {'error': 'Idempotency-Key reused with a different request'})
no key      (400, {'error': 'Idempotency-Key header required'})
charges: 1

The retry returns the same charge_id and creates nothing. The reused key with amount_cents: 9999 is rejected rather than quietly returning the 5000 charge, which would make the client think its new amount went through.

Gotcha: fingerprint a canonical body. I sort keys and strip whitespace (json.dumps(..., sort_keys=True, separators=(",", ":"))). Without that, a client library that reorders fields between retries would trigger false 422s.

Expiry

You can't keep keys forever, and you don't need to. Stripe v1 prunes after at least 24 hours; the draft says servers should publish whatever expiry policy they pick. I ran the purge by hand:

DELETE FROM idempotency_keys WHERE created_at < now() - interval '24 hours';

purged 1 -> (201, {'charge_id': 2, 'amount_cents': 5000}) charges: 2

After the purge the same key executes again and makes a second charge. That's expected and it's why the window matters: pick one longer than your clients' longest retry horizon, and say so in your API docs. Run the delete from a scheduled job (and index created_at if the table gets big; I didn't test table size).

What happens when the handler fails

Because the claim lives in the same transaction as the work, an exception rolls back both:

failed attempt: provider down
retry after failure (201, {'charge_id': 1, 'amount_cents': 5000}) charges: 1

The key wasn't stored, so the retry runs fresh. That's the right default for errors that happen before any irreversible side effect. Note the difference from Stripe v1, which saves and replays even 500s once execution has begun; that's a choice about where you think the failure boundary is.

The big caveat, and the one this demo can't show: if your handler calls an external API inside the transaction, a crash after the external call but before commit still loses the claim. The retry then calls the provider again. A single local transaction only makes your own database writes atomic. For real payments, pass the same idempotency key (or a key derived from it) on to the provider, so the dedupe extends past your boundary. I did not run that against a live provider here, so check your provider's docs for the exact header.

What I could not measure here

Idempotency tables matter most at scale: lots of keys, hot rows, multiple app servers, replicas. I ran one Postgres on one machine with at most 20 threads, so I can't tell you throughput, lock contention or storage growth. What the test does show is the correctness property, which doesn't depend on scale: the primary key serialises duplicates no matter how many app servers race.

What to do next

1. Add the idempotency_keys table and make the Idempotency-Key header required on every POST that moves money or sends something.
2. Wrap claim, work and stored response in one transaction, as in the handler above.
3. Pick "wait" or "409" for in-flight duplicates, and document the choice.
4. Fingerprint a canonical body and return 422 on mismatch.
5. Scope keys per account, and schedule the 24-hour (or longer) purge.
6. Forward the key to any external provider you call.
7. Copy the barrier-and-threads test into your suite and assert the row count.