SQLite in productie: waarom je .NET-app misschien geen Postgres nodig heeft

Er stond deze week een post op de Hacker News-voorpagina die me raakte: "SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers" (discussie op HN, origineel artikel). 239 punten, 74 reacties. En de reacties waren precies wat je verwacht: de helft roept dat SQLite prima schaalt tot verrassend ver, de andere helft roept dat je gek bent om geen Postgres te draaien.

Ik zit al jaren in het .NET-kamp en heb genoeg projecten gezien waar een volwaardige databaseserver werd opgetuigd voor een app met vierhonderd gebruikers. Dus die discussie ken ik. Laat me een kant kiezen en het onderbouwen, met code die je vanavond nog kunt draaien.

Waar de standaardinstellingen je pijn doen

Het eerste wat je moet weten: SQLite uit de doos is niet ingesteld voor een serverproces met meerdere threads. De defaults komen uit een tijd dat SQLite vooral een lokaal bestandsformaat was voor telefoons en desktopapps. Zet je dat onaangepast onder een ASP.NET Core-backend, dan krijg je vroeg of laat de beruchte melding SQLite Error 5: 'database is locked'.

Dat is geen bug. Dat is SQLite die zegt: je hebt me verkeerd geconfigureerd.

De standaard journal mode is DELETE. Daarbij pakt een schrijver een exclusieve lock op het hele bestand, en zolang die lock er ligt kan niemand lezen. Eén trage schrijfquery en je hele API staat stil. Voor een app met echt verkeer is dat onhoudbaar.

WAL-mode is de eerste knop die je omzet

Write-Ahead Logging draait de volgorde om. In plaats van dat een schrijver het hoofdbestand aanpast, schrijft hij nieuwe pagina's naar een apart .sqlite-wal-bestand. Lezers blijven ondertussen het hoofdbestand lezen. Het resultaat: lezers en schrijvers zitten elkaar niet meer in de weg.

Belangrijk detail dat vaak fout gaat in .NET-projecten: WAL is een eigenschap van het databasebestand zelf, niet van je connectie. Je zet het één keer en het blijft staan. Andere pragma's zoals busy_timeout en synchronous moet je juist per connectie zetten, want die reizen niet mee.

Zo ziet een gezonde openings-setup eruit met Microsoft.Data.Sqlite:

using Microsoft.Data.Sqlite;

var connection = new SqliteConnection("Data Source=app.db;Pooling=true");
connection.Open();

using (var pragma = connection.CreateCommand())
{
    pragma.CommandText = """
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA busy_timeout = 5000;
        PRAGMA cache_size = -64000;
        PRAGMA mmap_size = 268435456;
        PRAGMA foreign_keys = ON;
        """;
    pragma.ExecuteNonQuery();
}

synchronous = NORMAL mag in WAL-mode. In die combinatie verlies je bij een stroomstoring nooit een gecommitte transactie, hooguit de allerlaatste checkpoint. Dat is een veilige ruil voor flink minder fsync-calls. cache_size = -64000 betekent 64 MB pagecache. mmap_size zet memory-mapped I/O aan, hier op 256 MB.

De valkuil: busy_timeout is niet optioneel

Dit is de belangrijkste regel uit het hele stuk, en de auteur zegt het letterlijk: draai SQLite nooit in productie zonder busy_timeout. Ik heb dit te vaak fout zien gaan om er soepel overheen te stappen.

Zonder timeout geeft SQLite meteen database is locked terug zodra twee schrijvers elkaar tegenkomen. Met busy_timeout = 5000 wacht de tweede schrijver netjes tot vijf seconden lang tot de eerste klaar is. In de praktijk zijn schrijfacties in milliseconden gepiept, dus die vijf seconden bereik je bijna nooit. Maar die marge is precies wat een piek in verkeer opvangt.

In een .NET-app met een connectiepool zet je dit het schoonst met een interceptor, zodat elke connectie die EF Core opent automatisch goed staat:

using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;

public sealed class SqlitePragmaInterceptor : DbConnectionInterceptor
{
    public override void ConnectionOpened(
        DbConnection connection, ConnectionEndEventData eventData)
    {
        using var cmd = connection.CreateCommand();
        cmd.CommandText = """
            PRAGMA busy_timeout = 5000;
            PRAGMA synchronous = NORMAL;
            PRAGMA foreign_keys = ON;
            """;
        cmd.ExecuteNonQuery();
    }
}

Registreren doe je bij het opzetten van je context:

builder.Services.AddDbContext<AppDbContext>(options =>
    options
        .UseSqlite("Data Source=app.db;Pooling=true")
        .AddInterceptors(new SqlitePragmaInterceptor()));

De journal_mode = WAL hoef je hier niet in de interceptor te zetten. Doe dat één keer bij het opstarten van de app of in je migratiestap, want hij blijft toch staan op het bestand.

Single-writer blijft de harde grens

WAL lost het lees-schrijfprobleem op. Wat het niet oplost: SQLite laat maar één schrijver tegelijk toe. Punt. Hoeveel threads je ook op je API loslaat, schrijfacties gaan er één voor één doorheen.

Dat klinkt eng, maar reken even mee. Een simpele insert of update in WAL-mode kost al snel minder dan een milliseconde. Zelfs als je conservatief rekent met een paar honderd schrijfacties per seconde, zit je nog ruim onder de limiet van het bestand. Voor het overgrote deel van de business-apps die ik bouw is dat meer dan genoeg. De meeste apps lezen honderd keer zo vaak als ze schrijven.

