HN Explorer

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

200 points by vaughan ↗ HN
I know [RethinkDB][1] used to do this with their SQL-like ReQL language, but I looked around a bit and can't find much else about it - and I would have thought it would be more common.

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.

If we think about modern frontends using SQL-based backends, essentially every time we render, its ultimately the result of a tree of SQL queries (queries depend on results of other queries) running in the backend. Our frontend app state is just a tree of materialized views of our database which depend on each other. We've got a bunch of state management libraries that deal with trees but they don't fit so well with relational/graph-like data.

I came across a Postgres proposal for [Incremental View Maintenance][2] which generates a diff against an existing query with the purpose of updating a materialized view. Oracle also has [`FAST REFRESH`][3] for materialized views.

I guess it's relatively easy to do until you start needing joins or traversing graphs/hierarchies - which is why its maybe avoided.

EDIT: [Materialize][1] looks interesting in this space: "Execute streaming SQL Joins" but more focused on the event streams rather than general-purpose DML/OLTP.

[1]: https://github.com/rethinkdb/rethinkdb_rebirth

[2]: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance

[3]: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8361

[4]: https://materialize.com/

107 comments

[ 7.2 ms ] story [ 258 ms ] thread
stuiterbal
Maybe https://hasura.io is something to have a look at? It's a GraphQL server that gives you realtime GraphQL API's over Postgres/SQL.

I've been trying out https://nhost.io for some time now. They use Hasura and I'm impressed by how easy subscriptions are using Apollo to query the GraphQL API.

jrowley
I believe hasura uses polling under the hood to accomplish subscriptions, right?
tirumaraiselvan
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 way to get live updates for _any_ query, no matter how many joins, etc.

We talk more about this approach (and comparisons with other approaches), and benchmarks in this post: https://hasura.io/blog/1-million-active-graphql-subscription...

nerdbaggy
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.
mamcx
I wish this was possible to use without going full to Hasura.
eloff
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?

mamcx
Is like Wordpress vs. Flask, I prefer libraries to full frameworks.
eloff
Not everything makes sense as a library. Your database for example.

There's no reason using Hasura requires going "full hasura".

arcticfox
This feels like something I'd naturally use on a frontend, but would it be a dumb idea to use this for backend anything? Like triggering actions off data updates.
avinassh
> 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?

vaughan
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 query when any table is modified with DML and diff against previous result

-- 2b. Only re-run query if the changes would effect the result (e.g. if we add an `item` with `item.completed = false` then we know that our above query will never need updating.)

-- 2c. Do an efficient diff using some relational algebra magic. See Postgres IVM link I posted.

I posted the same example [here][1] with some more details.

[1]: https://math.stackexchange.com/questions/4112326/how-can-i-u...

eurasiantiger
A graph database would be a good fit for many-to-many data. In that biosphere, OrientDB has the concept of Live Queries.
gt565k
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

sathorn
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

skyde
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.

vaughan
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.
victor106
Noria looks cool but it requires Zookeeper which is a PITA.
mtrovo
> 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 support querying from other views that also aggregate the data on your query.

As pointed on another comment there's DB Noria [0] but I'm not sure how production ready it's right now. You an idea of the complexity of the task on a interview with one of the project leads [1].

[0] https://github.com/mit-pdos/noria [1] https://corecursive.com/030-rethinking-databases-with-jon-gj...

vaughan
> 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 think it can be simpler though if the SQL is kept simple with less exotic sub-queries, CTEs, JOINs.

Thanks for the links.

cirego
> 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.

vaughan
> 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.

cirego
> 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

vaughan
> 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/

mr_toad
> 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 might find it’s cheaper to do just that. It might cost you in computational resources, but save you millions in engineering time.

cirego
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 capture from external databases such as Postgres, S3, etc). Over time, however, we plan to add additional features that will allow users to also treat Materialize as a general purpose database.

Hope this helps and happy to answer any questions!

edit: I was imprecise in my usage of the term event streams. Materialize supports inserts, updates and deletes at its core (the topK query shown in the blog post above shows this). Materialize is a more general solution than something focused on append-only event streams.

albertwang
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/

jfim
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)?

benesch
Yes, exactly. Roughly speaking you'd see something like this:

    (id, text1, text2, timestamp, diff)
    ----
    (42, before, data, Friday April 23 12:27AM, -1)
    (42, after, data, Friday April 23 12:27AM, +1)
cirego
You are correct! Updates are a expressed as a retraction and an insert that happen within the same timestamp.

An example may not be necessary but it might also help clarify. Assuming you're using the psql client to run "TAIL WITH (PROGRESS)", the logical grouping for a single update will be a set of rows like the following:

  ...
  1608081358001 f -1 ['Lockal', '4590']
  1608081358001 f 1 ['Epidosis', '4595']
  1608081358001 f -1 ['Matlin', '5220']
  1608081358001 f 1 ['Matlin', '5221']
  1608081359001 t \N ['\\N', '\\N']
  ...
