Analysis and Solutions for Query Conflicts in PostgreSQL Hot Standby Mode

Problem Background and Error Analysis

In PostgreSQL's Hot Standby mode, when executing long-running queries on standby servers, administrators may encounter the "canceling statement due to conflict with recovery" error. This error typically occurs when query duration exceeds specific thresholds, particularly when queries involve tables that are frequently updated on the primary server.

The root cause lies in the data consistency maintenance mechanism between primary and standby servers. When the primary server performs update or delete operations, it generates corresponding WAL (Write-Ahead Logging) records. Standby servers maintain data synchronization by replaying these WAL records. However, conflicts arise when running queries on standby servers require access to old row versions that are about to be cleaned up.

Detailed Conflict Generation Mechanism

PostgreSQL employs Multi-Version Concurrency Control (MVCC) to manage data access. When rows are updated or deleted, the system retains old versions of row data until no active transactions require access to these old versions. On the primary server, the VACUUM process is responsible for cleaning up these unnecessary old row versions.

The situation becomes more complex in hot standby environments. The primary server remains unaware of queries running on standby servers, so it executes VACUUM operations according to normal cleanup policies. When standby servers receive cleanup WAL records from the primary, they must replay these operations, which may prevent running queries from accessing required row versions, thereby triggering recovery conflicts.

Core Solution Strategies

Configuring Delay Parameters

The most direct solution involves adjusting the max_standby_archive_delay and max_standby_streaming_delay parameters to control how standby servers handle recovery conflicts. These parameters define the maximum time queries are allowed to continue running when recovery conflicts occur.

Configuration example:

# In standby server's postgresql.conf file
max_standby_archive_delay = 900s
max_standby_streaming_delay = 900s

This configuration allows queries with durations up to 900 seconds to run normally on standby servers without being interrupted by recovery conflicts. For businesses requiring longer-running queries, these parameters can be set to higher values, or even to -1 to completely disable query cancellation.

Hot Standby Feedback Mechanism

Another solution involves enabling the hot_standby_feedback parameter. When set to on, standby servers feedback information about currently running transactions to the primary server, preventing the primary from cleaning up row versions still needed by standby servers.

Configuration example:

# In standby server's postgresql.conf file
hot_standby_feedback = on

While this method effectively prevents recovery conflicts, it's important to note that it may cause table bloat on the primary server due to the retention of more old row versions.

Primary Server Cleanup Delay

Another approach involves adjusting the vacuum_defer_cleanup_age parameter on the primary server to delay cleanup operations. This parameter specifies the number of transactions VACUUM should wait before cleaning up dead rows, providing a larger time window for queries on standby servers.

Configuration example:

# In primary server's postgresql.conf file
vacuum_defer_cleanup_age = 1000

Solution Comparison and Selection

Different solutions suit different business scenarios:

max_standby_streaming_delay approach: Suitable for scenarios primarily concerned with standby server performance without worrying about primary server impacts. This method confines effects to the standby server without causing side effects on the primary.

hot_standby_feedback approach: Appropriate for critical business scenarios requiring guaranteed query reliability, while accepting potential primary server table bloat risks. This method proves particularly effective when running long analytical queries or report generation on standby servers.

vacuum_defer_cleanup_age approach: Serves as an auxiliary measure that can be combined with the above methods to provide additional protection layers.

Practical Configuration Recommendations

For most production environments, a progressive configuration strategy is recommended:

First, set reasonable max_standby_streaming_delay values based on typical business query runtimes. For example, if most queries complete within 5 minutes, set it to 300 seconds.

If recovery conflicts persist, consider enabling hot_standby_feedback, but closely monitor table bloat on the primary server.

For particularly critical long-running queries, consider combining multiple methods and appropriately increasing vacuum_defer_cleanup_age values on the primary server.

Monitoring and Troubleshooting

After implementing solutions, establish effective monitoring mechanisms:

Monitor query cancellation counts and delay situations on standby servers to ensure configuration parameters achieve desired effects.

Regularly check table bloat conditions on primary servers, especially when hot_standby_feedback is enabled.

Utilize PostgreSQL system views like pg_stat_database and pg_stat_activity to track query performance and conflict situations.

Through proper configuration and continuous monitoring, recovery conflict issues in PostgreSQL hot standby environments can be effectively managed, ensuring standby servers reliably support read operations while maintaining data consistency with primary servers.