Does building an index block writes in 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 happens

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.

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

CREATE INDEX orders_status_idx ON orders (status);

The safe pattern

-- in the maintenance window, with PRAGMA busy_timeout set on application connections:
--    CREATE INDEX orders_status_idx ON orders (status);

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.

CREATE INDEX orders_status_idx ON orders (status);

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT);
CREATE INDEX orders_status_idx ON orders (status);

How to check locally

npx bolvrk check migration.sql --engine=sqlite