My SQLite file never shrinks after deletes.
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.
Can SQLite do decent full text search on a few hundred thousand rows?
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.
Do I need WAL mode for a read-heavy SQLite database?
Almost always. In the default journal mode a writer blocks readers; in WAL they proceed in parallel:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
The tradeoff is that WAL needs shared memory, so it does not work over most network filesystems. On a local disk it is strictly better for this workload.