Shrink a SQLite file in C# with incremental_vacuum · onelite cookbook

Deleting rows doesn’t make a SQLite file smaller: the pages go on a freelist. In a database created with auto_vacuum=INCREMENTAL, PRAGMA incremental_vacuum(N) gives up to N free pages back to the file system, a few at a time, without rewriting the whole file the way VACUUM does. OneLite does it from 0.3.0-preview.1.

Terminal

dotnet new console -o ShrinkDb && cd ShrinkDb
dotnet add package RecursiveAI.OneLite --version 0.3.0-preview.1

Put this in Program.cs and run dotnet run:

using RecursiveAI.OneLite;
 
File.Delete("notes.db");   // start fresh on every run
// auto_vacuum=INCREMENTAL is a property of the file, chosen when it is created.
await using var db = await Database.OpenAsync("notes.db",
    new DatabaseOptions { AutoVacuum = true, IncrementalVacuum = true });
Console.WriteLine($"auto_vacuum {await db.ScalarAsync<long>($"pragma auto_vacuum")}");
 
await db.ExecuteAsync($"create table notes (id integer primary key, body text)");
await db.ExecuteAsync($"""
    insert into notes (body)
    select replace(hex(zeroblob(1000)), '0', 'x') from generate_series(1, 2000)
    """);
await Report("after insert");
 
await db.ExecuteAsync($"delete from notes where id % 4 <> 0");
await Report("after delete");
 
await db.ExecuteAsync($"pragma incremental_vacuum(100)");   // free up to 100 pages
await Report("vacuum(100)");
await db.ExecuteAsync($"pragma incremental_vacuum");        // free the rest
await Report("vacuum");
Console.WriteLine(await db.ScalarAsync<string>($"pragma integrity_check"));
 
async Task Report(string step) => Console.WriteLine(
    $"{step,-13} page_count {await db.ScalarAsync<long>($"pragma page_count"),5}" +
    $"  freelist_count {await db.ScalarAsync<long>($"pragma freelist_count"),4}" +
    $"  file {new FileInfo("notes.db").Length,9:N0} bytes");

Text

auto_vacuum 2
after insert  page_count  1006  freelist_count    0  file 4,120,576 bytes
after delete  page_count  1006  freelist_count  500  file 4,120,576 bytes
vacuum(100)   page_count   906  freelist_count  400  file 3,710,976 bytes
vacuum        page_count   505  freelist_count    0  file 2,068,480 bytes
ok

The delete leaves 500 free pages and the file the same size. incremental_vacuum(100) frees 100 of them and the file loses 100 × 4,096 bytes; incremental_vacuum with no number frees the rest. auto_vacuum 2 is INCREMENTAL (0 is NONE, 1 is FULL).

The options are what turn it on. Database.OpenAsync without options opens in WAL mode, and once a file is in WAL mode pragma auto_vacuum = incremental no longer takes effect; the sqlite3 shell 3.53.4 behaves the same way.

The same steps in sqlite3

The sqlite3 shell (3.53.4, from sqlite.org) gives the same counts at every step. It prints one empty row per page incremental_vacuum frees; grep . drops those lines.

Terminal

sqlite3 shell.db "pragma auto_vacuum = incremental" \
  "create table notes (id integer primary key, body text)" \
  "insert into notes (body) select replace(hex(zeroblob(1000)), '0', 'x') from generate_series(1, 2000)" \
  "select 'after insert', page_count, freelist_count from pragma_page_count, pragma_freelist_count" \
  "delete from notes where id % 4 <> 0" \
  "select 'after delete', page_count, freelist_count from pragma_page_count, pragma_freelist_count" \
  "pragma incremental_vacuum(100)" \
  "select 'vacuum(100)', page_count, freelist_count from pragma_page_count, pragma_freelist_count" \
  "pragma incremental_vacuum" \
  "select 'vacuum', page_count, freelist_count from pragma_page_count, pragma_freelist_count" | grep .

Text

after insert|1006|0
after delete|1006|500
vacuum(100)|906|400
vacuum|505|0

Check each file with the other

sqlite3 checks the file OneLite shrank:

Terminal

sqlite3 notes.db "pragma integrity_check" "select count(*) from notes"

Text

ok
500

And OneLite checks the file sqlite3 shrank. Replace Program.cs with this and run dotnet run again:

using RecursiveAI.OneLite;
 
// Options as given keep the file's rollback journal (no switch to WAL).
await using var db = await Database.OpenAsync("shell.db", new DatabaseOptions());
Console.WriteLine(await db.ScalarAsync<string>($"pragma integrity_check"));
Console.WriteLine(await db.ScalarAsync<long>($"select count(*) from notes"));

Text

ok
500

Limits

Set it when the file is created. For an existing file with auto_vacuum NONE, run pragma auto_vacuum = incremental and then one full VACUUM; on OneLite the file then reported auto_vacuum 2 and sqlite3 checked it ok.
Same counts, not the same bytes. From the engine notes: “Which pages end up free is OneLite’s own choice (the lowest free pages are filled first), so the bytes of the file can differ from the reference’s while the counts and content match.”
All at once. It “does all the work on the first step (the reference frees one page per step, so a statement stepped once and then reset frees one page there and all of them here).”
It is a write. “It always begins a write transaction first, so a read-only connection or query_only gets attempt to write a readonly database.” In other auto-vacuum modes it does nothing.
It only gives back free pages: half-empty pages that still hold rows stay as they are. VACUUM rebuilds the whole file.
A preview: APIs may change between previews.

Engine: statistics and incremental vacuum · The happy path · All recipes