Handling Database Bloat

Explain why large deletes leave empty space and manage database compaction strategy.

In this lesson, you’ll learn why deleting rows doesn't automatically shrink your database file and how to reclaim that wasted space.

The Problem: Why Deletes Leave Gaps

When you use a lightweight database like SQLite, data is stored in fixed-size blocks called "pages." When you DELETE a row, the database doesn't actually shrink the file size; instead, it marks the space previously occupied by that data as "free."

Think of it like a bookshelf: if you remove a book, the shelf doesn't automatically shrink in length. You are left with a gap. The database will happily reuse that gap for future INSERT operations, but if you have a high churn of data—frequent inserts followed by deletes—your database file will eventually become bloated with these empty, fragmented "holes."

Managing Compaction

To reclaim this space, you must perform a compaction or "vacuum" operation. This process physically moves the remaining data to fill the gaps, effectively shrinking the file size back down to its true, necessary capacity.

In many embedded systems, you trigger this manually. Using the standard SQL command:

VACUUM;

When you execute VACUUM, the database creates a brand-new copy of the database file, copying only the live data and ignoring the empty gaps. Once complete, it replaces the original file with the new, smaller version.

Choosing Your Strategy

Because compaction is a heavy I/O operation—it requires reading the entire database and writing it back out—you shouldn't run it constantly. Instead, consider these management strategies:

Scheduled Maintenance: Run VACUUM only when the application is idle or during device charging (referencing our previous lessons on Battery Impact of Database Operations).
Auto-Vacuum: Some databases support an "auto-vacuum" mode, which moves deleted pages to a "freelist" and moves trailing pages to fill the gaps. While convenient, this incurs a slight performance penalty on every DELETE.
Threshold-based: Check the database file size or the ratio of free pages before triggering a cleanup to ensure the operation is worth the battery and CPU cost.

Key Takeaway: Deleting data creates gaps, not shrinkage; use VACUUM periodically to defragment your database and reclaim storage, balancing file size against the performance cost of the operation.