What Are Idempotency Keys and How Do They Prevent Duplicate Database Writes?
Learn how idempotency keys and unique database constraints stop retried requests from causing duplicate writes or double charges.
Expected Interview Answer
An idempotency key is a unique client-generated token attached to a write request that a database uses, via a unique constraint, to guarantee the same logical operation is applied exactly once even if the request is retried or received twice.
Clients generate a key (often a UUID) once per logical action, such as a single checkout attempt, and send it with every retry of that same attempt. The server stores completed operations keyed by that token in a table with a unique index, so a second insert attempt with the same key either fails on the constraint or, more usefully, is detected and short-circuited to return the original result instead of re-executing the write. This turns network retries, double-clicks, and at-least-once message delivery from a data-corruption risk into a safe no-op, without requiring the client and server to coordinate on anything beyond the shared key.
- Makes retried requests safe instead of causing duplicate side effects
- Protects against double-charging, double-shipping, or duplicate rows
- Relies on a database unique constraint rather than fragile application logic
- Works correctly even under network failures and client retries
AI Mentor Explanation
A stadium issues each ticket a unique barcode, and the turnstile scanner records that barcode the first time it is scanned, refusing entry on any repeat scan of the identical code even if a fan's phone glitches and resends the same QR image twice. The barcode itself is the idempotency key: the gate's database recognizes it has already processed that exact code and does not admit the fan a second time. A database write tagged with an idempotency key is checked against a unique index the same way, so a retried request with the same key never re-executes the operation.
Step-by-Step Explanation
Step 1
Client generates a unique key
A UUID or similar token is created once per logical operation and reused across all retries of that same attempt.
Step 2
Add a unique constraint
Store the idempotency key in a column with a unique index so the database itself rejects duplicate inserts.
Step 3
Check before or during the write
Attempt the insert; if it violates the unique constraint, look up and return the previously stored result instead of erroring the client out.
Step 4
Return the original result on retry
A retried request with the same key gets the same response as the original successful call, making the operation safe to repeat.
What Interviewer Expects
- Understanding that idempotency solves duplicate side effects from retries, not general correctness
- Knowledge that a unique constraint, not application-level checking alone, enforces the guarantee
- A concrete example like payment processing or webhook handling
- Awareness of the difference between idempotent HTTP methods and idempotency keys for non-idempotent operations like POST
Common Mistakes
- Generating a new idempotency key on every retry instead of reusing the same one
- Relying only on application-level duplicate checks without a database unique constraint
- Confusing idempotency keys with primary keys or auto-generated IDs
- Not returning the original response on a detected duplicate, causing client-side errors
Best Answer (HR Friendly)
“An idempotency key is a unique token the client generates once for a specific action, like a single checkout, and sends with every retry of that action. The database enforces a uniqueness constraint on that key, so if the same request comes in twice — say because the network dropped the first response — the second attempt is recognized as a duplicate and the original result is returned instead of the operation happening again.”
Code Example
CREATE TABLE payments (
payment_id BIGSERIAL PRIMARY KEY,
idempotency_key UUID NOT NULL UNIQUE,
order_id BIGINT NOT NULL,
amount_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Attempt the write; a retry with the same key violates the unique constraint
INSERT INTO payments (idempotency_key, order_id, amount_cents)
VALUES ('9f1c2e3a-...', 4501, 2599)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING payment_id;
-- If no row is returned, the key already exists:
-- look up and return the original payment instead of charging again.Follow-up Questions
- How long should a server retain idempotency keys before they can be safely reused?
- What happens if two requests with the same idempotency key arrive concurrently?
- How is an idempotency key different from making an HTTP method like PUT naturally idempotent?
- How would you handle a request that reuses a key but with different request parameters?
MCQ Practice
1. What database feature actually enforces an idempotency key guarantee?
The uniqueness of the key column is what physically prevents the database from accepting a duplicate write for the same logical operation.
2. When a retried request is detected via its idempotency key, what should the server typically do?
Returning the original result lets the retry behave as if it succeeded on the first try, which is the whole point of the guarantee.
3. Idempotency keys are most important for protecting against which scenario?
They exist specifically to make retried write requests safe, preventing effects like double-charging a customer.
Flash Cards
What is an idempotency key? — A unique client-generated token tied to one logical operation, used to detect and safely handle retried requests.
What enforces the guarantee at the database level? — A unique constraint or unique index on the idempotency key column.
What should happen on a detected duplicate? — Return the original operation's result rather than re-executing the write.
Give a classic real-world use case. — Payment processing, where a retried charge request must not double-bill the customer.