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

1–10 of 108 posts

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

#1

Post by vaughan »

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/

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

#2

Post by 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.

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

#5

Post by 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...

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

#6

Post by 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.

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

#7

Post by jrowley »

stuiterbal wrote: post #2

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.

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

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

#8

Post by vaughan »

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

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

#9

Post by tirumaraiselvan »

jrowley wrote: post #7
stuiterbal wrote: post #2

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.

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

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

#10

Post by vaughan »

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

Thanks for the links.

Post reply on HN