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

Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

news.ycombinator.com

Post reply on HN
Print view View original

81–90 of 108 posts

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#81

Post by kiwicopple »

cpursley wrote: post #78
thruflo22 wrote: post #69

Supabase’s realtime library consumes the logical WAL and provides a Phoenix channel + JavaScript API to subscribe to matching events from it: https://github.com/supabase/realtime Under the hood, the core implementation was copied (with credit / attribution) from: https://github.com/cainophile/cainophile I happened to do a similar thing but I adapted cainophile into an Elixir “OffBroadway” producer: https://github.com…

Nice, I've actually extracted out supabases WAL subscribe bits to use alongside with Hasura so that I can react to insert, update and delete events right in Elixir. Looks like you're doing something similar. I'd love to see this extracted out into a library by the supabase team.

> I'd love to see this extracted out into a library by the supabase team.

Supabase cofounder here - I'll raise this with the team. Feel free to email me if you have any specifics around how you're using it so we can make sure it fits your use-case (email in my profile)

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#82

Post by touisteur »

wolframhempel wrote: post #74

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

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#83

Post by touisteur »

rockwotj wrote: post #66
kissgyorgy wrote: post #64

It's not as simple as "subscribe to this SQL query", but you can do this relatively easy with PostgreSQL LISTEN / NOTIFY: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237... You just notify the clients with the primary keys of what changed, so you only need to run the query if something changed. If you implement it correctly, you only have to run the query once and push the same result to every client. I…

Fwiw this approach doesn't work for something like: SELECT * FROM Products WHERE price > 5; If a new product comes in with price 20 you won't know to add it to your result set and send it to the clients.

Even from a view? Materialized or not?

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#84

Post by nicoburns »

hpeinar wrote: post #76

Very 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…

You could probably find some way to combine every subscription into a single query (by storing the query parameters themselves in the database).

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#85

Post by sbuttgereit »

touisteur wrote: post #82
wolframhempel wrote: post #74

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

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 is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. However, no cleanup can take place if a session executes LISTEN and then enters a transaction for a very long time. Once the queue is half full you will see warnings in the log file pointing you to the session that is preventing cleanup. In this case you should make sure that this session ends its current transaction so that cleanup can proceed."

[edit: I previously said that you weren't quite correct... but I originally misread what you said, I've updated my comment to say you are correct.]

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#87

Post by vaughan »

hpeinar wrote: post #76

Very 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…

It's probably the simplest solution if you are running batch job once per day. Just ensure you are running one query per filter set, and you have your indexes setup correctly.

Maybe you could speed up your queries by using a materialized view for `FROM products WHERE id > 1234`. You could then maybe derive more materialized views for other attributes depending on the clustering of your watched queries...e.g. gender might split the dataset in half, but this is not guaranteed to improve perf and indexes might actually be faster, place there are space concerns and additional indexes.

If you want to run less queries you could combine queries like [Hasura does][1].

To avoid running queries with no new results you would need to watch DB writes, and map DML statements to query subs...but pointless in a batch setting.

[1]: https://github.com/hasura/graphql-engine/blob/master/archite...

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#88

Post by devit »

In PostgreSQL you can either add yourself as a replica with logical decoding (e.g. PostGraphile supports GraphQL with live queries that way) or do it manually with LISTEN/NOTIFY.

On top of that you can use transaction ids or manual transaction serials to get only new rows (but of course note that transactions can be long-running, so you need to either accept potentially unbounded repeated data, or serialize all write transactions on a single serial assignment single-row table, or record the whole set of committed transactions whose data you have processed rather than a single serial number).

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#89

Post by teh_klev »

BrentOzar wrote: post #71

Microsoft SQL Server has a rarely-used feature to do exactly this: [Query Notifications][1]. It sounded great in theory when it came out in 2005, but the drawback was that every open subscription was an open connection to the database server, from every application server. The more web servers you have, and the more queries you want to subscribe to, the more open connections you end up with, which at large scale can…

SqlDependency...now that's a bit of a blast from the past for me. I remember helping a customer get up and running with that, sadly it's so long ago I don't remember the details, but it was a fun diversion from the daily grind :)

Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?

#90

Post by rockwotj »

touisteur wrote: post #83
rockwotj wrote: post #66

Earlier quoted context omitted.

Fwiw this approach doesn't work for something like: SELECT * FROM Products WHERE price > 5; If a new product comes in with price 20 you won't know to add it to your result set and send it to the clients.

Even from a view? Materialized or not?

Only if you keep the whole table in memory or re-read every row that is written
Post reply on HN