PostgreSQL Tutorial: Myths and Truths About Synchronous Replication

Summary: In this tutorial, you’ll learn the core mechanisms of PostgreSQL synchronous replication, as well as some myths and truths.

Table of Contents

Core Background and Fundamental Concepts

1. WAL (Write-Ahead Log)

WAL is the foundation of PostgreSQL’s data integrity guarantees. Features like crash recovery, archiving, and replication are all built on top of WAL. WAL files are stored in the pg_wal directory, named using hexadecimal strings, and are typically 16MB in size by default.

2. Physical Streaming Replication Mechanism

• Primary: Responsible for generating WAL records. When a standby connects, the primary spawns a wal sender process to stream WAL data to the standby.
• Standby: Receives WAL streams via the wal receiver process and writes them to its local pg_wal directory. The standby’s startup process then reads and applies these changes, keeping the standby identical to the primary.

3. Streaming Replication Modes

• Asynchronous Replication: The primary writes WAL locally, flushes it to disk, and immediately returns a success status to the client without waiting for confirmation from standbys.
• Synchronous Replication: The primary must wait for confirmation from synchronous standby(s) (indicating the WAL was received, flushed to disk, or applied) before notifying the client that the transaction committed successfully.

Key Configuration Parameters

1. synchronous_commit

Configured on the primary node, this parameter dictates how strictly the primary waits for standby acknowledgments:

• on (Default): Ensures the transaction is durable locally on the primary and replicated/durable on the standby (protects against server crashes).
• remote_apply: The strictest level. Guarantees the transaction has been applied on the standby and is visible to standby client queries.
• remote_write: Waits only until the standby confirms writing the transaction to its OS buffer cache (not necessarily flushed to disk).
• local: Disables synchronous replication for the current session; transactions commit locally on the primary without waiting for standbys.

2. synchronous_standby_names

Defines the selection and confirmation rules for synchronous standbys:

• Priority Replication: Formatted as FIRST n (node1, node2). The primary strictly waits for responses from the first n available nodes in the list. Drawback: If the first node becomes unresponsive, it takes time for the primary to detect the failure before switching to the next node.
• Quorum Replication (Introduced in PG 10): Formatted as ANY n (node1, node2). The primary waits for confirmation from whichever n nodes respond fastest. Highly efficient, though it does not guarantee which specific standby received any given transaction.

5 Major Myths & Truths About Synchronous Replication

Myth 1: A transaction is committed on the primary only after receiving confirmation from synchronous standbys

✅ Truth: Transactions are ALWAYS committed locally on the primary first.

Mechanism: When a COMMIT command is issued, the primary writes and commits the record locally first, but holds active locks so the transaction remains invisible to other clients. Once enough standby acknowledgments arrive, the primary releases the locks, making the data visible and returning success to the application.

Edge Case / Risk: If the client cancels the query (e.g., Ctrl+C), the connection breaks, or PostgreSQL restarts while waiting, the lock-wait is canceled. The locally committed transaction then becomes visible on the primary, even though it was never successfully replicated to a standby.

Myth 2: Synchronous replication guarantees Zero Data Loss (RPO = 0) during failover

✅ Truth: Not guaranteed in all scenarios; visible data loss can still occur.

Potential Risk: As highlighted in Myth 1, if an application cancels a waiting commit or drops its TCP connection, the transaction may become visible on the primary. If the primary crashes immediately after and a failover promotes a standby, that un-replicated visible transaction will be lost.

Solutions: Applications can implement Two-Phase Commit (2PC), or use the txid_status() function upon reconnection to check whether a specific transaction ID actually committed, aborted, or remains in progress.

Myth 3: Reading from a synchronous standby yields identical results to reading from the primary

✅ Truth: Data on a synchronous standby can sometimes be visible EARLIER than on the primary.

Mechanism: Under remote_apply, as soon as a standby applies a transaction, it becomes visible to standby readers. However, the primary might still be blocked waiting for other quorum nodes to respond, leaving the data temporarily locked and invisible to readers on the primary.

Best Practice: Never perform write operations on the primary based on read queries from a standby. (To achieve strict consistency, configure all nodes synchronously or track WAL LSN alignment at the application layer).

Myth 4: Synchronous replication eliminates the need for the pg_rewind tool

✅ Truth: pg_rewind is still required after failovers, even in synchronous setups.

Mechanism: WAL generation on the primary occurs independently of standby acknowledgments. Background jobs (such as VACUUM) alter data pages and generate WAL logs. Before crashing, a primary might write extra WAL entries not yet shipped to standbys. When turning the old primary into a new standby, pg_rewind must be used to roll back these divergent WAL records. Additionally, asynchronous standbys in the cluster may be ahead of promoted synchronous standbys and will also require rewinding.

Myth 5: Synchronous replication makes database performance extremely slow

✅ Truth: Partially true; performance loss depends heavily on hardware specs and Round-Trip Time (RTT) latency.

Latency Breakdown: Single Transaction Latency ≈ Primary Disk Latency + Standby Disk Latency + Network RTT.

Benchmark Insights:

• In low-latency local networks (~5ms RTT), enabling synchronous replication adds a few milliseconds of transaction latency and cuts single-thread TPS roughly in half, but overall throughput scales well with higher connection concurrency.
• In high-latency networks (~100ms RTT), single transaction latency spikes above 100ms, causing a severe drop in TPS.

Practical Recommendation: Never deploy synchronous replication nodes across continents. Physical network latency will severely bottleneck application performance.

Reference

Myths and Truths about Synchronous Replication in PostgreSQL

See more

PostgreSQL Administration