SQLite WAL and VACUUM Test Harness

Validates the behavior of SQLite's VACUUM command in WAL (Write-Ahead Logging) mode.

Key Finding

In WAL mode, VACUUM does NOT write directly to the database file. It writes changes to the WAL file, requiring a checkpoint to actually shrink the database.

What This Tests

This test harness validates findings from SQLite: Vacuuming the WALs, specifically:

• Large transactions cause WAL to grow to ~100MB
• Small commits keep WAL around 4MB (auto-checkpoint threshold)
• VACUUM writes changes to WAL, not main DB file ← Critical!
• Checkpoint after VACUUM is required to shrink database
• PRAGMA wal_checkpoint(TRUNCATE) reclaims WAL disk space

Installation

npm install

Usage

npm test
# or
node test-sqlite-vacuum-wal.js

Test Results

The test creates temporary databases and measures file sizes at each step:

Initial: 100 rows × 1MB =        DB: 100.14 MB, WAL: 0 B
After deleting 50 rows:          DB: 100.14 MB, WAL: 132.80 KB
After checkpoint:                DB: 100.14 MB, WAL: 0 B

[Disable auto-checkpoint]

After VACUUM:                    DB: 100.14 MB, WAL: 50.37 MB  ← Still large!
After checkpoint:                DB:  50.07 MB, WAL: 0 B       ← Finally shrinks!

Space reclaimed: 50.07 MB (50%)

The Critical Maintenance Sequence

-- 1. Initial checkpoint (optional but recommended)
PRAGMA wal_checkpoint(TRUNCATE);

-- 2. VACUUM to rebuild database (writes to WAL!)
VACUUM;

-- 3. Final checkpoint (REQUIRED!)
PRAGMA wal_checkpoint(TRUNCATE);

-- 4. Update statistics (VACUUM is a schema change)
PRAGMA optimize;

Without that final checkpoint, the database file will NOT shrink!

Requirements

• Node.js 18+
• better-sqlite3 (automatically installed)

What Gets Tested

• Scenario 0-3: Large transaction behavior
• Scenario 2: WAL checkpoint truncation
• Scenario 20-21: VACUUM on empty database
• Scenario 11: VACUUM on full database (most important!)
• Scenario 30-35: Entire incremental VACUUM
• Scenario 40-45: Granular incremental VACUUM
• Scenario 60-66: WAL page write comparison
• BONUS: Complete maintenance sequence validation

Output

The test produces color-coded output showing:

• File sizes at each step
• Validation status (✓ or ✗)
• Comparison with expected values
• Percentage differences

Visualizations

SVG graphs showing experimental results are in the graphs/ directory:

• large-transaction-wal.svg - Large transaction WAL growth
• small-commits-wal.svg - Small commits with auto-checkpoint
• vacuum-sequence.svg - The critical VACUUM sequence

Blog Post

For a detailed explanation and analysis, see: https://photostructure.com/coding/how-to-vacuum-sqlite/

License

MIT

Author

Matthew McEachen (@mrm)