Can SQLite VACUUM Cause Data Loss in Corrupted Databases?

Understanding VACUUM’s Role in SQLite Database Maintenance

The SQLite VACUUM command is designed to rebuild the database file, repacking it into a minimal disk space footprint by reconstructing the B-tree structures and eliminating fragmented or unused pages. This process is often used to optimize performance and reclaim storage. However, when applied to a corrupted database, the behavior of VACUUM becomes a critical concern. The core issue revolves around whether executing VACUUM on a damaged database could exacerbate data loss or fail to preserve existing data.

Mechanics of VACUUM
The VACUUM operation creates a new database file by traversing the existing database’s B-trees (structures that organize table and index data) and copying their contents into contiguous pages. This process discards empty pages, defragments the file, and resets unused space. Crucially, VACUUM operates under the assumption that the database schema and B-tree linkages are intact. If the original database contains corruption—such as orphaned pages, broken indexes, or invalid row pointers—the VACUUM process may omit these elements during reconstruction.

Data Loss vs. Data Exclusion
A key distinction must be made between data loss (irreversible deletion of valid data) and data exclusion (failure to copy data due to corruption). When VACUUM encounters a corrupted B-tree, it cannot reliably distinguish between invalid data (e.g., pages unlinked from tables) and valid but fragmented data. The command will proceed to rebuild the database based on the schema’s logical structure, excluding any data that is not properly anchored to the B-trees. This exclusion is not an intentional deletion but a consequence of the database’s preexisting corruption.

The Role of Corruption in VACUUM Outcomes
A healthy database will not lose data during VACUUM. However, in a corrupted state, the outcome depends on the nature and extent of the corruption. For example:

• If a table’s root page is damaged, VACUUM may skip entire sections of data linked to that table.
• Index corruption might cause VACUUM to omit indexed data, though the underlying table data could still be preserved if intact.
• Free-list errors (misaccounting of unused pages) might lead VACUUM to skip pages erroneously marked as free.

The unpredictability lies not in VACUUM itself but in the undefined state of the corrupted database.

Why VACUUM Might Exclude Data in Corrupted Databases

1. Orphaned Data Pages
SQLite relies on B-tree structures to map data pages to tables and indexes. Corruption can break these linkages, leaving data pages "orphaned"—no longer referenced by any B-tree. During VACUUM, the command rebuilds the database by iterating through the B-trees. Orphaned pages, not being part of any B-tree, are not copied to the new database file. This exclusion is irreversible once the VACUUM completes.

2. Schema-Level Corruption
If the sqlite_schema table (which stores database schema metadata) is corrupted, VACUUM may misinterpret the database’s structure. For instance, a missing or invalid table definition could cause VACUUM to skip entire tables during reconstruction. Schema corruption is particularly dangerous because it undermines the command’s ability to identify and preserve data.

3. Index-Table Discrepancies
Indexes in SQLite are separate B-trees that reference table rows via row IDs. If an index is corrupted but the underlying table is intact, VACUUM will rebuild the index, potentially discarding invalid index entries. However, if the table itself is corrupted, rebuilding indexes (via REINDEX) may not resolve the issue.

4. Journaling and Rollback Mechanisms
SQLite’s atomic commit and rollback mechanisms depend on write-ahead logging (WAL) or rollback journals. If a database is corrupted due to incomplete writes or filesystem issues, these mechanisms may leave the database in an inconsistent state. VACUUM does not repair such inconsistencies; it rebuilds the database based on its current state, potentially solidifying corruption-related data loss.

5. Filesystem and Hardware Issues
Underlying storage problems (e.g., bad sectors, incomplete writes) can corrupt database files. VACUUM cannot detect or mitigate such issues. For example, if a page is partially overwritten due to a power failure, VACUUM may treat it as valid or invalid depending on checksum verification, leading to unpredictable outcomes.

Mitigating Data Loss Risks During Database Repair

Step 1: Prioritize Backup Preservation
Before attempting any repair, create a read-only copy of the corrupted database and its associated files (e.g., WAL files, shared memory files). Use operating system-level commands to ensure backups are immutable:

cp corrupted.db corrupted_backup.db  
chmod a-w corrupted_backup.db  

Work exclusively on writable copies to avoid altering the original corrupted state.

Step 2: Assess Corruption with PRAGMA integrity_check
Run the SQLite integrity check to identify corruption scope:

PRAGMA quick_check;  
PRAGMA integrity_check;  

quick_check verifies B-tree consistency, while integrity_check performs a deeper scan. If errors are reported, note the affected tables and indexes.

Step 3: Use .dump to Extract Salvageable Data
The .dump command in the SQLite CLI generates a SQL script containing schema and data. This script may bypass some forms of corruption by reading data through high-level queries rather than low-level B-tree traversal:

sqlite3 corrupted.db .dump > salvage.sql  
sqlite3 repaired.db < salvage.sql  

Inspect salvage.sql for errors during the dump process. Missing data or malformed INSERT statements indicate irrecoverable corruption.

Step 4: Execute VACUUM on a Copy
If proceeding with VACUUM, apply it to a copy of the database:

VACUUM INTO 'vacuumed.db';  

This leaves the original file untouched. After vacuuming, run PRAGMA integrity_check on the new file to verify its state.

Step 5: Rebuild Indexes Strategically
If indexes are corrupted but tables are intact, use REINDEX:

REINDEX;  

Alternatively, drop and recreate indexes manually. Avoid REINDEX if table corruption is suspected.

Step 6: Leverage SQLite’s Recovery Tools
For severe corruption, use tools like sqlite3_recover (a paid extension) or open-source alternatives to extract data. These tools bypass SQLite’s standard APIs to read raw database pages, offering a last-resort recovery method.

Step 7: Validate Against Backups
Compare the repaired database with the most recent backup. Use checksums or row-count queries to identify discrepancies:

SELECT COUNT(*) FROM table_name;  

Ensure transactional consistency by verifying foreign key constraints and triggers.

Step 8: Implement Corruption Prevention Measures
To minimize future corruption risks:

• Enable PRAGMA journal_mode = WAL; for robust transaction logging.
• Use PRAGMA synchronous = FULL; to ensure writes are fully flushed to disk.
• Avoid network filesystems for database storage.
• Regularly test backups with PRAGMA integrity_check.

Final Note on VACUUM’s Safety
In uncorrupted databases, VACUUM is safe and will not cause data loss. In corrupted databases, data exclusion is possible but not guaranteed. The safest approach is to treat VACUUM as a non-recovery tool and prioritize backups and data extraction methods that minimize write operations on the corrupted file.