SQLite and RocksDBintermediate

SQLite File Management

Managing the database file, vacuuming, integrity checks and taking a backup that is actually consistent.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

The files

In WAL mode a database is three files, and they belong together:

app.db — the database.
app.db-wal — the write-ahead log; contains committed data not yet checkpointed.
app.db-shm — shared memory index, recreated as needed.

Backup

# Consistent online backup: safe while the database is in use.
sqlite3 app.db ".backup '/backups/app-$(date -u +%Y%m%dT%H%M%SZ).db'"
 
# A text dump: portable across versions, slower to restore.
sqlite3 app.db .dump | zstd > /backups/app.sql.zst

# The backup API, incrementally, from application code.
source = sqlite3.connect("app.db")
target = sqlite3.connect("/backups/app.db")
with target:
    source.backup(target, pages=64, sleep=0.01)

The backup API copies pages while allowing other connections to continue, restarting if the source is modified during the copy. It is the correct mechanism for a live database.

Verify the copy rather than trusting it:

sqlite3 /backups/app-20260731.db "PRAGMA integrity_check;"
sqlite3 /backups/app-20260731.db "SELECT count(*) FROM orders;"

Integrity and maintenance

PRAGMA integrity_check;        -- full verification; slow on a large file
PRAGMA quick_check;            -- faster, less thorough
PRAGMA foreign_key_check;      -- validate referential integrity

ANALYZE;                       -- refresh query planner statistics
PRAGMA optimize;               -- run periodically; performs recommended maintenance

PRAGMA optimize before closing a long-lived connection is a cheap habit that keeps statistics current.

VACUUM

Deleting rows leaves free pages inside the file; the file does not shrink.

VACUUM;                             -- rebuilds the file, reclaiming space
PRAGMA auto_vacuum = INCREMENTAL;   -- must be set before the database is populated
PRAGMA incremental_vacuum(1000);    -- reclaim in bounded steps

Corruption

SQLite databases are robust, and corruption is almost always caused by the environment:

A database on NFS or SMB accessed from more than one host.
Copying the .db file without the -wal.
A filesystem or device that reports an fsync as complete before it is.
Two processes with different locking assumptions, such as a container bind mount across hosts.

# Attempt recovery: dump what is readable into a new database.
sqlite3 corrupted.db ".recover" | sqlite3 recovered.db
sqlite3 recovered.db "PRAGMA integrity_check;"

.recover extracts what it can and is not guaranteed to be complete — which is why the backup matters more than the recovery procedure.