Storing Keys: Database vs Redis — Idempotency & Retry-Safe Systems

The strongest design stores the key in the same database transaction as the business change: either the request commits with its key, or neither does. Redis is fast and has TTLs but isn't transactional with your database — use it for short locks or as a cache in front of the database record. Store: key, scope, request fingerprint, status (processing/completed), response status/body/headers, created_at, and locked_until for recovery.

Key storage options

Criterion	Same database (transactional)	Redis	Hybrid
Atomic with business write	Yes	No	Yes (DB is source of truth)
Latency	DB round trip	Sub-millisecond	Fast path via Redis
Expiry	Cleanup job	Native TTL	Both
Failure modes	DB contention on hot keys	Lost keys on failover; split brain	More moving parts
Best for	Payments, orders	Low-stakes dedupe, locks	High-traffic critical APIs

CREATE TABLE idempotency_keys (
  scope            text        NOT NULL,          -- account / api key
  key              text        NOT NULL,
  fingerprint      text        NOT NULL,          -- hash of method + path + body
  status           text        NOT NULL,          -- processing | completed
  response_status  int,
  response_body    jsonb,
  locked_until     timestamptz,
  created_at       timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (scope, key)
);
CREATE INDEX ON idempotency_keys (created_at);       -- for expiry cleanup

async with db.transaction():
    claimed = await db.fetchval(
        '''INSERT INTO idempotency_keys (scope, key, fingerprint, status, locked_until)
           VALUES ($1, $2, $3, 'processing', now() + interval '60 seconds')
           ON CONFLICT DO NOTHING RETURNING 1''', scope, key, fp)
    if not claimed:
        return await replay_or_conflict(scope, key, fp)
    order = await create_order(db, body)                 # business write
    await db.execute('''UPDATE idempotency_keys SET status='completed', response_status=201,
                        response_body=$3 WHERE scope=$1 AND key=$2''', scope, key, order.json())
return JSONResponse(order.json(), status_code=201)

Common pitfalls

Key stored in Redis, order in Postgres: crash between them breaks guarantees.
Storing full responses forever (privacy + storage).
Hot-row contention when one key is hammered by retries.

Key points

Prefer the same transaction as the work
Redis for locks/caching, not the source of truth
Store fingerprint, status and response

Further reading: Brandur: Implementing Stripe-like idempotency keys in Postgres

Practice questions

What goes wrong if the idempotency key is stored in Redis but the order in Postgres?

Answer

A crash or failover between the two writes can leave an order without a key (retry creates a duplicate) or a key without an order (retry returns a response for something that never happened).

Why store the response body with the key?

Answer

So retries can receive exactly the original result (same IDs) without re-executing the operation.