The SyncRep Detective Story: Chasing Ghosts in PostgreSQL, Finding Demons in Storage
Thursday, October 23 at 11:25–12:15
In a high-availability PostgreSQL architecture with sync replicas, a sudden spike in commit latency is a high-stakes crime. In our case, data from pg_wait_sampling pointed to a single, obvious suspect: the SyncRep wait event. This launched a critical investigation to restore our application's performance.
Our initial inquiry took us down a rabbit hole of what we now know was a classic red herring. We were convinced the culprit was network-related, spending valuable time investigating potential network misconfigurations and signs of overload. Yet, despite our best efforts, the evidence never fully added up, and the performance issues persisted.
The breakthrough came when we looked closer at the replica's activity and discovered our "smoking gun": persistently slow fdatasync() calls. This crucial clue exonerated the network and proved the problem wasn't in the transit of data, but in its persistence. The focus of our investigation pivoted instantly and intensely towards the storage layer, where the real mystery lay.
This presentation details our two-pronged counterattack:
The Tactical Containment: As we began the deep-dive into the storage system, we immediately implemented quorum-based synchronous commits. This provided crucial first-aid, reducing the immediate impact on the primary and buying us valuable time.
The Strategic Solution: The core of our story is a deep dive into the custom patch we engineered for PostgreSQL. We will walk through the logic of this patch, designed specifically to reduce the primary's performance sensitivity to slow replica disk writes.
Join this session to learn:
How to use low-level diagnostics to spot slow fdatasync() calls and correctly identify I/O-bound replication lag.
The practical benefits and implementation details of using quorum commits as a powerful mitigation strategy.
A technical breakdown of our PostgreSQL patch and the lessons we learned in modifying the source to solve a unique infrastructure challenge.
This is a real-world story of misdirection, deep-dive forensics, and pragmatic engineering. You will leave with a playbook for diagnosing the true source of replication latency, even when all initial signs point elsewhere.
