Vivian Voss

Read on LinkedIn

The Single Writer

sql architecture performance

By Design ■ Episode 1

In the year 2000, a man named D. Richard Hipp had a rather specific problem. He was building software for a U.S. Navy guided missile destroyer, and the existing Informix database installation required a database administrator. The DBA was not always available. When the database went down, the software went down. One imagines this is somewhat inconvenient on a vessel carrying Tomahawk missiles.

Hipp's solution was not to fix the server. It was to eliminate it entirely. The result was a database engine that compiled into 600 kilobytes, stored everything in a single file, and required precisely zero configuration. No daemon. No socket. No DBA. The library linked into your programme and that was the end of the conversation.

Twenty-six years later, his "temporary solution" is the most widely deployed software module in history. More instances than all other database engines combined. One does wonder whether the Navy knows.

The Complaint

"SQLite can't handle concurrent writes."

Correct. Only one writer at a time. The others queue. Every Stack Overflow thread about SQLite eventually contains this revelation, delivered with the gravity of someone discovering that water is wet.

It is, of course, true. SQLite serialises all write operations. If process A is writing, process B waits. If ten processes want to write simultaneously, nine of them form an orderly British queue. There is no lock escalation, no row-level locking, no MVCC for writers. One writer. Full stop.

The Stack Overflow commentator then concludes that SQLite is a toy. A footnote. Adequate for prototypes and mobile apps, but certainly not for anything serious. At which point one might gently direct their attention to the avionics system in the Airbus A350, which runs SQLite under DO-178B certification, the kind where human lives depend on the software working correctly. Not a prototype. Not a mobile app. A fly-by-wire system carrying 300 passengers at 900 kilometres per hour.

The Decision

Hipp did not forget to add concurrent writes. He chose not to.

This is the distinction that separates engineering from feature accumulation. A single writer means no deadlocks. No lock contention. No transaction ordering puzzles that require a PhD and three cups of coffee to debug. No write-write conflicts. No phantom reads between concurrent transactions. The entire concurrency model fits in your head, which is more than can be said for most PostgreSQL configurations and considerably more than can be said for any distributed database.

Concurrency: What You Must Manage Traditional RDBMS 1. Row-level locking 2. Lock escalation (row → page → table) 3. Deadlock detection + resolution 4. MVCC snapshot isolation 5. Transaction ordering 6. Write-write conflict resolution 7. Phantom read prevention 8. Connection pool tuning Your DBA's responsibility. Good luck finding one at 3 AM. SQLite One writer at a time. No deadlocks. No contention. No ordering puzzles. Fits in your head. Your responsibility: none. Sleep through the night.

Consider what you gain by not having concurrent writes. You do not need a connection pool. You do not need a connection pool manager. You do not need to tune the connection pool manager. You do not need to monitor the connection pool manager. You do not need a dashboard for the monitor of the connection pool manager. You do not need an alert for the dashboard for the monitor of the connection pool manager. The recursion of operational complexity stops before it starts.

WAL Mode: The Elegant Completion

In 2010, ten years after the initial release, Hipp introduced Write-Ahead Logging. The idea was simple: instead of writing directly to the database file, write to a separate log first. Readers read from the original file (or from a snapshot of it). The writer appends to the log. Periodically, the log is checkpointed back into the main file.

The result: unlimited concurrent readers, one writer, zero interference. Readers never block the writer. The writer never blocks readers. No locks held during reads. No stale data. No coordination overhead.

WAL Mode: Readers + Writer, Zero Interference Reader 1 Reader 2 Reader N database.db single file Writer WAL file append-only log checkpoint Readers: unlimited Writer: exactly one No locks held during reads Writer never blocks readers Readers never block writer WAL checkpoints periodically No coordination overhead

This is not a compromise. This is a model so clean that most applications never need anything more. Your web application serves 95 per cent reads? Congratulations, you have unlimited concurrency. The remaining 5 per cent of writes queue for microseconds. The theoretical bottleneck that keeps database architects awake at night simply does not materialise at the scale where 99 per cent of applications operate.

The Trade-Off, Honestly Stated

If your application genuinely needs hundreds of concurrent write transactions per second, SQLite is the wrong tool. Hipp says so himself, on his own website, in plain English, without hedging. This is not a limitation he is embarrassed about. It is a boundary he drew deliberately. The documentation does not bury the trade-off in a footnote. It leads with it.

