Cutting replication lag on a Postgres read fleet
A multi-week investigation turned a recurring multi-second tail into a steady sub-second one. The fix that mattered was not the one I had planned to ship.
- Date
- Q2–Q3 2025
- Role
- Individual contributor
- Stack
- PostgreSQL 14 · NestJS · pgBouncer
- Scale
- Large monorepo · high-traffic
- Status
- Shipped Q3 2025
For about a year and a half, the dashboard's portfolio-summary view had a recurring complaint pattern: customers would reload, see yesterday's numbers, reload again, and see today's. The reads were going to a replica that periodically lagged the primary by several seconds, sometimes longer. The lag would clear in a minute or two and the page recovered, but the customer-support ticket rate for this category was a steady stream.
The lag was repeatable: it landed almost exactly on the hour, during our scheduled reconciliation window. The obvious story was that the reconciliation cron was hammering the WAL pipeline, and the obvious fix was to slow the cron down. As it turned out, both of those statements were partially true and almost entirely misleading.
No sharding the primary this quarter. The roadmap had it slated for Q1 2026 and the org was not interested in pulling it forward.
No new pieces of infrastructure. No Cassandra, no Kafka, no CDC layer. The org-learning cost would have outweighed the engineering win.
Ship without a major release. The platform was three weeks from an unrelated audit; any change had to fit inside the patch-train.
Observability gap: we had per-query timings but no per-connection saturation metrics. Half the investigation was building enough telemetry to see the problem at all.
I started where it was easy to start: with the reconciliation cron. It was issuing tens of thousands of single-row updates inside a single transaction, which felt obviously bad. I batched it. The peak tail dropped noticeably but not nearly as much as the math said it should. The on-call rotation was happier; the ticket rate did not move.
That was the moment the investigation turned. The numbers said the reconciliation cron was not the dominant cost. Something else was holding the connection long enough that batching the writes did not buy what the math said it should.
The cron was the suspect because the timing fit. The timing was a coincidence inside a longer-running pattern.
Three decisions, in order. The third was the one that mattered.
Batched the reconciliation cron. Tens of thousands of single-row updates became a small number of batched updates. Easy win, not the bottleneck.
Moved one hot-path read to the replica. The portfolio-summary endpoint was issuing a SELECT to the primary inside the same transaction as its write. Splitting it into a replica-read inside its own short transaction reduced primary contention measurably.
Surfaced a pgBouncer transaction-pooling bug. The split-read pattern made visible something we had not seen before: pgBouncer was pinning a server connection for the full duration of certain transactions because of a SET statement we were issuing on connection acquisition. The lag was a connection-starvation symptom, not a WAL-replay symptom.
- p99 lag
- tens of seconds → sub-second
- Stale-read errors
- roughly two orders of magnitude
- Support tickets / wk
- steady stream → occasional
- Net infra added
- none
No sharding. No new piece of infrastructure. The change set was three pull requests, two of which were the connection- pooling fix and a small telemetry addition that I should have shipped first.
The two-week part of this investigation I would take back is the week I spent assuming the cron was the problem because its timing matched. That is the kind of reasoning a junior engineer does once and an associate engineer does maybe twice.
I would now profile the WAL replay pipeline before changing any application code, even if the application code looks obviously wrong. Tens of thousands of single-row updates inside a transaction is a real problem; it was not this problem.
I would not change the decision to batch the reconciliation cron. It is still better code than it was before. I would batch it differently, given what we learned about how the pooler holds server connections.