But those commits are FAST - tens of thousands of writes a second, easily. So if you want to handle multiple writing users at once, put the writes in an in-memory queue and send them to SQLite as fast as it will accept them.

If you mean tens of thousands of commits a second, it can only go that fast on SSD or battery-backed HDD.

write+sync is a speed limiter in general (even on SSDs), because of waiting for the syncs which are needed for commit durability. On HDD, serial transactions can't go faster than roughly twice the rotation time of the HDD per write, plus seeks, and on network filesystems they can't go faster than four times the network latency (two syncs per commit) plus whatever the backing store needs.

The trick to getting high commit throughput is to allow the durability part of multiple independent transactions to be committed concurrently. On a filesystem-backed DB this means writing multiple transactions per fdatasync. It would be tricky for SQLite to do this because of its architecture as a library and synchronous API, but not impossible.

I guess with the introduction of BEGIN CONCURRENT resolves this.

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...