PostgreSQL Replication Lag Under Write Surge
A write-heavy burst (batch imports, end-of-day reporting loads, or viral traffic) causes the WAL replication stream to fall behind. The replica accumulates lag, stale reads proliferate, and read-after-write consistency violations surface before the primary absorbs the burst and lag self-heals.
Primary PostgreSQL + 1 streaming replica serving read traffic
Degradation Replay
Nominal: Steady-State Replication
System at baseline: write rate within replica apply capacity
Critical: 12 exceeds critical threshold of 0.5 MB/s
- ·Replica lag < 100ms: imperceptible to application
- ·Read queries on replica return data consistent with primary within 1 WAL cycle
- ·pg_stat_replication.write_lag shows near-zero
- ·Primary accepts writes and flushes WAL
- ·Replica applies WAL continuously, staying within 1-2 WAL segments of primary
- !Replica serving read traffic normally: no user impact
Operational simulation model only, not a production forecast. Degradation stages are derived from structured operational knowledge, not measured telemetry. Do not use for capacity planning or incident response.
Run With Your Parameters
Adjust the parameters below to see how metric values shift across degradation stages. Formulas are deterministic: same inputs always produce the same output.
Simulation Parameters
Sustained write rate on the primary
Number of secondary indexes on the hot table
WAL apply throughput capacity on replicas
Computed Degradation Stages
System at baseline: write rate within replica apply capacity
Bulk write operation or traffic spike pushes write rate above replica apply capacity
Replication lag crosses 5-second warning threshold; ops team alerted
Lag exceeds 30s; primary absorbs redirected reads; OLTP latency spikes
Bulk write operation completes; write rate drops below replica apply rate
Interpretation
4 secondary indexes create 4.2× write amplification. Peak WAL: 2.3 MB/s vs 50 MB/s replica apply rate. Replication lag peaks at 0s.
Write amplification from secondary indexes exceeds replica apply rate
Reduce index count from 4 to the minimum required. Upgrade replica WAL apply capacity to 3 MB/s. Route time-sensitive reads to primary when lag exceeds 5s.
Parameterized simulation: not a production forecast. Values derived from deterministic formulas applied to your parameters. Do not use for capacity planning or operational decisions without validation.
Propagation Model
WAL segments accumulate faster than replica apply rate; lag grows at (write_rate - apply_rate) bytes/sec
Stabilizes: Lag stabilizes when write burst subsides and replica catches up at full apply rate
Read queries routed to replica return stale data; read-after-write violations surface to users
Stabilizes: Stale reads resolve once replica LSN overtakes the application write LSN
If lag alerting is wired to read routing, traffic shifts to primary; primary load increases
Stabilizes: Traffic returns to replica once lag drops below SLA threshold
Recovery Patterns
Self-healing after write burst completion
2-15 minutes depending on burst magnitude and replica apply rate- ·During recovery, primary bears full read load: OLTP latency elevated
- ·Replica catches up faster than it fell behind (no burst incoming)
- !If burst recurs immediately after recovery, lag accumulates again from non-zero baseline
- !Replica may need full resync if pg_wal segments were recycled during lag window
Forced throttle of write source
30-120 seconds after throttle applied- ·Throttling writes delays the batch job or ETL that caused the burst
- ·Downstream systems dependent on the write may experience delays
- !Throttled job must complete eventually: may cause secondary burst when restriction lifted
Operational Summary
Write bursts cause replica lag to accumulate when the primary WAL generation rate exceeds the replica apply rate. The lag window creates a stale read exposure period proportional to the burst magnitude and duration. If lag exceeds wal_keep_size, the replica must resync from scratch: a multi-hour operation on large databases. Mitigation requires read routing awareness, write throttling, and lag monitoring.