ksqlDB (http://ksqldb.io/) does exactly this out of the box. You write SQL queries whose results you can subscribe to with a client connection. Today these subscriptions are somewhat expensive, but we're actively working on making them lightweight and scalable. I'm really looking forward to seeing the end result, I think it opens up whole new use cases.
Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
101–108 of 108 posts
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#102It's certainly not production situation, but I quickly made this [1] a while back to help with "watching" certain queries for debugging. It uses a polling approach.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#103Earlier quoted context omitted.
I work at Hasura. Hasura uses a novel way of batching similar parameterized subscriptions together and then polls under the hood. This means that if there are 1000 subscribers of similar type of query, then underneath Hasura will only make a single query to Postgres (or few queries depending on the batch size). This approach, which we call "multiplexing" in short, scales really really well. And is also the simplest w…
I wish this was possible to use without going full to Hasura.
I advocated Hasura at a prior company I worked at, and came up with a plan to add it incrementally to our existing software. I think this is quite doable.
Why do you see it as an all or nothing choice?
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#104Very interesting topic! I feel like this would be the right time to ask for an advice regarding doing something similar for user search results with PostgreSQL (v11) Eg. User "subscribes" to product searches for "Women - Nike - Size M" and the system sends her a daily notification or email if there are new result within her filter. How would one solve this kind of subscription logic? So far what I've up with is just…
Because this can be done in a background batch job during off-peak hours, there's no harm in throwing 10K queries at the database - you should be able to run a 100x that with commodity cloud hardware without noticeably impacting performance, provided you take care to not just throw queries at the db at the max rate it can handle.
The simple approach is best here IMHO.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#105Earlier quoted context omitted.
I wish this was possible to use without going full to Hasura.
I advocated Hasura at a prior company I worked at, and came up with a plan to add it incrementally to our existing software. I think this is quite doable. Why do you see it as an all or nothing choice?
Is like Wordpress vs. Flask, I prefer libraries to full frameworks.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#106Earlier quoted context omitted.
I advocated Hasura at a prior company I worked at, and came up with a plan to add it incrementally to our existing software. I think this is quite doable. Why do you see it as an all or nothing choice?
Is like Wordpress vs. Flask, I prefer libraries to full frameworks.
Not everything makes sense as a library. Your database for example.
There's no reason using Hasura requires going "full hasura".
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#107Earlier quoted context omitted.
Hmmm has anyone had problems with notify/listen and pg. IIUC transactions aren't finished until all listeners have acked the notifications, so if you have a/some misbehaving listener/s you're in for strange undebuggable problems? I know there are things with 0mq or I guess kafka plugs, but I was wondering about real world experience.
That's correct. According to the documentation the waiting notifications are queued until ACKed. If the queue is full, then new transactions can start to fail when they try to add to the queue via NOTIFY. Also, certain in-progress transactions can prevent cleanup of the queue so a long transaction could lead to it getting full. Per the documentation( https://www.postgresql.org/docs/current/sql-notify.html ): "There i…
Hey thanks for taking the time to 1) answer (trying to teach) and 2) coming back to correct your answer and cite detailed explanations.
Wish I could upvote more than once sometimes.
I was thrilled when I learned - on this here site - about listen/notify, and then went on reading the docs and the code (pg's code is surprising easy to read when you know what you're looking for - in fact it gave me enough confidence to build my own extensions...) and felt a bit underwhelmed. Hopefully someone upgrades the whole listen/notify one day for pluggability.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#108Here's how it works with JOINS in Postgres, using the NOTIFY/LISTEN mechanism -- Run the query and store result in updated_ids WITH updated_ids AS ( UPDATE public.doc SET version = version + 1 -- Joins are not directly part of Postgres' update syntax, but this achieves the same FROM public.org WHERE public.org.id = public.doc.org_id AND public.org.type = 'customer' -- Returns all ids of affected rows RETURNING public…
Hmmm has anyone had problems with notify/listen and pg. IIUC transactions aren't finished until all listeners have acked the notifications, so if you have a/some misbehaving listener/s you're in for strange undebuggable problems? I know there are things with 0mq or I guess kafka plugs, but I was wondering about real world experience.
Another "gotcha" with PG notify is that in PG the payload cannot be more than 8kb long. If the UPDATE query changes thousands of rows, it's quite easy to end up with a generated text longer than 8000bytes. I'm not sure if this error raised by NOTIFY would block the UPDATE from executing, but would be worth testing.