Hey HN, I built this. Honker adds cross-process NOTIFY/LISTEN to SQLite. You get push-style event delivery with single-digit millisecond latency without a damon/broker, using your existing SQLite file. A lot of pretty high-traffic applications are just Framework+SQLite+Litestream on a VPS now, so I wanted to bring a sixer to the "just use SQLite" party.<p>SQLite doesn't run a server like Postgres, so the trick is moving the polling source from interval queries on a SQLite connection to a lightweight stat(2) on the WAL file. Many small queries are efficient in SQLite (<a href="https://www.sqlite.org/np1queryprob.html" rel="nofollow">https://www.sqlite.org/np1queryprob.html</a>) so this isn't really a huge upgrade, but the cross-language result is pretty interesting to me - this is language agnostic as all you do is listen to the WAL file and call SQLite functions.<p>On top of the store/notify primitives, honker ships ephemeral pub/sub (like pg_notify), durable work queues with retries and dead-letter (like pg-boss/Oban), and event streams with per-consumer offsets. All three are rows in your app's existing .db file and can commit atomically with your business write. This is cool because a rollback drops both.<p>This used to be called litenotify/joblite but I bought honker.dev as a joke for my gf and I realized that every mq/task/worker have silly names: Oban, pg-boss, Huey, RabbitMQ, Celery, Sidekiq, etc. Thus a silly goose got its name.<p>Honker waddles the same path as these giants and honks into the same void.<p>Hopefully it's either useful to you or is amusing. Standard alpha software warnings apply.
Is the main use case for this for languages that only have access to process based concurrency?<p>Struggling to see why you would otherwise need this in java/go/clojure/C# your sqlite has a single writer, so you can notify all threads that care about inserts/updates/changes as your application manages the single writer (with a language level concurrent queue) so you know when it's writing and what it has just written. So it always felt simpler/cleaner to get notification semantics that way.<p>Still fun to see people abuse WAL in creative ways. Cool to see a notify mechanism that works for languages that only have process based concurrency python/JS/TS/ruby. Nice work!
I actually hadn’t thought about it this way. The killer app I was imagining was 1ms reactivity without SQL polling and messaging atomic with business commits, plus “one db” and no daemon.<p>But this is actually a great main benefit as well.
He mentions Litestream, maybe this also works for litestream read-only replicas which may be in completely different locations?
Nice, I had no idea that stat() every 1 ms is so affordable. Aparently it takes less than 1 μs per call on my hardware, so that's less than 0.1% cpu time for polling.
"Syscalls are slow" is only mostly true. They are slower than not having to cross the userspace <-> OS barrier at all, but they're not "slow" like cross-ocean network calls can be. For example, non-VDSO syscalls in linux are about 250 nanoseconds (see for example <a href="https://arkanis.de/weblog/2017-01-05-measurements-of-system-call-performance-and-overhead/" rel="nofollow">https://arkanis.de/weblog/2017-01-05-measurements-of-system-...</a>), VDSO syscalls are roughly 10x faster. Slower than userspace function calls for sure, but more than affordable outside the hottest of loops.
Filesystem stuff tends to be slower than average syscalls because of all the locks and complicated traversals needed. If this is using stat instead of fstat then it’s also going through the VFS layer - repeated calls likely go through the cache fast path for path resolution but accessing the stat structure. There’s also hidden costs in that number like atomic accesses that need to acquire cache line locks that are going to cause hidden contention for other processes on the CPU + the cache dirtying from running kernel code and then subsequently having to repopulate it when leaving all of which adds contended L3/RAM pressure.<p>In other words, there’s a lot of unmeasured performance degradation that’s a side effect of doing many syscalls above and beyond the CPU time to enter/leave the kernel which itself has shrunk to be negligible. But there’s a reason high performance code is switching to io_uring to avoid that.
That’s ignoring the other costs of syscalls like evicting your stuff from the CPU caches.<p>But I agree with the conclusion, system calls are still pretty fast compared to a lot of other things.
Probably missing something, why is `stat(2)` better than: `PRAGMA data_version`?<p><a href="https://sqlite.org/pragma.html#pragma_data_version" rel="nofollow">https://sqlite.org/pragma.html#pragma_data_version</a><p>Or for a C API that's even better, `SQLITE_FCNTL_DATA_VERSION`:<p><a href="https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntldataversion" rel="nofollow">https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sql...</a>
Yeah the C API seems like a perfect fit for this use-case:<p>> [SQLITE_FCNTL_DATA_VERSION] is the only mechanism to detect changes that happen either internally or externally and that are associated with a particular attached database.<p>Another user itt says the stat(2) approach takes less than 1 μs per call on their hardware.<p>I wonder how these approaches compare across compatibility & performance metrics.
For one it seems to be deprecated.
Pretty cool! I have a half baked version of something similar :)<p>Can you use it also as a lightweight Kafka - persistent message stream? With semantics like, replay all messages (historical+real time) from some timestamp for some topics?<p>As with pub/sub, you can reproduce this with some polling etc but as you say, that's not optimal.
Neat idea!<p>Would it help if subscriber states were also stored? (read position, queue name, filters, etc) Then instead of waking all subscription threads to do their own N=1 SELECT when stat(2) changes, the polling thread could do Events INNER JOIN Subscribers and only wake the subscribers that match.
This is really interesting. I'm building something on Postgresql with LISTEN/NOTIFY and Postgraphile. I'd love to (in theory) be able to have a swappable backend and not be so tightly coupled to the database server.
I love the name!
Shameless plug: In the upcoming release of PostgreSQL 19, LISTEN/NOTIFY has been optimized to scale much better with selective signaling, i.e. when lots of backends are listening on different channels, patch: <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=282b1cde9" rel="nofollow">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...</a>
Couldn't you use inotify (and/or some cross-platform wrapper) to watch for WAL changes without polling?
Thanks for this!<p>I have a proliferation of small apps backed by SQLite. And most of these need a queue and scheduler.<p>I home rolled some stuff for it but was always pining for the elegance of the Postgres solutions.<p>Will give this a spin very soon
kqueue/FSEvents is tempting here, but Darwin drops same-process notifications. If you've got a publisher and listener in the same process the listener just never fires. Nasty thing to chase. stat polling looks gross but it's the only thing that actually works everywhere.<p>What happens on WAL checkpoint? When the file shrinks back, does that trigger a wakeup, or does the poller filter size drops?
This is the kind of skill-level tool I wish existed earlier — I hit
the exact pain point running a daily-chronicle site off SQLite + a
static deploy a week ago. Ended up with a crude polling loop because
the alternatives all wanted me to install Postgres for a single
notification semantic.<p>Question: any thoughts on what breaks first when a single process
has 10k+ concurrent listeners? I'm curious whether the SQLite side
can sustain what Postgres does cheaply.
If I'm using SQLAlchemy, can this integrate? It seems to want to make the db connection itself.
atomic commit with the business data is the selling point over separate IPC. external message passing always has the 'notification sent but transaction rolled back' problem and that gets messy.<p>one thing i'm curious about: WAL checkpoint. when SQLite truncates WAL back to zero, does the stat() polling handle that correctly? feels like there's a window where events could get lost.
Wouldn't processes on same machine be able to use different IPCs that don't even touch file ? It's neat but I have feeling in vast majority of cases just passing address to one of the IPC methods would be faster and then SQLite itself would only be needed for the durable parts.
This extension piggyback SQLite native transactions. For example, queueing data will be rolled back if transaction is rolled back due to some constrains violations.<p>It is possible to achieve with external IPC, but require a lot of very careful programming.
Awesome. I’m currently using AWS SQS which invokes lambda functions for asynchronous tasks like email sends, but Honker seems like a great local replacement.<p>Any conflicts or issues when running Litestream as well?
[dead]
[dead]
[dead]
[dead]
[flagged]