PostgreSQL Tutorial: Check long running transactions on standby

Summary: In this tutorial, you will learn how to check long running transactions on standby in PostgreSQL.

Table of Contents

Queries on Standby with hot_standby_feedback Enabled

In a streaming replication setup with hot_standby_feedback enabled, queries on the standby server can prevent VACUUM from reclaiming dead tuples on the primary. This is by design: hot_standby_feedback exists specifically to prevent dead tuple removal by VACUUM and avoid query conflicts on the standby.

Set up a streaming replication environment, change configuration postgresql.conf, enable hot_standby_feedback on the standby, and reduce wal_receiver_status_interval to increase the frequency of feedback from the standby to the primary.

hot_standby_feedback = on
wal_receiver_status_interval = 1s

On the standby, start a transaction and execute a query.

BEGIN;

SELECT pg_sleep(300);
 pg_sleep
----------

(1 row)

Note: We use pg_sleep() to prevent the snapshot from being released immediately when the query completes. You could also set the transaction isolation level to REPEATABLE READ to maintain the snapshot within the transaction.

On the primary, delete data and run VACUUM.

DELETE FROM t;

VACUUM (verbose) t;
INFO:  00000: vacuuming "postgres.public.t"
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 831, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 12 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

The message tuples: 0 removed, 100 remain, 100 are dead but not yet removable indicates that dead tuples were not reclaimed. The message removable cutoff: 831, which was 1 XIDs old when operation ended suggests that transaction ID 831 is the culprit.

Standby with hot_standby_feedback and VACUUM

Typically, PostgreSQL can clean up a row version as soon as it isn’t visible to any transaction. If you’re running PostgreSQL on a primary with a standby node, it’s possible for a vacuum to clean up a row version on the primary which is needed by a query on the standby. This situation is called a “replication conflict” — and when it’s detected, the query on the standby node will be cancelled.

To prevent queries on the standby node from being cancelled due to replication conflicts, you can set hot_standby_feedback to on, which will make the standby inform the primary about the oldest transaction running on it. As a result, the primary can avoid cleaning up rows which are still being used by transactions on the standby.

However, setting hot_standby_feedback to on also means that long running queries on the standby have the capability to block rows from getting cleaned up on the primary.

List long running transactions on standby

To find out the xmin of all standby servers, you can run the following query on the primary server:

SELECT pid, application_name, client_addr, state, backend_xmin
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Once the standby is identified, you can find the query holding back the xmin horizon and its connection’s pid in that standby by running the following command:

SELECT pid, datname, usename, state, backend_xmin, backend_xid
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY greatest(age(backend_xmin), age(backend_xid)) DESC;

To avoid having excessive bloat on the primary due to long-running transactions on the standby, you can take one of the following approaches:

• Continue dealing with replication conflicts and set hot_standby_feedback to off.
• Set vacuum_defer_cleanup_age to a higher value—in order to defer cleaning up rows on the primary until vacuum_defer_cleanup_age transactions have passed, giving more time to standby queries to complete without running into replication conflicts.
• Lastly, you can also track and terminate long running queries on the standby like we discussed for the primary in the long running transactions tutorial.

See more

PostgreSQL Monitoring