Waar het misgaat is de default transactiemodus. EF Core en de meeste code openen een transactie als DEFERRED. Die pakt pas een schrijflock op het moment dat de eerste schrijfquery komt. Draai je een read-modify-write, dan kan het gebeuren dat twee transacties allebei beginnen te lezen, allebei willen schrijven, en één van de twee een SQLITE_BUSY in je gezicht krijgt die geen busy_timeout meer oplost. Een deadlock in het klein.

De oplossing is BEGIN IMMEDIATE. Die pakt de schrijflock meteen bij de start van de transactie. De tweede schrijver wacht dan gewoon netjes via busy_timeout in plaats van halverwege te klappen. Met Microsoft.Data.Sqlite kan dat direct:

using var connection = new SqliteConnection("Data Source=app.db;Pooling=true");
connection.Open();

// deferred: false geeft een BEGIN IMMEDIATE
using var transaction = connection.BeginTransaction(deferred: false);

using var update = connection.CreateCommand();
update.Transaction = transaction;
update.CommandText = "UPDATE accounts SET balance = balance - @amount WHERE id = @id";
update.Parameters.AddWithValue("@amount", 25);
update.Parameters.AddWithValue("@id", 42);
update.ExecuteNonQuery();

transaction.Commit();

Vuistregel die ik in elk SQLite-project aanhoud: elke transactie die ook maar één schrijfquery bevat, start je met deferred: false. Read-only transacties laat je deferred. Wil je nog een stap verder, dan zet je alle schrijfacties op een eigen Channel<T> en laat je één achtergrondtaak ze serieel verwerken. Dan bestaat een lock-conflict per definitie niet meer, en heb je meteen een nette plek om retries en batching in te bouwen.

De WAL laten groeien is je eigen schuld

Nog zo'n verrassing die mensen op productie tegenkomen: het .sqlite-wal-bestand groeit en groeit. Dat komt doordat een checkpoint, het terugschrijven van de WAL naar het hoofdbestand, niet altijd vanzelf op tijd gebeurt onder constante belasting. Bij veel lezers krijgt een PASSIVE-checkpoint de kans niet.

De nette aanpak is een achtergrondtaak die zelf checkpoint. In .NET zet je dat in een BackgroundService:

public sealed class WalCheckpointService : BackgroundService
{
    private readonly IServiceScopeFactory _scopes;

    public WalCheckpointService(IServiceScopeFactory scopes) => _scopes = scopes;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await Task.Delay(TimeSpan.FromMinutes(1), stoppingToken);

            using var scope = _scopes.CreateScope();
            var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
            await db.Database.ExecuteSqlRawAsync(
                "PRAGMA wal_checkpoint(TRUNCATE);", stoppingToken);
        }
    }
}

TRUNCATE schrijft de WAL terug en snijdt het bestand daarna weer op nul. Zet daarnaast PRAGMA journal_size_limit = 67108864 zodat de WAL nooit ongebreideld boven de 64 MB uitgroeit tussen twee checkpoints door.

VFS-lagen: waar het echt interessant wordt

Het deel van het artikel dat op HN de meeste reacties losmaakte ging over de VFS-laag. SQLite laat je de onderliggende bestandslaag vervangen, en daar hebben mensen slimme dingen mee gedaan. Litestream streamt je WAL continu door naar object storage zoals S3, en LiteFS maakt van je SQLite-bestand een gerepliceerd ding over meerdere machines.

Voor mij is dat het scharnierpunt in de hele afweging. De klassieke tegenwerping tegen SQLite is: één bestand op één schijf, en als die machine wegvalt ben je alles kwijt. Litestream haalt die zorg weg. Je draait het als los proces naast je .NET-app, en het pusht je database near-realtime naar een bucket. Valt de machine om, dan restore je vanaf de laatste stand.

Wel een eerlijke kanttekening: dit werkt het mooist als je app op één machine draait. Ga je horizontaal schalen over meerdere nodes die allemaal willen schrijven, dan verlaat je het terrein waar SQLite sterk in is. Op dat punt is Postgres gewoon het juiste antwoord, en daar moet je niet krampachtig omheen willen bouwen.

Wanneer ik het wel en niet doe

Mijn eigen lijn na een paar van deze projecten is redelijk simpel. Draait de app op één server of container, is de verhouding lezen tot schrijven scheef, en wil de klant geen aparte databaseserver beheren en betalen? Dan is SQLite met WAL, een nette busy_timeout, BEGIN IMMEDIATE op schrijfacties en Litestream ernaast een verrassend solide keuze. Geen netwerklatency naar de database, want die zit letterlijk in je proces. Backups die je gewoon kunt kopiëren. Een lokale testomgeving die identiek is aan productie.

Zodra je meerdere schrijvende nodes nodig hebt, of teams die los van elkaar op dezelfde data moeten, verandert het plaatje. Dan pak ik Postgres, zonder aarzeling.

De HN-thread liet vooral zien hoeveel mensen SQLite nog steeds wegzetten als speelgoed. Dat is het al jaren niet meer. De defaults zijn alleen misleidend, en dat kost je een middag uitzoeken. Zet de juiste pragma's, respecteer de single-writer, en je hebt een database die voor een enorme categorie .NET-apps precies genoeg is. Soms is de saaie keuze de beste die je kunt maken.