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

Ninad Pathak

October 7, 2026

Deleted rows don’t shrink an SQLite file, because SQLite keeps the freed pages for future writes. In this article I’ll walk you through getting the space back with VACUUM, and when VACUUM INTO or auto_vacuum is the better fit.

TLDR

Deleting rows doesn’t shrink an SQLite file. The space stays inside it as free pages.
Run VACUUM; to rebuild and compact the file. It needs up to twice the file’s size in free disk space.

Use VACUUM INTO 'copy.db'; for a compacted copy of a database that’s in use.
Use PRAGMA auto_vacuum = INCREMENTAL with PRAGMA incremental_vacuum to reclaim space a little at a time.

What is VACUUM in SQLite?

VACUUM is the SQLite command that “rebuilds the database file, repacking it into a minimal amount of disk space” (VACUUM). It also removes traces of deleted content from the file.

To show you why you’d run it, I made a table of 20,000 rows of 500 characters each, then deleted 18,000 of them. 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

SQLite VACUUM syntax

The command takes an optional schema name and an optional target file:

VACUUM;
VACUUM schema_name;
VACUUM INTO 'file_name';

Statement	What it does
`VACUUM;`	Rebuilds the main database file in place
`VACUUM INTO 'copy.db';`	Writes a compacted copy and leaves the original unchanged
`PRAGMA auto_vacuum = INCREMENTAL;`	Lets you reclaim free pages later without a rebuild
`PRAGMA incremental_vacuum(N);`	Removes up to N free pages

How to shrink an SQLite database

Each step continues on the same app.db file.

Step 1: Run VACUUM

The file went from 11.7 MB to 1.2 MB, and no free pages remain:

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

VACUUM has two costs, both from the docs. It “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).

Step 2: Make a compacted copy with VACUUM INTO

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 copy a database that’s in use:

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

Examples

These examples cover the other ways SQLite reclaims space.

auto_vacuum and incremental vacuum

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

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

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).

incremental_vacuum in the shell’s column mode

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

Edge cases

These came up while testing.

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

VACUUM can’t run inside a transaction

sqlite> BEGIN;
sqlite> VACUUM;
Runtime error near line 2: cannot VACUUM from within a transaction

Commit or roll back first, then run VACUUM on its own.

Conclusion

Deleting data leaves free pages inside an SQLite file. Run VACUUM after large deletes to get the space back, and make sure there’s room on disk for a full copy while it runs. For a compact backup of a live database, use VACUUM INTO. For gradual cleanup, set auto_vacuum = INCREMENTAL before you create any tables.