SQLite VACUUM: Shrink the Database File, VACUUM INTO and auto_vacuum

Ray Ferrell

October 6, 2026

Deleting rows doesn’t make an SQLite file smaller. The space is kept as free pages for reuse. VACUUM “rebuilds the database file, repacking it into a minimal amount of disk space” (VACUUM), and it also removes any traces of deleted content.

Run VACUUM; to shrink the file after large deletes; it needs up to twice the file’s size in free disk space. Use VACUUM INTO 'copy.db'; for a compact copy of a live database, and PRAGMA auto_vacuum = INCREMENTAL plus PRAGMA incremental_vacuum to reclaim space a little at a time.

Every example on this page was run on 6 October 2026 with SQLite 3.51.0 on macOS. File sizes come from stat. Lines starting with sqlite> show the statement; the lines after it are its output.

Deleting rows doesn’t shrink the file

A table of 20,000 rows of 500 characters each, then 18,000 of them deleted. The file stays the same size, and freelist_count shows the pages now sitting empty:

sqlite> CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);
sqlite> WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000) INSERT INTO events (payload) SELECT printf('%0500d', i) FROM n;
$ stat -f %z app.db   # file size in bytes (macOS)
11730944
sqlite> DELETE FROM events WHERE id > 2000;
$ stat -f %z app.db   # file size in bytes (macOS)
11730944
sqlite> SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
page_count  freelist_count
----------  --------------
2864        2578

VACUUM

sqlite> VACUUM;
$ stat -f %z app.db   # file size in bytes (macOS)
1171456
sqlite> SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
page_count  freelist_count
----------  --------------
286         0

The file went from 11.7 MB to 1.2 MB, and no free pages remain. Two costs, both from the docs: VACUUM “will fail if there is an open transaction on the database connection”, and “as much as twice the size of the original database file is required in free disk space” (how VACUUM works).

VACUUM INTO: a compacted copy

With INTO, “the original database file is unchanged and a new database is created in a file named by the argument to the INTO clause” (VACUUM INTO). That makes it a simple way to back up a database that is in use:

sqlite> VACUUM INTO 'app-copy.db';
$ sqlite3 app-copy.db "SELECT count(*) FROM events; PRAGMA integrity_check;"
2000
ok

VACUUM can renumber rowids

“The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY” (VACUUM). If other data stores those numbers, declare an INTEGER PRIMARY KEY:

sqlite> CREATE TABLE tags (name TEXT); INSERT INTO tags VALUES ('a'), ('b'), ('c'); DELETE FROM tags WHERE name = 'a';
sqlite> SELECT rowid, name FROM tags;
rowid  name
-----  ----
2      b
3      c
sqlite> VACUUM;
sqlite> SELECT rowid, name FROM tags;
rowid  name
-----  ----
1      b
2      c

auto_vacuum and incremental vacuum

auto_vacuum must normally be chosen before any tables exist (PRAGMA auto_vacuum). Outside WAL mode, an existing database can be switched by setting the pragma and then running VACUUM in the same connection (VACUUM):

sqlite> PRAGMA auto_vacuum;
sqlite> PRAGMA auto_vacuum = INCREMENTAL;
sqlite> VACUUM;
sqlite> PRAGMA auto_vacuum;
auto_vacuum
-----------
0
auto_vacuum
-----------
2

In INCREMENTAL mode, deleted pages stay free until you ask for them back with PRAGMA incremental_vacuum(N), which removes up to N pages:

sqlite> DELETE FROM events WHERE id > 1000;
sqlite> SELECT freelist_count FROM pragma_freelist_count;
freelist_count
--------------
143
sqlite> PRAGMA incremental_vacuum(100);
sqlite> SELECT freelist_count FROM pragma_freelist_count;
freelist_count
--------------
43

A quirk we hit in testing: in the shell’s -column mode (and other columnar modes), sqlite3 3.51.0 freed only one page per call, while the default list mode freed all 100. From application code, read every row the pragma returns:

$ sqlite3 -column app.db "PRAGMA incremental_vacuum(10);"
sqlite> SELECT freelist_count FROM pragma_freelist_count;
freelist_count
--------------
42

FULL auto-vacuum truncates free pages at every commit, but “Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does” (PRAGMA auto_vacuum).

maintenance commands in the cheat sheet, .dump and .backup, WAL mode, and the official reference: VACUUM.