Reclaim space and keep planner statistics fresh in PostgreSQL with VACUUM, ANALYZE, and autovacuum

Documented steps

1. Routine case: plain VACUUM table_name; reclaims dead-tuple space for reuse without blocking reads or writes. Combine with statistics refresh: VACUUM (VERBOSE, ANALYZE) table_name;
2. Check whether autovacuum is keeping up: SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
3. For a severely bloated table, VACUUM FULL table_name; rewrites the table and returns space to the OS — but it takes an ACCESS EXCLUSIVE lock (blocks all reads and writes) and needs temporary disk space up to the table's size. Reserve for maintenance windows.
4. Monitor a running vacuum: SELECT * FROM pg_stat_progress_vacuum;
5. Watch transaction ID age to gauge wraparound risk: SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 10;
6. Review autovacuum settings: SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%'; — key knobs are autovacuum_naptime, autovacuum_max_workers, and autovacuum_freeze_max_age.
7. Official docs: https://www.postgresql.org/docs/current/sql-vacuum.html and https://www.postgresql.org/docs/current/routine-vacuuming.html