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

> Source: <https://dev.to/xxxn3m3s1sxxx/how-we-reduced-our-sqlite-database-from-87gb-to-38gb-without-downtime-187g>
> Published: 2026-08-14 13:23:24+00:00

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.*