SQLite 3.53 sets NOT NULL on a column in six writes where a rebuild needed 69,843

Those six write calls moved 8,716 bytes in total ^[7]. According to the post's author, SET NOT NULL rewrites the table's schema record in sqlite_master and leaves the row data pages where they are ^[8]. The old rebuild creates a new table with the constraint, copies every row, drops the old table, renames the new one and recreates the indexes ^[2]. It touches every row twice: once on the copy and again when the index is rebuilt ^[8]. By the strace count, the new path made about 11,600 times fewer write calls ^[1].

The 150 to 180 times speedup came from one table. It had 2 million rows and an index on the target column, and the OS page cache was dropped before each of three timed runs per approach ^[4][5]. Two things decide whether that figure carries over to another database: its size and its indexes. The rebuild's writes grow with every row and every index, while the schema rewrite stays at one record, so a larger table widens the gap ^[8]. Removing the index narrows it ^[11].

SQLite's release notes say execution time for a new NOT NULL constraint "is proportional to the amount of data in the table," because every existing row has to be checked ^[9]. The author's first run did not show this. It finished in 0.01 seconds for 2 million rows ^[10]. NULLs sort first in SQLite's b-trees, so when the column is indexed the check reads only the left edge of the index ^[12]. On an 8 million row table that took 13 page reads, and the author reports the count holds regardless of table size ^[11][12]. Without an index the check scans every data page. That came to 25,372 reads at the same size, about 1,950 times as many ^[11][2].

This is good engineering. The check reuses an ordering the index already keeps on disk, so validating an indexed column costs a few page reads ^[12]. On a warm cache both cases finish in well under a second ^[13]. The author wrote that the documentation's warning "only really bites when there's no index backing the column you're constraining" ^[17].

The failure path is rougher. If the column holds a null, the ALTER fails with the two words "constraint failed" ^[14]. The same violation through INSERT reports "NOT NULL constraint failed: t.b" ^[14]. Teams often tighten several nullable columns one at a time, and the ALTER message does not say which column or row broke the rule ^[14]. The author's workaround is a probe query such as SELECT rowid FROM t WHERE email IS NULL LIMIT 5 ^[15].

Ubuntu's packaged build cannot run any of this. The author used the 3.53.4 CLI because Ubuntu's apt repositories still top out at 3.45.1, which does not understand the new syntax ^[3]. That build is eight minor releases behind 3.53 ^[3].

One finding falls outside the documentation. SQLite's ALTER TABLE page documents only SET NOT NULL and DROP NOT NULL, and it says CHECK constraints can only be added through ADD COLUMN ^[16]. The author tried ALTER TABLE t ADD CONSTRAINT age_positive CHECK (age >= 0) anyway. It worked and validated existing rows ^[16]. I would keep it out of migration files until the documentation lists it.