阅读中文版本

Vacuum SQLite Safely Offline

Reclaim SQLite space with VACUUM INTO and swap the compacted file atomically to avoid blocking a live application.

sqlite3 app.db "VACUUM INTO '/var/data/app.compact.db';"
ls -l app.db app.compact.db

sqlite3 app.compact.db "PRAGMA integrity_check;"
mv app.compact.db app.db

What this recipe does

Compact a bloated SQLite database offline using VACUUM INTO and an atomic file swap.

A local SQLite store has doubled in size after deletes, and running VACUUM in place would lock the application.

Prerequisites

• SQLite 3.27 or later for VACUUM INTO
• Write access to the data directory
• A maintenance window or read-only mode

Procedure

1. Create a compacted copy without locking the live file.

sqlite3 app.db "VACUUM INTO '/var/data/app.compact.db';"
ls -l app.db app.compact.db

Expected result: A smaller, defragmented database file is produced while the original stays readable.

2. Verify integrity then swap atomically.

sqlite3 app.compact.db "PRAGMA integrity_check;"
mv app.compact.db app.db

Expected result: integrity_check returns ok and the swap replaces the file with no partial state.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the new file passes integrity_check, is meaningfully smaller, and the application opens it without errors.

Troubleshooting

VACUUM INTO fails on older SQLite — Upgrade SQLite or copy the file and vacuum the copy under an exclusive lock.

Writers still hold the old file — Stop writers during the swap; file replacement is only atomic for new opens.

Limitations

• Needs free space for the compacted copy.
• WAL mode requires checkpointing before the swap.
• Not safe to run concurrently with writers.