Autovacuum is PostgreSQL’s automatic table-maintenance process. It runs VACUUM and/or ANALYZE when needed, removing dead row versions and cleaning indexes so freed table space can be reused. It also freezes old rows to protect against transaction ID (XID) wraparound, while ANALYZE gathers statistics that help the query planner choose suitable plans. section 6.7
Disabling autovacuum because it consumes resources is a tempting short-term performance fix and a long-term operational mistake. Dead tuples accumulate as bloat, disk use and query costs grow, planner statistics become inaccurate, and automatic anti-wraparound work can eventually be forced. PostgreSQL still watches for XID exhaustion even when autovacuum is disabled. section 6.7 section 6.13
MVCC makes this maintenance necessary: old row versions cannot be removed while an open transaction might still need to see them. An idle-in-transaction session that modified data, or one using REPEATABLE READ or SERIALIZABLE, can prevent cleanup and can block vacuum from proceeding. Long-running queries and open cursors similarly retain snapshots; autovacuum skips dead-tuple cleanup when those snapshots predate the change. section 6.5.1 section 6.5.2
Treat these as warning signs and responses:
- Find actively running long queries through pg_stat_activity; use now() - query_start to measure execution duration rather than time spent blocked. section 6.5.2
- Prevent unreasonable idle transactions with idle_in_transaction_session_timeout, but set the timeout high enough for legitimate transaction-side processing. section 6.5.1
- When write volume burns XIDs rapidly, batch commits, reorganize incoming data, reduce unnecessary ingestion, and tune autovacuum more aggressively. section 6.6.2
- Assess and adjust autovacuum settings before tables accumulate massive bloat rather than waiting for performance to collapse. section 11.2
An idle transaction can postpone cleanup
A session that begins a transaction and leaves it open after reading or changing a table retains its transaction state. VACUUM must wait for needed locks, and recently dead rows may remain because that transaction could still require visibility of them. Ending the transaction releases the obstruction; otherwise bloat can grow and prolonged prevention of autovacuum raises XID-wraparound risk. section 6.5.1
