VACUUM, Fragmentation, and Database File Maintenance

Understanding Free Pages, Fragmentation, and Database Compaction in SQLite

In our previous guide on SQLite Memory Management and Page Cache Internals, we explored how SQLite uses memory and caching to improve performance.

Now we’ll turn our attention to something happening on disk:

What happens to the database file as records are inserted, updated, and deleted over time?

Many developers assume that deleting rows automatically shrinks a database file.

In SQLite, that isn’t usually the case.

As databases evolve:

• Records are added

• Records are updated

• Records are deleted

• Indexes change

Over time, this activity can create:

• Unused pages

• Internal fragmentation

• Larger-than-necessary database files

SQLite provides tools to manage this situation, most notably the VACUUM command.

In this guide, we’ll explore:

• How fragmentation occurs

• What free pages are

• How SQLite reuses space

• How VACUUM works internally

• When database compaction is beneficial

Understanding SQLite Pages

Before discussing fragmentation, let’s revisit how SQLite stores data.

SQLite organizes database files into fixed-size pages.

Common page sizes include:

• 4096 bytes

• 8192 bytes

• 16384 bytes

Pages store:

• Table data

• Index entries

• Internal B-tree structures

• Database metadata

As records are inserted and removed, page utilization changes.

What Happens When Rows Are Deleted?

Consider this table:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

Suppose the table contains 100,000 rows.

Later, 50,000 rows are deleted:

DELETE FROM customers
WHERE id <= 50000;

Many developers expect the database file to immediately shrink.

Instead:

• The rows are removed

• Their pages become available for reuse

• The database file size often remains unchanged

Why?

Because SQLite keeps those pages available for future growth.

What Are Free Pages?

A free page is a page that:

• Exists inside the database file

• No longer contains active data

• Can be reused later

Think of it like an empty apartment in a building.

The apartment still exists.

It’s simply available for a future tenant.

The Free List

SQLite tracks unused pages using a structure called the free list.

What the Free List Does

The free list maintains a record of:

• Available pages

• Reusable storage locations

When new data is inserted:

SQLite first checks:

Can an existing free page be reused?

If yes:

• SQLite uses the free page

• No file growth occurs

This helps reduce unnecessary expansion.

Why Database Files Continue Growing

Imagine this pattern:

1. Insert 1 million rows

2. Delete 700,000 rows

3. Insert 100,000 rows

The database may still occupy space originally allocated for 1 million rows.

This is normal behavior.

SQLite prioritizes:

• Space reuse

• Reduced file resizing

• Efficient future growth

Over time, however, unused space can accumulate.

Understanding Fragmentation

Fragmentation occurs when data becomes scattered throughout the database file.

Instead of being stored in a tightly organized manner:

• Active pages become separated

• Free pages appear between used pages

• Storage becomes less compact
  

Types of Fragmentation

Internal Fragmentation

Occurs when:

• Pages contain partially used space

• Data no longer fully occupies the page
  

Example:

A page originally stores:

100 Records

After updates and deletions:

55 Records

The remaining space cannot always be utilized efficiently.

External Fragmentation

Occurs when:

• Free pages become distributed throughout the database
  

Example:

Used Page
Free Page
Used Page
Free Page
Used Page

The database remains functional but less compact.

How Fragmentation Affects Performance

Fragmentation usually impacts:

Storage Efficiency

More disk space is consumed than necessary.

Backup Size

Backups include:

• Active pages

• Free pages
  

Larger database files mean:

• Larger backups

• Longer backup times
  

Cache Efficiency

A compact database often:

• Requires fewer pages

• Improves cache utilization

How SQLite Reuses Free Pages

SQLite does not immediately waste free space.

When new rows are inserted:

SQLite often reuses:

• Free pages

• Free blocks within pages
  

This helps control database growth.

In many applications:

• Free page reuse alone is sufficient

• VACUUM is rarely required
  

What is VACUUM?

The VACUUM command rebuilds the entire database.

VACUUM;

Unlike normal maintenance operations:

