Analyzing Database Fragmentation

Identify when vacuuming or reindexing is necessary to reclaim space and improve scan speeds.

In this lesson, you will learn how to identify when internal file fragmentation in embedded databases like SQLite necessitates maintenance to reclaim space and restore optimal query performance.

The Mechanics of Fragmentation

In embedded databases that utilize single-file storage, every DELETE or UPDATE operation leaves behind "holes" or free blocks within the database file. While the database engine maintains a "freelist" to track these empty pages for future use, it does not always shrink the file size automatically. Over time, as data is inserted and removed, your database file can become bloated with fragmented, non-contiguous space. This causes two primary performance issues: increased storage overhead and slower scan speeds, as the disk I/O head or OS controller must navigate fragmented pages to read consecutive data.

Identifying the Need for Maintenance

To decide if you need to run a VACUUM or a reindex operation, you should compare the total number of free pages against the total page count. In SQLite, you can query the internal page status using:

PRAGMA freelist_count;
PRAGMA page_count;

If the freelist_count represents a large percentage of the page_count, your database has significant internal fragmentation. Additionally, if you notice that sequential SELECT queries on large tables are taking progressively longer despite having indexes, it is a sign that the data has become scattered across non-contiguous file segments.

Reclaiming Space and Speed

When fragmentation levels reach a threshold that hinders performance, you have two primary options:

VACUUM: This command rebuilds the database file from scratch. It copies the entire database into a temporary file, effectively compacting the data and eliminating the "holes." This process also defragments the file, ensuring that pages are physically contiguous on the disk.
REINDEX: If your performance issues are specific to index lookups, REINDEX updates the B-tree structures. While it doesn't shrink the file size like VACUUM, it rebuilds the index nodes to ensure they are balanced and tightly packed, which significantly improves tree traversal speeds.

Key Takeaway: Monitor the ratio of free pages to total pages; if the freelist is bloated, a VACUUM is required to physically reclaim disk space and improve sequential scan performance.