SQLite, M/D/1 Queues, and the 3 AM Dashboard Test
Embedding SQLite simplifies software but removes the network boundaries operators rely on for structural telemetry during metastable failures.
By Owen Tate
Sparked by SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers · discussion
It feels like we're caught in a perpetual cycle of architectural backlash. Every time I open a tab these days, someone is loudly declaring that we’ve all been duped by Big Cloud: you don't need a distributed database, just use local SQLite in WAL mode. Looking at the widespread pushback against architectural complexity in recent Hacker News discussions and blog posts advocating for SQLite in WAL mode, they have a point. We should concede the physical reality here. For read-heavy workloads where contention is effectively zero, memory-mapped I/O is unbeatable. You simply cannot cheat the speed of light or local bus speeds. When a web server can pull a record out of a file that is already cached in RAM on the same machine, the latency graph looks like a rounding error. The happy path is beautiful.
But let’s put the happy path aside and run the math on what actually happens when multiple threads start fighting over a single-writer lock. Write-Ahead Logging provides great concurrency for readers by ensuring they don't block writers, but it has a fundamental physical limit on the write path. To understand why this inevitably breaks down under concurrent load, we need to model this as an M/D/1 queue.
In this model, we have a single server (the SQLite lock) handling requests with a deterministic service time ($S$), driven by a Poisson arrival process. Let's calculate the average wait time ($W$) in the queue as utilization ($U$) scales, using the standard formula: $W = (U \times S) / 2(1 - U)$.
Run the numbers with me. At 50% utilization, the system effortlessly absorbs the load. The wait time is a fraction of the service time. At 80% utilization, latency degrades, but the system stays mathematically bounded. The queue depth increases, but the disk can still keep up. But as $U$ creeps toward 99%—because real-world traffic is never perfectly smooth, and Poisson arrivals guarantee clumps of concurrent requests—the denominator approaches zero. The math dictates that the queue depth asymptotically heads toward infinity. A hard wall.
In a pristine academic simulation, this simply means requests wait longer before eventually completing. But drop this into a live production environment, and you quickly realize that incoming traffic shatters the illusion of clean mathematical vectors, arriving instead as a chaotic barrage of hardware blips, retry storms, thermal noise, and messy application logic. Native SQLite attempts to manage lock contention gracefully using a stepped sleep schedule, pausing briefly before retrying when it hits a busy lock. But modern web frameworks rarely let the database driver handle this cleanly. Application-layer ORMs and drivers inevitably wrap this base behavior in their own exponential backoff loops.
When the single-writer lock becomes heavily contested, the system state degrades rapidly. Application threads wake up, attempt to write, fail to acquire the lock, and immediately think: This lock is heavily contested, so I should probably just back off and try my luck in a few milliseconds. This transforms a simple mathematical queuing delay into a textbook metastable failure. The application threads spend all their CPU cycles context-switching, allocating retry objects, and managing backoff timers. The CPU load skyrockets while the database lock remains starved of actual sequential writes. The system begins doing zero useful work. A flawless traffic jam. (You've technically prevented database corruption, but only by completely paralyzing the host).
Let's translate this mathematical limit into an operational nightmare. Picture what this failure mode actually looks like when you are blearily staring at a metrics panel at three in the morning.
In a traditional multi-tier architecture, when a distributed database gets backed up, the network load balancer or the database's own ingress queue begins to swell. You can look at an APM graph and clearly see a massive spike in wait time at the network boundary. The database node might be struggling, but the application nodes remain healthy, happily queuing up requests over TCP.
But when you embed the database directly into the application server, that physical network boundary disappears. Without the network to act as a structural buffer, your queue morphs into a silent mass of application threads stuck in memory, blindly waiting on a local file descriptor.
The pager goes off. The operator opens Datadog. The database latency metrics show zero wait time (because there is no network transit to instrument). Instead, the application servers are inexplicably pegged at 100% CPU. The threads are secretly spinning in exponential backoff loops, choking the application host to death, while the database itself reports that everything is perfectly fine. The operators—despite their technical competence—are left entirely blind, because the architecture robbed them of their structural telemetry.[^1]
We often talk about network hops as a necessary evil for scaling capacity, but they serve a far more vital operational purpose. A network boundary is an observability boundary. Simplicity is a property of systems, not components: removing the network from your database might make the software simpler, but it makes the human's job infinitely harder.
[^1]: Technically, if you have meticulously configured eBPF probes attached to the application's runtime scheduler, you might spot these user-space sleep loops (something very few teams proactively deploy until after suffering exactly this kind of outage). Standard APM traces are entirely blind to them.