SQLite VACUUM Not Shrinking database.sqlite? Real Fixes

If VACUUM does nothing for your database.sqlite file size, you’re not alone. The usual culprit isn’t SQLite “not working” - it’s how journaling, locks, freelists, WAL, and operational reality collide. Here’s the operator-grade breakdown and a production-safe playbook to actually reclaim disk space without bricking your app.

SQLite stores data in fixed-size pages. Over time, deletes and updates create internal free pages (the freelist). VACUUM rebuilds the database from scratch into a compact, contiguous file with zero freelist, then swaps it in atomically. If you truly ran VACUUM to completion, and nothing else wrote during the operation, the resulting file should be the smallest possible representation of your current data.

So why do people see “no change”? Common reasons include: the VACUUM never actually ran (locked database, swallowed error), you’re looking at the wrong file (WAL still big), competing connections refilled the file as the operation completed, or the database already was minimal given your current content and page size.

In practice, SQLite’s journaling mode and concurrent reads/writes are the usual suspects. Understanding those is step one.

SQLite can use different journal modes. The defaults vary by platform, but two common modes are DELETE (rollback journal) and WAL (Write-Ahead Logging). These modes affect what “file size” means at any moment.

In DELETE mode, you typically see just database.sqlite and a transient -journal file during writes. After a clean shutdown, the -journal file usually disappears. VACUUM in DELETE mode rebuilds the main database file and is fairly straightforward.

In WAL mode, writes go to database.sqlite-wal and coordination metadata lives in database.sqlite-shm. The main database.sqlite might not change much during heavy writes - the WAL file grows. You can VACUUM the main file, but if you never checkpoint the WAL back into the database, you’ll keep a large -wal lying around. That’s why people say “VACUUM did nothing” while the -wal is quietly eating disk.

Operators see patterns. These are the high-frequency causes behind a stubborn file size:

1) The database was locked and VACUUM never ran. Many tools catch “database is locked” and move on. There’s no shrink because nothing happened. Check your logs and ensure you have an exclusive lock window.

2) WAL file remains large. You ran VACUUM but did not checkpoint the WAL with TRUNCATE, or long-running reads prevented the checkpoint from truncating. Your main file might be compact, but the -wal still holds bulk.

3) No free pages to reclaim. If you haven’t actually deleted significant data (or you deleted and then refilled), the on-disk minimum may be near current size. VACUUM won’t conjure savings that don’t exist.

4) auto_vacuum not configured. auto_vacuum can automatically move freed pages toward the end of the file and truncate, but it only takes effect after enabling it and running one full VACUUM to rewrite the database with that setting. Without this, repeated deletes won’t lead to smaller files between manual maintenance windows.

5) Not enough disk space for VACUUM to complete. VACUUM temporarily needs space roughly equal to the database size. If your volume is too tight, VACUUM fails and you’re back where you started.

6) Other writers refilled freed space during/after VACUUM. If you run maintenance in a live write-heavy environment, your “savings” can evaporate immediately.

7) Container and filesystem semantics. On overlay filesystems (e.g., Docker’s overlay2) and copy-on-write volumes (e.g., APFS), perceived size, layer diffs, and snapshot policies can make the shrink less obvious. The file size may drop, but the image or snapshot consumption doesn’t budge until you commit/prune layers.

Start by asking the database a few direct questions. Use the sqlite3 CLI in a maintenance window (or your language binding, but CLI keeps things honest):

sqlite3 database.sqlite "PRAGMA page_count; PRAGMA page_size; PRAGMA freelist_count; PRAGMA journal_mode; PRAGMA auto_vacuum;"

Interpretation basics:

- page_count × page_size ≈ theoretical minimal size with zero freelist (plus minor header/rounding). If freelist_count is large, there’s space to reclaim.

- journal_mode tells you if you’re in WAL. If WAL, list file sizes on disk and specifically check database.sqlite-wal.

