SQLite 3.53 heals stale expression indexes row by row, and only the rows you write

How expression indexes go stale

SQLite can index the result of an expression instead of a plain column, as in CREATE INDEX ... ON docs(lower(email)). The index stores computed values, so it stays correct only as long as the expression produces the same output for the same input. That assumption breaks when a custom SQL function gets a bug fix, a dependency changes behaviour, or — in a case SQLite itself documents — floating point conversion shifts by one unit in the last place between engine versions. The rows are fine; the index entries simply stop describing them accurately, and queries answered from the index can return wrong results with no warning.

Recreating the failure

A hands-on experiment published on dev.to set out to measure how far the self-healing mechanism introduced in SQLite 3.53.0, released in April 2026, actually reaches. The author registered a deterministic scalar function, classify(x), returning floor(x100), created a 1,000-row table with an expression index on classify(x), then reopened the same database file with a fixed variant returning floor(x100)+1 — standing in for a code change nobody paired with an index rebuild. Two builds compiled from the official amalgamation isolated the version as the only variable: 3.51.0, which predates self-healing, and 3.53.4.

On 3.51.0 the divergence was immediate. Asking for classify(x)=500 through the index returned row 309, while a full scan of the same data returned row 308 — two different answers, no error raised. Attempts to update the affected rows on that build failed with SQLITE_CORRUPT and a "database disk image is malformed" message, even though nothing else about the database was damaged. According to the dev.to post, that is the behaviour of every SQLite release before 3.53.

What self-healing covers, and what it doesn't

On 3.53.4 the picture is mixed. The read query still returned the wrong row before any write, because self-healing is triggered by write statements, not reads. After an update touching rows 300 through 320 — a range containing both 308 and 309 — the statement completed cleanly, and the indexed and full-scan paths then agreed on row 308.

The scope of the repair, however, is exactly the rows the write touched. An integrity check counted 1,000 stale entries before the update and 979 afterwards. The untouched rows stayed just as wrong and kept being served from the stale index without complaint, which means a read-mostly table can return incorrect answers indefinitely on 3.53 with no error and no write ever forcing a fix.

REINDEX EXPRESSIONS is the complete fix

Also introduced in 3.53, REINDEX EXPRESSIONS rebuilds expression indexes while leaving ordinary column indexes alone. On 3.51.0 the statement fails with "unable to identify the object to be reindexed" because the keyword is read as an object name; on 3.53.4 it repaired all 979 remaining rows instantly.

The targeted scope matters for maintenance windows. On a 500,000-row table carrying four indexes — one expression-based and three conventional — the experiment measured REINDEX EXPRESSIONS at 0.198 seconds versus 0.990 seconds for a full REINDEX of the table, roughly five times faster and in line with rebuilding one index instead of four.

Overhead and concurrency

Healing carries a measurable write cost. Identical updates over 200,000 rows took 0.164 seconds when the index was already consistent and 0.239 seconds when every row needed healing — about 46 percent slower, or roughly 0.4 microseconds per row. The tax is paid once per row after the underlying function changes, and the row stays clean afterwards.

A concurrency test with five connections issuing overlapping updates and no busy timeout produced three SQLITE_BUSY failures and two successes, which the author attributes to SQLite's ordinary single-writer lock rather than anything new introduced by self-healing.

Why it matters

A stale expression index is a silent correctness bug: the data is intact, the queries succeed, and the answers are wrong. Marking a custom function SQLITE_DETERMINISTIC tells the engine it may trust stored index entries without recomputing them, so nothing in the normal query path will flag the drift. Version 3.53 removes the most alarming symptom — routine updates failing with corruption errors — but it does not make read-heavy tables safe, because rows that are never written are never repaired. The practical rule for developers is straightforward: whenever a function used inside an expression index changes, or after a SQLite upgrade that could alter floating point behaviour, run REINDEX EXPRESSIONS instead of waiting for self-healing to catch up. On large tables it is fast, targeted, and the only way to confirm every entry matches what the expression computes today.