Is a text or blob primary key slow on a SQLite rowid table?

Text or blob primary key on a rowid table

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

What happens

On a rowid table a TEXT or BLOB primary key is not the table's key: rows live in a B-tree keyed by the hidden rowid, and the primary key is a separate unique index. Every lookup by key walks the index and then the table, and every row is stored twice over (key in the index, key in the row). WITHOUT ROWID makes the primary key the table's own B-tree, the manual recommends it exactly for this shape.

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 sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) STRICT;

The safe pattern

CREATE TABLE sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) WITHOUT ROWID, STRICT;

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 sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) STRICT;

CREATE TABLE sessions (id INTEGER PRIMARY KEY, token TEXT NOT NULL UNIQUE) STRICT;

CREATE TABLE sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) WITHOUT ROWID, STRICT;

How to check locally

npx bolvrk check migration.sql --engine=sqlite