"Idle cost is that one lightweight SELECT per millisecond per database — no page-cache pressure, no writer-lock contention, no kernel file watcher in the mix."
I think (respectfully) the LLM that probably wrote this overshot the mark here because busy-polling a select does not actually sound better to me than a "kernel file watcher".
Talking out your ass, a select to this table in sqlite is like a couple hundred microseconds. Fossil uses hundreds of individual queries to build pages and still completes in under 30 ms. It's not exactly charming but it is not a performance problem. https://sqlite.org/np1queryprob.html
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?
Yeah, I had the same instinct - this feels very much like a "nice idea" but the execution falls short. I mean - busily banging on sqlite like this? Shit at that point just use Redis.
I had a manual fs polling thing a while back. It was ugly (low time budget, didn't wanna mess with the native watchers), just scanned the whole thing once per second. It averaged out to like 0.3% CPU.
Not elegant, but acceptable for my purposes! (Small-ish directory, and "ping me within a second or two" was realtime enough for this use case.)
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.
It’s an interesting approach and can be quite fun to use for new projects.
> How it works: honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal.
"Idle cost is that one lightweight SELECT per millisecond per database — no page-cache pressure, no writer-lock contention, no kernel file watcher in the mix."
I think (respectfully) the LLM that probably wrote this overshot the mark here because busy-polling a select does not actually sound better to me than a "kernel file watcher".
"one lightweight SELECT per millisecond"
This reminds me of the teenager who told her dad that she was just a tiny little bit pregnant.
Talking out your ass, a select to this table in sqlite is like a couple hundred microseconds. Fossil uses hundreds of individual queries to build pages and still completes in under 30 ms. It's not exactly charming but it is not a performance problem. https://sqlite.org/np1queryprob.html
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?
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.
Yeah, I had the same instinct - this feels very much like a "nice idea" but the execution falls short. I mean - busily banging on sqlite like this? Shit at that point just use Redis.
What's the CPU usage? Like 2%?
I had a manual fs polling thing a while back. It was ugly (low time budget, didn't wanna mess with the native watchers), just scanned the whole thing once per second. It averaged out to like 0.3% CPU.
Not elegant, but acceptable for my purposes! (Small-ish directory, and "ping me within a second or two" was realtime enough for this use case.)
I'm not even saying it's unworkable, just, my intuition is not that the "lightweight per-millisecond select" is an optimal design.
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.
Maybe, I'm really writing about the language on this page, not about the design (I responded about this upthread).
Reminds me of Litestack for Rails. Eventually, it was abandoned because Rails itself started going all out on SQLite.
https://github.com/oldmoe/litestack
All in*
It’s an interesting approach and can be quite fun to use for new projects.
> How it works: honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal.
Prior discussion a few days ago: https://news.ycombinator.com/item?id=47874647
At the end it says: "pg-boss and Oban are the Postgres-side gold standards" -- but Oban supports SQLite now too https://github.com/oban-bg/oban