Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

AUTOVACUUM in SQLite: How Your Database Cleans Up After Itself

Up to this point, we’ve looked at how SQLite handles logic (triggers), structure (views), and identity (autoincrement).

Now we shift focus to something less visible but equally important i.e how SQLite manages space inside the database file.

Because unlike many systems, SQLite stores everything in a single file.

And over time, that file doesn’t always behave the way you expect.

The Default Mode: Nothing Gets Smaller Automatically

By default, SQLite runs in non-autovacuum mode.

This means that when you delete data or update rows, the database does not immediately shrink.

Instead, something quieter happens.

The space that was used by deleted data becomes free pages, and these pages are added to something called a freelist.

SQLite keeps track of these pages and reuses them later when new data is inserted.

So internally:

• The file size stays the same
• Free space exists inside the file
• Future inserts reuse that space

This is efficient, but it also means your database file can grow large and never shrink back down, even if you delete a lot of data.

Manual Cleanup: The VACUUM Command

If you want to actually shrink the database file, you need to run:

VACUUM;

This command:

• Rebuilds the database
• Removes unused pages
• Returns space to the file system

However, there are two important constraints:

• It cannot run inside a transaction (BEGIN ... END)
• It must be executed manually

This is why it’s called manual vacuuming.

Enter AUTOVACUUM: Automatic Space Reclaiming

SQLite provides a feature called autovacuum, which changes how this process works.

When autovacuum is enabled:

• Freed pages are still tracked during a transaction
• But at commit time, SQLite returns unused space back to the file system automatically

This means:

• The database file can shrink on its own
• You don’t need to run VACUUM manually

Once autovacuum is enabled, the VACUUM command becomes mostly unnecessary.

The Catch: Files Can Only Shrink From the End

Here’s where things get interesting.

Operating systems generally cannot remove space from the middle of a file. They can only shrink a file from the end.

So if SQLite frees pages in the middle of the file, it cannot just delete them directly.

Instead, it has to:

1. Move valid data from the end of the file into free space earlier in the file
2. Free up pages at the end
3. Then shrink the file

This process is called relocation, and it is essentially a form of internal compaction.

Pointer Map Pages: The Hidden Data Structure

To make relocation possible, SQLite needs to track how pages are connected. This is where pointer-map pages come in.

These are special pages inside the database that store:

• The type of each page
• The parent page that references it

Each entry is very compact:

• 1 byte → type
• 4 bytes → parent page number

This allows SQLite to quickly:

• Find relationships between pages
• Update references when pages are moved

Without this structure, relocating pages safely would be extremely difficult.

Why Pointer Maps Matter

In a normal database tree, parent pages point to child pages. But during relocation, SQLite needs the reverse—it must find the parent of a page quickly.

Pointer-map pages provide exactly that:
👉 a fast lookup from child → parent

This is critical when:

• Moving pages
• Updating references
• Maintaining database integrity

Types of Pointer Map Entries

Each page in the database is categorized using a type:

• ROOTPAGE → top-level page, no parent
• BTREE → regular internal page with a parent
• OVERFLOW (1st page) → linked from a data cell
• OVERFLOW (next pages) → linked from previous overflow page
• FREEPAGE → unused space

These types help SQLite understand how each page fits into the overall structure.

Tradeoffs of AUTOVACUUM

Autovacuum sounds like a clear win, but it comes with tradeoffs.

Advantages:

• No need for manual cleanup
• Database file stays compact
• Space is returned to the system automatically

Disadvantages:

• Slightly larger database size (due to pointer-map pages)
• Extra overhead during commits
• More internal complexity

When Should You Use AUTOVACUUM?

Autovacuum is useful when:

• Your database frequently deletes or updates data
• File size matters (e.g., mobile apps, embedded systems)
• You want automatic maintenance

You might avoid it when:

• You want maximum performance
• You prefer manual control using VACUUM
• Your data doesn’t change frequently

Final Thought

Autovacuum is one of those features you rarely think about—until your database file grows far larger than expected.

By default, SQLite optimizes for reuse, not shrinkage.

Autovacuum flips that behavior and keeps your file size in check, but it does so by adding internal complexity and overhead.

Understanding this tradeoff helps you decide whether you want a database that:

• Reuses space efficiently, or
• Actively keeps itself compact

Both approaches are valid.

The right choice depends on how your application actually uses data.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

| 🇩🇰 Dansk | 🇪🇸 Español | 🇮🇷 Farsi | 🇫🇮 Suomi | 🇯🇵 日本語 | 🇳🇴 Norsk | 🇵🇹 Português | 🇷🇺 Русский | 🇦🇱 Shqip | 🇨🇳 中文 |

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

 

     

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

• 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
• 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
• 🔁 Build a…

View on GitHub