Skip to content
Tutorials Center
My learning

Topics

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.

SQL
VACUUM;
ANALYZE;
ANALYZE orders;

Try it

SQL Create an index, ANALYZE and read the statistics ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL VACUUM after deleting rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 VACUUM runs.
  • VACUUM needs 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, VACUUM takes an exclusive lock for its duration. Plan it for quiet periods.
  • VACUUM can change rowid values of tables without an explicit INTEGER PRIMARY KEY. Never rely on implicit rowid values staying the same.
  • PostgreSQL rarely needs manual vacuuming. Autovacuum handles it, and VACUUM FULL is a last resort because it blocks access to the table.
  • Statistics go stale. After a bulk load, or when data changes shape, run ANALYZE so plans stay good.
  • In the in-browser examples each run starts from a fresh in-memory database, so VACUUM has no file to shrink. The statement runs, but you cannot see a size change.