What do VACUUM, ANALYZE and autovacuum do?

Definition:

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.

Key points:

• 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.

Interview point:

• Distinguish reclaiming space for reuse from returning disk space to the operating system.