sqlite-bank-space-diagnosis

Snapshot the database with sqlite3 ".backup", then run sqlite3_analyzer on the snapshot for per-table freelist and payload efficiency. Quantify reclaimable space with VACUUM INTO on the snapshot, and stat the whole data directory including -wal and -shm files.

Passive auto-checkpoints sync content but cannot truncate the WAL while any pooled connection holds a read lock, so already-checkpointed frames accumulate unbounded. Run PRAGMA wal_checkpoint(TRUNCATE) and check the result row: 0|0|0 means the entire WAL is reclaimable garbage.

No. In WAL mode readers and writers coexist, so a plain read transaction does not block VACUUM; only the single write lock does. To simulate VACUUM-busy in tests, hold BEGIN IMMEDIATE on another connection rather than a plain BEGIN with SELECT.

vec0 stores vectors in chunk tables where one chunk row holds capacity for 1024 vectors, so count(*) counts chunks, not vectors. Verify with dbstat per-table page counts, rowid and length(vectors) capacity math, and the validity bitmap rows before concluding the index is empty.

VACUUM rewrites the file through the WAL, so checkpoint again after VACUUM, and run ANALYZE after VACUUM because VACUUM drops sqlite_stat1. Use VACUUM INTO on a snapshot first to quantify reclaim read-only before touching the live database.

sqlite3_analyzer is a space-only tool: it reports freelist, payload efficiency, and overflow per table but has zero coverage of query performance, the WAL file, or FTS behavior. It cannot see a bloated WAL sitting next to the database file, so pair it with a checkpoint check.