Debezium is used for Change Data Capture, and created database changes event streams: https://debezium.io/documentation/reference/1.5/connectors/i...
It's not exactly what you're searching for, but might be worth a look.
Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
61–70 of 108 posts
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#62Earlier quoted context omitted.
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…
That's a great example, thanks a lot!
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#63I'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…
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
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.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#64It's not as simple as "subscribe to this SQL query", but you can do this relatively easy with PostgreSQL LISTEN / NOTIFY:
https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237...
You just notify the clients with the primary keys of what changed, so you only need to run the query if something changed. If you implement it correctly, you only have to run the query once and push the same result to every client.
I have a small project where I used this and wherever you change anything and send the NOTIFY, every client updates real-time.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#65I'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…
This sounds fascinating. Do you have any recommended source to read more about this? Like a book or a paper?
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.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#66It's not as simple as "subscribe to this SQL query", but you can do this relatively easy with PostgreSQL LISTEN / NOTIFY: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237... You just notify the clients with the primary keys of what changed, so you only need to run the query if something changed. If you implement it correctly, you only have to run the query once and push the same result to every client. I…
Fwiw this approach doesn't work for something like:
SELECT * FROM Products WHERE price > 5;
If a new product comes in with price 20 you won't know to add it to your result set and send it to the clients.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#67Earlier quoted context omitted.
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
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 a…
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
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#68check out Kafka Streams, they have streaming SQL as well https://www.confluent.io/online-talks/intro-to-ksql-streamin...
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#69Supabase’s realtime library consumes the logical WAL and provides a Phoenix channel + JavaScript API to subscribe to matching events from it:
https://github.com/supabase/realtime
Under the hood, the core implementation was copied (with credit / attribution) from: https://github.com/cainophile/cainophile
I happened to do a similar thing but I adapted cainophile into an Elixir “OffBroadway” producer: https://github.com/integratedb/integrate/blob/main/lib/integ...
These approaches rely on acking the WAL to confirm data has been processed. It’s simpler than running Debezium / Kafka for “zookept” CDC. However, they are “at least once” at best and it’s easy to shoot yourself in the foot so think twice before relying on this kind of thing for a real application.
Materialize is nice — TAIL is a lovely abstraction and their data ingest uses Debezium under the hood. That said, I believe their Postgres binlog source is still alpha / under active community dev.
Re: Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
#70Earlier quoted context omitted.
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 a…
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
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.