SQLite 3.53.0 makes expression index maintenance explicit

SQLite 3.53.0, released on 9 April 2026, adds REINDEX EXPRESSIONS and automatic repair for some stale expression-index entries. It also extends ALTER TABLE constraint editing and introduces a query result formatter. The release notes include the WAL-reset corruption fix. My main interest is how the release changes maintenance of databases whose indexed values depend on computation.

I have used SQLite for twenty-five years, and this site runs on it. I like the combination of SQL and an application-owned database file. That ownership includes the expression definitions, library versions, and migration procedures that give the file its meaning. The new index maintenance facilities address a concrete problem within that model, especially when an existing database moves between software versions.

Stored index keys can outlive their original computation

An expression index stores keys derived from an expression over a table row. SQLite's expression-index documentation explains that the planner matches query expressions against indexed expressions. It also requires the functions involved to be deterministic. That requirement makes sense because changing an expression's answer without changing its source row would separate the stored index key from the answer computed during a later operation.

For example, consider a table with a text measurement and an index over its numeric interpretation. The example illustrates a dependency on conversion behavior, without claiming that this particular input produces a cross-version difference.

CREATE TABLE measurements(
  id INTEGER PRIMARY KEY,
  raw_value TEXT NOT NULL
);
CREATE INDEX measurements_numeric
  ON measurements(CAST(raw_value AS REAL));

INSERT INTO measurements VALUES (1, '0.1');

The indexed value comes from evaluating a conversion. A review of this schema should therefore ask whether every executable that accesses the database computes the expression consistently. That question differs from asking whether each executable recognizes the SQL syntax. Both matter when a file has a longer lifetime than the program version that created it.

The project's stale expression-index explanation describes cases where ostensibly deterministic calculations vary across environments or library versions. Floating-point conversion changes are one example. The resulting index key can differ slightly from a newly computed value for the same unchanged row. This is the class of issue behind the recent compatibility work.

I would describe that as a dependency recorded indirectly in persistent data. A database schema tells a maintainer which expression exists, but the schema alone does not record every implementation detail of the function that originally evaluated it. This release makes the consequences easier to manage without removing the need to understand the dependency.

Rebuild the relevant indexes deliberately

The REINDEX documentation describes rebuilding indexes from scratch. The new REINDEX EXPRESSIONS form selects expression indexes, while the existing forms can select a particular index or table. That provides a direct maintenance operation when a known change in expression evaluation warrants regenerating the stored keys.

For the illustrative table, a maintenance script running with 3.53.0 can request the targeted operation directly. A named index remains useful when the intended scope is narrower.

REINDEX EXPRESSIONS;

REINDEX main.measurements_numeric;

I would choose one operation according to the actual maintenance scope. Running both is unnecessary for this small example. The first expresses a policy about every expression index in the databases under consideration, while the second identifies a particular object. An operational script should make that choice explicit and record which library executes it.

The syntax also has a naming caveat. SQLite documents how EXPRESSIONS interacts with a collation or database object bearing that name. Schema qualification avoids ambiguity when selecting a particular table or index. I would check existing names before introducing a generic maintenance command into an older application.

Automatic repair has a specific trigger

SQLite 3.53.0 also attempts repair when a DELETE or UPDATE encounters a stale expression-index entry. The stale-index documentation says that these operations can fix the index where older versions would raise an error. An integrity check can still report an imprecise stored floating-point value. This describes a particular repair path during modification, and I would avoid assuming that installing the library proactively rebuilds every index.

That distinction affects an upgrade procedure. If an application needs a known complete rebuild after changing an indexed function, explicit maintenance gives it a defined completion point. Opportunistic repair during later writes does not provide the same evidence that every old key has been revisited. The appropriate choice depends on what the application needs to establish before it resumes normal work.

I would preserve a representative database before testing the transition. Then I would compare indexed query results with the application's expected answers and exercise updates that alter the relevant rows. A result comparison should include boundary values that matter to the application. A generic statement that the database opens successfully says little about the meaning of its derived index keys.

Generated columns make the storage choice visible

SQLite's generated-column documentation distinguishes VIRTUAL expressions computed when read from STORED expressions computed when written. Indexes over VIRTUAL generated columns are expression indexes. An index over a STORED generated column indexes the stored column value. The choice therefore affects where the computed result persists and when it changes.

I would consider a STORED column when retaining the computed value is itself part of the intended data model. That decision deserves the same care as any other denormalized value. A later change in the desired calculation raises a migration question about existing rows. Choosing storage does not decide that application policy automatically.

For a database that must move among tools, the schema should communicate the intended policy as plainly as possible. An expression index is attractive when a calculation supports a query without becoming a separately managed stored attribute. A STORED column is a different representation with different migration responsibilities. I would decide between them based on those responsibilities and the actual query requirements.

Constraint editing removes some migration machinery

The release also adds support for adding and removing NOT NULL and CHECK constraints. The ALTER TABLE documentation describes the new ability to set or drop a column's NOT NULL constraint. SQLite stores schema definitions as SQL text, so its alteration machinery must update that representation while preserving a valid schema.

The documented NOT NULL syntax makes a small migration easier to state. This example starts with an empty table so that it makes no claim about resolving preexisting invalid rows.

CREATE TABLE labels(id INTEGER PRIMARY KEY, label TEXT);
ALTER TABLE labels ALTER label SET NOT NULL;

For an existing application, I would first define the behavior for missing labels and test the actual migration on a copy of representative data. Adding a database constraint should agree with the application's existing validation and write paths. A shorter DDL statement reduces the amount of migration code, but it does not resolve conflicting requirements about what existing records mean.

I would also retain a test that rejects an invalid subsequent write. Checking only the schema text verifies the declaration. Checking the write behavior verifies the application-facing property that motivated the change. Those tests should run against the library version that production will load.

Separate display changes from data changes

The new formatter improves human-readable CLI output, and the release preserves legacy formatting for batch sessions. Numeric values in tabular output are right-justified by default. I would review any workflow that reads interactive terminal output and makes assumptions about its layout, even if normal batch scripts remain unchanged.

SQLite also documents its floating-point representation. REAL values use binary floating-point approximations. Displaying more significant digits does not turn those values into exact decimal quantities. That distinction matters when a maintenance comparison exposes longer rendered numbers and someone interprets the textual difference as a change to all stored values.

I would keep the maintenance decision with the release configuration. A record that says only that SQLite was upgraded omits whether the operator rebuilt derived keys or relied on later repair during writes. That omission makes a subsequent investigation harder because two apparently identical deployments may have followed different maintenance procedures. The record should identify the input database and the operation selected for it.

The same record should distinguish a check that found nothing from an operation that changed something. Otherwise a later maintainer cannot tell whether the initial database already met the expected conditions. This requires little prose when the commands and their outputs are retained together.

For deployment, I would inventory expression indexes before choosing a rebuild policy, then test constraint migrations independently. The release's maintenance commands are useful because they let those decisions appear directly in a script. The script still needs a stated scope and an expected result for the particular database being maintained.