The auto_vacuum PRAGMA in SQLite: A Complete Guide

Abdul Wahab Junaid

2 years ago

If you’ve ever deleted a large chunk of data from a SQLite database and noticed the file size on disk didn’t shrink to match, you’ve bumped into one of SQLite’s more surprising behaviors. It turns out that deleting rows doesn’t automatically reclaim disk space — at least not without a little help. That’s where the auto_vacuum PRAGMA comes in, and understanding it can save you from confusing, bloated database files down the road.

Let’s walk through exactly how auto_vacuum works, the different modes available, and how to decide which one is right for your project.

What Is the auto_vacuum PRAGMA?

When you delete rows or drop tables in SQLite, the space those rows occupied on disk isn’t immediately returned to the operating system. Instead, SQLite marks those pages as “free” and keeps track of them internally, ready to reuse them the next time you insert new data. This is actually a sensible design — reusing freed pages is faster than constantly resizing the file on disk. But it also means your database file can end up larger than the data it actually contains, sometimes significantly so, especially after large deletions.

The auto_vacuum PRAGMA controls whether and how SQLite automatically reclaims this unused space and shrinks the database file. There are three modes:

NONE (0) — the default. Free pages are tracked and reused internally but never automatically returned to the OS; the file never shrinks on its own.
FULL (1) — every time a transaction commits and frees up pages, SQLite automatically moves data around and truncates the file to reclaim that space immediately.
INCREMENTAL (2) — free pages are tracked, but the file is only shrunk when you explicitly ask for it, via the incremental_vacuum PRAGMA, giving you control over when the (potentially expensive) reclaiming work happens.

Basic Syntax

Checking the current mode:

PRAGMA auto_vacuum;

This returns 0, 1, or 2, corresponding to NONE, FULL, and INCREMENTAL respectively.

Setting the mode:

PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA auto_vacuum = NONE;

You can also use the numeric equivalents:

PRAGMA auto_vacuum = 1;  -- FULL
PRAGMA auto_vacuum = 2;  -- INCREMENTAL
PRAGMA auto_vacuum = 0;  -- NONE

Here’s an important catch, similar to the encoding PRAGMA: changing auto_vacuum on an existing database with data in it doesn’t take effect immediately. If you change the mode on a non-empty database, the new setting is stored but won’t actually reorganize existing data until you run a full VACUUM command:

PRAGMA auto_vacuum = FULL;
VACUUM;

Only after running VACUUM does the database physically reorganize itself to match the newly requested auto-vacuum mode. For best results and to avoid confusion, it’s cleanest to set auto_vacuum before creating any tables in a brand-new database.

How Each Mode Works in Practice

NONE (default)

PRAGMA auto_vacuum = NONE;

This is what you get if you never touch the setting. Deleted data leaves behind free pages inside the file, which SQLite happily reuses for future inserts, but the file itself never shrinks. If you want to reclaim space manually, you run:

VACUUM;

This command rebuilds the entire database file from scratch, compacting it down and eliminating fragmentation and unused space — but it’s a potentially expensive, all-at-once operation, especially for large databases, since it essentially rewrites the whole file.

FULL

PRAGMA auto_vacuum = FULL;

With this mode, every COMMIT that frees up pages triggers SQLite to automatically move data to fill the gaps and truncate the file, keeping the database file’s size roughly proportional to its actual content at all times. This sounds great in theory, but it comes with a real performance cost: every deleting transaction now does extra work to reorganize the file, which can slow down write-heavy workloads.

INCREMENTAL

PRAGMA auto_vacuum = INCREMENTAL;

This is the middle ground. Free pages are tracked just like in FULL mode, but the actual reclaiming and file truncation only happens when you explicitly request it:

PRAGMA incremental_vacuum;

or, to limit how many pages get reclaimed in a single pass (useful for spreading the work out and avoiding long pauses):

PRAGMA incremental_vacuum(500);

This tells SQLite to reclaim up to 500 pages in this call, letting you break up the vacuuming work into smaller, more predictable chunks rather than doing it all in one potentially slow operation.

Practical Examples

Example 1: Setting up a new database with FULL auto-vacuum

sqlite3 newapp.db
sqlite> PRAGMA auto_vacuum = FULL;
sqlite> CREATE TABLE logs (id INTEGER PRIMARY KEY, message TEXT, created_at TEXT);

Because this PRAGMA was set before any tables were created, FULL mode is active from the start, and the database file will automatically shrink as old log entries are deleted.

Example 2: Converting an existing database to INCREMENTAL mode

sqlite3 existing.db
sqlite> PRAGMA auto_vacuum = INCREMENTAL;
sqlite> VACUUM;

Running VACUUM after changing the setting is essential here — without it, the database would keep its old NONE-mode behavior despite the PRAGMA reporting the new value.

