New top story on Hacker News: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
37 by vaughan | 23 comments on Hacker News.
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://bit.ly/3tLtmor [2]: https://bit.ly/3nkPF24 [3]: https://bit.ly/2RY5XCn [4]: https://bit.ly/3aw7GVG
37 by vaughan | 23 comments on Hacker News.
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://bit.ly/3tLtmor [2]: https://bit.ly/3nkPF24 [3]: https://bit.ly/2RY5XCn [4]: https://bit.ly/3aw7GVG
Comments
Post a Comment