All of these occur at the same timestamp, meaning that they should be applied atomically to maintain consistency of your dataset. In this case, my query is a top-10 query and Epidosis has now entered the top10 while Lockal has dropped out of the top10. Matlin remains in the top10 but their total has gone from 5220 to 5221. The final example record is produced when you run with PROGRESS enabled and serves as an indicator that 1608081359001 is now closed and no further updates will ever happen at timestamp 1608081359001.

I find that this stream of rows is very easy to convert to a data structure "{timestamp, inserts[], deletes[]}" and this, in turn, maps naturally onto reactive APIs, such as React or D3. My blog post, linked above, delves into this in more detail. Hope this explanation helps!

jfim
That's a great example, thanks a lot!
atwebb
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.

albertwang
Exactly! Since most ELT tools (including Airbyte) support json + csv output formats, those work perfectly well with Materialize out-of-the-box. I'm playing around with Slack+Stripe Airbyte sources to try and come up with some fun dashboards to show off in Materialize as we speak.
aravindet
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?

cirego
At the moment, we are focused on scaling up with a single instance. While there are a couple of strategies for scaling out, none of our current methods support scaling a single view beyond a single instance. This isn't really a technical limitation but rather more of a testing and supportability limitation. Timely and Differential (the incremental computation frameworks used by Materialize) suport scale-out and have been extensively tested in scale-out scenarios.

Do you have a limit in mind on how large you're willing to go? A brief bit of napkin math shows that, at 1KB per record, your largest view should fit within 100GB of memory. This is easily supported by Materialize and I can certainly understand if this exceeds your appetite!

If you're interested in reducing the size of the in-memory dataset, does your fact table have a temporal dimension to it? By default, Materialize stores data for all time (like a database) and you can write views in such a way that it will only materialize recent data (like a stream processor). Our co-founder Frank wrote a blog post[1] detailing how to do this.

I'm honestly not sure how TAIL-ing a non-materialized view would perform. Sounds like something that would be fun to test!

Happy to chat about this further in our community Slack channel if you have more questions.

[1] https://materialize.com/temporal-filters/

gst
Materialize definitely looks interesting. I'd have a few questions regarding the current product:

Does Materialize provide a commercial non-cloud version where workers can run on multiple nodes in a cluster? On the website I only see references to the BSL-licensed single-node version and to the cloud version that is hosted at Materialize. Would a company be able to run Materialize on their own Kubernetes cluster?

Also is it possible to add custom code for data sources (for example Kafka, but with a different format than what Materialize currently expects) or is Materialize limited to the pre-defined sources?

albertwang
Sorry if it's a bit of an enterprisy-response, but please reach out! We are supporting some non-cloud commercial customers on a case-by-case basis. The reason for this is that we find the support and maintenance burden to be much higher with a non-cloud delivery model, which isn't always a great experience for either party. We also have a managed product (where the data plane resides in your environment) that may work, depending on your infra and security requirements.

Re: custom code -- our codebase is fully source-available and open to contributions, but the source+sink code going through some refactoring to make it more beginner-friendly. Depending on your consistency requirements, we also support Debezium and our own CDC format (https://materialize.com/docs/connect/materialize-cdc/) for folks who want to bring in their own data sources. (For quick prototypes, we also support csv/json/plaintext source types, as well as SQL INSERTs!)

100011_100001
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.

teraflop
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.
[dead] redis_mlc
Sadly no. He's actually talking about using git instead of a database for storing data, which has come up a few times in the past month or so by clueless people.
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.

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

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)

kopos
We used flexviews for MySQL. It would use the MySQL bin log to read through the actual DML queries (UPDATE, DELETE, INSERT), parse the query and then make changes to the materialised views based on the dependency tree defined by the SQL queries.
jgraettinger1
Can you speak more to how updates get pushed out to your UI clients? Are you using Firestore ? Hasura? Polling?

Something we're looking at enabling w/ Estuary Flow [1] is materializing to Firestore for this use case (and Flow already supports PostgreSQL materializations).

Flow would capture changes from your database tables (or you write normalized events directly to Flow collections). You aggregate and join as needed, and then materialize your views into Firestore (or another Postgres table) where you'd subscribe for live updates.

[1]: https://github.com/estuary/flow

lgas
PipelineDB might be of interest. https://github.com/pipelinedb/pipelinedb
takeda
I also thought of it when I read the question, but looks like after they've been acquired by Confluent the project is essentially dead.
mejakethomas
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.

rockwotj
I'm not sure I would call this relatively easy even without joins. Without joins Google Cloud Firestore does exactly what you're describing. The initial query runs against the DB then the client gets a filtered stream of updates to only that query. Its distributed and scales logarithmically with the number of queries, as it doesn't need to keep the query results in memory/materialized.

The fun part of this problem is that it's really inverted from most traditional database literature out there. Mostly the problem is you have a query and need to find the matching rows. To make updates efficient you need to do this the other way around - if there is a row (that has changed) find all the matching queries.

