PostgreSQL Replication Lag Troubleshooting

Question

Read replicas are falling minutes behind the primary. How do you diagnose replication lag and remediate it safely?

Accepted Answer

Replication lag is a critical issue for read replicas, impacting data freshness and consistency. Diagnosing and remediating it requires a systematic approach, starting with understanding the root cause and then applying targeted solutions. **Measure Lag with `pg_stat_replication` and WAL Positions:** My first step is to quantify the lag precisely. I'd use `pg_stat_replication` on the primary to check the `write_lag` and `flush_lag` metrics for the lagging replica. To get a more granular understanding of WAL consumption and replay, I'd compare the WAL sender's position on the primary with the WAL receiver's position on the replica, often by examining `pg_current_wal_lsn()` on both sides or querying `pg_wal_lsn_diff()` for specific WAL segments. **Analyze I/O Saturation on the Replica:** High I/O wait times on the replica are a common bottleneck for WAL replay. I'd use tools like `iostat` or `vmstat` to check for high `%iowait` or disk utilization. Examining the disk queue length and latency can reveal if the storage subsystem is overwhelmed by the write operations originating from WAL replay. **Assess Network Bandwidth and Latency:** The network connecting the primary and replica is crucial for efficient WAL shipping. I'd use tools like `iperf3` to test bandwidth and `ping` to measure latency between the servers. Insufficient bandwidth or high latency can cause the replica to fall behind, especially during periods of heavy write activity on the primary. **Investigate `fsync` and Checkpointing Behavior:** `fsync` operations and checkpoint frequency significantly impact WAL replay performance. I'd review the `fsync` setting, as disabling it can improve performance but carries data durability risks. I'd also examine checkpoint-related parameters like `max_wal_size` and `checkpoint_timeout`. If checkpoints are too frequent or too aggressive, they can lead to periods of intense I/O, slowing down WAL replay. **Tune PostgreSQL Configuration Parameters:** Based on the diagnostics, I'd consider tuning relevant parameters. This might involve adjusting `wal_level` (though typically `replica` is sufficient), `max_wal_size` to allow for larger WAL segments and less frequent checkpoints, and adjusting `checkpoint_timeout` and `checkpoint_completion_target`. Additionally, I might look at `shared_buffers` and `wal_buffers` on the replica, and potentially explore logical replication if the issue stems from specific object replication. Once the root cause is identified and remediated, ongoing monitoring of replication lag is paramount. Implementing robust alerting mechanisms that trigger on predefined thresholds for lag will ensure timely intervention before it becomes a critical issue.

PostgreSQL Replication Lag Troubleshooting

Ready to prove your AWS SysOps knowledge?

Exactly-Once Effects with the Outbox Pattern

Design an E-commerce Checkout & Cart

Design a Distributed Job Scheduler

K8s Readiness vs Liveness Probes

Ensuring Data Consistency Across Microservices

Aurora vs RDS PostgreSQL vs EC2: Costs, Performance & Multi-Region Compared (2025)

Taming Toil: Eliminating Repetitive Work to Scale SRE Teams

The Pragmatic SRE Guide to SLOs: From Business Goals to Error Budgets