SQLite ANALYZE and VACUUM: Statistics and Reclaiming Space

Two Different Maintenance Jobs

ANALYZE and VACUUM get mentioned in the same breath, but they fix different problems.

• ANALYZE collects statistics about your data so the query planner makes smarter choices. It writes to a table called sqlite_stat1 and doesn't touch your actual rows.
• VACUUM rebuilds the file itself to reclaim unused pages and defragment storage. It doesn't change query plans directly.

If queries are picking the wrong index, you want ANALYZE. If the file is bigger than it should be after a lot of deletes, you want VACUUM. Confusing them leads to a lot of wasted maintenance time.

What ANALYZE Actually Does

The query planner has to guess. When it sees WHERE status = 'active', it needs to estimate how many rows match — one? a million? — to decide whether to use an index or scan the table. Without statistics, it falls back to crude heuristics.

ANALYZE walks each index and records summary information about how values are distributed:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

The sqlite_stat1 row tells the planner roughly how many rows the index has and how many duplicates a typical key has. Next time you query WHERE status = 'pending', it knows pending is rare and reaches for the index; for WHERE status = 'shipped', it might decide a scan is cheaper.

You can analyze a single table or index instead of the whole database:

ANALYZE orders;
ANALYZE idx_orders_status;

Run ANALYZE after bulk loads, after large schema changes, or when you notice the planner picking poor plans on tables whose distribution has shifted.

PRAGMA optimize: The Modern Default

Running ANALYZE blindly on every connection close is wasteful — most of the time, nothing has changed enough to matter. SQLite ships a smarter wrapper:

PRAGMA optimize;

PRAGMA optimize checks how the database has changed since the last analysis and runs ANALYZE only on tables that need it. The official recommendation is to call it on each long-lived connection right before closing, and periodically on connections that stay open for hours.

It's cheap when nothing's changed and effective when something has. Reach for optimize first; reach for raw ANALYZE only when you need to force a refresh.

What VACUUM Actually Does

When you delete rows or drop a table, SQLite marks those pages as free but doesn't shrink the file. The free pages get reused by future inserts, so most of the time this is fine. But two things accumulate over a lifetime of churn:

1. Free space the OS doesn't see. Your .db file stays at 2 GB even though only 800 MB is live data.
2. Fragmentation. Rows for the same table end up scattered across non-adjacent pages, hurting scan performance.

VACUUM fixes both by copying the entire database into a fresh file, packed tightly, and replacing the original:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

After VACUUM, the file is the size it would be if you'd inserted only the surviving 100 rows from scratch. As a side effect, all rowids stay the same but the on-disk layout is contiguous again.

A few things to know before running it:

• It needs an exclusive lock on the database for the duration. No other connection can write.
• It needs roughly twice the database size in free disk space — it builds the new file alongside the old one.
• It can't run inside a transaction, and it'll error if any active transactions are open.
• On a multi-GB database, it can take a long time. Plan for it.

When to Actually Run VACUUM

For most applications: don't, unless something specific changed.

Good reasons to run VACUUM:

• You just dropped a large table or deleted a huge batch of rows and want disk space back.
• The database has been churning for years and queries that scan tables feel slower than they used to.
• You're shipping a database file as part of a release and want it as small as possible.

Bad reasons:

• "Just to be safe." It rewrites the whole file every time. There's nothing safe about doing that on a live system.
• After every batch of deletes. The freed pages would have been reused anyway.

auto_vacuum and Incremental VACUUM

If you want SQLite to manage free pages automatically, you set auto_vacuum at database creation — it can't be changed later without a full vacuum:

PRAGMA auto_vacuum = INCREMENTAL;

Three modes:

• NONE (default): free pages stay in the file, reused on later inserts.
• FULL: every commit that frees pages also truncates the file. Convenient, but each transaction pays the cost.
• INCREMENTAL: SQLite tracks free pages but only releases them when you ask:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) releases up to N free pages back to the OS — fast, doesn't take an exclusive lock for long, and you can run it on a schedule. This is the sweet spot for write-heavy databases that need to stay compact without the cost of a full VACUUM.

VACUUM INTO: Exporting a Compact Copy

VACUUM INTO writes a fresh, compact copy to a new file without touching the original:

VACUUM INTO 'backup.db';

This is genuinely useful:

• Backups. The output is a consistent, fully-vacuumed snapshot — no half-written pages, no .wal to worry about. Better than copying the file with cp.
• Shrinking without locking writers for long. You vacuum to a side file, then atomically swap. Writers aren't blocked for the entire vacuum duration.
• Distribution. Ship a tiny, defragmented copy of a development database.

The destination file must not exist. If it does, you'll get an error.

A Practical Maintenance Recipe

For a typical application database:

-- On each long-lived connection, before close:
PRAGMA optimize;

-- After a large bulk load or schema change:
ANALYZE;

-- After you delete a lot of data and want disk back:
VACUUM;

-- For backups:
VACUUM INTO '/backups/app-2026-04-23.db';

If the database is heavily write/delete-driven and stays online 24/7, set auto_vacuum = INCREMENTAL at creation time and run PRAGMA incremental_vacuum(N) periodically — perhaps once a day during low traffic.

Diagnosing "Why Is My File So Big?"

Two pragmas tell you what's going on:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = current file size.
• freelist_count × page_size = bytes wasted on unused pages.

If freelist_count is a large fraction of page_count, a VACUUM (or incremental_vacuum) will visibly shrink the file. If it's small, the file is already efficiently packed and VACUUM won't help.

Common Pitfalls

• Running VACUUM inside a transaction. It can't. Commit first.
• Forgetting VACUUM needs free disk space. A 10 GB database needs another ~10 GB free to vacuum.
• Setting auto_vacuum after data exists. It's a no-op until the next full VACUUM. Set it when the database is created if you want it.
• Running ANALYZE and expecting smaller files. That's VACUUM's job.
• Running VACUUM and expecting better query plans. That's ANALYZE's job.

The two commands complement each other; neither replaces the other.

Next: Transactions

Maintenance commands like VACUUM highlight something we've taken for granted: SQLite's transactional model and what it locks, when. The next chapter starts there — how transactions work, what BEGIN / COMMIT / ROLLBACK actually guarantee, and how to use them to keep multi-statement work atomic.