PostgreSQL VACUUM: Check Old Transactions Before Tuning Cleanup

Imagine a billing app that updates invoices throughout the day. The invoice table keeps growing even though autovacuum has run. A reporting session started a transaction earlier and is now sitting idle. Before changing autovacuum thresholds, find out whether that session is still holding a transaction horizon that cleanup must respect.

Check transaction age and the transaction IDs PostgreSQL reports for active sessions. An old transaction can keep obsolete row versions around while PostgreSQL still needs them for visibility. Running VACUUM more often cannot make those versions safe to remove. An old connection or a recent autovacuum timestamp alone tells you little about which rows can be cleaned up. PostgreSQL explains routine vacuuming.

Start with the sessions that can hold back cleanup

Use a fresh monitoring query outside a long-lived transaction. A role with appropriate monitoring access can see more details for other sessions than an ordinary application role. This query avoids pulling SQL text, which can contain sensitive values. Start with the database where the table lives, then investigate cluster-wide sources separately if needed.

SELECT pid, application_name, state,
       now() - xact_start AS transaction_age,
       backend_xid, backend_xmin,
       age(backend_xmin) AS xmin_age_in_transactions
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND xact_start IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST, xact_start
LIMIT 20;

Read the columns together. xact_start measures elapsed time, while backend_xmin is the session’s reported visibility horizon. Its age is in transaction IDs, not minutes. backend_xid is the session’s assigned top-level transaction ID, if it has one. A null backend_xmin therefore isn’t enough to clear a session. An old horizon that persists across samples deserves investigation, especially when the session is idle in a transaction. The query is a shortlist, not a proof that one backend caused the growth. PostgreSQL defines these activity fields.

Check whether the table tells the same story

SELECT n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relid = to_regclass('public.invoices');

Substitute the actual schema and table. Dead and live tuple counts are estimates. A rising dead-tuple estimate alongside a persistent old horizon is a reason to investigate the transaction. It doesn’t measure how much disk space is recoverable. If a vacuum is running, check pg_stat_progress_vacuum for its current phase and progress. Also remember that ordinary VACUUM generally makes space reusable within the table. The file can remain large even after cleanup improves. The statistics reference covers table estimates and progress views.

Fix the transaction or follow the next lead

Trace the session to its application owner. If the app has finished its work, fix the code path so it commits or rolls back before waiting on a network call or returning a connection to the pool. Coordinate any decision to end a live session. Uncommitted work can roll back, and the client may retry. For a recurring idle transaction, test a role-specific idle_in_transaction_session_timeout and verify that the pool handles a closed connection before using it in production. The timeout applies to idle sessions inside transactions, so an active long-running report needs a different decision. PostgreSQL documents the timeout.

If the session evidence is weak, check other possible sources of an old horizon, including prepared transactions and replication slots that retain an xmin. Then look at update rate, vacuum progress and per-table autovacuum settings. After an approved fix, take fresh activity and table samples across a cleanup cycle. Look for the old horizon to clear and the dead-tuple trend to improve. Avoid expecting the disk file to shrink immediately. See replication slot statistics for the separate xmin and WAL-retention fields.

If you’re building a disposable environment for this check, Krova’s PostgreSQL setup guide covers running the database on a Cube.

This guide was prepared with AI assistance using the linked documentation. The billing app and SQL examples are illustrative. The queries were not run against a database for this article.