Freelist, Deleted Space, auto_vacuum, VACUUM, and File Shrinkage

Learning outcomes

Deleting 100,000 rows can leave a database file almost the same size. That is not evidence that DELETE failed. In the default auto-vacuum mode, SQLite keeps freed pages inside the file on a freelist so later inserts can reuse them without asking the filesystem to grow the file again. File shrinkage is a separate operation and has its own costs.

Freelist: free inside the file is not free outside the file

When a page is no longer needed by a table/index, SQLite can place it on the database freelist. The page still occupies bytes in the file but is available for reuse by future allocations. This avoids unnecessary filesystem shrink/grow churn.

PRAGMA auto_vacuum;PRAGMA page_size;PRAGMA page_count;PRAGMA freelist_count;-- After a large DELETE:PRAGMA page_count;PRAGMA freelist_count;

With the common auto_vacuum=NONE setting, expect freelist_count to rise while page_count and file size may remain almost unchanged.

auto_vacuum modes

SQLite must know how pages refer to one another to move free pages safely for auto-vacuum, so enabling FULL/INCREMENTAL requires database-format support established before normal table population or by setting the mode and rebuilding with VACUUM. Switching between FULL and INCREMENTAL is easier because both already have the pointer-map infrastructure.

Disposable experiment A: NONE + VACUUM

from pathlib import Pathimport sqlite3p = Path("vacuum-none.db")p.unlink(missing_ok=True)con = sqlite3.connect(p)con.execute("PRAGMA auto_vacuum=NONE")con.execute("CREATE TABLE payload(id INTEGER PRIMARY KEY, body BLOB NOT NULL)")con.executemany("INSERT INTO payload(body) VALUES(?)", [(b"x"*4096,) for _ in range(6000)])con.commit()def snap(label):    pages = con.execute("PRAGMA page_count").fetchone()[0]    free = con.execute("PRAGMA freelist_count").fetchone()[0]    print(label, "pages=", pages, "free=", free, "bytes=", p.stat().st_size)snap("after insert")con.execute("DELETE FROM payload WHERE id <= 5500")con.commit()snap("after delete")con.execute("VACUUM")snap("after VACUUM")con.close()

Expect the delete to create many free pages without proportionally shrinking the file. VACUUM rebuilds a compact database and normally reduces both page_count and file length. Your exact counts are local.

What VACUUM actually means operationally

VACUUM rebuilds the database into a new compact image and replaces the original through SQLite’s transactional machinery. It can reclaim freelist space and reduce fragmentation/partially filled pages. It is therefore much heavier than “remove free pages.”

VACUUM INTO: compact copy instead of in-place rebuild

VACUUM INTO 'file' produces a compact database copy at a target filename rather than replacing the source. It can be useful for creating a cleaned snapshot/export. The destination must be handled as a new artifact; it is not a substitute for the online backup API’s concurrency semantics in every scenario.

-- Run from the source database connection.VACUUM INTO 'fieldnotes-compact-copy.db';-- Then open the output separately and verify:-- PRAGMA integrity_check;-- PRAGMA foreign_key_check;

SQLite 3.53.x also has newer URI options for VACUUM INTO targets, but this chapter intentionally uses the simple portable form.

Disposable experiment B: INCREMENTAL auto-vacuum

Incremental auto-vacuum must be enabled before the database is populated (or enabled through a VACUUM rebuild). This fresh-file lab sets it before table creation.

-- On a NEW disposable database:PRAGMA auto_vacuum=INCREMENTAL;VACUUM;   -- establishes the format if needed before normal usePRAGMA auto_vacuum;CREATE TABLE payload(id INTEGER PRIMARY KEY, body BLOB NOT NULL);-- Populate with your local seeder, then delete most rows.PRAGMA page_count;PRAGMA freelist_count;PRAGMA incremental_vacuum(100);PRAGMA page_count;PRAGMA freelist_count;-- Run again only if your maintenance policy intentionally wants more reclamation.PRAGMA incremental_vacuum(100);

incremental_vacuum(N) requests removal of up to N pages from the freelist and truncates the file by the pages it can reclaim. It does nothing when the database is not in INCREMENTAL mode.

FULL auto-vacuum is not “VACUUM every commit”

FULL auto-vacuum moves freelist pages toward the file end and truncates at commit. It does not perform the complete repacking/defragmentation of a VACUUM rebuild and can itself increase fragmentation by moving pages. Choose it for a workload that values automatic file truncation enough to justify the pointer-map and layout tradeoffs.

Failure cases

Integrity and verification after maintenance

PRAGMA page_count;PRAGMA freelist_count;PRAGMA auto_vacuum;PRAGMA integrity_check;PRAGMA foreign_key_check;SELECT COUNT(*) FROM payload;

File-size maintenance must not replace semantic verification. integrity_check validates structural/index/constraint consistency but, as Chapter 5 taught, foreign keys require the separate foreign_key_check.

Checkpoint and bridge

Production judgment and next step

Page allocation explains file size, but page access is mediated through memory. Lesson 5 completes the storage model with the pager, page cache, memory mapping, temporary B-trees/files, the OS cache, and the VFS boundary.

Authoritative references

• SQLite — PRAGMA auto_vacuum, freelist_count, incremental_vacuum.
• SQLite — VACUUM.
• SQLite — Database File Format: freelist.
• SQLite — Integrity checking PRAGMAs.