How do you make a payment endpoint safe to retry?

The usual implementation — check whether you have seen the key, then charge — let 969 duplicate charges through 1,000 concurrent requests. Fifty identical requests carrying the same Idempotency-Key produced 49.5 charges on average. Not a rare race: near-total failure, every trial, on the first thing most people write.

Replacing the check with a single INSERT ... ON CONFLICT DO NOTHING and reading the affected row count produced 1 charge every time, at every concurrency level tested, for about the same latency. The hard part is not the duplicate. It is the request that arrives while the first one is still running.

The short answer

• SELECT then INSERT is not a check, it is a race. At concurrency 2 it double-charged in 20 of 20 trials. At concurrency 50 it produced 49.5 charges per trial. The window is exactly as wide as your handler is slow.
• Claim the key with the write, not with a read. INSERT INTO idem_keys ... ON CONFLICT DO NOTHING RETURNING key — if you get a row you own the request, if you get nothing someone else does. Zero duplicates at concurrency 2, 10 and 50, and it cost 0.01 ms at the median versus the broken version.
• A UNIQUE constraint with no application table stops the double charge and loses the answer. It returned 409 to the retry forever, with no charge id in it. Preventing the duplicate is half the job; replaying the original response is the other half.
• The in-flight case is the one nobody codes. When request B arrives 3 ms into request A, there is no stored response to return. Returning 409 with retry_after_ms settled B correctly in 2 calls and 56 ms; blocking on an advisory lock settled it in 12.6 ms but holds a connection.
• Automatic recovery of a stale in-flight key double-charged. Taking over an abandoned key after a timeout re-ran the charge: 2 charges from 1 intent. Stuck keys need a human or a provider-side lookup, not a takeover rule.

What was measured

Apple M3, 8 cores, 16 GB RAM, macOS 26.4.1. Node v23.5.0, Docker 29.5.2, Postgres 16.15 (arm64) in a container with the port published on localhost. The "charge" is fake — pg_sleep(0.01) to stand in for a 10 ms call to a payment processor, then an INSERT into a charges table. No real provider, no card data. The count of rows in charges for a given key is the number of times the customer got billed.

The endpoint is a plain node:http server with a pg pool of 60. Concurrency N means N fetch() calls to the same URL with the same Idempotency-Key, fired from one Promise.all.

Why did the same key charge twice?

Here is the handler almost everyone writes first:

const seen = await db.query('select response from idem_keys where key=$1', [key]);
if (seen.rowCount === 1) return [200, seen.rows[0].response];

const res = await callProcessor(body);          // 10 ms

await db.query(`insert into idem_keys (key, response) values ($1,$2)
                on conflict (key) do update set response = excluded.response`, [key, res]);
return [201, res];

It reads correctly. It is wrong, because between the SELECT and the INSERT there is a 10 ms hole in which every other copy of this request also reads zero rows. Twenty trials per concurrency level:

At concurrency 10 and below, every single request charged. The check never fired once. It is not a 1-in-1000 race you can hope to miss.

The reason this survives in production is that the window is narrow and deterministic. Firing two requests a fixed gap apart, 20 trials each:

The cliff sits at the handler's own duration, 17 ms at the median. A client retrying after a 30-second timeout never hits it, which is why the code passes review and passes staging. What hits it is a double-clicked button, a load balancer retrying on a connection reset, or two workers draining the same queue row. See which errors should you actually retry? for why a POST that timed out is the request most likely to be sent twice, and the timeout you forgot for where those duplicate sends come from.

Which fix actually works?

Four approaches, same endpoint, same 10 ms processor, five bursts per cell:

Duplicate counts are totals across five trials, so the broken row's 216 is 216 extra charges from 250 requests.

Every fix works. They are not equivalent.

ON CONFLICT DO NOTHING is the cheapest correct one — 17.34 ms against the broken 17.33 ms, which is to say free. The whole fix is that the row count of a write, unlike the result of a read, is decided by Postgres under the row lock:

const ins = await db.query(
  `insert into idem_keys (key, fingerprint, state) values ($1,$2,'in_flight')
   on conflict (key) do nothing`, [key, fingerprint(body)]);

if (ins.rowCount === 1) { /* I own this request — charge */ }
else                    { /* someone else owns it — read their row */ }

Catching 23505 costs 2.2 ms more at the median, because the failed INSERT aborts the transaction and the driver pays for the error round trip. Same guarantee, worse median. Use it when your ORM hides ON CONFLICT.

The advisory lock is the slowest under contention — 40.6 ms to settle a burst of 50, against 23.3 ms — because losers block rather than return. That cost buys something the others do not have, which is the next section.

The UNIQUE-constraint-only approach deserves its own warning. It stops the duplicate charge with no application code at all. It also has nowhere to put the response, so the retry that a client sends after a lost reply gets 409 duplicate_charge with no charge id, forever. The point of an idempotency key is that the second request gets the same answer, not that it gets refused.

What if the first request is still running?

This is the case the tutorials skip. Request B arrives 3 ms into request A. The key exists. The response does not. B must not charge, and B cannot return a result that has not been computed.

Measured with A in flight and B sent 3 ms later:

Three defensible answers, and the choice is about who holds the connection.

409 with a retry hint is what Stripe-style APIs do, and it is the cheapest: B is answered in 1.5 ms and no server resource is held. A client that honours the hint settled correctly after 2 calls and 56.2 ms:

