Vacuum, configuration, and capacity | PostgreSQL: operations and recovery

Concept and mechanism

UPDATE and DELETE leave older versions that MVCC may still need to show. Vacuum makes space reusable once those versions are unnecessary and contributes to wraparound protection. File size can remain similar after useful maintenance: ordinary vacuum does not compact the whole table to return space to the operating system. VACUUM FULL rewrites the table, needs extra space and ACCESS EXCLUSIVE, and therefore requires planning. An old transaction can prevent removal of still-visible versions. More frequent vacuum does not remove that visibility requirement. Distinguish maintenance backlog, snapshot retention, and legitimate volume growth.

Guided application

In a fictional low-disk incident, separate tables, indexes, temporary files, and WAL before choosing action. A stopped slot or unavailable archive destination can retain WAL; deleting files directly is not a retention policy. For configuration, compare effective value, source, and context: reloading files does not apply restart-only parameters or remove every session override. Alerts should anticipate time to capacity exhaustion and transaction age rather than only current unavailability. Shift handover records trend, limit, owner, agreed action, and escalation condition. A temporary mitigation needs a deadline and confirmation that the cause was corrected.

IN PRACTICE

Completed VACUUM and unchanged file size can coexist with reusable internal space.

Common pitfalls

FULL as impact-free maintenance; disabling autovacuum; configuration file as effective state.

Related topics: Connections, identities, and privileges · Transactions, blocking, and resumption · Plans, indexes, and memory

Take this idea with you

Preserve operational headroom and investigate the retention mechanism.

Create account

Reference: Vacuum and freezing · PostgreSQL 18 reference semantics;18.6 current stable at review