VACUUM, Page Size, and Cache

~12 min · vacuum, page-size, cache, maintenance

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Disk hygiene and memory tuning

Three knobs that affect SQLite performance at the storage layer:

VACUUM — rebuilds the database file, reclaiming space from deleted rows and defragmenting the B-trees. Slow (writes the whole file), but periodically useful after big deletes.
PRAGMA page_size = N — the unit of B-tree storage. Default 4096 bytes (post-3.12). Larger pages mean fewer I/O ops for sequential scans but more wasted space for small rows. Set before any tables are created — or run VACUUM after changing it.
PRAGMA cache_size = N — the in-memory page cache. Negative values mean kibibytes (e.g., -64000 = 64 MB). Bigger cache = fewer disk hits.

Warning: VACUUM requires up to 2x the database size in temporary disk space. On a 50 GB database, that's 100 GB free. Plan for it. auto_vacuum is an alternative that incrementalizes the work but has its own tradeoffs.

Code

Storage tuning sequence·sql

-- Inspect current settings
PRAGMA page_size;        -- 4096
PRAGMA cache_size;       -- -2000  (~2 MB; SQLite default)
PRAGMA page_count;       -- pages in db
PRAGMA freelist_count;   -- pages free for reuse

-- Bigger cache for read-heavy workloads (64 MB)
PRAGMA cache_size = -65536;

-- Reclaim space after a big delete
VACUUM;

Per-table size — useful for capacity planning·sql

-- dbstat virtual table is loaded with the standard build
SELECT name, sum(payload) AS bytes
FROM   dbstat
GROUP  BY name
ORDER  BY bytes DESC;

External links

Exercise

On a database where you've recently deleted millions of rows, measure file size before and after VACUUM. Note how long the VACUUM takes and how much disk it freed. Then compare query latency for a range scan with cache_size = -2000 (2 MB) vs cache_size = -65536 (64 MB) on a cold and a warm cache.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousANALYZE and PRAGMA optimize Next →Benchmarking Without Lying to Yourself

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.