VACUUM
VACUUM cleans up dead rows left behind by updates and deletes so the space can be reused.
In this page:
Syntax
VACUUM table_name;
VACUUM ANALYZE table_name;VACUUM
PostgreSQL's MVCC keeps old row versions until no transaction needs them, and VACUUM removes those dead tuples and updates visibility information.
Autovacuum runs it automatically. VACUUM (ANALYZE) also refreshes statistics, and VACUUM FULL rewrites the table to return space to the OS but takes an exclusive lock.
Note:
Keep autovacuum enabled and tune it for busy tables.
Example: VACUUM
shop=# VACUUM (VERBOSE, ANALYZE) orders;
INFO: vacuuming "public.orders"
INFO: table "orders": removed 12034 dead row versions in 310 pages
shop=# SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'orders';
relname | n_live_tup | n_dead_tup | last_autovacuum
---------+------------+------------+-------------------------------
orders | 250000 | 0 | 2024-03-15 09:12:44.51+00⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Disabling autovacuum
- Running VACUUM FULL on busy tables
- Long-running transactions blocking cleanup
Chapter Summary
- VACUUM removes dead rows
- Autovacuum does it automatically
- ANALYZE refreshes statistics
- VACUUM FULL locks the table
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: