Redis vs PostgreSQL for Idempotency State

Part of: Idempotency Key Storage & TTL Management

Every idempotency implementation needs a place to record “this request has already been seen.” The two dominant choices are an in-memory store like Redis and a relational store like PostgreSQL, and the choice is not cosmetic — it determines what happens when the store crashes mid-write, how tightly the dedup check couples to your business transaction, and how much latency you add to every request. This page compares them directly and then shows the hybrid pattern most production payment systems converge on.

This decision sits inside Idempotency Key Storage & TTL Management and assumes you have already read choosing TTL values for idempotency keys, since TTL behavior differs meaningfully between the two stores.

---

Decision matrix

---

Comparison

Durability is the sharpest distinguishing line. Redis’s AOF and RDB persistence mechanisms are good, but even appendfsync everysec accepts a 1-second loss window on crash — acceptable for a fast-path filter, not acceptable as the sole record of “did we charge this card.” PostgreSQL’s write-ahead log guarantees a committed row survives any crash, which is why it remains the system of record for unique-constraint-based deduplication.

Atomicity is comparable in mechanism but different in scope. Redis’s SET NX is atomic against Redis alone. PostgreSQL’s INSERT ... ON CONFLICT is atomic against Redis alone in the same sense, but critically it can sit inside a larger transaction alongside the business write — the same guarantee the transactional outbox pattern depends on.

Transactional coupling is where PostgreSQL wins decisively for anything touching money. If the idempotency record and the ledger update are separate stores, a crash between the two writes reintroduces the dual-write problem this whole site exists to eliminate — see the DynamoDB conditional-write pattern for how a single-store, non-relational alternative handles the same coupling requirement with ConditionExpression.

---

The hybrid pattern: Redis fast-path, PostgreSQL system of record

Most high-throughput payment systems run both: Redis rejects the overwhelming majority of duplicates in under a millisecond without touching the database, while PostgreSQL remains the durable, transactionally-coupled source of truth that Redis’s cache is built from.

Step 1 — Check Redis first

import redis

r = redis.Redis(host="localhost", port=6379, decode_responses=True)

def fast_path_check(idempotency_key: str) -> str | None:
    """Returns cached response if this key was already handled, else None."""
    return r.get(f"idem:{idempotency_key}")

Step 2 — On a Redis miss, fall through to PostgreSQL inside the business transaction

@Transactional
public PaymentResult capturePayment(String idempotencyKey, PaymentRequest req) {
    int inserted = jdbcTemplate.update(
        "INSERT INTO idempotency_keys (key, response, expires_at) " +
        "VALUES (?, '{}'::jsonb, now() + interval '24 hours') " +
        "ON CONFLICT (key) DO NOTHING",
        idempotencyKey
    );
    if (inserted == 0) {
        // Another request already claimed this key inside Postgres —
        // fetch and return its stored response instead of reprocessing.
        return fetchExistingResult(idempotencyKey);
    }

    PaymentResult result = chargeCard(req);

    jdbcTemplate.update(
        "UPDATE idempotency_keys SET response = ?::jsonb WHERE key = ?",
        result.toJson(), idempotencyKey
    );
    return result;
}

Step 3 — Populate Redis after the PostgreSQL commit succeeds

func populateFastPath(ctx context.Context, rdb *redis.Client, key, responseJSON string) error {
    // Only cache after Postgres has durably committed the result.
    return rdb.Set(ctx, "idem:"+key, responseJSON, 24*time.Hour).Err()
}

Step 4 — Handle a Redis outage gracefully

If Redis is unreachable, fall through directly to the PostgreSQL path rather than failing the request — Redis is an optimization, not a dependency.

async function checkIdempotency(key) {
  try {
    const cached = await redisClient.get(`idem:${key}`);
    if (cached) return JSON.parse(cached);
  } catch (err) {
    // Redis down: log and fall through to Postgres, do not fail the request here.
    logger.warn({ err, key }, "redis fast-path unavailable, falling back to postgres");
  }
  return null; // caller proceeds to the Postgres-backed transactional path
}

---

Verification and testing

Confirm the Redis fast-path actually short-circuits the database

redis-cli SET idem:pay_hybrid_1 '{"status":"captured"}' EX 86400 NX
# Time a request carrying this key; a query log should show zero Postgres queries
tail -f /var/log/postgresql/postgresql.log &
curl -X POST /charge -H "Idempotency-Key: pay_hybrid_1" -d '{}'
# Expected: no matching query appears in the Postgres log

Confirm PostgreSQL still enforces atomicity when Redis is cold

INSERT INTO idempotency_keys (key, response, expires_at)
VALUES ('pay_hybrid_2', '{}'::jsonb, now() + interval '24 hours')
ON CONFLICT (key) DO NOTHING
RETURNING key;
-- Run twice concurrently in two psql sessions; only one should RETURN a row

Simulate a Redis outage and confirm graceful fallback

redis-cli SHUTDOWN NOSAVE
curl -X POST /charge -H "Idempotency-Key: pay_hybrid_3" -d '{}'
# Expected: request still succeeds via the Postgres path, latency rises but no errors

---

Failure scenarios and debugging

---

SRE / observability checklist

1. redis_fast_path_hit_ratio — Gauge tracking the fraction of requests resolved by Redis without touching PostgreSQL; a sudden drop indicates cache population is broken.
2. redis_postgres_state_mismatch_total — Counter from a periodic reconciliation job comparing a sample of Redis keys against their PostgreSQL rows; should be 0.
3. postgres_connection_pool_saturation_ratio — Gauge; alert at 0.8 since a Redis outage forces full fallback load onto this pool.
4. idempotency_key_claimed_without_result_total — Gauge for rows claimed in PostgreSQL with no completed response after 60 seconds; a stuck-claim detector.
5. idempotency_ttl_drift_seconds — Gauge comparing Redis TTL remaining against PostgreSQL expires_at remaining for the same key; large drift signals a configuration mismatch.
6. Redis persistence lag — monitor rdb_last_save_time / AOF aof_last_write_status so a Redis crash’s data-loss window is known and bounded, not assumed.

---

Related

• Idempotency Key Storage & TTL Management — parent page on storage selection and key lifecycle.
• Choosing TTL Values for Idempotency Keys — how to size the TTL used by both stores in the hybrid pattern.
• Using Redis SETNX for Distributed Request Deduplication — the Redis-only fast-path mechanism detailed in depth.
• PostgreSQL Unique Constraints vs Application-Level Checks — the PostgreSQL-side atomicity primitive used as the system of record.
• DynamoDB Conditional Writes for Deduplication — a non-relational alternative when neither Redis nor PostgreSQL fits your infrastructure.