One does note, with a certain admiration, how rarely this level of honesty appears in technical documentation. Most database vendors will tell you their product scales to infinity and beyond, provided you buy the Enterprise tier and attend the three-day certification course. Hipp tells you when not to use his software. On the front page.

The trade-off bought him everything else:

  • One file. The entire database is a single, cross-platform, self-describing binary file. Copy it to another machine and it works. No export. No import. No schema migration tool. cp database.db /backup/ is a valid backup strategy.
  • Zero configuration. No my.cnf. No postgresql.conf. No tuning guide. No blog post titled "10 Settings You Must Change In Production."
  • No daemon. No process to start, stop, monitor, or restart at 3 AM when it segfaults.
  • No DBA. The role that justified its own salary by managing complexity that SQLite simply does not have.
  • No network. No TCP connection. No authentication. No TLS certificate renewal. No connection timeout. The database is a function call.

The Proof

The numbers are worth pausing over, because they are not marketing figures. They come from SQLite's own documentation, which is refreshingly honest about what SQLite is and what it is not.

SQLite: The Numbers 1 trillion active databases worldwide Every device Android, iOS, browser Chrome, Firefox, Safari DO-178B Airbus A350 avionics lives depend on it 35% faster than raw filesystem I/O for small reads 600 KB total binary size complete SQL engine Public domain no licence, no fees supported until 2050 156,000 lines of source code hand-written C 92 million lines of tests 590x test-to-code ratio Archival format Library of Congress recommended standard The "toy database" runs in more places than every other database engine combined. Sources: sqlite.org/mostdeployed.html, sqlite.org/fasterthanfs.html, sqlite.org/testing.html

That test-to-code ratio deserves its own moment of silence. 156,000 lines of source. 92 million lines of tests. That is 590 lines of test code for every line of production code. The test suite includes 100 per cent branch coverage, fault injection, out-of-memory simulation, I/O error simulation, and crash recovery testing. The test harness is larger than most commercial databases.

And yet the code is public domain. Not MIT. Not Apache. Not GPL. Public domain. No licence. No attribution requirement. No licence compatibility matrix. No legal department consultation. Hipp placed the entire work in the commons and committed to supporting it until 2050, which, at the time of writing, is rather further away than most venture-backed startups will survive.

The performance claim is perhaps the most counterintuitive: SQLite is 35 per cent faster than reading directly from the filesystem for small blobs. Not faster than another database. Faster than open(), read(), close(). The overhead of filesystem metadata, directory traversal, and inode lookup exceeds the overhead of a B-tree lookup in a single file. The database is faster than no database at all.

The Architecture You Do Not Need

Consider the typical web application database deployment. You need a server. The server needs a host. The host needs monitoring. The monitoring needs alerts. The alerts need an on-call rotation. The on-call rotation needs a policy document. The policy document needs a meeting. The meeting needs a calendar invite. The calendar invite needs a distribution list. The distribution list needs an owner. The owner is on holiday.

SQLite requires a file.

What You Deploy Client-Server Database Server process (daemon) Host machine or container Authentication + TLS Connection pool Monitoring + alerting Backup strategy + rotation Six components. Six failure modes. SQLite cp database.db /backup/ One file. Zero processes. Copy it. It works. One component. One file. Done.

This is not a simplification for the sake of a diagram. This is the actual operational difference. Every component in the left column is a real dependency with real failure modes, real upgrade paths, real security patches, and real 3 AM pages. Every component you do not have is a component that cannot break.

The Principle

Every design decision is a trade-off. The mediocre ones try to have everything. The brilliant ones know what to sacrifice.

Hipp sacrificed write concurrency. Not because he could not build it, not because he lacked the engineering skill, but because he understood that the absence of a feature can be more valuable than its presence. He gained simplicity so complete that his database became a file format. The Library of Congress recommends it for archival storage. Not as a database. As a format. The distinction matters: formats outlive software. The data will be readable when the application that created it has long been forgotten.

One trillion active databases. Every phone. Every browser. Every Airbus A350. The most deployed piece of software in human history, written by one man, supported until 2050, and released into the public domain.

One does wonder what we might accomplish if we had the courage to say "no" more often.

Every design decision is a trade-off. Hipp sacrificed write concurrency. He gained simplicity so complete that his database became the most deployed software module in history. 600 KB. One file. Zero configuration. Public domain. Supported until 2050. The "toy database" runs in more places than every other database engine combined.