Should a SQLite migration REINDEX everything?
REINDEX of everything
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
REINDEX with no argument rebuilds every index in every attached database in one write transaction, the write lock is held for the total, not per table. In a migration it is almost always broader than intended.
Why it is dangerous on a populated table
SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.
Fires on
REINDEX;The safe pattern
Name the index or table that actually needs rebuilding, REINDEX is only needed after a collation change.
REINDEX orders_status_idx;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
REINDEX;Stays silent (1)
REINDEX orders_status_idx;How to check locally
SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.sql --engine=sqlite