Postgres vs SQLite under load: where each one stops on one machine

For a web app with one instance, is a server database worth running at all? To get numbers, I put one Go shop service on PostgreSQL 18 and on SQLite 3.53, on identical machines, and raised the request rate until each side failed.

Scope first, because everything below depends on it: one app instance, with the database on the same machine. Code and scripts are in huseyinbabal/db-wars.

Results

The pass mark for a rate is p95 under 50 ms with under 1% failed requests. The table gives the highest rate held, in requests per second. Rounds with 3 runs show the median.

The spread matters when you read these. Individual runs of the single writer shop round were 2,991, 2,741 and 2,494 for Postgres against 2,496, 2,246 and 2,496 for SQLite, so one of those three ended level. Treat that gap as narrow. Postgres also moved between 700 and 1,000 on the same write round.

Each number is a step on a ladder, so a result of 11,934 means the 12,000 step held and the next one did not. Precision below the step size isn’t real.

The setup

All of it ran in DigitalOcean lon1. Both stacks ran at the same moment, each with its own load machine. A batch of rounds I had already finished turned out to have different CPU models on the two servers, so I threw those results away and ran everything again on a matching pair.

Each round starts from fresh data: one million orders and 100,000 products. The SQL is the same on both. Postgres is reached over its local Unix socket with pgx, and SQLite is compiled into the binary by go-sqlite3.

Settings that decide the outcome:

Both acknowledge a write only after it reaches the disk. The often recommended synchronous=NORMAL skips the sync on each WAL commit, so a power loss can drop recent commits. That is a weaker promise than Postgres gives here, so I didn’t use it and didn’t measure it.

The SQLite connection string:

file:/var/lib/shop/shop.db?_journal_mode=WAL&_synchronous=FULL&_busy_timeout=5000&_cache_size=-10240

k6 uses ramping-arrival-rate, so a slow database can’t slow the test down the way a closed loop of virtual users would:

./round.sh read-1 0 1000 1000 20 30s          # name, write share, start, step, steps, hold
./round.sh shop20-1 0.2 250 250 24 30s
LITE_WRITERS=1 ./round.sh shop20-1w-1 0.2 250 250 24 30s
SWAP=1 ./round.sh read-swap 0 1000 1000 20 30s

Why reads go to SQLite

At a fixed 4,000 reads per second:

The app shares the machine with Postgres, so the CPU it spends crossing a socket to another process comes out of the same two cores. System calls over five seconds of identical read traffic show the size of that:

bpftrace -e 'tracepoint:raw_syscalls:sys_enter /comm == "shop" || comm == "postgres"/ { @[comm] = count(); } interval:s:5 { exit(); }'

Postgres stack
@[postgres]: 62356
@[shop]: 246343

SQLite stack
@[shop]: 188168output

That’s about 309,000 against 188,000. A SQLite query is a function call, though it isn’t free of system calls, since it still reads through the page cache.

This gap depends on the app living next to the database. Move Postgres to its own machine and the app stops competing with it for CPU, which is a different test from this one.

Why writes go to Postgres

SQLite allows one writer at a time per database file, and WAL mode doesn’t change that. You can watch it with two sqlite3 shells:

-- first shell
BEGIN IMMEDIATE;
UPDATE products SET stock = stock - 1 WHERE id = 42;

-- second shell
.timeout 3000
UPDATE products SET stock = stock - 1 WHERE id = 7;

Runtime error: database is locked (5)output

After three seconds the second shell gives up. Running it again after the COMMIT in the first shell works.

Sleeping on the lock

A hundred pooled connections all want that lock. A connection that doesn’t get it sleeps and tries again, which you can count:

bpftrace -e 'tracepoint:syscalls:sys_enter_clock_nanosleep, tracepoint:syscalls:sys_enter_fdatasync, tracepoint:syscalls:sys_enter_fsync /comm == "shop"/ { @[probe] = count(); } interval:s:5 { exit(); }'

@[tracepoint:syscalls:sys_enter_clock_nanosleep]: 432
@[tracepoint:syscalls:sys_enter_fsync]: 2021output

One connection for writes

Give writes their own pool of exactly one connection and they queue inside the Go process:

wdb = db
if kind == "sqlite" && env("SQLITE_WRITERS", "0") == "1" {
	wdb = pool(driver, 1)
}

The same five-second measurement then shows zero sleeps:

@[tracepoint:syscalls:sys_enter_fsync]: 1791output

Write-only throughput went from 398 to 499 requests per second, and the shop with one write in five went from 1,748 to 2,496. For a Go service on SQLite this is the first change I’d make. It costs one extra sql.DB and a decision about which queries are writes.

Postgres still led in those rounds, between 700 and 1,000 for pure writes, because concurrent writers there don’t take turns on one file lock.

Where SQLite stops being an option

Throughput isn’t the hard limit here. Start two instances of the app, each with its own file, and:

curl -s -X POST localhost:9001/orders -d '{"customer_id":1,"items":[{"product_id":1,"qty":1}]}'
curl -s -o /dev/null -w '%{http_code}\n' localhost:9001/orders/101
curl -s -o /dev/null -w '%{http_code}\n' localhost:9002/orders/101

{"id":101,"total_cents":137}
200
404output

Several app instances on one shared database is where Postgres wins without a benchmark. Running instances on one host over one shared file works, but they still take turns on the same write lock, and SQLite’s own documentation warns against putting that file on a network filesystem.

What I’d pick

This test doesn’t cover replication, backups, a database on its own machine, or datasets far larger than memory. Any of them can change the answer.

Sources

• db-wars: the app, k6 script and setup scripts
• SQLite: write-ahead logging
• SQLite: PRAGMA synchronous
• SQLite: appropriate uses
• SQLite: file locking and concurrency
• PostgreSQL: WAL configuration
• go-sqlite3
• k6: ramping arrival rate executor