FAILURE MODES

I Deleted 900,000 Rows. The File Didn't Shrink.

A million-row SQLite table was eating disk, so I deleted the oldest 900,000 rows. The file came back byte-identical: 95,408,128 bytes before and after. VACUUM rebuilt it to 9.1 MB.

2026-10-31

A million-row events table was eating disk, so I deleted the oldest 90 percent to buy room. The delete ran clean. 100,000 rows left. Then I checked the file.

Before the delete: 95,408,128 bytes. After: 95,408,128 bytes. 900,000 rows gone, zero bytes freed.

The 59-second reel: I deleted 900,000 rows. The file didn’t shrink.

The measurement

The test table held 1,000,000 rows in a single SQLite file. The delete removed the oldest 900,000 rows, ids 1 through 900000, in 3.61 seconds. The file did not move by a single byte:

Step Rows File size
After insert 1,000,000 95,408,128 bytes (91 MB)
After DELETE WHERE id <= 900000 100,000 95,408,128 bytes (91 MB)
After VACUUM 100,000 9,560,064 bytes (9.1 MB)

The delete freed exactly 0 bytes. The file before and after was byte for byte identical.

Why the file didn’t shrink

Deleting rows doesn’t remove them from the file. SQLite parks the empty pages on a list called the freelist. After the delete, 20,958 of the file’s 23,293 pages sat empty, still on disk. The rows were gone from the table. Their pages were still in the file, waiting to be reused by future inserts.

The fix

One command, VACUUM. It rebuilds the file into a new, smaller one. The run took 0.61 seconds and brought the file from 91 MB down to 9.1 MB, same 100,000 rows, 81.9 MB recovered.

One trap: VACUUM rebuilds the file by copying the live pages first, so it needs free disk to run. Don’t run it on a disk that’s already full.

Postgres handles this differently

SQLite keeps one file and a freelist, so VACUUM rebuilds it. Postgres is different: dead rows pile up until VACUUM recycles them, and plain VACUUM usually doesn’t hand the space back to the operating system. VACUUM FULL does, but it takes an exclusive lock on the table, so don’t run it on a live one. pg_repack compacts without the lock.

What we did not verify

This was our own test on synthetic data, run Oct 30, 2026 and re-run Oct 31, 2026 with the corrected predicate (id <= 900000), on SQLite 3.45.1, measured with ls. It is one test on one table, not a benchmark, and it says nothing about how other engines or workloads behave. The Postgres claims are doc-sourced, not measured: no Postgres was available locally. The voiceover in the reel is AI-generated, the background footage is AI-generated, and the terminal shown in the reel is a recreation, not a screenshot of the test run. Watch the reel on Threads.

This post started as an Instagram post →

Discussion

Talk it through

Argue with us on Instagram.