Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

github.com

Post reply on HN
Print view View original

71–80 of 95 posts

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#71

Post by vrajat »

ncruces wrote: post #52
russellthehippo wrote: post #50

Earlier quoted context omitted.

It's good for pubsub but not for claim/ack workflow unless you do If-None-Match CAS semantics on a separate filesystem which, actually, yeah that's probably fine. Feels heavy on S3 ops. But! you do save on inter-AZ networking, the Warpstream hypothesis.

Claims kill this, IMO. Unless you have a single "reader", you don't mind the delay, and don't worry about redoing a bunch of notifications after a crash (and so, can delay claims significantly), concurrency will kill this.

I wrote a simple queue implementation after reading the Turbopuffer blog on queues on S3. In my implementation, I wrote complete sqlite files to S3 on every enqueue/dequeue/act. it used the previous E-Tag for Compare-And-Set.

The experiment and back-of-the-envelope calculations show that it can only support ~ 5 jobs/sec. The only major factor to increase throughput is to increase the size of group commits.

I dont think shipping CDC instead of whole sqlite files will change the calculations as the number of writes mattered in this experiment.

So yes, the number of writes (min. of 3) can support very low throughputs.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#73

Post by russellthehippo »

[Response to feedback]

Thanks all for your feedback, responses, and discussion. I've done a PR here taking your suggestions into account:

https://github.com/russellromney/honker/pulls/1

The PR implements a three-layer polling architecture: - PRAGMA data_version every 1ms - stat every 100ms - retry connection to handle blips

1. PRAGMA data_version every 1ms replaces stat-based (size, mtime) change detection. This is SQLite's own commit counter: monotonic, immune to clock skew, correctly handles WAL truncation and rolled-back transactions. ~3µs nonblocking query. Credit to ncruces for pointing to this. This is not done for performance but for correctness as it is slightly slower. tuo-lei also pointed out truncation risk, which turned out to be more real than i thought.

Interesting note: I found in testing that the C API's SQLITE_FCNTL_DATA_VERSION does not work cross-connection. So for now honker continues paying the cost of going through the VFS layer which vlovich123 pointed out and now we tradeoff explicitly.

2. Reconnect-on-error: if the data_version query fails (disk blip, NFS hiccup, corrupted connection), honker tries to reconnect and wakes subscribers as a precaution. zbentley pointed me in this direction.

3. stat identity check every 100ms: compares (dev, ino) against startup values to detect file replacement (atomic rename, litestream restore, volume remount). data_version can't catch this because it polls through the open fd, which follows the original inode even after replacement. Credit to zbentley for the file-replacement scenarios.

Again - thanks for the discussion, honker got better because of it and I learned some stuff. See you round

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#74

Post by russellthehippo »

Oxlamarr wrote: post #72

Very cool. Is the bottleneck under load mostly SQLite write throughput, or the WAL notification layer?

writes and claim/ack flow. really depends on your journal mode and synchrnous mode as well.

notifs are extremely cheap, either in the old stat(2) mode or the new PRAGMA page_version (see my update on feeback comment). Some other comments mentioned that stat(2) is about 1µs.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#75

Post by russellthehippo »

russellthehippo wrote: post #33
JoelJacobson wrote: post #27

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: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Great plug very relevant

link for all others - this commenter fixed a bug in core Postgres that fixed a scaling issue in actual listen/notify https://www.recall.ai/blog/postgres-listen-notify-does-not-s...

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#76

Post by grumbelbart2 »

russellthehippo wrote: post #2

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. SQLite doesn't run a server like Postgres, so the trick is movin…

Very cool!

Another maybe stupid question, would something like inotify(7) help to get rid of any active polling?

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#77

Post by kippinsula »

tuo-lei wrote: post #17

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. 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.

the atomicity is the whole game. we burned time on a Postgres+SQS setup where the enqueue happened in a trigger that fired before the commit was visible to other connections. added retry logic, then polling on the worker side, then eventually moved the enqueue inside the transaction. at that point you're basically reinventing what Honker does, just with more moving parts. the 'notification sent, row not committed' class of bug is usually silent and timing-dependent, which makes it brutal to track down.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#79

Post by PunchyHamster »

blacklion wrote: post #15
PunchyHamster wrote: post #12

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. It is possible to achieve with external IPC, but require a lot of very careful programming.

honestly I used those types of extensions so little I didn't even consider notifications being transactional and firing only when transaction completes

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#80

Post by xenadu02 »

ArielTM wrote: post #4

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. What happens on WAL checkpoint? When the file shrinks back, does that trigger a wakeup, or does the poller filter size drops?

This comment is completely incorrect.

kqueue VNODE events are delivered so long as your process has access to the file. There is no "same-process" notification filter.

Post reply on HN