[ncruces]

If you're not making any changes to the database, does the SELECT "kill" you?

And if you are making changes, don't you have to poll regardless after the file watcher wakes you?

For WAL mode, SQLite can probably satisfy this query just by inspecting some shared memory. But it is busy waiting, sure.

[billywhizz]

SQLite has a wal hook which calls you back every time a transaction is committed to the WAL. https://www.sqlite.org/c3ref/wal_hook.html

[ncruces]

That only catches changes made by the database connection being "hooked."

This has a thread running in the background trying to catch changes made by other connections, potentially (I'm not sure here, but I suspect as much) in different processes that are modifying the same database.

[billywhizz]

good point. but ime and as seems to be widely understood writing from multiple connections is a bit of a minefield in SQLite. and afaik it still would be possible to have a hook on all connections you expect to be writing?

[duped]

That wouldn't work across processes. And if you only care about in-process queuing then you might find it easier/faster to use another kind of storage or roll your own WAL.

[billywhizz]

i did a quick benchmark on this with a single db connection updating user_version in a tight loop with the wal_hook callback enabled.

on my crappy old i5 with the db file on /dev/shm it can do ~150k writes a second with the wal_hook callback called on every write. and this is using JS bindings to C++ so has some unnecessary overhead.