SQL Reference ยท Data Definition
SQL VACUUM and ANALYZE statements
Reference for VACUUM and ANALYZE: reclaim space, rebuild a database file, refresh optimizer statistics, and the equivalent maintenance in each dialect.
VACUUM and ANALYZE are maintenance statements. VACUUM reclaims the space left behind by deleted or updated rows and tidies how data is stored. ANALYZE gathers statistics about table and index contents so the query planner can choose better plans.
Neither changes the data you see in a query. They change how the database stores it and how quickly it finds it. They are not in the SQL standard, but PostgreSQL, SQLite and a few others provide them under these names, and every other system offers something similar under a different name.
VACUUM; ANALYZE; ANALYZE orders;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
VACUUM [schema_name] [INTO 'new_file'];
ANALYZE;
ANALYZE table_or_index_name;
| Part | Description |
|---|---|
schema_name |
Optional. Which attached database to vacuum. Defaults to main. |
INTO 'new_file' |
Writes a compacted copy to a new file and leaves the original unchanged. Usable as a simple backup. |
table_or_index_name |
Optional. Limits ANALYZE to one table or index instead of the whole database. |
Effect: VACUUM rebuilds the whole database file, which can shrink it. ANALYZE stores its findings in internal tables such as sqlite_stat1.
Dialect differences
| Dialect | Reclaiming space | Statistics |
|---|---|---|
| SQLite | VACUUM rebuilds the file. PRAGMA auto_vacuum can free pages automatically, and PRAGMA optimize runs ANALYZE only where it is likely to help. |
ANALYZE |
| PostgreSQL | VACUUM marks dead rows reusable without locking out readers, VACUUM FULL rewrites the table and locks it, and autovacuum runs by itself. |
ANALYZE, or VACUUM ANALYZE together |
| MySQL | OPTIMIZE TABLE rebuilds an InnoDB table. |
ANALYZE TABLE |
| SQL Server | ALTER INDEX ... REORGANIZE or REBUILD; DBCC SHRINKFILE shrinks files. |
UPDATE STATISTICS, and automatic statistics |
| Oracle | ALTER TABLE ... SHRINK SPACE, ALTER TABLE ... MOVE. |
DBMS_STATS.GATHER_TABLE_STATS |
-- PostgreSQL VACUUM (ANALYZE, VERBOSE) orders; -- MySQL OPTIMIZE TABLE orders; ANALYZE TABLE orders; -- SQL Server UPDATE STATISTICS dbo.orders;
Usage notes
- Deleting does not shrink an SQLite file. The freed pages are reused by later inserts, but the file keeps its size until
VACUUMruns. VACUUMneeds free disk space. SQLite builds a full copy, so it may need up to twice the database size, and it cannot run inside a transaction.- It blocks writers. In SQLite,
VACUUMtakes an exclusive lock for its duration. Plan it for quiet periods. VACUUMcan changerowidvalues of tables without an explicitINTEGER PRIMARY KEY. Never rely on implicitrowidvalues staying the same.- PostgreSQL rarely needs manual vacuuming. Autovacuum handles it, and
VACUUM FULLis a last resort because it blocks access to the table. - Statistics go stale. After a bulk load, or when data changes shape, run
ANALYZEso plans stay good. - In the in-browser examples each run starts from a fresh in-memory database, so
VACUUMhas no file to shrink. The statement runs, but you cannot see a size change.