Maintenance

Vacuum performs key maintenance on PostgreSQL data files:

1. Recovers free space consumed by older versions of records under MVCC.
2. Updates statistics used by the query planner.
3. Updates the visibility map, speeding up index-only scans.
4. Prevents loss of very old data caused by transaction ID (multixact) wraparound.

Operations

• vacuum can run in parallel with other clients/operations.
• vacuum full requires an access exclusive lock. This should be avoidable if vacuum is performed routinely.

Autovacuum status

select relname as tablename
     , n_live_tup as livetuples
     , n_dead_tup as deadtuples
     , last_autovacuum as autovacuum
     , last_autoanalyze as autoanalyze
 from pg_stat_user_tables;

Autovacuum configuration

select category
     , name
     , setting
     , unit
     , source
     , min_val
     , max_val
  from pg_settings
 where category = 'Autovacuum';