SQLite fixes corruption in automatic space reclamation

Returning unused space to the filesystem requires SQLite to change the physical arrangement of a database. A deleted row can leave a free page inside the file, while shrinking the file requires its trailing pages to become unused. That difference makes automatic space reclamation part of the storage engine. SQLite 3.19.3, released on 8 June, fixes an auto_vacuum bug that can corrupt a database.

The release notes trace the regression to version 3.16.0, released on 2 January. This is therefore relevant to applications that adopted the January release or its successors, even if they have not adopted the May planner changes. The patch has one new fix. The much longer list above it on the release page describes changes inherited from earlier releases in the same series.

I regard this as a more consequential maintenance release than its size suggests. SQLite is attractive because an application can own a database file directly. A defect in reclaiming that file affects the persistence guarantee that makes the arrangement useful, independently of whether the application needs any new SQL functionality.

Free space inside a database

The auto-vacuum tests shipped with 3.19.3 make the difference between logical deletion and physical layout concrete. They create a table and an index, insert rows large enough to use overflow pages, and delete those rows in several orders. After each transaction, the tests check the remaining data and run PRAGMA integrity_check. Once all rows are gone, they also inspect the file size.

Those are separate properties. Returning the correct remaining rows checks the logical contents. An integrity check examines database consistency. The file-size assertion checks that space reclamation actually occurred. A storage change can satisfy one property while breaking another, so a query that happens to return expected data is not a complete test of this mechanism.

The same test file exercises table and index root pages. Creating a new table can require moving an existing data page to make room for a root page. Dropping a table can also cause another root page to move into a vacated location. References to moved pages must remain correct after those operations.

This is why automatic reclamation is more involved than trimming unused bytes from a file. A page can still contain live data even when some other page is free. The engine needs to reorganize the file so that truncation removes only unused storage. Its internal references must describe the new arrangement when the transaction finishes.

Moving a live trailing page

The released B-tree implementation describes incrVacuumStep() as an attempt to make the last page currently in use become unused. Its inputs include the desired final size and whether the operation is part of vacuuming at commit. The implementation examines pointer-map information to determine what kind of page occupies the end of the file.

When the trailing page is already free, the incremental path removes it from the free list. When it contains live content, the code finds a free destination page and relocates the content. The source distinguishes an incremental operation from an operation that continues until the intended commit-time size has been reached. That distinction changes how it chooses destination pages and handles free-list entries.

The incremental entry point also saves cursors and invalidates cached overflow information before moving pages. These actions concern state held by the running engine, beyond the bytes eventually written to storage. A page relocation must remain consistent with the objects that refer to pages during execution.

The commit-time function describes its own result in terms of a truncation boundary. A successful operation reorganizes the database so that only the first specified number of pages remain in use. The pager can then truncate the file during commit. This gives the storage operation a precise objective, while leaving logical table contents unchanged.

These implementation details explain the general mechanism. They do not establish the exact trigger for the newly fixed regression. The release notes identify the affected subsystem, its corruption consequence and the introducing release. That evidence is enough to establish the relevance of the patch without inventing a narrower workload description.

Configuring a new database

The release tests enable automatic vacuuming before creating tables. This short example follows the same ordering for a new database.

PRAGMA auto_vacuum = 1;
CREATE TABLE records(id INTEGER PRIMARY KEY, payload);
INSERT INTO records VALUES(1, example);
DELETE FROM records WHERE id = 1;
PRAGMA integrity_check;

The example demonstrates configuration and ordinary data operations. It is not a reproducer for the June corruption bug. Its final command asks SQLite to check the resulting database, but a successful result on this small input cannot show that an older library is unaffected by the regression.

The historical tests also check configuration across reopening a database. Other cases attempt to change the setting after creating a table and inspect the resulting value. That is a reminder that a database-level storage choice has rules beyond accepting a pragma statement. Treating the command as an unrestricted switch on any existing file would conceal those rules.

I find the tests useful here because they describe observable responsibilities of the engine. They check values after page movement and continue using indexes after tables have been dropped. That gives a more useful picture of storage correctness than treating an eventual reduction in file size as the only outcome that matters.

What the patch changes

Version 3.19.3 fixes the regression in the engine. The announcement does not claim to reconstruct data already damaged by it. Preventing a faulty operation and repairing its earlier consequences are different tasks, and the release statement supports the former.

The page also includes the earlier May fixes to LEFT JOIN flattening. Those belong to 3.19.1 and 3.19.2, while the auto-vacuum correction belongs to 3.19.3. Keeping those dates separate matters when assessing why an application needs this patch. The new reason concerns storage reclamation, regardless of whether the application uses a query shape affected by the earlier optimizer bugs.

The published source identifier and sqlite3.c digest identify the corrected release artifacts. For an embedded library, the relevant implementation is the one the application actually loads. The substantive change in that implementation is a correction to a storage operation that moves pages so the file can shrink while preserving its live contents.