In a clustered PostgreSQL (often abbreviated as PSQL) setup, especially with configurations such as three-node clusters, replication and high availability play crucial roles. PostgreSQL's replication system allows one node to act as a primary server, writing all changes to its database, while other nodes, referred to as replicas or standby servers, copy these changes to keep their databases updated.

However, in these configurations, not all nodes must or should have identical roles regarding data synchronization. This is where the synchronous_standby_names setting comes into play, allowing for sync and async configurations.

Understanding Sync and Async Nodes

In PostgreSQL's replication:

• Synchronous Replication:
  • A sync node ensures that data modifications are only considered successful once they've been recorded on the standby server. This guarantees no data loss, as every transaction on the primary is acknowledged as committed only once it's safe on at least one standby.
• Asynchronous Replication:
  • An async node allows transactions to be considered committed without waiting for the standby to acknowledge the receipt of changes. This can lead to less latency in write operations but might present a risk of data loss in the event of a primary server failure.

Why Combine Sync and Async Nodes?

When architecting a three-node cluster where one node is sync and another is async, there are several strategic advantages:

1. Balance Between Safety and Performance:
   • The cluster benefits from the data safety provided by synchronous replication while still enjoying the increased throughput of asynchronous replication. Write operations are faster when waiting for acknowledgments is not always required.
2. Optimized Resource Usage:
   • Using only one synchronous node reduces the resource overhead compared to configuring every node for synchronous replication, which would require primary to wait for all standbys, leading to potential bottlenecks.
3. Flexibility and Fault Tolerance:
   • If the synchronous node fails, the asynchronous node still ensures that there is a standby ready to take over, albeit with potential data lag. This setup minimizes downtime and enhances availability.
4. Improved Disaster Recovery:
   • In the event of a failure, administrators can promote the async node or force the remaining sync node to operate as the primary, providing more strategies for disaster recovery.

Example Configuration

Here's an example scenario with PostgreSQL replication in a three-node cluster:

• Node 1 (Primary): sync_state = sync
• Node 2 (Standby): sync_state = async
• Node 3 (Standby): sync_state = shutdown

Assuming you have set the PostgreSQL configuration as such:

synchronous_standby_names = 'ANY 1 (Node2, Node3)'

This configuration implies:

• Node2 will acknowledge transactions in an asynchronous manner.
• Node3 is currently in a shutdown or maintenance mode, not actively replicating.

Key Considerations

• Network Latency:
  • Synchronous replication can be sensitive to network latency, which can slow down the primary node's operations if the sync standby is lagging.
• Priority of Data Safety:
  • The business requirements regarding data safety and consistency should dictate whether to prioritize synchronous replication over performance.
• Promotion and Failover Strategies:
  • Clear strategies should be laid out on how to handle node promotions or recover from failures effectively.

Comparison Table

In conclusion, configuring a PostgreSQL cluster with a mix of synchronous and asynchronous nodes offers a balanced approach to achieving both high data safety and operational efficiency. As with any system architecture choice, it requires careful consideration of application requirements, network characteristics, and recovery processes.