A PostgreSQL transaction can remain idle while still preserving a visibility horizon that constrains cleanup elsewhere. Rows updated or deleted after that transaction acquired its snapshot may become obsolete for newer transactions, yet some older versions can remain potentially visible to the retained snapshot. VACUUM cannot reclaim a row version merely because the newest application state no longer references it.
This is a direct consequence of multiversion concurrency control. Visibility and physical reclamation are separate decisions: one transaction changes which row version is current, while the database must retain versions that can still be observed by relevant snapshots.
Updates create versions rather than replacing bytes in place
PostgreSQL represents an update by creating a new row version and marking the previous version with transaction metadata that affects visibility. A delete similarly makes a version no longer visible to transactions whose snapshots place the deleting transaction in their visible past.
The old version does not instantly become removable. PostgreSQL has to account for transactions that can still see database state from before the update or delete became visible.
Consider a transaction that obtains a snapshot while a row contains status queued. A second transaction later changes that row to running and commits. New snapshots can see running, while the older snapshot can still require the prior version under isolation modes that retain the snapshot for later statements.
The storage engine therefore has two distinct facts:
logical state for new snapshots: running
older version still potentially visible: queuedA cleanup decision has to respect the second fact even though application traffic may mostly observe the first.
VACUUM is bounded by visibility requirements
Routine VACUUM reclaims storage occupied by row versions that are no longer needed by any relevant transaction. It also performs other maintenance, including visibility-map work and transaction ID freezing, but dead-version reclamation depends on proving that old versions are no longer potentially visible.
A long-lived snapshot can delay that proof. The important property is not simply wall-clock transaction duration. The constraint comes from the transaction state and visibility horizon that PostgreSQL must preserve.
This distinction matters when diagnosing a table with persistent dead tuples. Frequent autovacuum activity does not imply that every obsolete-looking version is immediately reclaimable. A vacuum can run successfully and still leave versions whose removal would violate visibility semantics.
The effect can span tables that the old transaction has not recently queried. Snapshot visibility is tied to transaction ordering, not to a list of rows the application remembers reading.
Isolation level changes snapshot lifetime
Snapshot retention depends on transaction isolation behavior.
At PostgreSQL’s default READ COMMITTED level, each command receives a snapshot that sees transactions committed before that command begins. A transaction that remains open between statements does not necessarily retain one statement snapshot for its entire lifetime.
At REPEATABLE READ, a transaction observes a stable snapshot across its ordinary queries after the snapshot is established. SERIALIZABLE also relies on transaction-level snapshot semantics, with additional serialization conflict tracking.
This makes the phrase “long transaction blocks vacuum” too coarse as a universal rule. A long transaction can matter, but the exact cleanup boundary depends on whether it holds an old transaction ID or snapshot horizon relevant to vacuum, along with other PostgreSQL mechanisms that can retain visibility state.
Operational analysis should therefore use PostgreSQL’s reported transaction and snapshot-related state rather than inferring retention solely from connection age.
Idle sessions can preserve active transaction state
An application can finish its immediate database work yet leave a transaction open. Connection pools, missing commit paths, exception handling, and explicit transaction scopes can all produce sessions that are idle from the application’s perspective while PostgreSQL still considers the transaction active.
Such a session is materially different from an idle connection with no open transaction. The latter does not preserve transactional visibility merely by remaining connected.
This boundary is visible in server activity state. pg_stat_activity distinguishes sessions such as idle and idle in transaction, and exposes transaction timing and backend transaction identifiers where applicable. Those signals allow retention analysis to focus on database state rather than TCP connection lifetime.
Terminating an old transaction removes its visibility claim only by ending that transaction. If the session is doing legitimate work that requires a stable historical view, termination changes application behavior. Cleanup pressure and snapshot semantics are therefore competing constraints rather than a maintenance switch with no semantic cost.
Dead tuple counts are evidence, not a complete retention map
PostgreSQL statistics can report estimated dead tuple counts, and vacuum logging can expose cleanup activity. These are useful observables, but they do not encode every reason that physical table size remains large.
Standard VACUUM generally makes reclaimed space available for reuse inside the relation. It does not normally compact the entire table file and return all free space to the operating system. VACUUM FULL uses a different, more intrusive rewrite strategy and requires stronger locking.
As a result, three states must not be collapsed into one metric:
row version is no longer visible
row version is reclaimable
relation file becomes smallerThe first is a logical visibility property. The second depends on database cleanup horizons. The third depends on storage layout and the vacuum operation used.
A table can therefore have completed useful vacuum work without a proportional decrease in filesystem size. Conversely, relation growth can have causes beyond one old snapshot, including sustained update volume and index behavior.
Replication and prepared transactions add separate retention boundaries
Old application snapshots are not the only state that can constrain PostgreSQL cleanup.
Prepared transactions can retain transaction state across sessions until they are explicitly committed or rolled back. Replication slots can also retain database resources according to their configured and observed state; fields such as xmin and catalog_xmin expose horizons relevant to row or catalog cleanup for applicable slot types.
These mechanisms should be separated during diagnosis because their remediation differs. Ending an application transaction does not advance a horizon retained by an old prepared transaction or a replication slot. Dropping a replication slot has consequences for the consumer that depends on it and is not equivalent to clearing an idle client session.
The shared mechanism is retention of history that some database participant can still require. The participants and guarantees differ.
Vacuum frequency cannot override a retained horizon
Increasing autovacuum frequency can reduce the delay between a row version becoming reclaimable and PostgreSQL processing it. It cannot make a still-visible version safe to remove.
That limit prevents a common configuration error: treating more aggressive cleanup scheduling as a substitute for resolving old transactional state. If the oldest relevant horizon does not advance, repeated vacuum work remains constrained by the same visibility requirement.
Autovacuum settings still matter. Tables with heavy update or delete activity need timely maintenance, and PostgreSQL uses vacuum for several purposes beyond dead tuple reuse. The narrower point is that scheduling and eligibility are independent controls.
A useful diagnostic sequence therefore compares change rate, vacuum activity, and retained transaction horizons. A high dead-version estimate with an old active horizon suggests a different mechanism from a table that simply has not been vacuumed often enough.
Snapshot retention converts logical history into storage pressure
MVCC permits readers and writers to proceed with less direct blocking because readers can observe suitable row versions rather than requiring every update to wait for every read. The cost is version history that must remain available until no relevant observer can require it.
Long snapshot lifetimes extend that history window. On update-heavy tables, more superseded versions can accumulate during the interval. The exact storage effect depends on workload, tuple sizes, page reuse, indexes, vacuum timing, and other database state, so transaction age alone does not imply a fixed amount of growth.
The mechanism is still deterministic at the semantic boundary: PostgreSQL cannot reclaim a version that remains potentially visible under its MVCC rules.
This makes transaction lifetime part of storage behavior. A transaction boundary is not only an atomicity boundary for application changes; when it retains an old visibility horizon, it can also determine how long earlier row versions must remain present in the physical database.