VACUUM
VACUUM is a SQL statement, used by databases like PostgreSQL and DuckDB, that performs maintenance such as reclaiming space from deleted rows or refreshing planner statistics.
Overview
VACUUM originated in PostgreSQL, where MVCC leaves behind "dead" row versions after updates and deletes; running VACUUM reclaims that space and prevents unbounded table bloat. The exact behavior of VACUUM differs significantly between database systems, so it's worth checking what it actually does in the one you're using rather than assuming Postgres semantics apply everywhere.
DuckDB's VACUUM
DuckDB only has basic support for VACUUM, provided mainly for SQL compatibility with Postgres-style tooling. Plain VACUUM is essentially a no-op in DuckDB — it does not reclaim disk space freed by deleted or updated rows. To actually reclaim space after large deletes, you need to run CHECKPOINT (or, in some cases, export and re-import the database).
What VACUUM does do in DuckDB is refresh statistics used by the query optimizer. VACUUM ANALYZE recomputes table- and column-level statistics — such as distinct-value counts — that may have gone stale after inserts, updates, or deletes, which helps the cost-based optimizer make better join-order and cardinality decisions:
Copy code
-- Recompute statistics for every table
VACUUM ANALYZE;
-- Recompute statistics for one table/column
VACUUM ANALYZE my_table(my_column);
Because DuckDB automatically maintains statistics as data is loaded, VACUUM ANALYZE is mainly useful after bulk updates or deletes that could leave existing statistics stale.
Related terms
A cost-based optimizer chooses among logically equivalent query plans by estimating the execution cost of each one, using statistics about table sizes and data distributions, and picking the cheapest estimate.
analyze →Analyze is an SQL statement used in various database systems, including DuckDB, to gather statistics about tables and columns.
Execution plan →An execution plan is the tree of operators a database will run, in a specific order, to produce the result of a query — the concrete strategy chosen by the query planner and optimizer.
DELETE statement →The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
MERGE statement →MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.
Query planner →The query planner is the database component that translates a parsed SQL statement into an execution plan — a tree of operators describing how the query will actually be run.
FAQS
No. Unlike PostgreSQL, plain VACUUM in DuckDB does not reclaim space from deleted rows. Use CHECKPOINT to reclaim space after deletes.
It recomputes stale table and column statistics, such as distinct-value counts, that the query optimizer uses to estimate cardinality and choose an efficient plan.
