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

91–100 of 108 posts

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

#91

Post by Diggsey »

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.

I don't think that's correct - this would only happen if the notification queue was already full.

Assuming the notification queue is not full then the original transaction does not wait for the notification to be ACKed.

You can avoid most problems with listeners by having a single dedicated listener connection per application process (so all requests handled by that process multiplex onto the same listener). Since this connection only listens for notifications and never enters a transaction, it would be very difficult to fill up the notification queue.

The queries themselves are executed on a separate connection pool, so that connections are never shared between the listener and other operations.

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

#92

Post by kissgyorgy »

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.

You would send the new id in NOTIFY when something inserts the new product.

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

#93

Post by sumtechguy »

andyxor wrote: post #68

check out Kafka Streams, they have streaming SQL as well https://www.confluent.io/online-talks/intro-to-ksql-streamin...

I came here to say that one of the few I can think of is KSQL. The downside is you pretty much have to turn your entire table into an event/message system.

However, once you add a event/message system into place you could just have a topic you hang out on and just redo the changes on the query once the event pops. Something like select col1,col2 from table where lastchangetime >= mylastquerytime. Instead of polling with the same thing. Downsides to this is the extra index needed and polling overhead to name the biggest issues.

KSQL however is one of those things that looks really cool when you look at it. But if you dig a bit deeper, it makes less and less sense to use. For example in this case if you are using KSQL instead of normal streams/topic pumps you have a bit of overhead of SQL and yet another server cluster in the mix instead of the avro/json that is already built in. Also if you have already decomposed your data into messages into topics you can just as easily just wait on the topic for new data to show up. As that is something kafka does very easily and is built in already. Then also it does not work cross DB (say broker->sqlserver). Then also you now have a 4th type of code path in your system (kafka topics, kafka streams, SQL queries, and KSQL streams).

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

#94

Post by caseymarquis »

It's less turnkey, but Postgres does support Pub/Sub. If you're willing to follow your updates with a pub message, you can then manually refresh your data elsewhere in response. I use this feature to horizontally scale websocket updates across multiple servers.

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

#95

Post by okennedy »

We haven't updated it in a while, but check out DBToaster [1]. Give it some SQL queries and a set of tables, and it'll compile for you a Scala or C++ class with insert() and delete() functions for every table listed, as well as a functions to get the (incrementally maintained) result of any query. Supports SQL92, with some decent extensibility for UDFs, etc... and has quite a bit of theory backing it.

[1] https://dbtoaster.github.io/

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

#96

Post by joel_dice »

I've implemented a RDBMS that supports this [1]. It handles joins, views (which are automatically materialized and incrementally updated), etc. It's memory only, and it doesn't support exotic stuff like recursive CTEs, but it does exactly what you're asking for. We used it in production successfully for frequently-updated real time data at the company where I used to work.

Notably, it uses persistent search trees such that each revision shares structure with the previous one, which makes diffing two closely-related revisions extremely efficient (just skip over any shared structure). Subscribers just receive a stream of diffs, with backpressure handled automatically by skipping over intermediate revisions. See [2] for a more detailed summary.

It also exposes revisions as first-class objects, which allows you to tag, diff, and even three-way merge them. Specifically, you can run arbitrary queries on both revisions and diffs. See [3] for examples.

It's no longer maintained, unfortunately. Someday I may revive it, perhaps adding support for spilling data that won't fit in memory to log-structured merge trees. I'd also rewrite it in a language like Rust, which will help flatten some of the more pointer-heavy data structures and reduce tail latencies. If anyone is interested in seeing that happen or helping out, let me know.

I'm really surprised this still isn't supported in mainstream DBMSes. The MVCC model in PostgreSQL seems particularly well suited to it.

[1]: https://github.com/ReadyTalk/revori

[2]: https://github.com/ReadyTalk/revori/wiki/Design-and-Implemen...

[3]: https://github.com/ReadyTalk/revori/wiki/CLI-Revori-Client

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

#97

Post by mejakethomas »

lgas wrote: post #35

PipelineDB might be of interest. https://github.com/pipelinedb/pipelinedb

PipelineDB was awesome - built some v. neat things with it 4-5 years ago. Wouldn't use it now since the team went to Confluent. https://ksqldb.io/ functionality is getting close.

Flink is a solid option. Materialize shows a ton of promise.

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

#100

Post by rockwotj »

kissgyorgy wrote: post #92
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.

You would send the new id in NOTIFY when something inserts the new product.

You'd have to send the full row otherwise you wouldn't know if it matches the query or not
Post reply on HN