Idempotency in Database Operations

Network failure is the default

Any time your application calls the database over a network, the call can fail in three ways: before the database saw it, after it processed but before the response arrived, or successfully. From the application's perspective, "I got no response" is identical to "the request never arrived" or "the request succeeded but I never heard back." Your retries must be safe regardless.

Idempotent operations

An operation is idempotent if running it twice (or twenty times) has the same effect as running it once. SELECT is idempotent. SET status = 'paid' is idempotent. SET balance = balance - 100 is not. Many natural-looking writes are not idempotent.

Patterns that make writes idempotent

Use ON CONFLICT DO NOTHING or DO UPDATE for inserts.
Pass an idempotency key from the client; insert it into a table; reject duplicates.
Use absolute updates (SET balance = ?) when possible instead of deltas (SET balance = balance - ?).
Track applied operations in a side table so you can detect re-execution.

Code

Client-supplied idempotency key·sql

CREATE TABLE payments (
    id              UUID PRIMARY KEY,
    idempotency_key TEXT UNIQUE NOT NULL,
    amount          NUMERIC(10,2) NOT NULL,
    status          TEXT NOT NULL DEFAULT 'pending',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Same key twice → second insert silently skipped, no double-charge
INSERT INTO payments (id, idempotency_key, amount)
VALUES (gen_random_uuid(), :key, :amount)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING *;

Idempotent UPDATE via WHERE·sql

-- Only marks paid if not already paid; safe to retry
UPDATE invoices
SET status = 'paid', paid_at = now()
WHERE id = ? AND status <> 'paid';

Idempotency in Database Operations

Network failure is the default

Idempotent operations

Patterns that make writes idempotent

Code

External links

Exercise

Progress

Comments 0