How We Reduced Our SQLite Database from 8.7GB to 3.8GB Without Downtime

A development team reduced its SQLite database from 8.7GB to 3.8GB without downtime by pruning redundant event rows. The team used a chunked DELETE strategy with passive checkpoints, overcoming rowid reuse issues, and verified the process with a multi-agent protocol. The database size dropped by 54%, WAL size by 96%, and event rows by 65%, with zero data loss.

Our OpenCode session database had grown to 8.7GB — 1.26 million event rows, most of them redundant state updates. Sessions wouldn't load, queries took 2+ seconds, and the WAL was 107MB behind. Here's how we pruned it live, without downtime, using a multi-agent verification protocol. The event table stored every state change as a full JSON snapshot. After months of use: event 633K older than 48 hours part tool transcripts, ~5.9GB Sessions wouldn't load. The UI froze on session list. Attempt 1: 250K chunk DELETE + PASSIVE checkpoint after each chunk Root Cause Analysis: Rowid Reuse After deletions, SQLite reused freed rowids for new events. Our chunk loop started at rowid 0, hit empty chunks immediately, and broke: BUG: breaks on first empty chunk rowid reuse if n == 0 and start 0: break CHUNK = 25 000 max rowid = con.execute 'SELECT MAX rowid FROM event' .fetchone 0 for start in range 0, max rowid, CHUNK : con.execute "DELETE FROM event WHERE rowid = ? AND rowid < ? " "AND json extract data,'$.time' IS NOT NULL " "AND json extract data,'$.time' < ?", start, start + CHUNK, cutoff ms con.commit Per chunk, NO intermediate checkpoint Key changes: | Metric | Before | After | Delta | |---|---|---|---| | DB Size | 8,703 MB | 3,783 MB | -54% | | WAL | 107 MB | 4 MB | -96% | | Event Rows | 1,259,602 | 437,506 | -65% | | Session/Message/Part | 873/165K/667K | 874/166K/668K | 0 loss | | freelist count | — | 0 | Fully compact | We didn't just wing it. Three AI agents verified every step: Done Gate: VERIFIED — all 9 checkpoints passed, zero data loss. This case study is part of our ClearWeb Phase 1 — publishing real engineering decisions with full transparency. The scripts are available in our repository. Written by a multi-agent swarm dev, suckz, atlas core with human oversight. All verification steps documented.