Hold on -- if it really is "one lightweight SELECT per millisecond", and you're saying a select is "a couple hundred microseconds", say generously 200us?, then you're spending 200us out of every 1000us just selecting. That's a lot of polling!

Yeah, again, to be clear: I get how SQLite works and I'm not dunking on the design, I'm just saying the comparison set up on this page snags. It's a classic LLM negated triptych, but "one of these things is not like the other": cache pressure: bad, writer contention: bad, kernel file watcher: ... good, actually? Intuitively seems better than this design?

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.

With SQLite, you're basically funneled towards a single-writer / single-process design anyway ... in which case why not use a more traditional condvar + mutex rather than polling?

Are you trying to avoid sleep?

Really might be in sqlite. I've learned to never trust my intuition about performance with that thing. So many times I've gone to "optimize" something and discovered that the naive hack way I had been doing it was faster anyway. It's built for this sort of bullshit.

i mean, technically this is once per millisecond, so this would happen 1000x more. In your case due to the kernel overhead you would likely not even be able to do it (300% CPU?).

Either way this does seem like a very large overhead due to the fact that there's just no other way to do it without a deeper kernel integration which might be outside the scope of what sqlite is trying to do.