SQLite async connection pool for high-performance

if you enable WAL mode with sqlite then readers are not blocked by writer so only writers queue up without needing any special case handling to archive it

(in general you _really_ should use WAL mode if using sqlite concurrently, you also should read the documentation about WAL mode tho)

Writers won't queue up, rather they'll storm the place, taking turns at asking “can I go now” and sleeping for (tens, hundreds of) milliseconds at a time.

This only gets “worse” as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left, because they didn't line up?

And, if you have a single limited pool, your readers will now be stuck waiting for an available connection too (because they're all taken by sleeping writers).

It's much fairer and more efficient for writers to line up with blocking application locks.

I was running into some horrendous issues with WAL, where the WAL file would grow boundlessly, eventually leading to veery slow reads and writes.

It's fixable by periodically forcing the WAL to be truncated, but it took me a lot of time and pain to figure it out.

Interesting, were there any warning signs beyond general query slowdown?

No warning signs and very little about it on the Internet. Just performance slows to a grind. Also hard to replicate.

If I had a blog, I'd be writing about it.

WAL doesn’t cure concurrency issues for SQLite. WAL plus single writer, multiple reader threaded is required. It’s blazing fast though.

I think this is mentioned in the docs https://www.sqlite.org/wal.html

The is why I said read the WAL doc page in a different answer ;)

They do point out the risks here: https://sqlite.org/wal.html#avoiding_excessively_large_wal_f...

sqlites design makes a lot of SQL concurrency synchronization edge cases much simpler as you can rely on the single writer at a time limitation. And it has some grate hidden features for using it as client application state storage. But there are use-cases it's just not very good at and moving from sqlite to other DBs can be tricky (if you ever relied on the exclusive write transaction or the way cells are blobs which can mix data types, even it it was by accident)

I did read it. For whatever reason, automatic checkpoints basically would stop from time to time, and the WAL file would start growing like crazy.

In the end I wrote an external process that forced a checkpoint a few times a day, which worked. I came across other exasperated people in various dark corners of the Internet with the same symptoms.

And how big was the WAL file getting compared to normal? As someone running SQLite in prod it would be comforting at least to have some heuristics to help detect this situation!