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
Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
41–50 of 108 posts
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#42Earlier 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…
I wish this was possible to use without going full to Hasura.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#43Disclaimer: 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.
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.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#44It is a feature, although experimental, of ClickHouse
https://clickhouse.tech/docs/en/sql-reference/statements/cre...
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#45Disclaimer: 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…
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? Als…
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!)
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#46Here'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.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#47Earlier quoted context omitted.
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)?
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)Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#48There 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.
Noria looks cool but it requires Zookeeper which is a PITA.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#49Earlier quoted context omitted.
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)?
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']
...
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!
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#50In Go you can use GraphJin an automatic GraphQL to SQL compiler that supports GraphQL subscriptions (aka Live Queries). GraphJin works either as a standalone service or as a library in your own code. It uses a very efficient mechanism to manage subscriptions keeping db load low while subscribing to an SQL query using polling.
https://pkg.go.dev/github.com/dosco/graphjin@v0.16.44/core#e...