Indexes built before the bulk copy in a rebuild

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

In a table rebuild the copy is the expensive step. Creating the new table's indexes before INSERT ... SELECT makes SQLite maintain every index row by row during the copy, random B-tree inserts for each, instead of one sorted build per index afterwards. The manual's own recipe creates the indexes after the copy.

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 TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
CREATE INDEX orders_new_user ON orders_new (user_id);
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;

The safe pattern

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;
CREATE INDEX orders_new_user ON orders_new (user_id);

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 TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
CREATE INDEX orders_new_user ON orders_new (user_id);
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;
CREATE INDEX orders_new_user ON orders_new (user_id);

CREATE TABLE lookup (id INTEGER PRIMARY KEY, code TEXT) STRICT;
CREATE INDEX lookup_code ON lookup (code);
INSERT INTO lookup (id, code) VALUES (1, 'a'), (2, 'b');

How to check locally

npx bolvrk check migration.sql --engine=sqlite