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.
Tool steps: 3 (reasoning summary, call to python, execution output)
rows = run_sql("EXPLAIN (ANALYZE) SELECT ...")
print(rows[:3])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 msSQLite has no date type. What do people store?
Integer Unix seconds for anything you compare or sort, and ISO-8601 text only when humans read the file directly. Mixing the two in one column is the failure mode, because comparisons then silently sort text against numbers.
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.