if (row.state === 'in_flight') return [409, { error: 'request_in_flight', retry_after_ms: 50 }];

Blocking — advisory lock, or SELECT ... FOR UPDATE on the key row inside the same transaction as the charge — gives B the real answer in 12.6 ms with no client changes. It also parks a connection per waiter for the duration of the charge, so 50 waiters means 50 connections, which is exactly the thing connection limits punish.

Server-side polling (loop on the key row, return when state='done') landed in between at 15.8 ms and combines the worst property of each: a held connection and a poll loop. It is what people build when they want blocking without the lock, and it is not better.

Return the 409. If a client cannot be changed, block — but cap the wait and fall back to 409 or 504.

What if the body changed?

A client reuses a key with a different payload. Maybe it templated the key from an order id and the order changed; maybe it is a bug. Returning the first result silently is the wrong answer, because the caller now believes a $99.99 charge happened when a $19.99 one did.

The fingerprint is a SHA-256 of the canonicalised body, stored next to the key and compared before any replay:

const fp = crypto.createHash('sha256')
  .update(JSON.stringify({ amount: body.amount, customer: body.customer }))
  .digest('hex');

Canonicalise deliberately: hashing raw bytes makes key order and whitespace significant, so a client that re-serialises its retry gets a 422 for an identical request. Hash the fields that decide the charge.

What do you store, and for how long?

The response must be replayed, not recomputed. Recomputing means calling the processor again, which is the bug you are fixing. So the row holds the status code and body as they were sent.

With a realistic 372-byte JSON charge response, 100,000 keys occupied 52 MB of table and 5,376 kB of index — 57 MB total, 602 bytes per key. A service handling a million paid requests a day accumulates 0.60 GB a day of key rows. That is why keys expire: 24 hours is the common contract, and the sweep is cheap. Deleting 59,993 of 100,000 rows took 23 ms.

Adding an index on created_at did not help: 22 ms, and EXPLAIN ANALYZE showed the planner choosing a Seq Scan anyway, since 60% of the table qualified. Skip the index; run the delete on a cron.

What if the charge succeeds but the response write fails?

The torn write. The processor took the money, then the process died before the key row was updated. Whether that is recoverable depends on one decision: are the key row and the charge in the same transaction?

The third row is the result I did not want. Taking over a key whose in_flight row has gone stale is the obvious repair — it is what you write when the second row's stuck keys start paging you — and it double-charged, because the charge from the dead request is still there and the takeover ran another one. Nothing in the key table can know that. Recovering a stale key safely requires asking the processor whether that idempotency key already produced a charge, which is exactly the lookup you were trying to avoid.

One transaction is correct here only because the "processor" was the same database. With a real HTTP processor, wrapping the call in a Postgres transaction does not roll the charge back — it just holds a transaction open across a network call, which is its own problem. The honest design is: write the in_flight row and commit, call the processor, then write the result; accept that a crash in the middle leaves a key that answers 409 until a human or a reconciliation job resolves it against the provider. A stuck key is a support ticket. A silent second charge is a chargeback.

Check it yourself

No dependencies beyond Docker. It starts Postgres 16, races ten shell processes through the naive check, then through the claim-by-insert version, and cleans up:

#!/usr/bin/env bash
set -euo pipefail
N=${N:-10}
docker run -d --name idem-demo -p 55683:5432 -e POSTGRES_PASSWORD=idem postgres:16 >/dev/null
until docker exec idem-demo pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done
q() { docker exec idem-demo psql -U postgres -qtAX -c "$1"; }
q "create table charges (id bigserial primary key, idem_key text);
   create table idem_keys (key text primary key);" >/dev/null

charge() { q "select pg_sleep(0.05)" >/dev/null; q "insert into charges (idem_key) values ('$1')" >/dev/null; }

naive() {   # SELECT, then INSERT
  if [ "$(q "select count(*) from idem_keys where key='$1'")" = "0" ]; then
    charge "$1"
    q "insert into idem_keys (key) values ('$1') on conflict do nothing" >/dev/null
  fi
}
claim() {   # INSERT ... ON CONFLICT DO NOTHING RETURNING
  if [ -n "$(q "insert into idem_keys (key) values ('$1') on conflict do nothing returning 1")" ]; then
    charge "$1"
  fi
}

for i in $(seq "$N"); do naive naive_key & done; wait
echo "naive SELECT-then-INSERT, $N concurrent: $(q "select count(*) from charges where idem_key='naive_key'") charges"
for i in $(seq "$N"); do claim claim_key & done; wait
echo "INSERT ON CONFLICT DO NOTHING, $N concurrent: $(q "select count(*) from charges where idem_key='claim_key'") charges"

docker rm -f -v idem-demo >/dev/null

Output here:

naive SELECT-then-INSERT, 10 concurrent: 10 charges
INSERT ON CONFLICT DO NOTHING, 10 concurrent: 1 charges

Ten charges for one intent, from the version that looks correct.

Where this goes next

An idempotency key is the receiving half of a retry policy. The sending half — what to retry, how to back off, and why exactly-once delivery is not a thing you can buy — is in retries, and why exactly-once delivery is a lie and which errors should you actually retry?. Every HTTP step in Workflow Builder claims its key with the insert, fingerprints the body, and answers 409 while a run is in flight — because the version that reads first is the one that charges twice.