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

41–50 of 95 posts

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

#41

Post by russellthehippo »

infogulch wrote: post #10
andersmurphy wrote: post #8

Earlier quoted context omitted.

Is the main use case for this for languages that only have access to process based concurrency? 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.…

He mentions Litestream, maybe this also works for litestream read-only replicas which may be in completely different locations?

Whoa I really hadn’t considered this. Do a litestream read replica, trigger across machines with S3 as the broker essentially. But you’re still stuck with the litestream sync interval. Maybe interesting for cross server notify?

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

#42

Post by russellthehippo »

ncruces wrote: post #23
psadri wrote: post #22

Earlier quoted context omitted.

For one it seems to be deprecated.

It's not.

Yep, definitely still in use. Do yall above have an opinion if the pragma is better than the syscall? What are the trade offs there? Another comment thread mentioned this as well and pointed to io uring. I was thinking that dism spam is worse than syscall spam.

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

#43

Post by russellthehippo »

rich_sasha wrote: post #25
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…

Pretty cool! I have a half baked version of something similar :) 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.

Absolutely! That’s the durable pubsub angle for sure.

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

#44

Post by Retr0id »

russellthehippo wrote: post #34
Retr0id wrote: post #5

Couldn't you use inotify (and/or some cross-platform wrapper) to watch for WAL changes without polling?

Breaks cross-platform, specifically Macs swallow silently. stat just works

I don't believe this to be true.

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

#45

Post by russellthehippo »

Retr0id wrote: post #44
russellthehippo wrote: post #34

Earlier quoted context omitted.

Breaks cross-platform, specifically Macs swallow silently. stat just works

I don't believe this to be true.

See comment below - Darwin silently drops same-process notifs. I could change the behavior depending on same vs cross process and platform but I wanted to”just one thing to worry about”. Potentially a good optimization later. Would help reduce syscalls.

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

#46

Post by russellthehippo »

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?

Actually need to test this. Will report back

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

#47

Post by infogulch »

russellthehippo wrote: post #41
infogulch wrote: post #10

Earlier quoted context omitted.

He mentions Litestream, maybe this also works for litestream read-only replicas which may be in completely different locations?

Whoa I really hadn’t considered this. Do a litestream read replica, trigger across machines with S3 as the broker essentially. But you’re still stuck with the litestream sync interval. Maybe interesting for cross server notify?

I guess the idea is to have all writes go through a central server with local read replicas for improved read perf. The default litestream sync interval is 1s. I bet many use-cases would be satisfied with a few seconds delay for cross-region notifications.

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

#48

Post by Retr0id »

russellthehippo wrote: post #45
Retr0id wrote: post #44

Earlier quoted context omitted.

I don't believe this to be true.

See comment below - Darwin silently drops same-process notifs. I could change the behavior depending on same vs cross process and platform but I wanted to”just one thing to worry about”. Potentially a good optimization later. Would help reduce syscalls.

I believe you are mistaken. If you are referring to the comment from ArielTM, that's an LLM bot regurgitating your readme.

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

#49

Post by russellthehippo »

hk1337 wrote: post #6
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…

I love the name!

honk

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

#50

Post by russellthehippo »

infogulch wrote: post #47
russellthehippo wrote: post #41

Earlier quoted context omitted.

Whoa I really hadn’t considered this. Do a litestream read replica, trigger across machines with S3 as the broker essentially. But you’re still stuck with the litestream sync interval. Maybe interesting for cross server notify?

I guess the idea is to have all writes go through a central server with local read replicas for improved read perf. The default litestream sync interval is 1s. I bet many use-cases would be satisfied with a few seconds delay for cross-region notifications.

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.
Post reply on HN