VACUUM, ANALYZE, PRAGMA optimize, auto_vacuum, and Routine Maintenance

Learning outcomes

Maintenance should respond to state, not to a calendar superstition. Running VACUUM every night, ANALYZE every hour, and TRUNCATE checkpoints after every write can waste I/O and create latency spikes. A good SQLite maintenance loop observes planner, file, freelist, WAL, and integrity state, then runs the smallest justified action.

Four maintenance jobs that solve different problems

Current guidance: prefer PRAGMA optimize for statistics maintenance

Since SQLite 3.46.0, upstream documentation recommends PRAGMA optimize as the normal application interface for deciding when ANALYZE work is useful. It temporarily limits ANALYZE work so it finishes promptly even on large databases.

-- Short-lived connections: near closePRAGMA optimize;-- Long-lived connection: once when freshly openedPRAGMA optimize=0x10002;-- Then periodically during the connection lifetime, and after schema/index changesPRAGMA optimize;-- Diagnostic: report proposed optimizations without applying themPRAGMA optimize(-1);

A freshly opened connection has no query-history evidence, which is why current guidance adds the 0x10000 bit in 0x10002 so all tables can be considered. The exact internal decisions may evolve between releases; applications should not hard-code assumptions about which table optimize will analyze.

When direct ANALYZE still belongs in the toolbox

Direct ANALYZE is useful when you intentionally want to gather statistics for a database/table/index, for controlled plan-stability workflows, or for an experiment. Do not manually edit sqlite_stat* in ordinary maintenance.

SELECT name FROM sqlite_schemaWHERE name GLOB 'sqlite_stat*'ORDER BY name;SELECT * FROM sqlite_stat1 ORDER BY tbl, idx;-- Controlled experiment only:ANALYZE perf_reading;EXPLAIN QUERY PLANSELECT reading_id FROM perf_readingWHERE device_id=17ORDER BY observed_at DESC LIMIT 20;

Statistics are not automatically refreshed as data distribution changes. That is why production tests should use realistic distributions and why optimize/ANALYZE belongs after meaningful schema/data evolution—not on every request.

VACUUM: justified rebuild, not routine ritual

DELETE usually puts pages on the freelist for reuse rather than shrinking the operating-system file. That is often fine: reusable free pages can make later inserts cheaper. VACUUM is justified when you actually need a smaller compact file, want to reclaim substantial internal fragmentation, or need one of its documented format-changing effects.

PRAGMA page_size;PRAGMA page_count;PRAGMA freelist_count;PRAGMA auto_vacuum;-- If policy and evidence justify a full rebuild:VACUUM;PRAGMA page_count;PRAGMA freelist_count;

VACUUM can require additional disk space, performs extensive I/O, needs suitable locking, and can change ROWIDs of tables without an explicit INTEGER PRIMARY KEY. Do not schedule it blindly in latency-sensitive hours.

auto_vacuum and incremental vacuum

auto_vacuum=FULL moves freelist pages to the end and truncates them at commits, which can increase fragmentation and work. INCREMENTAL records the same reverse-mapping metadata but leaves truncation to PRAGMA incremental_vacuum. The database must be configured for the mode appropriately; changing from NONE to FULL/INCREMENTAL on an established database generally requires VACUUM.

PRAGMA auto_vacuum;       -- 0 NONE, 1 FULL, 2 INCREMENTALPRAGMA freelist_count;-- Only meaningful when auto_vacuum=INCREMENTAL:PRAGMA incremental_vacuum(100);PRAGMA freelist_count;

WAL checkpoint maintenance

WAL mode needs checkpoints so committed frames are copied back to the main database and the WAL can be reused. New connections default to automatic PASSIVE checkpointing at 1000 pages (or the compile-time default). Long-lived readers can prevent a checkpoint from completing all frames, so WAL growth is often a reader-lifetime symptom rather than a reason to spam TRUNCATE.

PRAGMA journal_mode;PRAGMA wal_autocheckpoint;-- NOOP reports status without doing checkpoint work in current SQLite.PRAGMA wal_checkpoint(NOOP);-- PASSIVE does as much as possible without waiting for readers/writers.PRAGMA wal_checkpoint(PASSIVE);

A conservative maintenance script

This example reports state first, runs integrity checks separately from performance actions, and allows policy flags to choose expensive operations. It does not assume that a high freelist count is bad.

from pathlib import Pathimport sqlite3DB = Path("fieldnotes.sqlite")con = sqlite3.connect(DB, isolation_level=None)con.execute("PRAGMA foreign_keys=ON")state = {    "sqlite_version": con.execute("SELECT sqlite_version()").fetchone()[0],    "journal_mode": con.execute("PRAGMA journal_mode").fetchone()[0],    "page_count": con.execute("PRAGMA page_count").fetchone()[0],    "freelist_count": con.execute("PRAGMA freelist_count").fetchone()[0],    "auto_vacuum": con.execute("PRAGMA auto_vacuum").fetchone()[0],}print(state)print("quick_check:", con.execute("PRAGMA quick_check").fetchone()[0])print("fk_violations:", con.execute("PRAGMA foreign_key_check").fetchall())# Planner maintenance: current upstream-recommended interface.con.execute("PRAGMA optimize")if state["journal_mode"].lower() == "wal":    print("checkpoint:", con.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone())# VACUUM is NOT automatic here. A separate operational policy decides# whether file shrink/compaction is worth the lock + I/O + space cost.con.close()

Maintenance decision flow

Planner regression / major data-shape change?  -> inspect plan + statistics  -> PRAGMA optimize (normal path)  -> direct ANALYZE only for a deliberate reasonLarge file after deletes?  -> page_count + freelist_count + future growth needs  -> if free pages will be reused: do nothing  -> if compact file is operationally required: schedule VACUUM  -> if INCREMENTAL auto_vacuum: consider bounded incremental_vacuumGrowing WAL?  -> inspect reader lifetimes + checkpoint result  -> keep transactions/read cursors short  -> tune checkpoint ownership only after measurementCorruption suspicion?  -> this is NOT a performance-maintenance task  -> backup/recovery/integrity runbook from Chapter 16

Checkpoint

Bridge to the performance review

By now you have measured queries, transaction boundaries, configuration, and maintenance. Lesson 5 asks the harder question: what if the database is slow because the application asks SQLite to do the wrong work—or because the workload has outgrown the embedded architecture?

Authoritative references

• SQLite ANALYZE
• SQLite PRAGMA optimize / VACUUM / auto_vacuum
• SQLite VACUUM
• SQLite WAL and checkpoints