With joins (or any data dependant query where you can't tell a row is in the result set without looking at other data) you need to keep the query results materialized otherwise you can't have enough information without going back to disk or keeping everything in memory, which isn't really feasible in most cases.

Source: I worked on Google Cloud Firestore from it's launch until 2020 and was the on responsible for the current implementation and data structures of how changes get broadcasted to subscribed queries.

davty
This sounds fascinating. Do you have any recommended source to read more about this? Like a book or a paper?
rockwotj
Sadly I never thought to trying to publish a white paper with my co workers while I was working on this. I'd love the chance to do so. We did do many internal company presentations on the matter however.

That being said as far as I know there isn't any published works on a "Reverse Query Engine" or RQM for short which is the name we settled on internally for this subsystem within Firestore.

wikibob
I would love to read this paper when you write it.
toolslive
Mind:

  - the query language is quite limited (fe, searching on the absence of an attribute is not possible)
  - shame you can't do projections on the changes server side.
    you get the whole thing and not just the fields you're interested in
rockwotj
The query language is limited, but a lot of the limitations are intentional.

Here is a post from a while back where I explained some of the reason for some of these limitations; https://groups.google.com/g/google-cloud-firestore-discuss/c...

Granted not all of them are due to this philosophy and some are just hard problems due to other constraints like the security rules verification system.

RE projections: this is actually due to some limitations in processing security rules and not a limitation in the query matching.

Edit: RE matching on missing attributes. This is due to Firestore having sparse indexes and being schemaless, if you write explicit null values you can query for those fields being null.

toolslive
If your building a reactive website then firestore is actually great. Edit: but yes IMNSHO, there are also some design mistakes (schemaless, silly limitations on keys, ...) both in the service and the client libs. I only used the python and js clients though. I might be biased
rockwotj
Yupp, certainly don't disagree. It's not a perfect system - to comment on those two issues: there were some pipe dreams when I was there to support schemas, including partial schemas, but we needed a better migration story (and someone to convince the powers that be it was the right thing to work on). The limitations on keys are mostly due to the system being compatible with Cloud Datastore.
[dead] gjs278
ive used golang to read the live mysql bin log for cache invalidation before
Fire-Dragon-DoL
This problem cannot really be solved without application participation, because to keep the data up to date across joins at some point you need cross table locking, which would become highly inefficient. This is the whole cqrs thing by the way
lsiebert
Here's my thoughts. Not so much for you as for myself because it's an interesting question.

I once did a very hacky version of something like with mysql, where I was batch updating ranking data for a leaderboard, using internal variables based on a Stack Overflow answer, when I was a junior. Nobody should do that lol.

In fact, I'd avoid mysql for this if at all possible.

It seems like this fairly easy to do in postgres, and a pain in the neck in mysql.

In postgres I'd use a materialized view with a unique index and refresh it CONCURRENTLY where it will compare the view and the underlying data and only change things as needed. It just doesn't do this with a stream, you have to request updates. You can presumably refresh it internally using TRIGGERS.

I mean you can do your own stream with postgres as well (using NOTIFY and LISTEN to subscribe to notifications created with AFTER CUD TRIGGERs) or even in MYSQL in a hacky way and refresh it on the back end or in the browser, but it's likely more efficent to let the db handle batch updating itself rather than handrolling your own updates. Probably easier to update to incremental views if and when those are added.

I guess the question is, what to do when either of the above isn't fast enough? My thought would be to switch between two identical materialized views with one always updating (not using concurrently). Alternatively use an external cache to do something of the same thing. Then multiple systems can query data, with refreshes being triggered by one of the selects, which switches the view to read from after updating, or which updates the cache atomically.

neximo64
Try Supabase out. Its basically a Postgres plugin and you can subscribe to tables live changes using Websockets.

If you have a query you could reorientate the table's changes and see if the changes affect the output incrementally manually.

theospears
Disclaimer: I work at Asana

We have an in house system (LunaDb) which is a little like this. There's a tech talk available about how it works at https://blog.asana.com/2015/10/asana-tech-talk-reactive-quer... - it's from a few years ago, but the core ideas are there. There's also some details on the caching layer we built for it at https://blog.asana.com/2020/09/worldstore-distributed-cachin...

A few properties based on your questions and the observations here:

- We don't attempt to incrementally update query results. Given the number of simultaneous queries the system handles, we've found it much more important to instead by very precise about only re-running exactly the right queries in response to data modification.

- We support joins (although not queries of arbitrary complexity). We avoid a race conditions and cross-table locking issues by using the binlog as our source of changes, which imposes a linearization on the changes matching the state in the db. Correctly determining which queries to update for these requires going back to the database.

- Performance is an interesting problem. It's easy to arrange situations where total load is a function of "rate of data change" * "number of queries over that data", so being overly broad in triggering recalculations gets expensive fast.

We're actively hiring to work on this - if you are interested my contact details are in my hn profile.

[dead] 02020202
you could tail the binary log with custom app that checks for the tables from the query and if present, it re-runs the query and returns the desired results(pagination or "after id"...)