Synchronous Read-Only Replication in Azure PostgreSQL: Possibilities and Limitations

Quote from: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-read-replicas

Read replicas are primarily designed for scenarios where offloading queries is beneficial, and a slight lag is manageable. They’re optimized to provide near real time updates from the primary for most workloads, making them an excellent solution for read-heavy scenarios. However, it’s important to note that they aren’t intended for synchronous replication scenarios requiring up-to-the-minute data accuracy. While the data on the replica eventually becomes consistent with the primary, there might be a delay, which typically ranges from a few seconds to minutes, and in some heavy workload or high-latency scenarios, this delay could extend to hours.

Azure Database for PostgreSQL’s replication capabilities are designed primarily for scaling read workloads and providing disaster recovery options. Achieving truly synchronous read-only replication with guaranteed up-to-the-minute accuracy under heavy load presents significant challenges due to both architectural limitations and performance considerations.

The Challenge of Synchronous Replication in Azure PostgreSQL

Current Azure PostgreSQL Replication Architecture

Azure Database for PostgreSQL flexible server supports read replicas using asynchronous replication technology. These replicas are updated using PostgreSQL’s native physical replication[2][8]. While this approach efficiently scales read workloads, it comes with an important limitation:

“The feature is meant for scenarios where replication lag is acceptable, and is meant for offloading queries. It isn’t meant for synchronous replication scenarios where replica data is expected to be up to date.”[2]

The delay between primary and replica can range from seconds to minutes or even hours under heavy workloads[2]. This inherent lag means that read replicas cannot guarantee up-to-the-minute accuracy for read operations.

Configuration Limitations in Azure PostgreSQL

While PostgreSQL itself does support synchronous commit options through the synchronous_commit parameter, there appear to be significant limitations when using Azure’s managed service:

Users have reported being unable to set synchronous_commit to “remote_apply” in Azure PostgreSQL Flexible Server[10], which would be the necessary setting for ensuring that read replicas have applied all transactions before they become visible.
A Microsoft representative confirmed this limitation, noting: “Seems like it is not possible to change to parameters to ‘remote_apply’. It is not supported yet in Azure PostgreSQL Flexible.”[10]

Understanding PostgreSQL Synchronous Commit Options

Standard PostgreSQL offers several synchronous commit options that provide different levels of durability guarantees:

synchronous_commit = off: Transactions don’t wait for writes to be confirmed
synchronous_commit = local: Waits for transaction to be flushed to local disk
synchronous_commit = on: Waits for transaction to be confirmed as written to disk on standby servers
synchronous_commit = remote_write: Waits for transaction to be written to the standby’s operating system
synchronous_commit = remote_apply: Waits for transaction to be applied to the database on the standby[4]

The last option, remote_apply, would theoretically ensure that read replicas always have up-to-date data. However, this setting appears unavailable in Azure PostgreSQL Flexible Server.

Performance Implications of Synchronous Replication

Even if technically possible, synchronous replication comes with significant performance costs:

“With synchronous replication, the replication delay directly affects the elapsed time of transactions on the master.”[4]

This means that under heavy load, write performance would be severely impacted as each transaction would need to wait for confirmation from replica servers before completing.

Potential Alternatives and Workarounds

1. Monitor and Manage Replication Lag

Azure provides metrics for monitoring replication lag[8]. Applications could potentially:

Check the lag metric before critical read operations
Direct critical reads to the primary when fresh data is essential
Use replicas only for operations that can tolerate some staleness

2. Application-Level Solutions

Implement application-level caching or consistency strategies:

Write-through caching for critical data
Session affinity to direct related operations to the same server
Conditional reads based on timestamps or version numbers

3. Logical Replication

Azure Database for PostgreSQL flexible server supports logical replication[5], which might offer more granular control over replicated objects, though it remains asynchronous.

4. Load Balancing Solutions

Tools like pgpool (mentioned in the search results[10]) can help with load balancing between primary and replica servers, but they don’t solve the fundamental synchronicity issue.

Conclusion

Currently, achieving true synchronous read-only replication with guaranteed up-to-the-minute accuracy in Azure PostgreSQL is not directly supported through the platform’s built-in capabilities. The synchronous_commit parameter with remote_apply setting, which would provide this functionality, appears to be unavailable in Azure PostgreSQL Flexible Server at this time[10].

For applications requiring absolute read consistency, the safest approach is to direct those specific queries to the primary server, while using read replicas for queries that can tolerate some data staleness. This hybrid approach allows you to balance performance with data accuracy requirements.

For future developments, it’s worth monitoring whether Azure adds support for the synchronous_commit = remote_apply parameter, as this would enable truly synchronous read-replicas, albeit with a performance trade-off for write operations.

Sascha Corti's Digital Garden

Explorer