Example 3: Manually reclaiming space in INCREMENTAL mode

DELETE FROM logs WHERE created_at < '2024-01-01';

PRAGMA incremental_vacuum(1000);

This deletes old log rows and then reclaims up to 1,000 freed pages, shrinking the file without the potentially heavier all-at-once cost of a full VACUUM.

Example 4: Checking the current mode before making assumptions

sqlite> PRAGMA auto_vacuum;
0

A result of 0 confirms the database is running in NONE mode — a good reminder to check before assuming your database will automatically shrink after large deletions.

Common Use Cases

Mobile and embedded applications with limited storage. Apps running on phones or IoT devices, where disk space is at a premium, often benefit from FULL or INCREMENTAL auto-vacuum to avoid database files ballooning over time.
Applications with frequent large deletions. Log rotation systems, message retention policies, or cache-like tables that regularly purge old data are prime candidates for auto-vacuum, since without it, the file size would just keep climbing even as content shrinks.
Long-running databases where periodic manual VACUUM is impractical. If your application can’t easily schedule downtime for a full VACUUM operation (which locks the database while it runs), INCREMENTAL mode lets you reclaim space gradually during normal operation instead.
Systems where write throughput matters more than file size. In these cases, NONE mode combined with occasional, deliberately scheduled VACUUM operations (during low-traffic periods) is often the better choice, since FULL mode’s per-transaction overhead can meaningfully slow down heavy write workloads.

Important Considerations

FULL mode has a real performance cost. Every commit that frees pages triggers immediate reorganization work. For applications with frequent deletes or updates that free pages, this ongoing overhead can add up, especially compared to the “reuse pages internally, deal with size later” approach of NONE mode.

VACUUM (and by extension FULL/INCREMENTAL reclaiming) requires free disk space. Because these operations essentially rebuild parts of the database file, you need enough temporary free disk space, roughly equal to the size of the database itself in the worst case, for the operation to complete.

auto_vacuum mode is stored in the database file itself. Unlike cache_size, which is a per-connection setting, auto_vacuum is a persistent property of the database file (similar to encoding). Once set (and applied via VACUUM if needed), it stays in effect across all future connections until explicitly changed again.

Switching modes on a large existing database can be slow. Running VACUUM on a large database is not a trivial operation — it can take a significant amount of time and temporarily lock the database, so plan accordingly, ideally during a maintenance window rather than during peak usage.

Incremental vacuum needs to be triggered deliberately. Unlike FULL mode, INCREMENTAL mode doesn’t reclaim space automatically as part of normal operation — you (or your application’s scheduled maintenance logic) need to periodically call PRAGMA incremental_vacuum for space to actually be reclaimed.

Best Practices

Use NONE (the default) if file size isn’t a major concern. For many applications, especially ones where the database size stays relatively stable over time, the default behavior is perfectly fine, and you can periodically run manual VACUUM during maintenance windows if needed.
Choose FULL for applications where storage is tight and writes are lighter. Mobile apps, small utility tools, and systems with infrequent writes are good candidates, since the per-transaction overhead of FULL mode matters less when writes are rare.
Choose INCREMENTAL for write-heavy applications that still need space reclaimed over time. This gives you the best of both worlds — controlled, on-demand space reclamation without the constant overhead of FULL mode on every commit.
Always set auto_vacuum before creating tables in a new database when possible. This avoids the need for a potentially slow VACUUM operation down the line to apply the setting retroactively.
Schedule incremental_vacuum calls during low-traffic periods. If you’re using INCREMENTAL mode, treat space reclamation as a maintenance task — run it during off-peak hours, and consider passing a page-count limit to keep individual calls fast and predictable.
Monitor actual file size versus data size. Periodically compare your database file size against the amount of data you’d expect it to contain. A growing gap is a sign you may need to enable some form of auto-vacuum or schedule regular manual VACUUM operations.

Troubleshooting Common Issues

I changed auto_vacuum but my database file size didn’t shrink. This almost always comes back to the same root cause discussed earlier: changing the PRAGMA on a non-empty database only updates the setting for future behavior — it doesn’t retroactively reorganize existing data until you explicitly run VACUUM. Run VACUUM; once after changing the mode to apply it to your existing data, and from that point forward the new mode will govern how the database behaves going forward.

VACUUM is taking a very long time or seems to hang. For large databases, VACUUM genuinely can take a while, since it’s rebuilding the entire file from scratch. Make sure you’re running it during a period where you can tolerate the exclusive lock it holds on the database, and confirm you have enough free disk space available (roughly equal to the current database size) for the temporary copy it needs to create during the process.

