What do VACUUM, ANALYZE and autovacuum do?
Understand the concept
PostgreSQL keeps older row versions for concurrent readers, so updates and deletes can leave dead versions behind. VACUUM makes eligible dead space reusable and supports visibility and transaction-ID maintenance; ANALYZE collects statistics for planning queries. Autovacuum runs this maintenance in the background but can be limited by long-running transactions or workload settings.
Important ideas
- VACUUM reclaims dead-row space for reuse; it usually does not shrink the file.
- ANALYZE refreshes data statistics used by the query planner.
- Autovacuum automates maintenance; long transactions can stop old row versions from being removed.
Reference: PostgreSQL VACUUM.
- VACUUM FULL rewrites a table and has different locking and operational costs from ordinary VACUUM.
Can you explain it without the notes?
What do VACUUM, ANALYZE and autovacuum do?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
PostgreSQL keeps older row versions for concurrent readers, so updates and deletes can leave dead versions behind. VACUUM makes eligible dead space reusable and supports visibility and transaction-ID maintenance; ANALYZE collects statistics for planning queries. Autovacuum runs this maintenance in the background but can be limited by long-running transactions or workload settings.
Turn this concept into a clear answer.
- Distinguish reclaiming space for reuse from returning disk space to the operating system.
Keep connecting the ideas.
Next lesson: JSON vs JSONB, and views vs materialized views?Adapted from 05_SQL_POSTGRESQL.md · “What do VACUUM, ANALYZE and autovacuum do?”. Examples are study fragments; framework behavior can depend on your version.