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

xxxn3m3s1sxxx — Fri, 14 Aug 2026 13:23:24 +0000

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

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 Problem

The event table stored every state change as a full JSON snapshot. After months of use:

1.26M rows in event (633K older than 48 hours)
406K rows with no top-level timestamp (NULL-time events)
667K rows in part (tool transcripts, ~5.9GB)
WAL checkpoint 107MB behind

Sessions wouldn't load. The UI froze on session list.

The Solution: Chunked DELETE Without Downtime

What We Tried First (And Why It Failed)

Attempt 1: 250K chunk DELETE + PASSIVE checkpoint after each chunk

Deleted ~192K events before blocking the live session
Checkpoint contention + large chunks = lock collision

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

The Fix: Bucket Scan Without Early 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:

25K chunks (not 250K) — each DELETE commits in milliseconds
No intermediate checkpoints — checkpoint was the contention point
Loop to max_rowid — never break early, rowids get reused
6 passes until stable — pass 1 deleted 633K, passes 2-6 found 0

Results

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

The Safety Net: Multi-Agent Verification Protocol

We didn't just wing it. Three AI agents verified every step:

Scout — mapped the codebase, identified affected tables
Spec Critic — defined acceptance criteria (MUST: zero session/message/part loss)
Verifier — clean checkout, independent hard subset check against pre-prune backup

Done Gate: VERIFIED — all 9 checkpoints passed, zero data loss.

Lessons Learned

Extract BEFORE destructive steps — veto culture forced backup + export
Live pruning is possible — WAL + chunked DELETE + VACUUM INTO as safety net
Rowid chunking breaks on reuse — fix: scan all buckets, never early-break
Binary vs. Decimal — always specify units (MiB vs MB)

What's Next

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.

DEV Community: xxxn3m3s1sxxx

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

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

The Problem

The Solution: Chunked DELETE Without Downtime

What We Tried First (And Why It Failed)

The Fix: Bucket Scan Without Early Break

Results

The Safety Net: Multi-Agent Verification Protocol

Lessons Learned

What's Next