Don't underestimate SQLite: a benchmark I didn't expect
I pointed SQLite at 82,000 documents, expected it to tap out, and had to double-check my benchmark code.
I was exploring RAG on the cheap. Needed something local, low-compute, no infrastructure overhead. SQLite seemed fine for the job — the kind of “good enough” choice you make when you want to move fast and not stand up a Postgres instance for a weekend experiment. I pointed it at 82,000 Ubuntu man pages and started benchmarking ingestion rates.
I expected 5–10k documents per second at best. I was wrong.
The reflex
The instinct when someone says “concurrent writes to SQLite” is to wince. SQLite is single-writer — one write transaction at a time. It’s the database on your phone. It’s the database in your browser. It’s not the database you reach for when multiple things are writing simultaneously.
That reflex is technically correct. It’s also incomplete.
WAL mode and a write queue
The first unlock is WAL mode. With PRAGMA journal_mode = WAL, readers never block writers and writers never block readers. The database stops being a serialization point for reads while a write is in progress. Pair that with PRAGMA synchronous = NORMAL and PRAGMA busy_timeout = 5000 and you have a connection that won’t fall over the moment two threads look at it funny.
But the bigger trick is enforcing the single-writer contract without fighting it. Instead of serializing writes at the lock layer, you put a Channel<T> in front of each database connection with SingleReader = true. dotnet’s channel enforces the invariant — one consumer, no contention. The database never sees competing writers because the channel absorbs them first.
_channel = Channel.CreateBounded<ManPage>(new BoundedChannelOptions(batchSize * 4){ SingleReader = true, SingleWriter = false, FullMode = BoundedChannelFullMode.Wait});
_writerTask = Task.Run(WriterLoop);The pragmas are set once on the connection and never touched again:
private static void ApplyPragmas(SqliteConnection conn, bool writer){ using var cmd = conn.CreateCommand(); cmd.CommandText = """ PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA cache_size = -65536; PRAGMA temp_store = memory; PRAGMA mmap_size = 268435456; """; if (writer) cmd.CommandText += "\nPRAGMA wal_autocheckpoint = 1000;"; cmd.ExecuteNonQuery();}The writer loop drains the channel into batches and flushes on size. One transaction per batch instead of one per row — that’s the second unlock. SQLite loves big transactions; it hates small ones.
private async Task WriterLoop(){ var batch = new List<ManPage>(_batchSize);
await foreach (var page in _channel.Reader.ReadAllAsync()) { batch.Add(page); if (batch.Count >= _batchSize) { FlushBatch(batch); batch.Clear(); } }
if (batch.Count > 0) FlushBatch(batch);}
private void FlushBatch(List<ManPage> batch){ using var tx = _writer.BeginTransaction(); using var cmd = _writer.CreateCommand(); cmd.Transaction = tx; cmd.CommandText = """ INSERT INTO docs (name, section, filename, description, content, ingested_at) VALUES ($name, $section, $filename, $desc, $content, $ts) """;
var pName = cmd.Parameters.Add("$name", SqliteType.Text); var pContent = cmd.Parameters.Add("$content", SqliteType.Text); // ... remaining parameters
foreach (var p in batch) { pName.Value = p.Name; pContent.Value = p.Content; cmd.ExecuteNonQuery(); }
tx.Commit(); Interlocked.Add(ref _docCount, batch.Count);}Sharding along a natural boundary
The Ubuntu man pages have a natural shard boundary: sections 1–8. Each section gets its own SQLite file — man1.db through man8.db — each with its own channel and its own dedicated connection. One writer per file, forever.
This matters because sharding eliminates cross-shard contention entirely. Writers for man1.db and man3.db are writing to different files on disk. No lock negotiation. No waiting. The coordination overhead drops to zero because there is no coordination.
There’s also a catalog.db — a thin shard registry that records doc counts and ingest history per section. It’s the coordination layer, kept deliberately separate from the data shards so it never becomes a write bottleneck.
The benchmark
To make the numbers fair, I ran three modes against 80,000 synthetic in-memory documents — no file I/O, pure SQLite write throughput:
[A] Naive: 8 concurrent writers → 1 shared SQLite file, 1 row/tx 80 000 docs, 76 364 ms, 1 048 docs/sec (lock errors: 4)
[B] Sharded: 8 writers → 8 separate files, 1 row/tx (no queue) 80 000 docs, 1 348 ms, 59 347 docs/sec
[C] Full architecture: Channel<T> SingleReader per shard + batch inserts
Batch Docs Insert ms Insert/sec FTS ms End-to-end/sec------------------------------------------------------------------------50 80 000 327 244 648 587 87 527100 80 000 364 219 780 2 266 30 418250 80 000 1 212 66 007 2 253 23 088500 80 000 403 198 511 6 056 12 3861 000 80 000 1 217 65 735 6 195 10 7932 000 80 000 411 194 647 5 158 14 3655 000 80 000 611 130 933 2 320 27 294------------------------------------------------------------------------A→B alone is a 57× speedup from sharding. No batching, no channels — just separate files. Then the channel and batch transactions on top of that puts you at the number in the batch=50 row. The one I had to double-check.
FTS is a separate concern
The end-to-end rates in the table are lower than the insert rates because of the FTS5 rebuild. After all documents are inserted, each shard rebuilds its full-text search index. This is CPU-bound work: tokenizing content with a Porter stemmer, building an inverted index across 10,000 documents per shard. It has nothing to do with SQLite’s write throughput — it’s just expensive, and it runs after the inserts are done.
public void RebuildFts(){ using var cmd = _writer.CreateCommand(); cmd.CommandText = "INSERT INTO docs_fts(docs_fts) VALUES('rebuild')"; cmd.ExecuteNonQuery();}The pattern I settled on: insert fast, defer the rebuild. If you’re ingesting continuously, rebuild in a background worker when traffic is low. The inserted rows are queryable through the base table immediately; FTS catches up in the background. Keeping the FTS virtual table as a content table (content = docs, content_rowid = id) means the index stays in sync with the source rows automatically — you’re just choosing when to pay the indexing cost.
SQLite as an ingestion buffer
This is where it clicked. SQLite isn’t trying to be your search layer. It’s a fast, local, zero-infrastructure buffer that can absorb an ingestion spike without making callers wait.
Endpoint timeouts are real. If you’re ingesting tens of thousands of documents and you try to normalize, embed, and index them synchronously on the way in, you’re going to drop connections. Getting data onto disk fast and processing it later is the reliable path.
Depending on your budget, the architecture looks different:
Budget stack: SQLite end-to-end. Ingest at full speed, FTS rebuild offloaded to a background worker. Runs on a laptop, no infrastructure. Good enough for small-scale search and you’ll have it running longer than you think.
Corporate stack: SQLite as the ingest sink, behind a queue. A normalization service picks up documents, cleans structure or generates embeddings, hands off to a proper RDBMS — Postgres with BM25, pgvector, whatever fits — for search. SQLite’s job is to absorb the spike, not do the ranking.
In both cases the principle is the same: separate ingestion speed from processing correctness. Get the data in. Figure out what to do with it later.
The “SQLite can’t do concurrency” reflex is correct if you’re naive about it — throw eight threads at one file with one row per transaction and you get exactly what you deserve. Add WAL, enforce the single-writer contract with a channel, shard along a natural boundary, and you have a legitimate ingestion pipeline that runs on commodity hardware with no infrastructure overhead. The wall exists. It’s just further out than the reflex suggests.