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

21–30 of 108 posts

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

#21

Post by vaughan »

skyde wrote: post #16
sathorn wrote: post #4

There is also the research DB Noria[0] that's based on this idea. It maintains materialized views for queries and efficiently updates them when the data changes. [0] https://github.com/mit-pdos/noria

the cool thing with noria is that it behave like a cache. So it only store the row from the materialized view that are frequently needed instead of storing the complete materialized view. But if you query for a row which is missing it will rematerialize this row on demand with "up-query" without having to run the expensive query that a materialized view refresh normally need.

This approach would be cool if it ran on a database on the frontend too. You could run a complex query against an in-browser DB with a few joins and some params, and then tweak the params, and it would only need to pull down the missing rows. Then your app is offline and super snappy.

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

#22

Post by teraflop »

100011_100001 wrote: post #17

It's not clear to me if this would solve your problem, because it kind of depends on how you are planning on reacting, but Github and Gitlab have the concept of a Code Owner and it can work for specific files. So if you know that your queries that you want to monitor exist in tidy DB Impl classes or maybe a specific repository, you could be the code owner of those files. This approach is pro-active because you can bl…

It sounds like you're talking about a totally different question. OP is interested in subscribing to changes in the results of a query at runtime, not changes to the text of the query itself.

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

#23

Post by brianr »

I like this idea a lot. In a sense it's thinking of your application as a spreadsheet, where the database is a data tab and the frontend is the summary tabs. If there's a change to the data tab, the "spreadsheet engine" (or "materialized view engine" in your case) walks the dependency graph and updates all the relevant parts of the summary tabs.

In the Rollbar UI, we implemented a lot of this type of logic by hand to make our real-time aggregated Items list work (i.e. each time an Item has a new occurrence, we update its stats in the database and push the diff to connected clients). It would save an immense amount of code to have had a solution that did this out of the box.

The closest thing I'm aware of to this is BigQuery's materialized views ( https://cloud.google.com/bigquery/docs/materialized-views-in... ), which take care of making otherwise expensive queries cheap fast, but they are rather limited (i.e. no subqueries or joins), and don't have the "streaming output of changes" you describe.

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

#24

Post by nerdbaggy »

tirumaraiselvan wrote: post #9
jrowley wrote: post #7

Earlier quoted context omitted.

I believe hasura uses polling under the hood to accomplish subscriptions, right?

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…

It’s crazy how well it works. I was skeptical at first but one of projects has 15k complexish subscriptions and it scales amazing. A few Hasura hosts and a single Postgres instance does amazing. 8 cores Postgres instances running at about 20% utilization.

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

#25

Post by atwebb »

cirego wrote: post #6

Disclaimer: I work at Materialize. This is the exact problem that we are solving here at Materialize! I wrote an example blogpost that details to how to subscribe to a SQL query in practice: https://materialize.com/a-simple-and-efficient-real-time-app... Regarding your comment about "focus on streams", it's true that we first focused on extracting event data from other sources (Kafka, Debezium, direct change data cap…

Following your project and Airbyte very closely, both seem to have caught and, if coupled, would provide some pretty compelling use cases, especially for retaining control of systems.

Thought for a while most of the analytics engines are really just getting around materialized view limitations.

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

#26

Post by jfim »

albertwang wrote: post #20
cirego wrote: post #6

Disclaimer: I work at Materialize. This is the exact problem that we are solving here at Materialize! I wrote an example blogpost that details to how to subscribe to a SQL query in practice: https://materialize.com/a-simple-and-efficient-real-time-app... Regarding your comment about "focus on streams", it's true that we first focused on extracting event data from other sources (Kafka, Debezium, direct change data cap…

As cirego mentioned, we (Materialize) have the TAIL operator, which was built to allow users to subscribe to changes: https://materialize.com/docs/sql/tail/

Out of curiosity, how does TAIL behave with updates?

For example, say that I have a table foo that has a single row, if that row's contents is updated, would I get two updates (a deletion and an insert)?

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

#27

Post by slifin »

I think most of the time I would use SQL subscriptions to notify web clients that they are stale but don't auto update the client's state unless the form is read only otherwise current client state could be destroyed

React server components can do intelligent merges, in client action or on tick, I guess it would just mean hooking into that system to prevent automatically merging in all cases in favour of manual merges client side sometimes

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

#28

Post by edoceo »

if you're looking at pg materialized views, look at listen/notify too.

so, the builder Listens and write queries then Notify, so re-do the m-view?

or just use Notify with good payload after each write you think is important? (I use this sometimes)

I mean, a trigger to push could work, yea?

or, some clever rig with pg-logical replication (I'd have to think on this one more, may be crap)

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

#29

Post by aravindet »

cirego wrote: post #6

Disclaimer: I work at Materialize. This is the exact problem that we are solving here at Materialize! I wrote an example blogpost that details to how to subscribe to a SQL query in practice: https://materialize.com/a-simple-and-efficient-real-time-app... Regarding your comment about "focus on streams", it's true that we first focused on extracting event data from other sources (Kafka, Debezium, direct change data cap…

I am considering Materialize for a project, and I'm concerned about the limitation that materialized views should fit into the memory of a single host.

My use case is a materialized OLAP "fact" tables, created by joining a 5-10 separate tables. While the biggest source tables have under a million rows, the joined fact table would have perhaps 100 million rows which I fear might be too big.

One workaround I considered is creating a non-materialized view, TAIL-ing it, and persisting the results to another database.

Could you comment on this problem and workaround?

Post reply on HN