VACUUM / ANALYZEAdministration
Buy Me A Coffee

Maintenance operations that reclaim storage from dead rows (VACUUM) and update query planner statistics (ANALYZE), keeping performance stable over time.

PostgreSQL command that updates statistics about the distribution of data in tables and indexes. The query planner uses these statistics to choose optimal execution plans. Run after significant data changes.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 OPTIMIZE TABLE reclaims space from deleted rows in InnoDB and MyISAM. ANALYZE TABLE updates index statistics used by the optimizer. Both can be run online (non-blocking) in InnoDB since 5.6. No VACUUM keyword.
PostgreSQL ✓ Supported 7.4 VACUUM reclaims storage from dead tuples created by MVCC updates/deletes. VACUUM FULL rewrites the table (locks table, reclaims more space). ANALYZE updates planner statistics. Autovacuum runs both automatically. VACUUM FREEZE prevents transaction ID wraparound.
SQL Server ✓ Supported 2000 No VACUUM equivalent — SQL Server uses a separate background thread for ghost record cleanup. UPDATE STATISTICS or sp_updatestats updates query planner statistics. DBCC SHRINKFILE / SHRINKDATABASE reclaims space (use sparingly — causes fragmentation). REBUILD INDEX is the primary defragmentation tool.
Oracle ✓ Supported 7 Oracle uses UNDO segments rather than dead tuple storage, so no VACUUM equivalent is needed. DBMS_STATS.GATHER_TABLE_STATS updates optimizer statistics. Segment shrink (ALTER TABLE ... SHRINK SPACE) reclaims space below the high-water mark.
SQLite ✓ Supported 3.0.0 VACUUM rewrites the entire database file to reclaim space from deleted rows and reduce fragmentation. ANALYZE updates statistics in the sqlite_stat tables used by the query planner. Both are manual operations; no auto-vacuum by default (can be enabled with PRAGMA auto_vacuum).

Details

PostgreSQL is unique in requiring explicit vacuuming as a consequence of its MVCC model — dead tuples from updated and deleted rows accumulate and must be reclaimed, and transaction ID wraparound will eventually corrupt the database if VACUUM FREEZE is not run. Autovacuum handles this automatically in typical workloads but should be monitored. MySQL and SQL Server handle dead row cleanup internally (InnoDB purge thread, SQL Server ghost cleanup), making OPTIMIZE TABLE / SHRINK operations rarely necessary and sometimes counterproductive. Oracle's UNDO-segment architecture means old row versions never accumulate in the table itself. Statistics maintenance (ANALYZE / UPDATE STATISTICS / GATHER_TABLE_STATS) is important on all engines — stale statistics are one of the most common causes of sudden query plan regressions, particularly after large bulk loads.

Standard Syntax

-- PostgreSQL VACUUM ANALYZE orders;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2000 Oracle: Since 7 SQLite: Since 3.0.0

Per-Database Syntax & Notes

MySQL

OPTIMIZE TABLE reclaims space from deleted rows in InnoDB and MyISAM. ANALYZE TABLE updates index statistics used by the optimizer. Both can be run online (non-blocking) in InnoDB since 5.6. No VACUUM keyword.

ANALYZE TABLE orders; -- update statistics OPTIMIZE TABLE orders; -- reclaim space (rebuilds table) CHECK TABLE orders; -- verify integrity

PostgreSQL

VACUUM reclaims storage from dead tuples created by MVCC updates/deletes. VACUUM FULL rewrites the table (locks table, reclaims more space). ANALYZE updates planner statistics. Autovacuum runs both automatically. VACUUM FREEZE prevents transaction ID wraparound.

VACUUM orders; -- reclaim dead tuples, non-blocking VACUUM FULL orders; -- full rewrite, exclusive lock ANALYZE orders; -- update statistics only VACUUM ANALYZE orders; -- both at once VACUUM FREEZE orders; -- also freeze old tuples

SQL Server

No VACUUM equivalent — SQL Server uses a separate background thread for ghost record cleanup. UPDATE STATISTICS or sp_updatestats updates query planner statistics. DBCC SHRINKFILE / SHRINKDATABASE reclaims space (use sparingly — causes fragmentation). REBUILD INDEX is the primary defragmentation tool.

UPDATE STATISTICS dbo.orders; -- update stats for one table EXEC sp_updatestats; -- update all stale stats ALTER INDEX ALL ON dbo.orders REBUILD; -- rebuild all indexes (defrag + stats) ALTER INDEX ALL ON dbo.orders REORGANIZE; -- online, lighter defrag

Oracle

Oracle uses UNDO segments rather than dead tuple storage, so no VACUUM equivalent is needed. DBMS_STATS.GATHER_TABLE_STATS updates optimizer statistics. Segment shrink (ALTER TABLE ... SHRINK SPACE) reclaims space below the high-water mark.

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema', 'ORDERS'); -- update stats ALTER TABLE orders SHRINK SPACE; -- reclaim space ALTER TABLE orders MOVE; -- rebuild segment

SQLite

VACUUM rewrites the entire database file to reclaim space from deleted rows and reduce fragmentation. ANALYZE updates statistics in the sqlite_stat tables used by the query planner. Both are manual operations; no auto-vacuum by default (can be enabled with PRAGMA auto_vacuum).

VACUUM; -- rewrite entire db file ANALYZE; -- update query planner stats PRAGMA auto_vacuum = INCREMENTAL; -- enable incremental auto-vacuum