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

71–80 of 108 posts

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

#71

Post by BrentOzar »

Microsoft SQL Server has a rarely-used feature to do exactly this: [Query Notifications][1].

It sounded great in theory when it came out in 2005, but the drawback was that every open subscription was an open connection to the database server, from every application server. The more web servers you have, and the more queries you want to subscribe to, the more open connections you end up with, which at large scale can cause performance issues even when the connections aren't doing anything.

You can mitigate this by using less subscriptions to monitor more data - but when anything changes in the underlying result set, you end up fetching the entire result set. With a lot of web/app servers, this means an avalanche of queries running at exactly the same time, trying to hit the same data to refresh their cache.

In a perfect world, I'd try to mitigate this by offloading these kinds of queries to read-only replicas. That's a feature limited to the expensive Enterprise Edition ($7K USD per CPU core), and when you scale out to a lot of database servers to leverage this, then it becomes prohibitively expensive.

Another way to mitigate the many-app-servers, many-subscriptions problem is to have a central app server manage the caching for everyone else.

[1]: https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/ena...

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

#72

Post by wikibob »

rockwotj wrote: post #65
davty wrote: post #55

Earlier quoted context omitted.

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.

I would love to read this paper when you write it.

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

#73

Post by BenoitP »

I think streaming-first systems like Flink are best positioned to answer your need.

You might need to abstract the query or subscription infrastructure into your data model though:

Subscriptions:

    INSERT INTO user_orders_total_feed(user_id, data)
    SELECT 
      uo.user_id,
      SUM(o.price) total
    FROM user_order uo
    JOIN order o ON uo.user_id = o.user_id
    JOIN user_live_subscription uls ON uo.user_id = uls.user_id
    GROUP BY uo.user_id
    WHERE uls.subscription_topic = 'orders_total'
And for queries you would join on a temporal snapshot using 'FOR SYSTEM TIME AS OF' [1]

You could have your data fully stored in Kafka or Pulsar, Kappa-architecture-style ; or you could have it in regular rdbms but with Change Data Capture on top [2] (basically shipping your WAL to Kafka/Pulsar)

The good thing about Kafka and Flink is that they scale. Your regular db might not sustain the query rate if every query needs a write to a table, but Kafka/Flink will.

[1] https://ci.apache.org/projects/flink/flink-docs-stable/dev/t...

[2] https://debezium.io/

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

#74

Post by wolframhempel »

Here's how it works with JOINS in Postgres, using the NOTIFY/LISTEN mechanism

  -- Run the query and store result in updated_ids
  WITH updated_ids AS (
   UPDATE public.doc
   SET version = version + 1
   
  --  Joins are not directly part of Postgres' update syntax, but this achieves the same
   FROM public.org
   WHERE public.org.id = public.doc.org_id AND public.org.type = 'customer'
   
  -- Returns all ids of affected rows
   RETURNING public.doc.id
  )
  -- convert the returned ids into a comma-separated string that we can sent via the NOTIFY mechanism
  , id_string_result AS (
   SELECT ARRAY_TO_STRING(ARRAY_AGG(id) ,',') AS id_string FROM updated_ids
  )
  
  -- use LISTEN/NOTIFY to publish a changed_row_ids event with a comma separated list of changed row ids
  SELECT pg_notify( 'changed_row_ids', id_string_result.id_string )

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

#75

Post by justsomeuser »

If you have spare CPU capacity, you might want to just re-run and diff the query results. You are already paying for the idle CPU anyway.

I think the issue is that most SQL engines are not designed for streams, just a single request/response. Once the query is complicated enough I think it would be difficult to do efficiently and correctly.

I would go with SQLite in WAL mode (concurrent reads), many cores, and many threads/green threads in a compiled language.

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

#76

Post by hpeinar »

Very interesting topic!

I feel like this would be the right time to ask for an advice regarding doing something similar for user search results with PostgreSQL (v11)

Eg. User "subscribes" to product searches for "Women - Nike - Size M" and the system sends her a daily notification or email if there are new result within her filter.

How would one solve this kind of subscription logic? So far what I've up with is just to save and re-run the user queries from the API side and have last results last primary ID in a separate table to know which results are to be considered new. (So I can use primary key filter on the query to lessen the products DB has to go through, EG: "SELECT id FROM products WHERE [user filters] AND id > 1234"

But it doesn't feel right to bombard the DB with 10k queries on daily basis to achieve this, but maybe I'm overthinking and it is viable.

I looked through some of the comments mentioning NOTIFY / LISTEN but I don't think I could viably use this as I can't apply separate filters on this on the DB level.

Note: I'm looking for general ideas / things I should consider. I'm not expecting anyone to do my work.

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

#77

Post by daniel_iversen »

I'm not 100% sure but might you be able to use some sort of Event Stream Processing[1] product on top of your database (if you need the database) and use that layer in the architecture instead of SQL and the DB?

[1]: https://en.wikipedia.org/wiki/Event_stream_processing

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

#78

Post by cpursley »

thruflo22 wrote: post #69

Supabase’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…

Nice, I've actually extracted out supabases WAL subscribe bits to use alongside with Hasura so that I can react to insert, update and delete events right in Elixir.

Looks like you're doing something similar. I'd love to see this extracted out into a library by the supabase team.

Post reply on HN