PostgreSQL and Redis synchronization latency under high-concurrency load

Direct answer

Under write‑heavy bursts, strict ACID forces PostgreSQL to hold row‑level locks until transaction commit, which increases lock wait time and can exhaust the connection pool. Relaxing consistency (e.g., staying at READ COMMITTED with asynchronous replicas) shortens lock duration but allows temporary divergence between the primary DB and Redis cache, observed as synchronization latency spikes.

Likely explanation

The trade‑off manifests as follows: when the system prioritizes strict ACID, transactions linger longer on hot rows, queuing new connections and raising tail latency. When availability is favored, commits return sooner, reducing lock contention but leaving Redis with stale data until the cache is updated, producing the observed sync lag.

Confirmed facts (from research)

• Strict ACID → row‑level locks held until commit → increased lock wait and connection‑queueing under write‑heavy load.
• Relaxed consistency (READ COMMITTED + async replicas) → shorter lock windows → possible temporary DB‑Redis divergence.
• Row‑locking contention mitigation: tune max_connections, use PgBouncer in transaction mode, set statement_timeout, apply SELECT … FOR UPDATE SKIP LOCKED or advisory locks, batch updates into fewer transactions.
• Redis‑side latency sources: simultaneous expiration of high‑traffic keys (cache stampede). Mitigations: add random jitter to TTL, use a write‑behind queue (Redis LIST/BRPOP worker), stagger EXPIRE offsets.

Steps needed for this case

1. Verify current isolation level: SHOW transaction_isolation;
2. If lock wait is dominant, enable transaction‑mode pooling with PgBouncer and set a reasonable max_connections based on CPU/memory.
3. Add statement_timeout (e.g., 2s) to abort long‑running writes that would otherwise hold locks.
4. For hot rows (e.g., vote counters), replace plain UPDATE with UPDATE … WHERE id = $1 RETURNING * combined with FOR UPDATE SKIP LOCKED or use advisory locks to serialize only the necessary fraction.
5. Batch multiple vote/comment updates into a single transaction to reduce lock frequency.
6. On the Redis side, modify TTL assignment: EXPIRE key $((base + RANDOM % jitter)) where jitter is 10‑20% of base TTL.
7. Introduce a write‑behind queue: have PostgreSQL push cache‑invalidations to a Redis LIST; a separate BRPOP worker applies SET/DEL after the DB commit, decoupling cache reload from DB lock duration.
8. Monitor: pg_stat_activity for lock wait events and redis-cli --latency or LATENCY command during a write‑heavy benchmark (e.g., pgbench -c 200 -T 300).

Missing diagnostic detail

To confirm whether lock contention or cache stampede is the primary driver, could you share the 95th‑percentile lock wait time from pg_stat_activity observed during the latency spike?