RU version is available. Content is displayed in original English for accuracy.
Advertisement
Advertisement
⚡ Community Insights
Discussion Sentiment
85% Positive
Analyzed from 1082 words in the discussion.
Trending Topics
#sqlite#wal#stat#file#polling#notify#syscalls#honker#cross#process
Discussion (23 Comments)Read Original on HackerNews
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 (https://www.sqlite.org/np1queryprob.html) 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.
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.
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.
Honker waddles the same path as these giants and honks into the same void.
Hopefully it's either useful to you or is amusing. Standard alpha software warnings apply.
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.
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!
https://sqlite.org/pragma.html#pragma_data_version
Or for a C API that's even better, `SQLITE_FCNTL_DATA_VERSION`:
https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sql...
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?
As with pub/sub, you can reproduce this with some polling etc but as you say, that's not optimal.
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.
But I agree with the conclusion, system calls are still pretty fast compared to a lot of other things.
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.
I have a proliferation of small apps backed by SQLite. And most of these need a queue and scheduler.
I home rolled some stuff for it but was always pining for the elegance of the Postgres solutions.
Will give this a spin very soon
What happens on WAL checkpoint? When the file shrinks back, does that trigger a wakeup, or does the poller filter size drops?
It is possible to achieve with external IPC, but require a lot of very careful programming.
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.
Any conflicts or issues when running Litestream as well?