Rendered at 20:03:55 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
tptacek 1 days ago [-]
"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".
felooboolooomba 1 days ago [-]
"one lightweight SELECT per millisecond"
This reminds me of the teenager who told her dad that she was just a tiny little bit pregnant.
nine_k 22 hours ago [-]
One cannot be a little bit pregnant. But a DB can be only a little bit in the RAM, and specifically in the page cache. SQLite can act exactly like that, and it's damn fast as long as it does not need to durably write a transaction. Polling once a millisecond could spend a few microseconds.
I wonder if using a tiny Redis instance, or even something like LevelDB would be even more efficient.
Retr0id 12 hours ago [-]
With the file-watch APIs is that you don't need to poll at all - free is better than cheap.
sroussey 23 hours ago [-]
Thing of the battery!
(read that in the way of "think of the children!")
giraffe_lady 1 days ago [-]
[flagged]
rv64imafdc 1 days ago [-]
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!
giraffe_lady 1 days ago [-]
I mean only in the same sense that you spend 1 second per second doing something. Time is probably not the best way to evaluate the resources this consumes and I doubt it takes much of anything else either.
It does seem weird though even for sqlite. I wonder how oban does it. I also wonder if OP knows oban can run on sqlite.
tptacek 1 days ago [-]
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?
8note 23 hours ago [-]
to me it sounds like they asked it to not make a kernel file watcher, and now it writes that into every comment everywhere, despite not even being in the implementation
russellthehippo 22 hours ago [-]
Yup
russellthehippo 22 hours ago [-]
Respectfully (thanks haha) - yeah probably right. Original intent was to use inotify type thing but i avoided per-platform differences at the outset. this was definitely a for fun project that blew up unintentionally and am working to harden/improve.
Love Fly.
ncruces 8 hours ago [-]
One of the things people seem to forget is that SQLite itself polls every millisecond or so to grab a lock.
So yes, don't use this in a mobile device, or a server if you want to let the CPU enter a low power state.
Otherwise, a single thread doing this in an otherwise idle server, doesn't seem that terrible. And if it's not idle, inotify won't help you (need to query what changed afterwards).
conradev 13 hours ago [-]
A prepared `PRAGMA data_version` is likely quite cheap to run because it hits the same page every time…
…but some other push-based IPC mechanism would be a lot more battery friendly
ncruces 1 days ago [-]
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.
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 23 hours ago [-]
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?
billywhizz 21 hours ago [-]
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.
duped 19 hours ago [-]
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.
redsocksfan45 1 days ago [-]
[dead]
d1l 1 days ago [-]
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.
koito17 1 days ago [-]
For what it's worth, Kine (software that k3s uses to replace etcd with SQL databases) implements etcd watches on SQLite through polling[1]. The reason being that SQLite does not offer NOTIFY/LISTEN like MySQL and Postgres do. Ironically, Honkey attempts implementing NOTIFY/LISTEN through polling.
k3s has been running on my home server for about three years now (using the default SQLite backend), and there doesn't seem to be excessive CPU usage despite dozens of watches existing in the simulated etcd. Of course, this doesn't say much about Honker, but it's nonetheless worth pointing out that sometimes the choice of database forces one towards a certain design.
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?
sroussey 23 hours ago [-]
Are you trying to avoid sleep?
tptacek 1 days ago [-]
I'm not even saying it's unworkable, just, my intuition is not that the "lightweight per-millisecond select" is an optimal design.
giraffe_lady 1 days ago [-]
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.
tptacek 1 days ago [-]
Maybe, I'm really writing about the language on this page, not about the design (I responded about this upthread).
giraffe_lady 1 days ago [-]
Oh, yes, I see what you mean now.
andai 1 days ago [-]
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.)
kimixa 16 hours ago [-]
If this stops the core being able to drop to a lower power state it can be whole multiples of power use on some devices.
Wake ups are death for mobile form factors, even if not really doing much work.
andai 3 hours ago [-]
This is a pretty good argument against the way we do operating systems now, right?
booi 23 hours ago [-]
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.
nine_k 22 hours ago [-]
If the fs tree scanned once per second had 1000 files, it would be once per millisecond for a file.
paulddraper 23 hours ago [-]
> one lightweight SELECT per millisecond
For the low, low cost of $1 per minute, you can also lease a supercar.
djdillon 1 days ago [-]
[flagged]
codedokode 23 hours ago [-]
> Once real work flows through a SQLite-backed app, you need a queue. The usual answer is “add Redis + Celery.”
Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid going to kernel when the tasks arrive quickly). Why do you need redis for that? If you need persistent tasks, then you can store them in the table, and still use futex for notifications. This polling is inefficient and they should not make it a library which will cause other lazy developers add it to their app.
> 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
That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.
Like Electron, this feels like written by a web developer and not a real programmer.
Groxx 23 hours ago [-]
>That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.
I suspect that's actually "per process, per database (usually 1)", and not based on number of threads or tables. `data_version` semantics mean there's no need for more than one connection polling it, and it's being used as a relatively lightweight "DB has changed, check queues" check (that's pretty much its whole purpose).
Also I believe this is mostly intended for multi-process use, e.g. out-of-process workers, so an in-process dirty tracker (e.g. just check after insert/update/delete) isn't sufficient.
So I do think it's somewhat crazy, but it is at least very simple. fsnotify-like monitoring seems like a fairly obvious improvement tho, not sure why that isn't part of it. Maybe it's slower? I haven't tried to do anything actually-performant-or-reliable with fs notifications, dunno what dragons lie in wait.
deepsun 23 hours ago [-]
Nevertheless, expect articles like "We replaced our redis cluster with this simple extension and got it N times faster".
Key difference vs SQL polling is that we’re touching metadata instead of data pages. I have work in process to make this work without any polling (innotify, kqueue, mmap’d shm file check) after the original stat(2) direction proved unreliable if lightweight.
Would love your feedback and or contributions in the repo - still figuring out the end shape.
itopaloglu83 1 days ago [-]
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.
arlobish 1 days ago [-]
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
I've implemented something similar in the past, but using inotify. You need to watch the -wal file for IN_MODIFY. To make it work reliably I found I had to run:
BEGIN IMMEDIATE TRANSACTION; ROLLBACK;
Otherwise the new changes weren't guaranteed to be visible to the process. I'm sure there's a more targetted approach that would work instead - maybe flock on a particular byte in the `-shm` file.
22 hours ago [-]
tengbretson 19 hours ago [-]
I'm a big fan of SQLite and all that, but if SQLite constrains you to a single writer process, why not do this in your application layer anyway?
SQLite 10 hours ago [-]
SQLite allows multiple writers. The constraint is that only one of how writers can be actively writing at any moment in time. If there are multiple processes wanting to write, they take turns. SQLite prevents two or more writes from running concurrently, so there is nothing the application needs to do to implement this, other than responding to SQLITE_BUSY replies from failed (concurrent) write attempts and retrying after a short delay.
Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.
At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.
A good reason: you do not want npm AND docker AND java just for your queue.
kweiza 22 hours ago [-]
On edge this misses Durable Objects + alarms — same primitives, no polling, no Redis to skip in the first place.
maxdo 1 days ago [-]
Almost feels like someone is trying to joke about similar postgres application .
To make it look even more absurd . SQLite is not concurrent and you’ll have tons of problems using it practically .
ghm2180 21 hours ago [-]
Can this work with lightstream?
deferredgrant 1 days ago [-]
This seems especially appealing in the awkward middle: too serious for in-memory queues, not big enough to justify Kafka-shaped machinery.
neocron 21 hours ago [-]
No maven package for java? Guess this isn't a serious project
andrewstuart 1 days ago [-]
Suggestion for the author wind back the polling to once a second when nothing is happening.
andrewstuart 1 days ago [-]
I can’t see any benchmarks or performance stats.
I’d like to see messages per second.
canadiantim 1 days ago [-]
Could this work with Turso, the SQLite rust rewrite?
russellthehippo 22 hours ago [-]
Author here. Yeah doesn’t depend on the underlying db if it speaks SQLite.
enduku 22 hours ago [-]
I think this is interesting too sqlite a as the coordination boundary: business state, queue state, stream offsets, retries, and acks all sharing one transactional substrate. The 1ms polling is getting a lot of weight in the thread though :)
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".
This reminds me of the teenager who told her dad that she was just a tiny little bit pregnant.
I wonder if using a tiny Redis instance, or even something like LevelDB would be even more efficient.
(read that in the way of "think of the children!")
It does seem weird though even for sqlite. I wonder how oban does it. I also wonder if OP knows oban can run on sqlite.
Love Fly.
So yes, don't use this in a mobile device, or a server if you want to let the CPU enter a low power state.
Otherwise, a single thread doing this in an otherwise idle server, doesn't seem that terrible. And if it's not idle, inotify won't help you (need to query what changed afterwards).
…but some other push-based IPC mechanism would be a lot more battery friendly
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.
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.
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.
k3s has been running on my home server for about three years now (using the default SQLite backend), and there doesn't seem to be excessive CPU usage despite dozens of watches existing in the simulated etcd. Of course, this doesn't say much about Honker, but it's nonetheless worth pointing out that sometimes the choice of database forces one towards a certain design.
[1] https://github.com/k3s-io/kine/blob/648a2daa/pkg/logstructur...
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.)
Wake ups are death for mobile form factors, even if not really doing much work.
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.
For the low, low cost of $1 per minute, you can also lease a supercar.
Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid going to kernel when the tasks arrive quickly). Why do you need redis for that? If you need persistent tasks, then you can store them in the table, and still use futex for notifications. This polling is inefficient and they should not make it a library which will cause other lazy developers add it to their app.
> 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
That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.
Like Electron, this feels like written by a web developer and not a real programmer.
I suspect that's actually "per process, per database (usually 1)", and not based on number of threads or tables. `data_version` semantics mean there's no need for more than one connection polling it, and it's being used as a relatively lightweight "DB has changed, check queues" check (that's pretty much its whole purpose).
Also I believe this is mostly intended for multi-process use, e.g. out-of-process workers, so an in-process dirty tracker (e.g. just check after insert/update/delete) isn't sufficient.
So I do think it's somewhat crazy, but it is at least very simple. fsnotify-like monitoring seems like a fairly obvious improvement tho, not sure why that isn't part of it. Maybe it's slower? I haven't tried to do anything actually-performant-or-reliable with fs notifications, dunno what dragons lie in wait.
Key difference vs SQL polling is that we’re touching metadata instead of data pages. I have work in process to make this work without any polling (innotify, kqueue, mmap’d shm file check) after the original stat(2) direction proved unreliable if lightweight.
Would love your feedback and or contributions in the repo - still figuring out the end shape.
> 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.
https://github.com/oldmoe/litestack
Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.
At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.
To make it look even more absurd . SQLite is not concurrent and you’ll have tons of problems using it practically .
I’d like to see messages per second.