SQLite 3.53's SET NOT NULL turns a full table rebuild into six disk writes

Adding NOT NULL becomes a metadata change

SQLite 3.53.0, released in April 2026, adds ALTER TABLE ... ALTER COLUMN ... SET NOT NULL and DROP NOT NULL, and benchmarks published on dev.to quantify what that changes operationally. Until now, adding the constraint meant building a replacement table, copying every row across, dropping and renaming tables, and recreating any indexes by hand. On a two-million-row test table with an index on the target column, that rebuild took between roughly 1.5 and 1.8 seconds across three runs. The new single statement finished in about a hundredth of a second, a difference the author measured at 150 to 180 times.

Under strace, the rebuild issued 69,843 pwrite64 calls, while SET NOT NULL made six writes totalling 8,716 bytes. The new statement rewrites the schema record in sqlite_master and never moves the row data pages. The rebuild, by contrast, writes every row twice: once into the new table and again when the index is rebuilt. Miss that manual index-creation step and queries end up scanning the whole table without any warning. SET NOT NULL leaves existing indexes in place.

The tests were run against the 3.53.4 CLI; the author notes Ubuntu's apt repositories still ship SQLite 3.45.1, which rejects the new syntax outright.

Rows still get checked, unless an index covers it

SQLite's release notes state the operation's runtime is proportional to the table's data, because every existing row must be checked for NULLs. The dev.to measurements show when that applies. On an eight-million-row table, a column with an index needed only 13 page reads: NULLs sort first in SQLite's b-trees, so verifying the column means inspecting the start of the index rather than the table itself. On a column without an index, the same table required 25,372 reads. Cold-cache timings came out around 0.010 seconds for the indexed case versus 0.11 seconds for the unindexed one — both far below a rebuild, but only the unindexed path scales with table size.

Failure messages leave you guessing

If the column already contains a NULL, the ALTER is refused, correctly. The error message, however, is a bare "constraint failed" with no column name, constraint type or row identifiers, while an INSERT violating the same constraint produces "NOT NULL constraint failed: t.b". On a wide table being tightened one column at a time, the author points out, you are left to locate offending rows yourself with a query such as selecting rowids where the column IS NULL.

Undocumented CHECK constraint support

While testing, the author tried the ANSI-style ADD CONSTRAINT name CHECK (...) form, expecting a parse error, because sqlite.org's ALTER TABLE reference only documents SET NOT NULL and DROP NOT NULL and says CHECK constraints arrive via ADD COLUMN. It worked: existing rows were validated, a violating insert was rejected with a properly named error, and DROP CONSTRAINT removed it cleanly. A search of the reference page found no mention of either statement.

Readers are not blocked during the scan

Locking behaves like any other write. With busy_timeout at zero and a write transaction open elsewhere, the ALTER fails immediately; with a timeout configured, it queues and runs once the lock frees.

The notable result involves concurrent readers. While an unindexed eight-million-row check ran, a count query from a second connection completed immediately in both rollback-journal and WAL modes. The verification phase takes only a shared lock, and the exclusive lock appears at the very end for the small schema write, so read-heavy applications can run the migration without stalling queries.

The post also reports that the documented no-op behaviour — applying SET NOT NULL to a column that already carries the constraint — held up in testing.

Why it matters

Converting nullable columns to required ones is basic schema hygiene that SQLite previously made disproportionately expensive: a full table copy, a manual index-recreation step that is easy to forget, and I/O proportional to every row twice over. Version 3.53 collapses this into a schema-record update plus a null check, and the check is close to free when the column is indexed. That turns a maintenance-window migration into something a live, read-heavy deployment can absorb.

The practical caveats remain: packaged SQLite versions lag far behind the current release, so the syntax may simply not parse on your existing stack, and scanning for NULLs beforehand is advisable given how uninformative the failure message is.