--- title: "Shrink a SQLite file in C# with incremental_vacuum" url: https://recursiveai.org/onelite/cookbook/shrink-sqlite-file-incremental-vacuum-csharp/ description: "Shrink a SQLite database file in C# with PRAGMA incremental_vacuum and auto_vacuum=INCREMENTAL, using OneLite: page_count, freelist_count and file size before and after, the same steps in the sqlite3 shell with the same counts, and integrity_check both ways." version: "0.3.0-preview.2" --- # Shrink a SQLite file in C# with incremental_vacuum 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. ```shell dotnet new console -o ShrinkDb && cd ShrinkDb dotnet add package RecursiveAI.OneLite --version 0.3.0-preview.2 ``` Put this in `Program.cs` and run `dotnet run`: ```csharp 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($"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($"pragma integrity_check")); async Task Report(string step) => Console.WriteLine( $"{step,-13} page_count {await db.ScalarAsync($"pragma page_count"),5}" + $" freelist_count {await db.ScalarAsync($"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). Through the ADO.NET provider, from 0.3.0-preview.2, a reader on `pragma incremental_vacuum(5)` returns five rows with no columns, as `sqlite3` does, and `ExecuteNonQuery` frees the pages too. The options are what turn it on. `Database.OpenAsync` without options opens in WAL mode, and from [the engine notes](https://recursiveai.org/onelite/docs/engine/#statistics-and-incremental-vacuum): “`PRAGMA auto_vacuum` is fixed once the database file is initialised, as in SQLite: switching a new file to WAL (which `Database.OpenAsync` does at open) initialises it, so a later `PRAGMA auto_vacuum = incremental` silently has no effect (it reads back 0). Run `VACUUM` after setting it to apply it (it works in WAL mode), or set it before the switch to WAL (observed with the pinned shell).” Passing `DatabaseOptions` sets it as the file is created. ## 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. ```shell 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: ```shell 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: ```csharp 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($"pragma integrity_check")); Console.WriteLine(await db.ScalarAsync($"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](https://recursiveai.org/onelite/docs/engine/#statistics-and-incremental-vacuum): “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](https://recursiveai.org/onelite/docs/engine/#statistics-and-incremental-vacuum) · [The happy path](https://recursiveai.org/onelite/docs/happy-path/) · [All recipes](https://recursiveai.org/onelite/cookbook/)