8.2 Synchronous vs. Asynchronous Replication

8.2 Synchronous vs. Asynchronous Replication

[!IMPORTANT] The Tradeoff Frontier: In distributed database design, high availability and replication are governed by an inescapable engineering tradeoff: transaction commit latency and throughput versus data loss prevention. PostgreSQL allows administrators to position their clusters anywhere along this spectrum—from pure asynchronous replication prioritizing raw speed to multi-node synchronous quorum replication guaranteeing strict zero data loss (RPO = 0).

Understanding how PostgreSQL controls transaction durability across network boundaries requires mastering the synchronous_standby_names parameter, the internal mechanics of the synchronous_commit hierarchy, and the operational risks of replica stall.

---

Asynchronous Replication: High Performance with RPO Exposure

By default, PostgreSQL operates in asynchronous replication mode (synchronous_standby_names = '').

Commit Sequence Under Asynchronous Replication

1. A client application executes an INSERT, UPDATE, or DELETE statement inside a transaction block.
2. The client issues COMMIT.
3. The primary backend process writes the transaction's commit WAL record to WAL buffers in shared memory and flushes it to the primary's local disk via fsync.
4. Immediate Client Acknowledgment: As soon as the local fsync returns successfully, the primary server releases row and table locks, and sends a COMMIT success message back to the client application.
5. Background Transmission: Concurrently and independently, the walsender process detects the new WAL records and transmits them asynchronously across the network socket to the standby replica.

Client                Primary Backend             Local Disk               walsender              Standby
  |                          |                        |                        |                     |
  |--- COMMIT -------------->|                        |                        |                     |
  |                          |--- write & fsync ----->|                        |                     |
  |                          |<-- disk flush ok ------|                        |                     |
  |<-- COMMIT SUCCESS -------|                        |                        |                     |
  |                          |                        |                        |                     |
  |                          |                        |--- stream WAL -------->|                     |
  |                          |                        |                        |==== TCP Stream ====>|

Advantages and Disadvantages

• Throughput & Latency: Client commit latency is completely decoupled from network bandwidth, network jitter, physical geographic distance, and standby performance. If the standby slows down or the network drops completely, primary transaction throughput remains unaffected.
• Recovery Point Objective (RPO > 0): Because the primary acknowledges commits before WAL is guaranteed to have left the primary node, an abrupt primary crash (e.g., immediate host motherboard failure or unrecoverable hypervisor crash) can result in data loss. Transactions that committed locally but whose WAL records were still in flight or unsent are lost if a standby is promoted to primary.

---

Synchronous Replication: Zero Data Loss Architecture

To achieve a Recovery Point Objective of zero (RPO = 0), PostgreSQL supports synchronous replication. Under synchronous replication, the primary backend process does not confirm a COMMIT to the calling application until the required number of standby servers have acknowledged receipt and processing of the corresponding WAL record.

Internal Sync-Rep Queue Mechanics

1. The primary backend flushes the commit WAL record to local disk.
2. Instead of returning to the client, the backend inserts its current LSN into an internal shared memory queue (SyncRepQueue) and enters an efficient sleep state on a process latch.
3. The walsender transmits the WAL record to the standby.
4. The standby's walreceiver processes the WAL payload and sends back a StandbyReplyMessage with its updated LSN status.
5. Upon receiving the standby acknowledgment, walsender inspects the SyncRepQueue, identifies all backends whose commit LSNs have been satisfied, and awakens their latches.
6. The primary backends release their transactional locks and return COMMIT success to their respective clients.

---

Standby Candidate Selection: synchronous_standby_names

The primary server determines which standbys participate in synchronous confirmation using the synchronous_standby_names parameter. Standby nodes are identified by their application_name, which is specified in each standby's primary_conninfo.

PostgreSQL supports two distinct parsing formats for synchronous_standby_names:

1. Priority-Based Selection

A comma-separated list of standby application names without keywords (or explicitly prefixed with FIRST):

# Priority list: node1 is the active synchronous standby
synchronous_standby_names = 'node1, node2, node3'

# Equivalent explicit syntax: FIRST 1
synchronous_standby_names = 'FIRST 1 (node1, node2, node3)'

• Behavior: PostgreSQL evaluates the listed nodes in strict left-to-right order. The first reachable, active standby in the list becomes the active synchronous standby (sync_state = 'sync' in pg_stat_replication).
• Potential Standbys: The remaining connected nodes are marked as potential (sync_state = 'potential'). If node1 disconnects or crashes, PostgreSQL immediately elevates node2 to become the active synchronous standby, ensuring uninterrupted zero-data-loss protection.
• Multiple Priority Standbys: Specifying FIRST 2 (node1, node2, node3) requires the first two active standbys in the list to acknowledge each commit.

2. Quorum-Based Selection (ANY)

A quorum configuration where any arbitrary subset of the listed standbys satisfies the synchronous commit requirement:

# Quorum list: Any 2 of the 3 nodes must confirm
synchronous_standby_names = 'ANY 2 (node1, node2, node3)'

