Skip to content
Quantoros

What does VACUUM do in PostgreSQL?

SQLLevel: IntermediateDatabase Design

What does VACUUM do in PostgreSQL?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

(PostgreSQL-specific)

PostgreSQL uses MVCC (Multi-Version Concurrency Control) — when you update or delete a row, the old version isn't immediately removed. It's marked as dead but still takes up space. VACUUM reclaims that space and cleans up dead row versions.

VACUUM ANALYZE also updates the table statistics that the query planner uses to make decisions.

AUTOVACUUM runs automatically in the background. You generally don't need to run VACUUM manually unless you've done a large bulk delete/update.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions