SQLite in production: why your .NET app might not need Postgres

A post hit the Hacker News front page this week that I kept thinking about: "SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers for Low-Latency App Servers" (HN discussion, original article). 239 points, 74 comments. And the thread went exactly where you'd expect. Half the room says SQLite scales far further than people give it credit for. The other half says you're reckless not to run Postgres.

I've been in the .NET camp for years, and I've lost count of the projects where a full database server got provisioned for an app with four hundred users. So I know this argument well. Let me pick a side and back it up, with code you can run tonight.

Where the defaults hurt you

First thing to understand: SQLite out of the box is not tuned for a multi-threaded server process. The defaults come from an era where SQLite was mostly a local file format for phones and desktop apps. Drop that unchanged under an ASP.NET Core backend and sooner or later you'll meet the infamous SQLite Error 5: 'database is locked'.

That's not a bug. That's SQLite telling you it's misconfigured.

The default journal mode is DELETE. A writer grabs an exclusive lock on the whole file, and while that lock is held nobody can read. One slow write and your entire API stalls. For anything with real traffic that's a non-starter.

WAL mode is the first knob you turn

Write-Ahead Logging flips the order around. Instead of a writer mutating the main database file, it appends new pages to a separate .sqlite-wal file. Readers keep reading the main file in the meantime. The result: readers and writers stop blocking each other.

An important detail that people get wrong in .NET projects: WAL is a property of the database file itself, not of your connection. You set it once and it sticks. Other pragmas like busy_timeout and synchronous are per-connection instead, so they don't travel with the file.

Here's what a healthy open looks like with 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 is safe in WAL mode. In that combination you never lose a committed transaction on a power cut, at worst the last checkpoint. That's a good trade for a lot fewer fsync calls. cache_size = -64000 means a 64 MB page cache. mmap_size enables memory-mapped I/O, here at 256 MB.

The trap: busy_timeout is not optional

This is the single most important line in the whole piece, and the author says it flat out: never run SQLite in production without a busy timeout. I've watched this go wrong too many times to gloss over it.

Without a timeout, SQLite returns database is locked the instant two writers collide. With busy_timeout = 5000 the second writer politely waits up to five seconds for the first one to finish. In practice writes complete in milliseconds, so you almost never reach those five seconds. But that headroom is exactly what absorbs a traffic spike.

In a .NET app with a connection pool, the cleanest place for this is an interceptor, so every connection EF Core opens is configured automatically:

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();
    }
}

Register it where you set up your context:

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

Notice journal_mode = WAL isn't in the interceptor. Set that once at app startup or in a migration step, since it persists on the file anyway.

Single-writer is still the hard limit

WAL fixes the read-versus-write problem. What it does not fix: SQLite allows exactly one writer at a time. Full stop. However many threads you throw at your API, writes go through one at a time.

That sounds scary, so do the math. A simple insert or update in WAL mode easily costs under a millisecond. Even if you're conservative and assume a few hundred writes per second, you're still well under what the file can take. For the vast majority of business apps I build, that's plenty. Most apps read a hundred times more often than they write.

Where it goes wrong is the default transaction mode. EF Core and most code open a transaction as DEFERRED. That only acquires a write lock when the first write query arrives. Run a read-modify-write, and you can end up with two transactions both starting to read, both wanting to write, and one of them getting an SQLITE_BUSY in the face that busy_timeout can no longer resolve. A tiny deadlock.

The fix is BEGIN IMMEDIATE. It grabs the write lock right at the start of the transaction. The second writer then simply waits through busy_timeout instead of blowing up halfway. Microsoft.Data.Sqlite supports this directly:

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

// deferred: false issues a 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();

The rule I keep in every SQLite project: any transaction that contains even one write starts with deferred: false. Read-only transactions stay deferred. Want to go one step further? Push all writes onto a dedicated Channel<T> and let a single background task process them serially. Then a lock conflict can't exist by definition, and you've got a clean place to add retries and batching too.

Letting the WAL grow is on you

Another surprise people meet in production: the .sqlite-wal file grows and grows. That happens because a checkpoint, the act of writing the WAL back into the main file, doesn't always get its turn under constant load. With lots of readers around, a PASSIVE checkpoint never gets the chance.

The clean approach is a background task that checkpoints itself. In .NET you park that in a 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 writes the WAL back and then cuts the file to zero. Add PRAGMA journal_size_limit = 67108864 so the WAL never balloons past 64 MB between two checkpoints.

VFS layers: where it gets genuinely interesting

The part of the article that drew the most comments on HN was the VFS layer. SQLite lets you swap out the underlying file layer, and people have done clever things with that. Litestream continuously streams your WAL to object storage like S3. LiteFS turns your SQLite file into a replicated thing across multiple machines.

For me that's the hinge of the whole decision. The classic objection to SQLite is: one file on one disk, and if that machine dies you lose everything. Litestream takes that worry away. You run it as a separate process next to your .NET app and it pushes your database near-realtime to a bucket. Machine falls over, you restore from the latest state.

One honest caveat: this shines when your app runs on a single machine. Scale horizontally across multiple nodes that all want to write, and you're leaving the ground SQLite is strong on. At that point Postgres is simply the right answer, and you shouldn't be building awkward workarounds to avoid it.

When I reach for it and when I don't

My own line, after a handful of these projects, is fairly simple. Does the app run on a single server or container, is the read-to-write ratio lopsided, and does the client not want to manage and pay for a separate database server? Then SQLite with WAL, a proper busy_timeout, BEGIN IMMEDIATE on writes and Litestream alongside is a surprisingly solid choice. No network latency to the database, because it lives inside your process. Backups you can just copy. A local test environment identical to production.

The moment you need multiple writing nodes, or teams operating independently on the same data, the picture changes. Then I reach for Postgres without hesitation.

Mostly the HN thread showed how many people still write SQLite off as a toy. It hasn't been one for years. The defaults are just misleading, and that costs you an afternoon of digging. Set the right pragmas, respect the single writer, and you get a database that's exactly enough for a huge category of .NET apps. Sometimes the boring choice is the best one you can make.