hot_standby_feedback explanation

Francesco Canovai
Francesco Canovai

The hot_standby_feedback parameter is designed to inform the primary about open transactions on the secondary without introducing a delay in the stream.

In PostgreSQL, the transaction isolation guarantees that except when explicit locking is used, writers only block writers, and that readers will never block anything. This is implemented with MVCC, where updates cause new copies of the row to be written to disk. The new rows are marked with the transaction id of the writer in the hidden xmin or xmax fields, and are authoritative from when that transaction commits.

Those copied rows must be removed once no longer needed. Autovacuum is the process of removing the old row versions. Once there are no transaction, slot, etc that can ever need to read those rows, they can be removed and later overwritten.

A row may still be needed by a transaction on a streaming physical replica even if no transaction on the master still needs to see it. Normally autovacuum will remove the row on the master, and the vacuum operations would be replayed on the standby. The standby must then either cancel any queries that might still need this row, or pause redo until transactions that may still need it have finished. The max_standby_streaming_delay and max_standby_archive_delayparameters control how long a standby will wait to apply the WALs coming from the master before killing any conflicting transactions and continuing. Transactions will be aborted with:

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed

When reading from the heap or any index, the snapshot is vulnerable to showing incorrect data because the threshold has been crossed since it was generated, reading any page with an LSN past the snapshot LSN causes an error to be thrown. This usually occurs when a row is removed by VACUUM on the master while still being read on the slave. At the end of transaction an error will be thrown:

ERROR: snapshot too old.

(This error may also arise when the old_snapshot_threshold setting in postgres 9.6 and newer is used to limit snapshot validity.)

hot_standby_feedback provides an alternate solution by pushing the problem back to the master. Standbys will send their oldest needed xmin to the master in their replay feedback messages, and the master will consider them when making vacuuming decisions. The master avoids vacuuming away rows any standby still needs, so there's no need to pause redo or cancel queries on standbys. The price is bloat on the master; it cannot reclaim those row versions so heap and index space can be wasted. Even on tables or whole databases the standby queries aren't interested in.

So hot_standby_feedback is usually fairly lightweight. But it can be an issue for high-churn-rate tables like work queue tables, where bloat can massively decrease performance, so check for tables with very high insert/update/delete rates. There is no way to make hot_standby_feedback per-table or per-database.

If the downstream uses a replication slot (primary_slot_name in recovery.conf) then the xmin set by hot_standby_feedback is persistent, whether or not the replica is connected. If the replica does not use a slot, hot_standby_feedback is reset when the replica is not connected, so a crashed replica won't cause excess bloat on the master, but network issues can cause query cancels on a replica.

PostgreSQL 10 and above sends the standby's global catalog_xmin and xmin separately in hot_standby_feedback messages. This prevents slots with a catalog_xmin on the standby setting xmin on the master and causing a lot of bloat.

Was this article helpful?

0 out of 0 found this helpful