- auto_vacuum returns NONE, FULL, or INCREMENTAL. NONE means the file won’t auto-shrink on deletes; FULL or INCREMENTAL can help ongoing hygiene after a one-time VACUUM to seed the setting.

Now inspect your files at rest:

ls -lh database.sqlite*
# Expect possibly: database.sqlite, database.sqlite-wal, database.sqlite-shm

If -wal dwarfs the main file, your “no shrink” problem is really a checkpoint problem, not a VACUUM problem.

Here’s the operator playbook I’ve used across services and cron jobs that depend on SQLite. Tailor it to your traffic and SLAs, but don’t deviate on the sequence without strong reasons.

1) Quiesce writers. Stop jobs or temporarily gate traffic so you can get an exclusive lock. Long-running readers prevent checkpoint truncation; ensure they finish.

2) Checkpoint WAL with TRUNCATE. In WAL mode, do a full checkpoint and truncate the WAL file:

sqlite3 database.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

3) Run VACUUM. Now compact the database itself:

sqlite3 database.sqlite "VACUUM;"

4) Optionally use VACUUM INTO for extra safety. If you’re nervous about disk pressure or need a clean handoff, write to a new file and swap:

sqlite3 database.sqlite "VACUUM INTO 'database.compacted.sqlite';"
# Then atomically replace after verification

5) Verify. Recheck page_count, page_size, and freelist_count. freelist_count should be 0 post-VACUUM. Confirm on-disk size dropped and that -wal remains small after traffic resumes.

6) Decide on auto_vacuum policy. If you routinely delete large volumes, enable FULL or INCREMENTAL to reduce future manual intervention (details below). Remember: after changing auto_vacuum, you must VACUUM once to rewrite the database with the new mode.

SQLite supports three modes via PRAGMA auto_vacuum:

- NONE: Default in many builds. Deletes keep freed pages on the freelist until a manual VACUUM. Lowest write overhead, no automatic shrinking.

- FULL: Frees pages are immediately moved to the end of the file and truncate can happen as pages are freed. Good for workloads with bursts of deletes where you care about disk footprint staying lean. Slight overhead on updates/deletes.

- INCREMENTAL: Similar to FULL but requires running PRAGMA incremental_vacuum(N) periodically to actually release pages, giving you more control. Great if you want a scheduled task to control I/O.

Implementation tip: If your app is resource-sensitive or runs on constrained devices/containers, INCREMENTAL with a daily or weekly PRAGMA incremental_vacuum(0) (0 = as many pages as possible) is predictable and low drama.

In WAL mode, don’t let the -wal file grow unbounded. Set a policy:

- Trigger passive checkpoints on size thresholds (e.g., 64–128MB WAL). Many bindings expose options to auto-checkpoint every N pages.

- During maintenance, run PRAGMA wal_checkpoint(RESTART) or TRUNCATE. TRUNCATE reclaims disk immediately, RESTART resets for new writes. TRUNCATE is your friend when the goal is disk savings.

- Kill long-lived readers. A single connection that keeps a read transaction open prevents checkpoints from cleaning older frames. This is a silent footgun in background services and dashboards.

If VACUUM does nothing because it never started, chase these specifics:

- Locks: Ensure your process can take an exclusive lock. Configure a generous busy_timeout and backoff, or stop traffic. Don’t run VACUUM inside a transaction; SQLite forbids it.

- Disk headroom: You typically need free space approximately equal to the database size for the temporary copy. If you’re short, use VACUUM INTO on another volume or prune data first.

- Permissions and read-only mounts: Containers and certain cloud paths mount read-only by default. If your CI/ops pipeline made the volume read-only, VACUUM won’t be able to write the new file.

Not all megabytes are equal. Certain patterns bloat SQLite more than others and make it feel like VACUUM “doesn’t work” - because it’s compressing to the true minimum, and that minimum is still large.

- Massive indexes: Index pages add up. If you no longer need old indexes, drop them before VACUUM. If you do need them, note that they’re part of the minimal footprint - VACUUM won’t make them vanish.