VACUUM creates:

• A new compact database structure

• A reorganized file layout

• Removal of unused pages

How VACUUM Works Internally

When VACUUM executes:

SQLite:

1. Creates a temporary database

2. Copies all active data

3. Rebuilds tables

4. Rebuilds indexes

5. Removes free pages

6. Replaces the original database
   

The result:

• Smaller database file

• Reduced fragmentation

• Improved organization

Why VACUUM Can Take Time

VACUUM essentially rewrites the database.

For large databases:

• Every table is copied

• Every index is rebuilt

• Significant disk I/O occurs
  

The larger the database:

• The longer VACUUM requires
  

Disk Space Requirements

One important consideration:

VACUUM needs temporary working space.

A simplified example:

Database Size: 2 GB

SQLite may temporarily require:

2 GB + additional working space

Developers should ensure adequate free storage before running VACUUM.

VACUUM and WAL Mode

If your database uses WAL mode:

SQLite handles VACUUM slightly differently.

Before completion:

• WAL information must be incorporated

• Database consistency must be maintained
  

The process remains safe but can require additional work internally.

What is AUTO_VACUUM?

SQLite also supports automatic space reclamation.

Options include:

PRAGMA auto_vacuum;

Modes:

• NONE

• FULL

• INCREMENTAL
  

AUTO_VACUUM = FULL

PRAGMA auto_vacuum = FULL;

SQLite attempts to reclaim free pages automatically.

Advantages:

• Database growth remains controlled
  

Disadvantages:

• Additional overhead during operations
  

AUTO_VACUUM = INCREMENTAL

PRAGMA auto_vacuum = INCREMENTAL;

Free pages accumulate normally.

Developers choose when to reclaim them:

PRAGMA incremental_vacuum;

This provides more control.

When Should You Run VACUUM?

VACUUM is useful when:

• Large amounts of data were deleted

• File size is significantly larger than active data

• Database migration is occurring

• Storage optimization is important
  

When VACUUM May Not Help Much

VACUUM may provide little benefit when:

• Most pages are actively used

• The database continues growing rapidly

• Free space is already being reused efficiently
  

In these cases:

The performance gain may be negligible.

Checking Free Page Information

SQLite exposes useful statistics.

Example:

PRAGMA freelist_count;

This returns:

• Number of pages currently available for reuse
  

A high value may indicate:

• Significant free space

• Potential compaction opportunities
  

Practical Example

Imagine an audit table:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    event TEXT,
    created_at DATETIME
);

Over several years:

• Millions of records accumulate

• Older records are deleted
  

Eventually:

PRAGMA freelist_count;

returns a large number.

Running:

VACUUM;

may significantly reduce:

• Database size

• Backup size

• Storage consumption
  

Best Practices for Database Maintenance

Monitor Free Pages

Periodically review:

PRAGMA freelist_count;

Avoid Unnecessary VACUUM Operations

VACUUM is expensive.

Run it when there is a clear benefit.

Schedule During Low Activity

VACUUM can consume:

• CPU

• Disk I/O

• Storage bandwidth

Maintenance windows are often ideal.

Evaluate AUTO_VACUUM Carefully

Automatic reclamation can be helpful but may introduce overhead.

Test with your workload before enabling it.

Closing Thoughts

SQLite databases naturally accumulate unused space as data changes over time.

Key takeaways:

• Deleted rows do not automatically shrink database files

• SQLite tracks reusable space through the free list

• Fragmentation develops as pages are reused and redistributed

• VACUUM rebuilds the database and removes unused pages

• AUTO_VACUUM provides automatic space reclamation options

• Database maintenance should balance performance, storage, and operational cost
  

Understanding free pages and fragmentation helps you make informed decisions about long-term database maintenance, especially as your SQLite databases grow and evolve.

In the next guide, we’ll explore SQLite backup strategies and how online backup operations work internally.

Subscribe Now

Stay ahead with practical SQLite tutorials, with real-world examples. Join the SQLite Forum and be part of a growing global community of developers building smarter, faster applications.