A common pattern (which I've implemented myself in Datasette) is to put your writes in an in-memory queue and apply them using a single dedicated connection.
We have server app on a single beefy server servicing ~100,000 simultaneous users with avg user action per second of 0.2 (~20k actions per second) requiring some ~60k sqlite writes per second.
A single thread handles that with message passing with little load. We estimate we can handle 5-10 times as much without changing anything. That's with SSD's and standard OS caching. With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous.
Who needs durability anyway, right?
Suitability depends on what the requirements are.
But more seriously if you want the best of both worlds (in-memory speeds and durability) there are NVDIMMs.
SQL Server can take advantage of them: https://docs.microsoft.com/en-us/sql/relational-databases/pe...
I imagine with some effort SQLite could as well.
[1] example: not quite WAL, but disabling full logging, so it was then on simple logging, on MSSQL as part of a log shrink process. On a cient site. Who did 3-hourly log backups.
1 connection per database is critical. WAL is important. Not double-locking helps (most builds of SQLite serialize all writes internally).
You do all of these things correctly at the same time, then you can easily handle tens of thousands of transactions per second.
Additionally, we also do a per session database concept where data is scoped as specifically as possible. Using just 1 sqlite database for the whole application would be a mistake imo.
Synchronous replication is our next step, but we might build something in-house for this.
Even if you are taking advantage of the built in WAL feature?
https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...
If you’re averaging 1000/s and you get 1010 req in one sec, yes this leads to an infinite queue - but your average is now above 1000/s! Then if a few seconds later you get 990 requests, the queue catches up (because your avg is back to 1000)
IOW - it assumes only positive variability and no negative variability which does not mimic real world systems in my experience.
It’s an academic exercise that’s interesting to think about, but has no practical application. Else we would see infinite queue backups every time a system received a burst of traffic beyond their maximum throughput in any given time period.
What am I missing?
You do actually expect the server to eventually recover from all load spikes, but that dead time means cumulatively you'll typically have queued more requests than you've handled at long enough time scales. On average that surplus is infinite, despite it sometimes being as low as 0.
It's not just academic, but it's not surprising that doesn't echo your real-world experience either. We don't usually run systems anywhere near capacity, and even when you do you'll still expect the queue to be able to handle the load eventually.