- Large BLOBs: Images, PDFs, and binary payloads embedded in SQLite quickly dominate size. Offload them to object storage and keep references.

- FTS (full-text search) virtual tables: FTS4/5 create sizable auxiliary structures. Use the module’s optimize command where applicable. VACUUM will compact, but the FTS footprint can still be material by design.

There’s nothing glamorous here - just habits that keep your database small without heroics:

- Maintenance windows: Put a calendar block for checkpoint + VACUUM (or incremental_vacuum) in low-traffic hours. This is ten minutes that saves GBs and avoids fire drills.

- Reasonable page_size: 4096 or 8192 bytes are balanced defaults. Oversized pages can waste space for small-row workloads; tiny pages increase overhead. Pick, then stick.

- Avoid long-lived read transactions: Educate devs. Use connection pools that time out idle reads. Dashboards should refresh, not camp on old snapshots.

- Delete in batches: If you need to purge millions of rows, chunk deletes. Then checkpoint and run incremental_vacuum. This avoids huge WAL spikes and lock contention.

Even if you’re not a database engineer, this shows up in your world: container images that won’t deploy because volumes are full, backup costs creeping up, mobile apps shipping with bloated local stores, workflows failing mid-campaign. SQLite underpins desktop tools, scrapers, edge functions, event stores, and local caches in your marketing stack.

If you run outbound sequencing, web scraping for ICP research, or build automation on Make/n8n with local SQLite bridges, housekeeping matters. Small, compact files sync faster, back up faster, and fail less - directly improving campaign speed and uptime.

Run the playbook above once, bake a light cron into your ops, and you eliminate an entire class of “random” production issues that otherwise show up the night before launch.

Quick context for readers weighing “who should own this.” Anton Chisliuk · AI Marketing Ops runs on an operator model: one senior builder ships strategy and implementation across SEO, outbound, HubSpot/CRM, and automation. This is where small, disciplined data ops (like SQLite hygiene, n8n/Make workflows, and checkpoints) pair with growth work to prevent outages. Proof: we took Cleverence from 0 to ~80K organic visits/month and ~2K inbound leads/month while keeping systems sane. If you want an alternative to agency tax, skim the overview on our About page to see how the operator model slots into your stack.

If you need a copy-paste baseline, here are minimal snippets. Always test in staging.

Shell (cron-friendly):

# Stop writers, then:
sqlite3 database.sqlite "PRAGMA wal_checkpoint(TRUNCATE); VACUUM;"
# Or safer two-step with verify
sqlite3 database.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"
sqlite3 database.sqlite "VACUUM;"
sqlite3 database.sqlite "PRAGMA page_count; PRAGMA freelist_count;"

Node.js (better with a maintenance script):

const Database = require('better-sqlite3');
const db = new Database('database.sqlite');
db.pragma('journal_mode = WAL');
db.pragma('wal_checkpoint(TRUNCATE)');
db.exec('VACUUM;');
const pageCount = db.pragma('page_count', { simple: true });
const free = db.pragma('freelist_count', { simple: true });
console.log({ pageCount, free });
db.close();

Python (sqlite3 stdlib):

import sqlite3
con = sqlite3.connect('database.sqlite')
cur = con.cursor()
cur.execute('PRAGMA wal_checkpoint(TRUNCATE);')
cur.execute('VACUUM;')
cur.execute('PRAGMA page_count;'); print('pages:', cur.fetchone()[0])
cur.execute('PRAGMA freelist_count;'); print('free:', cur.fetchone()[0])
con.close()

Myth: “VACUUM is risky and corrupts databases.” Reality: VACUUM is a first-class SQLite operation designed to be safe. It needs headroom and exclusive access; respect that and it’s fine.

Myth: “I must run VACUUM weekly.” Reality: It depends on churn. If you’re not doing large deletes, you might not need it often. With auto_vacuum INCREMENTAL and consistent checkpoints, you can go months without a full VACUUM.

Myth: “VACUUM will shrink the WAL.” Reality: No - checkpointing shrinks WAL. Run a TRUNCATE checkpoint specifically for that.