• Behavior: The primary does not care which specific nodes confirm, so long as at least $N$ nodes acknowledge receipt. In pg_stat_replication, all eligible connected standbys are marked with sync_state = 'quorum'.
• Performance Benefit: Quorum replication eliminates the "tail latency" of waiting for a single specific slow node. If node1 experiences a momentary I/O spike, faster acknowledgments from node2 and node3 satisfy the quorum, allowing client commits to proceed without delay.

---

The 5 Tiers of synchronous_commit

The configuration parameter synchronous_commit controls the exact operational guarantee and wait boundary required before a transaction reports success. Crucially, synchronous_commit can be configured globally in postgresql.conf, per user/database, or dynamically changed within a single transaction session using SET LOCAL synchronous_commit.

-- Tune durability for a specific bulk batch transaction
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO clickstream_logs SELECT ...;
COMMIT;

1. synchronous_commit = off (Local Asynchronous Commit)

• Wait Point: The backend reports COMMIT success to the client application immediately after writing the WAL record into the primary's memory WAL buffers—before flushing to local disk!
• Durability: If the primary crashes, the last few transactions (governed by wal_writer_delay, default 200ms) may be lost, even though the client received a success message. Database integrity is preserved (no corruption), but transactional durability is relaxed.
• Use Case: High-frequency, non-critical logging, caching, or time-series data where ingestion speed outweighs absolute durability.

2. synchronous_commit = local (Local Synchronous Only)

• Wait Point: The backend waits for the commit record to be written and flushed to the primary's local disk (fsync). It completely ignores synchronous_standby_names and does not wait for any standby acknowledgment.
• Durability: Standard single-instance ACID durability. Survives primary crashes, but risks data loss (RPO > 0) upon primary failover.

3. synchronous_commit = remote_write (Standby OS Write)

• Wait Point: The primary backend waits until the primary has flushed the WAL locally AND the synchronous standby's walreceiver has acknowledged writing the WAL to the standby's operating system cache (write_lsn).
• Durability: The primary does not wait for the standby to execute an fsync to disk. The data is safe if the PostgreSQL server process on the standby crashes (since the OS kernel will write out cached buffers), but data could be lost if the standby server suffers a simultaneous unbuffered power outage.

4. synchronous_commit = on (Default Synchronous Mode: Standby Disk Flush)

• Wait Point: The primary backend waits until the primary has flushed locally AND the synchronous standby has written and flushed the WAL to durable disk storage (flush_lsn).
• Durability: True RPO = 0 disaster recovery durability. Even if both the primary and standby suddenly lose power simultaneously, all committed transactions are fully durable on disk.
• Latency: Every client write transaction incurs the round-trip network latency to the standby plus the standby's physical disk fsync write time.

5. synchronous_commit = remote_apply (Standby Redo / Query Visibility)

• Wait Point: The primary backend waits until the primary has flushed locally AND the standby's startup process has fully replayed the WAL record into the standby's database pages (replay_lsn).
• Durability & Consistency: Provides causal consistency ("read-your-writes" consistency). When a client commits a write on the primary and immediately issues a SELECT against the standby replica, the newly committed data is guaranteed to be visible immediately, eliminating standby replication read lag.
• Latency: Incurs the highest latency penalty, as the primary must wait for the standby's CPU and I/O subsystems to complete the physical redo logic.

---

The Synchronous Availability Hazard: The Primary Stall

A critical operational hazard inherent to synchronous replication is write stall during replica failure.

[!CAUTION] The Synchronous Standby Lockup: If synchronous_standby_names is configured to require 1 standby, and that standby replica crashes, suffers a network partition, or is shut down for maintenance, all subsequent write transactions (INSERT, UPDATE, DELETE, COMMIT) on the primary server will block and hang indefinitely waiting for a standby acknowledgment! Read-only queries (SELECT) will continue to function normally, but the application's write pipeline will freeze completely.

To prevent unplanned production write lockups, high-availability architectures should:

1. Deploy at least two standby replicas and configure quorum replication (ANY 1 (node1, node2)) so that either replica can satisfy commits if the other goes offline.
2. Utilize automated failover/monitoring daemons (such as Patroni) to automatically adjust synchronous_standby_names if node counts fall below quorum.

---

Exam Tips and Common Pitfalls

• Exam Trap: Read-Your-Writes Consistency: If an exam scenario describes an application that writes to a primary database, immediately reads from a standby replica, and gets stale data, the default setting synchronous_commit = on is not sufficient to prevent this! The setting synchronous_commit = on only waits for disk flush (flush_lsn), not replay. The only setting that guarantees instant query visibility on the replica is synchronous_commit = remote_apply.
• Exam Trap: Granular Overrides: You do not have to run an entire cluster in synchronous or asynchronous mode. You can leave synchronous_commit = on globally for financial transactions, and set synchronous_commit = off or local inside specific batch scripts or sessions to maximize bulk loading throughput.
• Exam Trap: Quorum vs Priority Syntax: In synchronous_standby_names, the keyword ANY denotes quorum (any $N$ nodes satisfy the commit), whereas FIRST denotes strict priority order (the first $N$ reachable nodes in the listed order satisfy the commit).