Concurrency and cache architecture for idempotency at scale

Two requests with the same idempotency key arrive at the server simultaneously — a real scenario when a mobile client fires two retries before the first acknowledgment. A naive SELECT-then-INSERT lets both through. Both charge the customer.

The race condition in naive implementations

A common first implementation:

-- Not atomic!
SELECT * FROM idempotency_keys WHERE key = $1;
-- Time passes here. A second request slips in.
INSERT INTO idempotency_keys (key, fingerprint, status, expires_at)
  VALUES ($1, $2, 'in_progress', NOW() + interval '24 hours');

Between the SELECT and the INSERT, a second concurrent request can execute the same SELECT (seeing no row), then also INSERT. Both succeed. Both start processing. Both charge the customer.

Fix 1: Postgres INSERT … ON CONFLICT DO NOTHING RETURNING

INSERT INTO idempotency_keys (key, fingerprint, status, expires_at)
  VALUES ($1, $2, 'in_progress', NOW() + interval '24 hours')
  ON CONFLICT (key) DO NOTHING
  RETURNING *;

If the RETURNING set is empty, this request lost the race. It then re-reads the row:

• If status=in_progress → return 409 Conflict.
• If status=completed → return the cached response.

The INSERT is atomic at the database level. No race.

Fix 2: Postgres advisory locks

SELECT pg_advisory_xact_lock(hashtext($1));
-- Now safe to SELECT-then-INSERT

pg_advisory_xact_lock holds a session-level exclusive lock for the transaction duration. Only one connection can hold the lock for a given hash at a time.

Tradeoff: hashtext is 64-bit — collisions are possible at ~10⁻¹⁰ probability per 10M keys per day. Acceptable for most workloads; security-critical paths use a full unique index instead. The lock must be held for the entire request including any external API calls (tens to hundreds of milliseconds) — watch pg_locks for pool exhaustion during incidents.

Scaling the cache: Redis Cluster

A single Postgres primary bottlenecks at roughly 5–10k writes/sec on commodity hardware. At 50k req/sec, the idempotency write becomes the bottleneck.

Redis Cluster with SETNX:

SETNX idempotency:{key} {fingerprint}:{status} EX {ttl_seconds}

SETNX is atomic set-if-not-exists. Redis Cluster shards by key hash, spreading load across nodes. Each node handles ~50–100k SETNX/sec.

Durability risk: Redis defaults to async fsync. A crash within milliseconds of a write can lose the entry. For a payment API, this means the key is gone and the next retry is treated as new — potential double charge.

Two-tier cache: the production pattern

Payment APIs that hold legal liability for double charges use a hybrid:

1. Hot path: Redis — SETNX on the key. If it succeeds (new key), process and write to Postgres asynchronously via the outbox. If it conflicts (existing key), read Redis for the cached response.
2. Cold path: Postgres — authoritative record. If Redis misses (rare, on crash), fall through to the Postgres table to recover the authoritative response.

Request → Redis SETNX
          ├─ New: process + store in Postgres async + cache in Redis
          ├─ Conflict: return cached response from Redis
          └─ Redis miss: read from Postgres → populate Redis → return

Stripe and Square use this hybrid in production. Pure Redis is acceptable for non-financial workloads (signups, analytics events) where a rare duplicate is logging noise rather than a compliance event.