Updates can leave an older version
PostgreSQL MVCC can retain an older row version after an update because a snapshot acquired earlier may still need it. Visibility of the new value and permission to remove the old physical version are different boundaries. Repeated updates can therefore create space awaiting cleanup even while the logical row count stays constant. The relevant criterion is whether the version can still be needed under PostgreSQL’s visibility rules, not simply how many seconds have elapsed. This guide follows ordinary VACUUM rather than assuming that every old version disappears immediately after another session observes the new value.
Trace an old snapshot
Consider an illustrative Repeatable Read session A whose first relevant SELECT observes value ten. Session B updates it to twenty and commits. A can continue ordinary reads using its earlier snapshot, so the version it needs cannot be reclaimed merely because B committed. Ending A may remove one constraint, but other sessions or retention requirements can remain. Do not infer that closing one long transaction guarantees immediate complete cleanup. The example explains visibility conditions and is not an executed production database experiment. Record the actual isolation level and snapshot establishment point when reproducing a similar schedule.
Distinguish ordinary VACUUM from FULL
Ordinary VACUUM makes eligible space reusable within the relation; it does not promise to shrink every table file and return all freed capacity to the operating system. VACUUM FULL can compact a relation through rewriting, but its locking and temporary-space requirements differ. A file size that does not fall is therefore not sufficient evidence that ordinary vacuuming failed. Evaluate internal space reuse separately from external file-length reduction. Choosing a rewrite solely because a disk chart looks large can impose unnecessary disruption. The desired outcome and operational cost should determine which maintenance action is appropriate.
Freezing is not the same as dead-tuple cleanup
Transaction identifiers occupy a finite space, so freezing old identifiers is essential to continued visibility interpretation. Although performed as part of related maintenance, freezing is not identical to removing dead tuple versions. A table with few dead tuples can still require age-related work. Conversely, old snapshots can constrain which versions may be removed. Monitor identifier age and maintenance progress alongside update volume and retention causes. Counting only dead tuples misses an important part of the maintenance contract. Plan with headroom rather than treating protective intervention near wraparound limits as a normal substitute for routine maintenance.
Relate observations to specific causes
Inspect long transactions through activity views and compare them with maintenance progress and table statistics. Statistics can be estimated or updated asynchronously, so one counter may not reconstruct every physical row version exactly. A slow VACUUM can reflect work volume, I/O, cost settings, contention, or retained snapshots. Narrow the cause before stopping sessions or making extreme parameter changes. Also separate read-only observation from a decision to initiate extra production maintenance. Appropriate privileges and operating procedures matter. A collection of diagnostic views supports a hypothesis; it does not by itself justify a disruptive remedy.
Check visibility and subsequent reads
Vacuuming also updates information such as the visibility map, which affects whether an index-only scan must consult the heap. Merely including all requested columns in an index does not ensure zero heap access. Compare plans under consistent data and query conditions before and after maintenance. ANALYZE’s distribution statistics and VACUUM’s version cleanup have different purposes even when an operational command combines them. Connect each proposed action to the expected change. File size, reusable space, planning accuracy, and tuple visibility are different observations; a single success label obscures which maintenance function actually helped.