阅读中文版本

Shrink SQLite with Page Reclaim

Lower the page size and auto_vacuum settings to reclaim free pages and keep a local SQLite file compact between maintenance runs.

sqlite3 app.db "PRAGMA page_count; PRAGMA freelist_count; PRAGMA auto_vacuum;"

sqlite3 app.db "PRAGMA auto_vacuum=incremental; VACUUM;"
sqlite3 app.db "PRAGMA incremental_vacuum(0);"

What this recipe does

Reclaim free pages in SQLite by enabling incremental auto_vacuum and tuning page size.

A local cache database holds many free pages after churn, so disk usage stays high even though the data shrank.

Prerequisites

• sqlite3 CLI
• Exclusive access for the conversion
• A backup of the database

Procedure

1. Check current page usage and free page count.

sqlite3 app.db "PRAGMA page_count; PRAGMA freelist_count; PRAGMA auto_vacuum;"

Expected result: freelist_count greater than zero means free pages can be reclaimed.

2. Enable incremental auto_vacuum and compact.

sqlite3 app.db "PRAGMA auto_vacuum=incremental; VACUUM;"
sqlite3 app.db "PRAGMA incremental_vacuum(0);"

Expected result: After VACUUM the freelist shrinks and incremental vacuum keeps it small over time.

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 freelist_count drops to near zero and the file size reflects the smaller logical data set.

Troubleshooting

auto_vacuum change needs a full VACUUM — Changing auto_vacuum only takes effect after a VACUUM on the same connection.

Incremental vacuum not shrinking — Pass a positive page count or zero to reclaim all available free pages.

Limitations

• Incremental vacuum can fragment if run too aggressively.
• Requires exclusive access at conversion time.
• Does not reclaim WAL file space by itself.