My SQLite file never shrinks after deletes

You

My SQLite file never shrinks after deletes.

ChatGPT

Freed pages are reused, not returned, unless you ask:

PRAGMA auto_vacuum = INCREMENTAL;   -- set before the first table is created
PRAGMA incremental_vacuum(1000);

On an existing database only a full VACUUM reclaims space, and it needs room for a second copy of the file while it runs.

Tool steps: 3 (reasoning summary, call to python, execution output)

reasoning summary

Checked the query plan, then compared index options.

call to python

rows = run_sql("EXPLAIN (ANALYZE) SELECT ...")
print(rows[:3])

execution output

Seq Scan on events  (cost=0.00..812431.00 rows=41203122 width=16)
  Rows Removed by Filter: 38996878
Planning Time: 0.214 ms
Execution Time: 11842.663 ms

You

Can SQLite do decent full text search on a few hundred thousand rows?

ChatGPT

Yes, FTS5 handles that size comfortably:

CREATE VIRTUAL TABLE notes_fts USING fts5(title, body, content=notes);
INSERT INTO notes_fts(notes_fts) VALUES('rebuild');
SELECT title, snippet(notes_fts, 1, '<b>', '</b>', '...', 12)
FROM notes_fts WHERE notes_fts MATCH 'archive NEAR/5 export';

The snippet function is the part people miss: it returns the matching fragment, which is what makes results readable rather than a list of titles.