Vacuum and analyze a table to reclaim space
Low risk
What do risk levels mean?
- Read-only
- Inspects state without changing anything.
- Low risk
- Reversible with a routine follow-up command.
- Medium risk
- Changes state; undo path documented.
- Destructive
- Deletes or overwrites; confirmation required.
Reclaims storage occupied by dead rows and refreshes query planner statistics so the optimizer makes better decisions.
postgresqlvacuummaintenanceoptimize
Parameters
Name of the table to vacuum and analyze.
Commands
Inspect dead-tuple count before vacuuming to see whether it is worthwhile
psql -c "SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = '<table_name>';"Reclaim dead-tuple space and refresh optimizer statistics
psql -c "VACUUM ANALYZE <table_name>;"Verification
psql -c "SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = '<table_name>';"The dead-tuple count is now zero or near zero.
Undo
Not undoable
VACUUM ANALYZE is irreversible but safe; it only defragments and updates statistics.
Pitfalls
- VACUUM ANALYZE acquires a SHARE UPDATE EXCLUSIVE lock which does not block reads or writes.
- On very large tables this may take minutes; consider VACUUM without ANALYZE first if time is tight.
- Autovacuum normally handles this; manual vacuum is useful after bulk deletes or before a major query.
- Does not return disk space to the operating system; use VACUUM FULL for that (which locks the table).