A tiny Postgres replica lag bug that only showed up in August

We ran a 3-node Postgres 14 cluster on OVH in Roubaix and Gravelines, streaming replication, maybe 400 GB of data and a write rate of roughly 1200 rows/s at peak. For ten months it was fine. Then in late July the read replica in Gravelines started falling behind by 20–40 seconds every afternoon, always between 14:00 and 17:00 local time, and always recovering by 19:00. Application latency paged us twice before I took it seriously.

My first guess was network, because the two sites are about 300 km apart and OVH’s inter-DC link is shared. That was wrong. pg_stat_replication showed write_lag near zero and replay_lag climbing, which means the WAL arrived on time and the replica was slow to apply it. So I looked at the replica’s own disk, not the link.

What I actually did: on the Gravelines box I sampled pg_stat_activity and iostat -x 5 during the window, and found the WAL replay was fighting with our nightly-ish pg_dump and a batch of analytics queries. The dump was scheduled in UTC (01:00 UTC = 03:00 CEST), so that wasn’t it. The real culprit was a cron on an app server that ran every 15 minutes from 12:00 to 16:00 UTC and did a large SELECT ... ORDER BY created_at against the replica. Under the August data volume the sort spilled to disk, and the replica’s single WAL replay process lost I/O priority to it.

Concrete change, in postgresql.conf on the replica:

max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 2

And on the analytics side, we capped the query:

SET statement_timeout = '30s';
SET work_mem = '64MB';
SELECT ... ORDER BY created_at LIMIT 5000;

We also moved that cron to the primary’s read-only connection pool behind PgBouncer, so it stops competing with replay.

The mechanism is boring once you see it: Postgres has one startup process applying WAL, and it does not get special I/O priority. A big sort on the same disk can starve it. Raising parallel workers helps the analytics query finish faster but also adds more disk pressure, so the real fix was moving the query off the replica, not tuning it. The cost is that analytics now hits the primary, which we can afford at our size but would not at 10x.

Where this breaks: if your replica is on network storage or a noisy-neighbor VM, the same symptom can come from the hypervisor and no postgresql.conf change will help. I also would not trust this diagnosis from replay_lag alone on Postgres 13 and older; the lag fields behave differently and I got misleading numbers before we upgraded. And if you run synchronous replication, starving replay will stall the primary, which is a much louder failure and a different debugging path.

What I still don’t know: whether the right long-term move is a dedicated logical replica for analytics, or just accepting that our read replica is not a reporting box. If you’ve run both, what actually held up under month-end load?