SQLite VACUUM INTO builds a compact database without replacing the source

Compacting a database and replacing the database being used are separate operations. Ordinary SQLite VACUUM joins them together. It builds another database, copies the content into it, then transfers that rebuilt content back into the original. SQLite 3.27.0, released on 7 February, adds VACUUM INTO, which lets the rebuilt database remain in a named destination. The source database does not need to become the output of the compaction.

That is useful whenever the desired artifact is a separate database file. A publisher preparing a dataset for distribution may want the compacted result while leaving the working database alone. A backup workflow may also want a new database containing the retained content without carrying the source's unused space. The command gives these operations a direct SQL expression, with the rebuild performed by SQLite itself.

I value SQLite's database file as a unit that can be inspected and transferred independently of an application server. This addition extends that property in a practical direction. Producing a new file no longer requires asking an in-place maintenance command to alter the working copy first. The output path becomes part of the database operation.

What ordinary VACUUM has to do

The vacuum implementation in the 3.27.0 source explains the older algorithm in a comment. It creates a transient database, copies the content into it, then copies that content back into the original database. The comment accounts for temporary storage for the rebuilt database and for the rollback journal used during the copy back. Its approximate two-times space estimate describes that ordinary rebuild-and-replace path.

Keeping the original file in place has a reason. The same comment considers deleting the original and renaming the transient file as an alternative. Other processes may already have the original open. There is also a failure interval between deleting one file and renaming the other. The implementation consequently preserves the original file and transfers the rebuilt content through SQLite's database machinery.

VACUUM INTO changes the desired result. A caller asking for a new output file does not need that final transfer into the source. The code shares the rebuilding logic with ordinary VACUUM, then conditions the copy-back operation on the absence of an output argument. This explains both the command's usefulness and the limits of the change. SQLite still has to reconstruct the database in the destination.

The distinction also prevents an incorrect interpretation of the ordinary command's space estimate. A comment describing the rollback journal for replacing the source is not a universal storage formula for every invocation with INTO. The output file remains a storage requirement, and the source must remain readable while the reconstruction runs. The omitted copy-back stage changes the work being requested.

Naming the output

The basic form identifies the destination as a SQL string. A schema name can identify the database to rebuild when that needs to be explicit.

VACUUM main INTO 'catalogue-compact.db';

The release's VACUUM INTO regression tests exercise this form against a table containing binary values and an index. The fixture inserts rows, deletes a subset and creates the output database. A second connection opens the resulting file and checks the row count, the sum of the integer keys and the total length of the binary values. Those checks establish content preservation for that fixture after deletion and rebuilding.

The filename argument is an expression, rather than a grammar production restricted to a literal. The same tests obtain it through a subquery and through a registered SQL function. A null value fails with a non-text filename error. An unresolved identifier fails as an unresolved column. These are useful details for applications that generate the destination through SQL, because filename evaluation follows expression rules.

An application can keep the common case simple by using a text destination. The command does not infer a path from an unquoted bare word. Ordinary SQL string quoting remains relevant even though the result identifies a file. Treating the output argument as an expression also means that the caller is responsible for supplying the intended value, just as with other SQL expressions.

The implementation rejects a destination file whose size is greater than zero. Its test suite verifies that a second attempt to write the same populated output fails. This is a refusal to overwrite an existing database, rather than an implicit replacement policy. A workflow producing successive artifacts needs distinct names or an explicit decision about removing an old destination before starting another operation.

How SQLite reconstructs the file

The source attaches the destination under an internal database name. It then reads table definitions from the source schema and executes corresponding creation statements in the destination. Index definitions follow. SQLite copies table contents with generated INSERT INTO ... SELECT * FROM ... statements. This is a reconstruction using the database's schema and retained rows, rather than an unexamined copy of source file bytes.

Views and triggers need different treatment because they do not own ordinary table storage. The implementation copies their schema entries after copying the table contents. It also handles virtual table schema entries in that stage. Describing these phases matters because a usable database consists of more than the visible rows of its largest table. The operation needs schema information to preserve how those rows can be queried.

Selected database metadata also transfers. The source lists the schema version, default cache size, text encoding, user version and application identifier in the metadata copying code. The schema version receives an increment. These details make the resulting file suitable for database use without requiring a text dump to be parsed by a separate import program.

There is an important identity detail in the table copy. The generated statement copies the columns selected by SELECT *. It does not explicitly select and reinsert an otherwise hidden rowid. An application should therefore distinguish a declared key that participates in the copied table columns from an incidental hidden row identifier. The operation's purpose is to rebuild the database, and its source shows exactly which values the copying statement names.

Transaction and durability boundaries

The command refuses to run inside an existing transaction. The code checks the connection's autocommit state before proceeding and also rejects other SQL statements still in progress on that connection. This prevents treating the new syntax as an arbitrary statement that can be placed in the middle of a larger application transaction. Its transaction management belongs to the vacuum operation itself.

When an output is present, the source opens a read transaction on the database being copied. Ordinary VACUUM requests a write transaction for the source because it will copy the rebuilt database back. That branch is a concrete explanation of how the new command leaves the source unchanged. Both forms need access to the source content, but only one intends to replace it.

The destination also has a durability qualification visible in this version's implementation. SQLite sets its pager to synchronous off. A successful reconstruction and a guarantee that storage has survived a sudden power failure are different claims. A publication workflow that needs additional filesystem durability must account for that requirement outside the simple fact that a database file has been produced.

Page size handling supplies another example of shared behavior with ordinary vacuuming. The implementation checks whether the source uses WAL and clears a pending page size change in that case. Its comment explicitly says not to attempt a page size change for a WAL database. The new output argument therefore does not make every setting independent of the source database's state. Reading the branches around the shared reconstruction code is necessary to distinguish a new destination from an unrestricted database conversion facility. The release adds a targeted operation with existing vacuum machinery behind it.

I find this explicit separation useful when reasoning about backups. The command defines how a new database is constructed. Naming, retaining and making that artifact durable remain decisions for the surrounding application. There is no need to invent a general backup policy to understand the improvement in the SQL interface.

For an application whose next step is to distribute or archive another SQLite file, VACUUM INTO expresses the relevant transformation directly. The engine recreates schema and content in the destination, retains selected database metadata and avoids copying the rebuilt database back into the source. The resulting artifact can then be opened as a SQLite database through a separate connection, which is precisely the behavior exercised by the release's regression test.