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

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

#11

Post by vaughan » Thu, Apr 22, 2021, 2:41 PM UTC

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…

Very clever approach and a great overview of the solution space.

> We made significant investment in investigating this approach coupled with basic incremental updating and have a few small projects in production that takes an approach similar to [this talk][1]. From https://github.com/hasura/graphql-engine/blob/master/archite...

Do you have/know of any findings/lessons learned from these projects?

[1]: https://www.postgresql.eu/events/pgconfeu2018/sessions/sessi...

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

#12

Post by cirego » Thu, Apr 22, 2021, 4:22 PM UTC

vaughan wrote: post #10

mtrovo wrote: post #5

> I guess it's relatively easy to do This is a very hard problem to do the right way and probably would need some changes on the RDBMS itself. You would need to monitor all tables that might affect your query for changes and how these changes affect your query (say you're just reading a value, aggregating with sum, doing average with count of rows, the list goes on). Add more complexity on top of that if you want to…

> the right way Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. > how these changes affect your query Yeh, this is where it gets tricky. I…

> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring.

Unfortunately, it turns out that recursively refreshing views still leads to surprising behavior. I think post summarizes the problem quite nicely: https://scattered-thoughts.net/writing/internal-consistency-.... If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset.

When looking at automatic refreshing, simple triggers and `LISTEN/NOTIFY` don't scale, as was mentioned in the comment regarding Hasura's multiplexing. I think, in the absence of incrementally maintained views, their multiplexing strategy is a good compromise for databases like postgres. However, it should be noted that continuous query / subscription of views is the exact scenario under which incremental computation will provide both lower latency and greater resource efficiency.

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

#13

Post by vaughan » Thu, Apr 22, 2021, 5:22 PM UTC

cirego wrote: post #12

vaughan wrote: post #10

Earlier quoted context omitted.

> the right way Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. > how these changes affect your query Yeh, this is where it gets tricky. I…

> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. Unfortunately, it turns out that recursively refreshing views still leads to surprising b…

> If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset.

In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined.

> incremental computation will provide both lower latency and greater resource efficiency.

Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.

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

#14

Post by eurasiantiger » Thu, Apr 22, 2021, 5:24 PM UTC

vaughan wrote: post #8

avinassh wrote: post #3

> I'm more interesting in queries with joins and doing it efficiently, instead of just tracking updates to tables that are modified, and re-rerunning the entire query. can you give an example?

Take a standard M-M join where an `Item` can belong to many `Lists`. select l.name, i.name from list as l inner join list_items as li on li.list_id = l.id inner join items as i on i.id = li.item_id where items.completed = true Imagine it's like a collaborative bookmarking app. Your options are basically: 1. Polling - re-run query periodically 2. Monitor changes to tables using triggers or CDC -- 2a. Re-run entire que…

A graph database would be a good fit for many-to-many data. In that biosphere, OrientDB has the concept of Live Queries.

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

#15

Post by cirego » Thu, Apr 22, 2021, 5:31 PM UTC

vaughan wrote: post #13

cirego wrote: post #12

Earlier quoted context omitted.

> Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring. Unfortunately, it turns out that recursively refreshing views still leads to surprising b…

> If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. > incremental computation will provide both lower latency and greater resource efficiency. Wish we had some better database primitives to assemble rather than building everything on Po…

> In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined.

I see that now -- makes sense!

> Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.

I'm curious to hear more about this! We agree that better primitives are required and that's why Materialize is written in Rust using using TimelyDataflow[1] and DifferentialDataflow[2] (both developed by Materialize co-founder Frank McSherry). The only relationship between Materialize and Postgres is that we are wire-compatible with Postgres and we don't share any code with Postgres nor do we have a dependence on it.

[1] https://github.com/TimelyDataflow/timely-dataflow [2] https://github.com/TimelyDataflow/differential-dataflow

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

#16

Post by skyde » Thu, Apr 22, 2021, 6:48 PM UTC

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.

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

#17

Post by 100011_100001 » Thu, Apr 22, 2021, 7:08 PM UTC

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 block merges that aren't as well thought out, and you can be the final arbiter of when something gets merged.

I feel like it's kind of an out of a box way of doing what you want. It doesn't give you any visual tools or anything that makes the relationships clearer to see, but it does allow you to get closer to the code that affects what you care about.

Also since I do DevOps, if you are competent or your DevOps is competent (aka they are not just glorified Ops) you can create performance tests that either trigger on merges or like a cron job. They can even trigger daily or hourly depending on code change frequency. Then you can see trends, for example how certain inserts, deletes etc change over time, if you have a semi competent metrics driven architecture you could even see how things change over time graphically.

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

#18

Post by gt565k » Thu, Apr 22, 2021, 10:41 PM UTC

vaughan wrote: post #8

avinassh wrote: post #3

> I'm more interesting in queries with joins and doing it efficiently, instead of just tracking updates to tables that are modified, and re-rerunning the entire query. can you give an example?

Take a standard M-M join where an `Item` can belong to many `Lists`. select l.name, i.name from list as l inner join list_items as li on li.list_id = l.id inner join items as i on i.id = li.item_id where items.completed = true Imagine it's like a collaborative bookmarking app. Your options are basically: 1. Polling - re-run query periodically 2. Monitor changes to tables using triggers or CDC -- 2a. Re-run entire que…

What exactly are you trying to solve/fix?

If you want to improve performance in an application, structure it in a way that lets you cache the result set in-memory at the app layer. Assuming the only way to update data is through the same app, refresh/rehydrate the related cache when records are updated/deleted/inserted. You'll only hit the database the first time, and then again when the cache is cleared in your app layer logic.

This is more or less how Active Record ORM caching in Rails works.

You gotta key off of something so it is very application specific but lets take your example a step further.

The items and list are for a specific user. Whenever an item gets added to a list or a list gets added to an item, nuke the cache key for 'user/$ID/list_items' or whatever you want to call it. Whenever you read, you hit the same cache key in-memory, so your reads are always avoiding a database roundtrip. You can do the same logic for deletes/updates, etc... and structure based on the hierarchy of your data or your tree dependency of records

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

#19

Post by vaughan » Thu, Apr 22, 2021, 10:47 PM UTC

cirego wrote: post #15

vaughan wrote: post #13

Earlier quoted context omitted.

> If you cannot refresh all of the views, at a single point in time, then there will be internal inconsistencies in your dataset. In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. > incremental computation will provide both lower latency and greater resource efficiency. Wish we had some better database primitives to assemble rather than building everything on Po…

> In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined. I see that now -- makes sense! > Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things. I'm curious to hear more about this! We agree that better primitives are required and that's why Materialize is written in Rust using using Ti…

> I'm curious to hear more about this

I think the [FoundationDB layer concept][1] said it well:

"When you choose a database today, you’re not choosing one piece of technology, you’re choosing three: storage technology, data model, and API/query language..."

I like the idea of [Apache Calcite][2] that provides an API to access the query planner. I think if you had more convenient access to some of the underlying components you could build a lot of cool stuff. There's too much magic where you punch in an SQL command or a config and hope it eventually does what you need.

I haven't look too much into the internals, but I'm keen to do so soon.

[1]: https://apple.github.io/foundationdb/layer-concept.html [2]: https://calcite.apache.org/

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

#20

Post by albertwang » Thu, Apr 22, 2021, 10:58 PM UTC

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/