Idempotency keys for POST

API design: REST, HTTP, GraphQL and gRPC Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Fill the blank

Make the key claim race-free: a second request with the same key must fail instead of running.

SQL · PostgreSQL 17
CREATE TABLE idem (
  key text ____,
  request_hash text NOT NULL,
  response jsonb
);
INSERT INTO idem VALUES ('k1', 'h1', NULL);
INSERT INTO idem VALUES ('k1', 'h1', NULL);

Options
Hint 1 of 1

Which constraint rejects duplicates?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A PRIMARY KEY

Correct. The second insert fails with 23505, so only one request does the work.

Only a unique constraint makes the database itself reject the second claim atomically. Two requests racing on a check-then-insert would both see no row and both run.

Why not the others?
  • B NOT NULL. Duplicates would still be allowed.
  • C DEFAULT gen_random_uuid(). That makes the server choose keys; the client's key is the point.
  • D CHECK (key <> ''). Rejects empty keys but not duplicates.

30-second answer

Have the client generate a unique Idempotency-Key per logical operation and send it with every retry. The server stores the key with the request fingerprint and, once done, the response. A retry with the same key gets the stored response instead of running again; a concurrent duplicate waits or gets 409; the same key with a different body is rejected with 422. Keep keys for at least as long as clients retry, often 24 hours.

Practise saying it · 35 s. No recording, just a timer

Explain idempotency keys.

Say your answer out loud, then check which of these points you covered:

  • Client generates per operation
  • Store key, fingerprint, response
  • Unique insert, same transaction
  • Replay stored response
  • Pass downstream

Deep dive

Insert the key row first with a unique constraint, inside the same transaction as the order, so two racing requests can't both proceed.

Store a hash of the request body to detect key reuse with different content.

Pass the key (or a derived one) to the payment provider, so the downstream charge is idempotent too.

The client must reuse the key across retries and make a new one for a genuinely new order.

Claim a key and replay the stored answer Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TABLE idem (
  key text PRIMARY KEY,
  request_hash text NOT NULL,
  status int,
  response jsonb
);
-- first request claims the key, does the work, stores the answer
INSERT INTO idem (key, request_hash) VALUES ('order-7f3a', 'sha256:ab');
UPDATE idem SET status = 201, response = '{"order_id": 42}'
WHERE key = 'order-7f3a';
-- the retry finds the key and replays
INSERT INTO idem (key, request_hash) VALUES ('order-7f3a', 'sha256:ab')
ON CONFLICT (key) DO NOTHING;
SELECT status, response->>'order_id' FROM idem WHERE key = 'order-7f3a';

Common mistakes

  • Generating the key on the server.
  • Checking for the key and then inserting it as two steps (race).
  • Storing keys forever, or for less time than clients retry.

Senior take & follow-ups

Explain who generates the key, what the server stores, and the race-free insert.

Follow-up questions

Think of your answer first, then open the hint.

  1. What if the first request is still running?

    Show hint

    Return 409 or make the duplicate wait on the row lock.

  2. Where else do keys need to flow?

    Show hint

    Into downstream calls like the payment provider.

Related questions

Suggest a fix if something here is wrong or out of date.