Idempotency keys: exactly-once, built from at-least-once
Retries are everywhere you didn't put them: clients re-send timed-out requests, load balancers replay, job queues redeliver, your own retry loop reruns transactions. The network can only promise at least once. If the operation is "charge $30", at-least-once is a lawsuit — so the receiver must make duplicates harmless: idempotency.
The entire pattern is the previous lesson's unique constraint pointed at a new target: not the data, but the request identity. The client names each logical operation once (req-42); the server inserts that name as its first move, and the insert's result — row or no row — decides everything:
The client sends 'charge $30' with idempotency key req-42. Server A processes it.
A> BEGIN;
BEGIN
A> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- a row came back: this key is new — do the work
idempotency_key
-----------------
req-42
(1 row)
A> UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMITThe response is lost in the network. The client retries the same request; server B picks it up.
B> BEGIN;
BEGIN
B> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- 0 rows: already processed — skip the charge, return the stored result
INSERT 0 0
B> SELECT amount FROM payments WHERE idempotency_key = 'req-42';
amount
--------
30
(1 row)
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- charged exactly once
balance
---------
70
(1 row)The nasty case: the retry arrives while the original is still in flight, uncommitted.
A> BEGIN;
BEGIN
A> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
idempotency_key
-----------------
req-99
(1 row)
A> UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE 1
B> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
⏳ B is waiting for a lock…The unique index parks the retry until the original decides. A commits — the retry absorbs to 0 rows.
A> COMMIT;
COMMIT
⏵ B resumes:
INSERT 0 0Even the in-flight duplicate cannot double-charge.
A> SELECT balance FROM accounts WHERE id = 1; -- 100 - 30 - 25: every charge applied exactly once
balance
---------
45
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The gate and the charge share one transaction, and that's the load-bearing detail: if the server crashes after inserting req-42 but before charging, atomicity rolls back both — a retry finds no key and safely charges. Split them, and a crash in the gap leaves a claimed key with no charge: the retry then does nothing, forever.
The in-flight retry
"Already processed" was easy. The nastier duplicate arrives while the original is still running — no committed row exists to conflict with yet.
The unique index parks the retry until the original commits (the wait-on-transactionid mechanics from the previous lesson), then absorbs it: 0 rows, no second charge. Time-of-check races don't exist here — there is no check, only an insert with one winner.
Three things make this pattern work. The gate and the side effect share one transaction, so they commit or vanish together. The key names the operation, not the data, which is why the sender must mint it — one key per logical action, reused verbatim on every retry. And INSERT ... ON CONFLICT DO NOTHING RETURNING is the entire server-side protocol: a row back means do the work, nothing back means it's already done, so store whatever the caller needs re-answered alongside the key.
The boundary is the catch: only effects inside the transaction are protected. An email sent mid-transaction still sends twice, and anything external needs its own idempotency story — which is what chapter 6 builds next with the transactional outbox.