Myth: “VACUUM didn’t work because the file size is the same.” Reality: It might be the minimal size for your data and page_size. Check freelist_count; if it’s zero and WAL is small, you’re done.

Codify this so it’s not tribal knowledge:

- On deploy or weekly low-traffic window: PRAGMA wal_checkpoint(TRUNCATE); If deletes in past week exceeded X% of rows, also run VACUUM (or incremental_vacuum).

- Alert when -wal exceeds N MB for more than M minutes (implies stuck reads or checkpoint issues). Page the on-call owning the service, not “the database team.”

- After schema changes or major purges: VACUUM INTO a new file, swap atomically, verify freelist_count == 0.

- Document who can stop writers and how to validate no long-lived readers remain.

If this kind of housekeeping is falling between dev and marketing, fold it into your automation layer. We build Make/n8n workflows that checkpoint and compact quietly during low-traffic windows, alongside campaign ops and data syncs. If you want that wired-in with alerts and owner routing, consider our marketing automation implementation as part of a wider ops foundation. As reference, our systems work helped support results like Cleverence moving from no organic footprint to consistent inbound at scale - see the public proof in our Cleverence case study.

If VACUUM seems to do nothing for database.sqlite, it’s rarely SQLite’s fault. Most often, you’re fighting WAL bloat, lock contention, or the reality that your schema and data genuinely require the current size. The fix is operational: checkpoint with TRUNCATE, schedule a real maintenance window, run VACUUM (or VACUUM INTO), and consider auto_vacuum + incremental_vacuum for steady-state hygiene.

If you’d like a second set of senior eyes to wire this into a reliable growth stack - from CRM data integrity to outbound and SEO content systems - start with a free Growth Audit. Month-to-month, flat-fee, outcomes over optics.

Article by Anton Chisliuk - leads, sales, content, and systems - one senior operator.

- Why didn’t VACUUM shrink my SQLite database at all?

Likely because it never ran (lock, lack of disk space), the WAL file stayed large (no TRUNCATE checkpoint), or the database was already at its minimal size for the current data and page_size. Check freelist_count, journal_mode, and -wal size.

- Do I need to close all connections to run VACUUM?

You need to prevent other connections from writing and ideally avoid long-lived reads. VACUUM requires an exclusive lock. In practice, quiesce writers and ensure readers finish before you start.

- How do I reduce a huge database.sqlite-wal file?

Run PRAGMA wal_checkpoint(TRUNCATE). If it won’t truncate, you probably have active readers. Stop traffic or restart the process cleanly so the checkpoint can complete, then verify the -wal shrinks.

- Should I use FULL or INCREMENTAL auto_vacuum?

FULL automatically moves free pages to the end so truncation can occur; INCREMENTAL does it when you call incremental_vacuum. If you want zero-ops shrinking, pick FULL. If you want control and predictable I/O, pick INCREMENTAL and schedule a cron.

- What’s safer: VACUUM or VACUUM INTO?

Both are safe when done correctly. VACUUM INTO lets you write a compacted database to a new file, verify it, and then swap atomically. It’s great when disk headroom is tight or you want an extra safety step.

- How often should I run VACUUM?

Run it after major purges, schema rewrites, or if freelist_count is high. With WAL checkpoints and incremental_vacuum in place, many apps can go weeks or months without a full VACUUM.

- Will changing auto_vacuum take effect immediately?

No. After setting PRAGMA auto_vacuum, you must run VACUUM once to rewrite the database with the new mode. Only then does the setting apply fully.

- Can large indexes or FTS tables stop shrinking?

They don’t stop shrinking; they set the lower bound of “minimal.” If you need the indexes/FTS data, that footprint is real. Review which indexes are necessary, and consider FTS optimize where available.

- Is it okay to delete -wal and -shm files manually?

Only when you are absolutely sure no process has the database open. The safer approach is to checkpoint with TRUNCATE or cleanly stop the process so SQLite removes or truncates them.