SQLite 3.54 skips expression index rewrites when updates leave the indexed value unchanged

What changed in 3.54

SQLite 3.54.0 was released on 9 October 2026, with a 3.54.1 follow-up on 16 October fixing an unrelated 32-bit buffer bug, according to a hands-on benchmark published on dev.to. Among the release's changes is a small but targeted one: an UPDATE against a table with an expression index no longer rewrites that index when the value the expression computes stays the same.

Expression indexes are built over a computed value rather than a bare column, such as an index on lower(email). Before 3.54, an update that rewrote a row's email in a different case — leaving the lowercase form identical — still triggered a delete-and-reinsert of the corresponding index entry.

Measured effect

The dev.to benchmark built a 200,000-row users table with an index on lower(email), then upper-cased the stored emails of 50,000 rows. Because lower(email) is the same before and after, only the index maintenance differed between versions. WAL pages written were counted using a passive checkpoint issued immediately after the statement, before an automatic checkpoint could obscure the figure, and the tests used the precompiled Linux CLI builds from sqlite.org.

On 3.53.0 the update wrote 2,143 WAL pages; on 3.54.1 it wrote 511, a 76% reduction. Wall time fell from 0.1213 seconds to 0.0792 seconds, taking the fastest of five runs.

The striking detail: running the same update on a copy of the table with no expression index at all also writes 511 pages, on both versions. So 3.54 does not merely shrink the index overhead for this case — it removes it entirely, matching what a table without the index would pay.

At full-table scale the gap narrows. Updating all 200,000 rows wrote 3,779 pages on 3.53.0 versus 2,148 on 3.54.1, a 43% reduction, because the base-table pages both versions must write dominate at that scope.

The resulting databases are identical: after a full checkpoint, both files came out at 17,862,656 bytes with 4,361 pages. The savings exist only in the volume written while the statement runs, not in final on-disk size.

Where it does nothing

When the benchmark changed emails to genuinely new values, both versions wrote 2,294 pages — no difference at all. Timings overlapped as well, with 3.53.0 ranging from 0.150 to 0.175 seconds across five runs and 3.54.1 from 0.146 to 0.219 seconds, so the extra comparison work the new version performs does not register as a measurable cost at this scale.

Catching up to plain indexes

The benefit is specific to expression indexes. A self-assigning update against a plain index on an integer column wrote 133 pages on both 3.53.0 and 3.54.1, meaning ordinary column indexes already skipped redundant rewrites and this release simply closes the gap. A diff of the bytecode each version's EXPLAIN produces shows 3.54.1 emitting an additional IsNull guard that jumps past the index-insert instruction; per the author, that jump is the entire mechanism, letting execution skip the insert whenever the recomputed key does not need to change.

A determinism loophole remains

The benchmark also probed what SQLite will accept inside an index expression. Indexing random() fails on both versions with an error stating that non-deterministic functions are prohibited in index expressions, but datetime('now') is accepted without complaint on either release. The creation-time check apparently covers functions flagged as non-deterministic in SQLite's function table, not every expression whose result moves with the clock. The author notes that SQLite 3.53 added REINDEX EXPRESSIONS to repair indexes that drift, which fits a world where not every unstable expression is caught up front.

Why it matters

SQLite ships inside browsers, phones and countless servers, often on flash storage where every extra page written costs battery life and wear. Normalising values on write — lower-casing an email, reformatting a field, re-saving a row after an unrelated column changed — is an everyday pattern, and until now each such write silently paid an index-maintenance tax whenever an expression index sat on the table. A 76% cut in pages written for those updates is a real reduction in write amplification and WAL churn, with no measured penalty when indexed values actually change and no difference in the resulting database file. The change also quietly removes a long-standing inconsistency, since expression indexes now behave like plain ones. The remaining caveat is the one the benchmark surfaced: nothing stops you from building an expression index on a wall-clock function, so that particular footgun is still loaded.