Resolving PostgreSQL Replica Lag under 80k TPS

Problem Description

During a high-volume retail event, a core transactional PostgreSQL 16 cluster experienced streaming replication lag spiking to >450 GB, threatening to exhaust the primary node WAL disk capacity (/var/lib/postgresql/data/pg_wal).

---

Root Cause Analysis (RCA)

Using pg_stat_replication and eBPF kernel tracing, we isolated three concurrent bottlenecks:

1. Kernel TCP Socket Buffer Exhaustion:

The default OS net.core.wmem_max was set to 2MB. At 80,000 TPS with large JSONB payloads, the socket sender buffer hit full capacity, throttling WAL streaming.

2. Single-Threaded WAL Application Bottleneck:

The standby node was bottlenecked by a single CPU core processing WAL records linearly while disk IOPS were idle at 15%.

3. Exclusive Table Locks during Heavy Index Maintenance:

A scheduled concurrent index build on the primary created catalog lock contention on the standby.

---

Resolution Strategy & Configuration Fixes

Step 1: Sysctl Network Tuning

# Apply OS Kernel socket buffer increases

sysctl -w net.core.rmem_max=67108864

sysctl -w net.core.wmem_max=67108864

sysctl -w net.ipv4.tcp_rmem="4096 87380 67108864"

sysctl -w net.ipv4.tcp_wmem="4096 65536 67108864"

Step 2: Postgresql.conf Replication Optimizations

# Enable parallel WAL recovery and increase streaming buffers

max_wal_senders = 16

wal_sender_timeout = 60s

wal_buffers = 64MB

max_standby_streaming_delay = 30s

hot_standby_feedback = on

---

Key Takeaways

• Always match Linux network kernel parameters with your high-throughput NIC capability.
• Set up automated alert metrics for pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) at the 10GB threshold.