My INCREMENTAL mode database isn’t shrinking even though I deleted a lot of data. Remember that INCREMENTAL mode tracks free pages but doesn’t reclaim them automatically — you need to explicitly call PRAGMA incremental_vacuum; (optionally with a page-count argument) for the file to actually shrink. If you never call this, INCREMENTAL mode behaves essentially like NONE mode in terms of file size, with the one difference being that the bookkeeping needed for eventual reclamation is already in place.

FULL mode is causing noticeable slowdowns in my write-heavy application. This is a known trade-off with FULL mode — every commit that frees pages triggers immediate file reorganization. If this overhead is causing real problems, consider switching to INCREMENTAL mode instead, which lets you control when that reorganization work happens, ideally scheduled during quieter periods rather than on every single write.

Frequently Asked Questions

Does auto_vacuum affect query performance, aside from the overhead of the vacuuming itself?

Not directly. The main performance consideration is the overhead of the reclaiming process itself (immediate in FULL mode, on-demand in INCREMENTAL mode). Query performance for normal SELECT, INSERT, UPDATE, and DELETE operations isn’t meaningfully affected by which auto-vacuum mode you’re using, beyond that reclaiming overhead.

Can I switch between FULL, INCREMENTAL, and NONE modes freely over the life of a database?

Yes, you can change modes as often as you like using PRAGMA auto_vacuum, but remember that each change on a non-empty database requires a subsequent VACUUM call to actually take effect and reorganize the existing data accordingly.

Is running VACUUM the same thing as enabling auto_vacuum?

No. VACUUM is a one-time, on-demand operation that reclaims space and defragments the database file, regardless of the current auto_vacuum setting. auto_vacuum controls whether and how that kind of reclaiming happens automatically as part of normal operation, without you needing to manually run VACUUM yourself.

Does INCREMENTAL mode require more setup than FULL mode?

Slightly, yes — INCREMENTAL mode requires you to actually schedule and call PRAGMA incremental_vacuum periodically, whereas FULL mode handles reclamation automatically without any further action needed from your application. This extra bit of setup is the trade-off for INCREMENTAL mode’s more predictable, controllable performance characteristics.

What happens if I never call incremental_vacuum after setting the mode to INCREMENTAL?

The database will simply keep accumulating free (but unreclaimed) pages internally, similar to NONE mode, except that the file has already been reorganized to support efficient future reclamation whenever you do decide to call incremental_vacuum. In other words, setting the mode without ever calling the reclaiming PRAGMA gives you the tracking infrastructure without the actual space savings.

Does auto_vacuum mode affect database file portability across systems?

No, the resulting database file remains a completely standard, portable SQLite file regardless of which auto-vacuum mode was used to manage it. The mode is purely an internal space-management detail and doesn’t affect compatibility with any standard SQLite tools or libraries on other systems.

A Simple Decision Guide

With three modes to choose from, it can help to have a straightforward set of questions to walk through when deciding which one fits your project.

Does your application delete or update large volumes of data regularly? If deletions and updates that free significant space are rare, the default NONE mode combined with an occasional manually scheduled VACUUM is probably sufficient, and you can skip the added complexity entirely.

Is disk space genuinely constrained in your deployment environment? On servers with ample storage, a database file that’s somewhat larger than its actual content usually isn’t a real problem. On mobile devices, embedded systems, or environments with strict storage quotas, keeping the file size close to actual content matters much more, and FULL or INCREMENTAL mode becomes more attractive.

Can your application tolerate a small amount of per-transaction overhead on every write? If writes are infrequent or your application isn’t latency-sensitive, FULL mode’s automatic reclamation is the simplest option, since it requires no ongoing maintenance logic on your part. If writes are frequent and performance-sensitive, that per-commit overhead adds up, making INCREMENTAL mode’s controlled, scheduled approach a better fit.

Do you have a reliable way to schedule maintenance tasks? INCREMENTAL mode only pays off if something in your application actually calls PRAGMA incremental_vacuum periodically. If you don’t have (or don’t want to build) a scheduling mechanism for maintenance tasks, FULL mode’s fully automatic behavior might be the more practical choice, despite its overhead.

Running through these questions honestly, rather than defaulting to whichever mode a blog post happened to recommend, will get you to the right choice for your specific application faster than guessing.

Wrapping Up

The auto_vacuum PRAGMA addresses a genuinely surprising aspect of how SQLite manages disk space: deleting data doesn’t automatically shrink your database file. Depending on your application’s needs, you can choose to leave this alone (NONE), have SQLite handle it automatically and immediately (FULL), or take a middle path where you control when space gets reclaimed (INCREMENTAL).

There’s no universally “correct” choice here — it depends on how write-heavy your application is, how much you care about file size staying lean, and whether you can tolerate the overhead of automatic reorganization on every commit. Understanding these trade-offs means you can make a deliberate decision instead of being surprised months later by a database file that’s mysteriously much larger than the data it holds.