SQLite · alpha
note
LK · Locks & blocking
free in the CLI — --engine=sqlite
Index build on an existing table
Note — this works, but it is a documented trap or a cost the author may not have meant.
What it catches
SQLite has no CONCURRENTLY: CREATE INDEX scans the table and writes the index under the database's write lock, and every other writer waits (or gets SQLITE_BUSY) for the duration. Fine on a small table; on a large one this is the moment the application stalls.
Fires on
CREATE INDEX orders_status_idx ON orders (status);Do this instead
Build large indexes in a window, with busy_timeout set on the application's connections so a wait is a wait and not an error.
-- in the maintenance window, with PRAGMA busy_timeout set on application connections:
-- CREATE INDEX orders_status_idx ON orders (status);Catch this before it ships
SQLite support is in alpha: this rule runs locally in the free CLI, static only, and not in the hosted service yet: npx bolvrk check migration.sql --engine=sqlite