How to VACUUM SQLite in WAL Mode (and why you need a checkpoint)

How to VACUUM SQLite in WAL Mode (and why you need a checkpoint)

If you’re using SQLite in WAL (Write-Ahead Logging) mode and running VACUUM to reclaim disk space, you almost double disk consumption. This isn’t a bug. It’s how WAL mode works. And there’s a simple fix.

TL;DR

VACUUM;

-- After VACUUM: DB file shrinks (if there have been deletes or updates)
-- but the WAL contains the entire rebuilt database

-- Note: VACUUM is atomic, so the WAL auto-checkpoint threshold
-- doesn't prevent unlimited WAL growth!

PRAGMA wal_checkpoint(TRUNCATE);

-- After checkpoint: WAL truncated, disk space actually reclaimed
-- Net disk savings: 40-50% (based on fragmentation level)

The Problem

In WAL mode, SQLite doesn’t write directly to the main database file. It writes changes to a separate WAL (Write-Ahead Log) file, which gets periodically merged back through a process called “checkpointing.”

This includes VACUUM.

When you run VACUUM in WAL mode:

  1. VACUUM executes as a single atomic transaction (cannot be interrupted)
  2. SQLite rebuilds the entire database in compacted form
  3. The entire rebuilt database gets written to the WAL file (size = compacted DB size)
  4. Auto-checkpoint (if enabled) triggers after VACUUM completes (PASSIVE mode)
  5. Main database file does shrink to the compacted size
  6. BUT WAL file stays allocated with the full rebuilt database still in it (PASSIVE doesn’t TRUNCATE)
  7. Net disk usage: unchanged (you moved bloat from DB to WAL)

Why the automatic WAL checkpoint doesn’t save us

  • VACUUM is atomic
  • Auto-checkpoint only runs between transactions, not during them

So even with the default ~4 MB threshold, the WAL will grow to contain the full rebuilt database during VACUUM.

Proof: Experimental Validation

A test harness exercises SQLite with a realistic 30+ column schema (integers, floats, variable-length strings) to prove this behavior.

Production Recommendations

Essential WAL Setup

When opening a database, configure these PRAGMAs:

-- Enable WAL mode (must be set first)
PRAGMA journal_mode = WAL;

-- NORMAL balances safety and performance
-- (FULL is 10-20x slower, OFF risks corruption)
PRAGMA synchronous = NORMAL;

-- Use memory for temp tables
PRAGMA temp_store = MEMORY;

-- Memory-mapped I/O improves read performance
PRAGMA mmap_size = 268435456; -- 256 MB

VACUUM Maintenance

Complete sequence:

VACUUM;
PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA optimize;

Best practices:

  1. Always use checkpoint(TRUNCATE) after VACUUM - Otherwise you’ve just moved bloat from DB to WAL (net zero savings)

  2. Schedule during low-traffic periods - VACUUM locks the database exclusively

  3. Monitor WAL file size - Alert if it grows unexpectedly large

  4. Measure before/after - Verify disk space is actually reclaimed

  5. Consider dedicated maintenance process - Complex applications may benefit from a separate service handling checkpoints rather than using wal_autocheckpoint in every connection

  6. journal_size_limit caveat - Useful for normal operations but does NOT prevent WAL growth during VACUUM (VACUUM is atomic and can exceed the limit)

Why VACUUM Works This Way

In WAL mode, SQLite optimizes for concurrent reads. The main database file is only updated during checkpoints, allowing readers to access a consistent snapshot without blocking writers.

VACUUM rebuilds the entire database, so writing directly to the main file would require locking out all readers for the duration. By writing to the WAL instead, VACUUM completes faster, then the checkpoint moves changes atomically.

This is actually a feature—it allows VACUUM to run with less disruption. But it means you must checkpoint afterward to actually apply the changes.

Test It Yourself

Repository: github.com/photostructure/test-sqlite-vacuum-wal

git clone https://github.com/photostructure/test-sqlite-vacuum-wal.git
cd test-sqlite-vacuum-wal
npm install
npm test

The harness includes tests with realistic multi-column schemas (30+ columns with integers, floats, and variable-length strings) representative of actual application databases.

Credits

This research validates and extends findings from The Unterminated String’s article on SQLite vacuuming. The test harness uses better-sqlite3 to measure actual file sizes with realistic multi-column schemas.

Special thanks to the SQLite developers for WAL mode and comprehensive documentation.