PostgreSQL vacuum lag: investigate the transaction horizon before tuning

Vacuum can run without being able to remove every old version

PostgreSQL keeps row versions so transactions can see a consistent view of data. Vacuum maintenance can reclaim versions that are no longer needed, but an old transaction or other retention boundary can keep some versions relevant. Seeing an autovacuum process run does not prove every dead-row problem is resolved.

This guide investigates retention and maintenance evidence. It distinguishes reusable space inside database files from space returned to the operating system. It does not recommend VACUUM FULL as a routine first response to a growing table.

Setup: connect growth with the workload timeline

Record the PostgreSQL version, database, affected tables, write workload and maintenance history. Review table statistics and autovacuum activity alongside transaction age and storage trends. Use an account with appropriate monitoring privileges, and avoid publishing SQL text or session details that contain customer data.

An illustrative read-oriented session inventory is:

SELECT pid, state, xact_start, backend_xmin
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Interpret the result in the context of the installed version and workload. An old session is not automatically a harmful transaction, and one query does not enumerate every possible retention source. Investigate replication-related retention and prepared transactions when the evidence points there.

Illustrative incident: an idle transaction survives an import

In this illustrative scenario, an application leaves a transaction open after a reporting request. A separate import updates many rows. Maintenance runs, but the old transaction still requires a historical visibility boundary, so the team does not observe the cleanup it expected.

The database and application owners trace the old transaction to a connection-handling path. They define how to close it safely and prevent recurrence. They do not terminate every old-looking session without understanding what it owns or whether its business operation can be retried.

Troubleshooting: separate retained versions from maintenance capacity

Statistics are indicators rather than an exact inventory of physical table contents. Compare more than one observation and account for when statistics were collected. Keep wraparound protection concerns separate from an ordinary space-efficiency review; the urgency and response can be different.

Correct the retention cause before adding maintenance load

Resolve the responsible transaction lifecycle, retention configuration or maintenance bottleneck with the relevant owner. An application fix may require shorter transactions, explicit cleanup and timeouts suited to the business operation. Test failure and cancellation paths as well as successful requests.

Review vacuum tuning only with measured table behavior and available I/O capacity. Do not schedule a table rewrite or aggressive maintenance blindly on a busy production database. Preserve a recovery plan, understand locking and storage implications, and monitor replica effects before selecting a disruptive operation.

Verification, monitoring and SOP

Confirm the unwanted retention boundary is gone, maintenance makes progress and application behavior remains correct. Observe another comparable write cycle rather than expecting one immediate filesystem number to prove success. Include transaction-ID age, long-transaction duration and maintenance completion in the monitoring plan.

An internal SLO can set a reviewed maintenance and transaction-duration envelope; it is different from a customer-facing database SLA. The SOP should identify session ownership, safe termination criteria, retained-version evidence, maintenance limits, escalation and recovery acceptance. Record whether the objective was reusable capacity, OS space recovery or protection from transaction-ID exhaustion.

See Clouvenix postgresql administration for a reviewed implementation and operational handover.

Technical reference

PostgreSQL routine vacuuming, PostgreSQL statistics and activity views. Reviewed 19 October 2026; use documentation for the installed product and release.