PostgreSQL Replication: Synchronous vs. Asynchronous
Replication is a fundamental technique for ensuring high availability and data durability in PostgreSQL. It involves copying data from one PostgreSQL server (the primary or master) to one or more other PostgreSQL servers (the replicas or standbys). This allows for read scaling, disaster recovery, and zero-downtime upgrades. A key decision in setting up replication is choosing between synchronous and asynchronous methods, each with its own trade-offs.
Understanding Replication Modes
PostgreSQL offers two primary modes for replication: asynchronous and synchronous. The choice between them significantly impacts data consistency, write performance, and the potential for data loss during failures.
Asynchronous replication prioritizes write performance over immediate data consistency.
In asynchronous replication, the primary server commits a transaction and immediately acknowledges the client without waiting for confirmation from the replica. The replica receives the transaction logs (WAL records) and applies them at its own pace. This offers higher write throughput but carries a risk of data loss if the primary fails before the WAL records are sent and applied to the replica.
Asynchronous replication is the default and most common mode. The primary server writes WAL records to its WAL buffer and then sends them to the replica(s). Once the primary has written the WAL record to its own WAL files, it can acknowledge the transaction to the client. The replica receives these WAL records and applies them. If the primary server crashes, any transactions that were committed on the primary but not yet sent or applied to the replica will be lost. This mode is ideal when write latency is critical and a small window of potential data loss is acceptable.
Synchronous replication guarantees that committed transactions are present on at least one replica before acknowledging the client.
Synchronous replication requires the primary server to wait for confirmation from at least one designated replica that it has received and written the WAL records for a committed transaction before acknowledging the client. This ensures that committed data is durably stored on multiple servers, eliminating the risk of data loss during a primary failure. However, it introduces latency to write operations, as the primary must wait for network round-trip times and replica write acknowledgments.
Synchronous replication enhances data durability by ensuring that a transaction is safely replicated before the client receives a confirmation. The primary server sends WAL records to the replica(s) and waits for an acknowledgment that the replica has received and written these records to its WAL files. Only then does the primary acknowledge the transaction to the client. This provides strong consistency but can significantly increase write latency, especially with multiple synchronous replicas or geographically distant replicas. PostgreSQL allows configuring the number of synchronous replicas required for acknowledgment.
Increased write latency.
| Feature | Asynchronous Replication | Synchronous Replication |
|---|---|---|
| Data Durability | Lower (potential data loss) | Higher (no data loss for committed transactions) |
| Write Performance | Higher (lower latency) | Lower (higher latency) |
| Consistency | Eventual consistency | Strong consistency (for committed transactions) |
| Complexity | Simpler to configure | More complex configuration, requires careful tuning |
| Use Case | Read scaling, disaster recovery with acceptable data loss | Mission-critical applications requiring zero data loss |
Configuration and Tuning
The behavior of replication is controlled by parameters in the
postgresql.conf
wal_level
max_wal_senders
wal_keep_segments
wal_keep_size
synchronous_commit
synchronous_commit
synchronous_standby_names
synchronous_commit
synchronous_standby_names
Choosing the right replication mode is a balancing act between data safety and application performance. For most applications, asynchronous replication is sufficient, but for critical data, synchronous replication is essential.
It's also important to consider network latency and bandwidth when configuring synchronous replication. A poorly performing network can turn the benefits of synchronous replication into a significant bottleneck. Hybrid approaches, where some replicas are synchronous and others asynchronous, can also be employed to achieve a balance.
Learning Resources
The official and most comprehensive guide to PostgreSQL replication, covering all aspects from setup to advanced configurations.
A detailed blog post explaining the differences, pros, and cons of synchronous and asynchronous replication in PostgreSQL.
This article discusses how replication contributes to HA/DR and outlines strategies for database upgrades using replication.
Specific documentation on PostgreSQL's streaming replication, the underlying mechanism for both synchronous and asynchronous modes.
A clear explanation of the different replication modes available in PostgreSQL and their implications.
An in-depth look at the configuration and performance considerations for synchronous replication in PostgreSQL.
A step-by-step tutorial guiding users through setting up basic replication in PostgreSQL.
A practical guide on setting up streaming replication for high availability, covering both primary and standby roles.
A video explanation that visually breaks down the concepts of synchronous and asynchronous replication in PostgreSQL.
Essential reading to understand the Write-Ahead Logging mechanism, which is fundamental to how PostgreSQL replication works.