17 ms·

Streaming data in Postgres to 1M clients with GraphQL

192 points by tango12 4y ago

tango12 4y ago
Streaming data to clients at the edge (apps or services) is a hard problem. We built an approach that keeps Postgres at the center and allows clients to consume a stream of data that is already in Postgres without any additional moving parts. This works with read replica style scaling or with Postgres flavours that support scaling out easily (eg: Cosmos Postgres, Yugabyte & cockroach coming soon).
canadiantim 4y ago
This looks really cool and solves my major gripe with something like Hasura, which is the duplication of data.
- Twisol 4y ago
  Can you go into more detail about what you mean by "duplication of data"? I worked on a team whose product uses Hasura; my understanding is that data still comes directly from Postgres, even if Hasura is sitting in the middle. Where is the data being duplicated?
  - robertlagrant 4y ago
    They're saying products like Hasura suffer from this, and Hasura doesn't.
    - exelib 4y ago
      Nope, they're saying that products like hasura resolve the issue with the duplication of data. Or to put in another way, you don't need multiple stores like kafka with redundant data storage. Just use hasura.
jitl 4y ago
It sounds like the stream must be an append-only table. This is awkward - I would expect streaming updated results for a query. If I want clients to refetch changed record in real time, do I still need to build that on top of this stream primitive? Like, I stream an audit log style table, and then refetch any IDs mentioned in the stream separately?
- llamaLord 4y ago
  I think you’re missing the key part which is that this is delivered over a single GraphQL subscription. So as new data is added to the table, hasura automatically pushes it to the client via the Websocket subscription. No refetch needed.
- tirumaraiselvan 4y ago
  I work at Hasura. You don't need an append-only table always. You may have to add a monotonic ID to your table which increments on DMLs (and use it as the cursor for streaming) and this can be done using a post-DML trigger in Postgres. Also, streaming works over tables with relationships as well so you can get related data in the same stream as well. Also, there is another interface for real-time subscriptions called live queries which might be more appropriate depending on the use-case: https://hasura.io/docs/latest/subscriptions/postgres/index/#types-of-subscriptions https://hasura.io/docs/latest/subscriptions/postgres/index/#... .
- dagss 4y ago
  This is my favorite kind of schema -- do not UPDATE Person.Name, instead INSERT into PersonNameChange. With the right indexes and lateral joins/cross applies you can make the aggregate you need when you query instead of when you write.
agrippanux 4y ago
My company is using this in production to stream messages/online presence to our Unity game clients and it works very well.
- an_opabinia 4y ago
  Which is it? Sounds cool.
rpd9803 4y ago
I sort of hate GraphQL. Multi-mutation syntax sucks, no select-all.. do not like.
maxpert 4y ago
While I love ingenuity of developers at Hasura (because I've been personally through these scaling challenges), I always get a gag reaction with GraphQL. I've honestly tried hard to digest it, and I can tell you at large scale where single DB won't cut it, you would either need to develop a large federation layer like [Netflix](https://netflixtechblog.com/how-netflix-scales-its-api-with-graphql-federation-part-1-ae3557c187e2 https://netflixtechblog.com/how-netflix-scales-its-api-with-...), or just rip-it out. Streaming might elevate the problem a little, but I real problem still lurks under the hood. The fact that front-end community wants to fit everything under GraphQL bothers me, because every backend developer knows that a single tool/technology is usually not the best tool for solving all problems of your life. Remember the golden words, THERE IS NO SILVER BULLET!
- arcticfox 4y ago
  I also have issues with GraphQL at times, but mostly because I find the syntax unintuitive. What about the concept doesn't work? It's just a syntax for queries, I'm confused why it wouldn't scale.
  - eurasiantiger 4y ago
    It’s not just a syntax for queries—GraphQL is essentially a single-stop shop for entity resolution.
  - lakomen 4y ago
    I've been thinking about the problem and the more I think about it, I tried many different alternatives, it's best to have plain SQL being sent for queries. The problem there is whi has the right to run which queries? So the real problem is authorization.
- nrb 4y ago
  If database capacity is your main concern and not independent schema deployability, then federation is overkill. You can just connect to whichever databases contain your data in your resolvers within a single service. You have to be at pretty massive scale before federation becomes necessary and by then (if ever) your frontend teams have experienced benefits that are pretty much miraculous. The reason frontend wants to fit as much as possible into it is because it's vastly better than what came before it unless you have a 95th percentile org that is really doing an outstanding job managing the API via other means.
dinvlad 4y ago
I believe they had a POC for this already a few years ago, but great to see the examples expanded, and seems like it’s more “prod-ready” now.
- tirumaraiselvan 4y ago
  I work at Hasura. We have 2 interfaces for real-time subscriptions: live queries and streaming. The former has been around for few years now. You can read more here: https://hasura.io/docs/latest/subscriptions/postgres/index/#types-of-subscriptions https://hasura.io/docs/latest/subscriptions/postgres/index/#...
  - cpursley 4y ago
    I'm a (very satisfied) hasura user and trying to figure out which realtime interface to pick for a chat component. Can you explain the difference between the two and how/when to pick the right one?
    - altschuler 4y ago
      You would benefit from using a streaming subscription. If you use a live query, you have to manually maintain a "cursor" (ie last message you fetched) and update your graphql subscription accordingly every time a new message is received or sent. If you don't you'll be refetching the same messages repeatedly, and they'll just grow as more messages are added. With a streaming subscription this is all taken care of, you simply do something like this: 1) Load the last 50 messages (or whatever number of existing messages suits you) 2) Run your streaming subscription using the most recent of the messages from above as you cursor 3) When you send a message, do nothing and let the subscription handle getting the message you just sent
gip 4y ago
I've been using Hasura in production for small commercial projects deployed on AWS and I've been positively impressed by the stability and the speed up - it makes spinning up a graphQL backend with row-level security straightforward.
- dinvlad 4y ago
  Indeed, and it consumes very little memory as well, even compared to any “native” Node.js GraphQL servers.
- felixyz 4y ago
  Just curious: my understanding is Hasura kind of discourages using RLS in lieu of their own access control layer[1]. Did you consider pros/cons of either approach? [1] https://hasura.io/docs/latest/auth/authorization/basics/ https://hasura.io/docs/latest/auth/authorization/basics/
  - exelib 4y ago
    In short, they aren't really comparable. The thing is, you use postgres RLS for DB users (or give access to an app). But hasura ACL is for app users.
    - felixyz 4y ago
      People do use RLS for app user-level access control. It has been advocated by PostgREST for years [1] and also widely practiced by the PostGraphile community [2]. Hasura distinguishes itself by not actively advocating db-level access control. From what I recall, the first versions of Hasura were created at a time when Postgres' RLS was still very slow, but it's not clear to me why they still push their own solution. (Not saying there can't be a good reason for it, just not clear to me what it is, and I'd rather default to using functionality built into the database.) [1] https://postgrest.org/en/stable/auth.html#roles-for-each-web-user https://postgrest.org/en/stable/auth.html#roles-for-each-web... [2] https://www.graphile.org/postgraphile/security/ https://www.graphile.org/postgraphile/security/
praveenweb 4y ago
We have written a post[1] on building a real-time chat app with Streaming Subscriptions on Postgres. It gives a quick overview of the architecture used and how you can leverage the API on the client side with AuthZ. There’s a live demo that you all can try out.[2] [1] https://hasura.io/blog/building-real-time-chat-apps-with-graphql-streaming-subscriptions/ https://hasura.io/blog/building-real-time-chat-apps-with-gra... [2] https://eclectic-dragon-25a38c.netlify.app/ https://eclectic-dragon-25a38c.netlify.app/ Would love to see more use cases coming out of this :)
dsandip 4y ago
FD: I work at Hasura. Seeing some feedback on GraphQL - Hasura has had support for converting templated GraphQL into RESTish endpoints (with Open API Spec docs if needed). We are planning to do the same for this streaming API as well - does anyone have good examples of existing REST/RESTish endpoints that something similar?
- nicoburns 4y ago
  We’ve been using hasura at work, but we’ve stopped using it for everything other than subscriptions in favour of hand written rest apis. The problem for us wasn’t really graphql itself, but the fact that the client app was determining the query. If the client could request a “named query”, that was then determined by the backend (perhaps via a web hook?)then we’d have been able to use hasura more.
  - llamaLord 4y ago
    I’ve been a big hasura user for a while. Give their RESTified endpoints a go, solves this issue for you and still gives you all and access control goodness and subscriptions under one roof.
  - Dystopian 4y ago
    Hasura also has controls for allow listing opertaions for that composed on the FE problem (https://hasura.io/docs/latest/security/allow-list/ https://hasura.io/docs/latest/security/allow-list/). There's also a few NPM packages for auto-generating that allow list from your project (https://www.npmjs.com/search?q=hasura%20allow%20list https://www.npmjs.com/search?q=hasura%20allow%20list -- the one I've used before was from `tallerdevs`).
benjaminwootton 4y ago
I think there’s a huge unacknowledged gap in the database industry for good streaming products. If we are building a report or dashboard that we pull up a few times a day then a pull based model where we query the database on page load is fine. For almost anything else such as an app, a microservice, an alerting system, a web page, a dashboard, we want to be able to update it in near real time for the user experience. Receiving a stream of query results is by far the easiest way to do this. Polling is obviously a poor interim solution. I think streaming will be a huge story in data over the next decade. The products are coming through now which is a start.
- Already__Taken 4y ago
  That's why I was watching rethinkDB but that didn't pan out.
- tixocloud 4y ago
  Would you not be able to solve this by having a messaging queue and getting the database to publish it’s updates? Curious what products you’ve seen as well.
  - dagss 4y ago
    A message queue is a database too. So the question is: Why have two copies of your data, two products to learn and monitor and operate, write boilerplate to move data between the DBs, etc.? A "message queue" comes down to being another index on your table/set of tables ordered by a post-commit sequence number. These are things all SQL DBs have already, it just lacks a bit of exposing/packaging to be as convenient to use as a messaging queue.
- lmm 4y ago
  Streaming has been the story for the past couple of decades already. But once you accept the value of an event-first architecture, the benefits of traditional RDBMSes become much weaker.
  - benjaminwootton 4y ago
tango12 4y ago
Has anyone come across neat tools for load-testing streaming APIs? We used https://github.com/hasura/graphql-bench https://github.com/hasura/graphql-bench and a set of scripts to monitor runtime characteristics of Hasura and Postgres, and reconciliation to make sure data was received as expected and in-order. But would love to see if there's other tools that folks have come across!
hsuperman 4y ago
I would have hoped to see some benchmark latency numbers. What does it mean to easily handle a 1M clients?
- tirumaraiselvan 4y ago
  It is given here: https://github.com/hasura/graphql-engine/blob/master/architecture/streaming-subscriptions.md#performance-benchmarks https://github.com/hasura/graphql-engine/blob/master/archite...
  - exelib 4y ago
    It shows only connections count and CPU. No other metrics, unfortunately.
deleted 4y ago
[deleted]
atentaten 4y ago
How different is this from Supabase Realtime?
- tango12 4y ago
  Haven’t looked at the internals or the API, but I think for starters, Hasura is designed for working on any Postgres including ones that have existing data, especially modern Postgres offerings (crunchy, alloy db, neon, Aurora, cosmos Postgres, timescale, yugabyte)
- Dystopian 4y ago
  Disclaimer - I work on Hasura, so I won't comment too much on which is better. They're all similar flavors of producing realtime results - which take similar, but different, methods to their approach. My understanding (please feel free to correct me if I'm wrong): - Supabase Realtime uses WAL. - Hasura Streaming Subscriptions uses a query which will be append-only (could be a sort-by or also WAL). - Hasura Live Queries uses interval polling, refetching, and multiplexing. - Supabase uses Postgres RLS for authorization, while Hasura uses an internal RLS system which composes queries (which allows for features like the multiplexing above). - All 3 use websockets for their client communication. Supabase Realtime https://github.com/supabase/realtime#introduction https://github.com/supabase/realtime#introduction https://supabase.com/docs/guides/realtime https://supabase.com/docs/guides/realtime Hasura Streaming Subscriptions / Live Queries https://github.com/hasura/graphql-engine/blob/master/architecture/streaming-subscriptions.md https://github.com/hasura/graphql-engine/blob/master/archite... https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md https://github.com/hasura/graphql-engine/blob/master/archite...
  - atentaten 4y ago
    Thanks for this. I was more interested with the differences in implementation rather than which one is better.
midislack 4y ago
How does it do on the Pi4 4GB, the universal standard benchmark system?

192 points by tango12 4y ago

Streaming data to clients at the edge (apps or services) is a hard problem. We built an approach that keeps Postgres at the center and allows clients to consume a stream of data that is already in Postgres without any additional moving parts. This works with read replica style scaling or with Postgres flavours that support scaling out easily (eg: Cosmos Postgres, Yugabyte & cockroach coming soon).

canadiantim 4y ago

This looks really cool and solves my major gripe with something like Hasura, which is the duplication of data.

Twisol 4y ago
Can you go into more detail about what you mean by "duplication of data"? I worked on a team whose product uses Hasura; my understanding is that data still comes directly from Postgres, even if Hasura is sitting in the middle. Where is the data being duplicated?
- robertlagrant 4y ago
  They're saying products like Hasura suffer from this, and Hasura doesn't.
  - exelib 4y ago
    Nope, they're saying that products like hasura resolve the issue with the duplication of data. Or to put in another way, you don't need multiple stores like kafka with redundant data storage. Just use hasura.

jitl 4y ago

It sounds like the stream must be an append-only table. This is awkward - I would expect streaming updated results for a query. If I want clients to refetch changed record in real time, do I still need to build that on top of this stream primitive? Like, I stream an audit log style table, and then refetch any IDs mentioned in the stream separately?

llamaLord 4y ago
I think you’re missing the key part which is that this is delivered over a single GraphQL subscription. So as new data is added to the table, hasura automatically pushes it to the client via the Websocket subscription. No refetch needed.
tirumaraiselvan 4y ago
I work at Hasura. You don't need an append-only table always. You may have to add a monotonic ID to your table which increments on DMLs (and use it as the cursor for streaming) and this can be done using a post-DML trigger in Postgres. Also, streaming works over tables with relationships as well so you can get related data in the same stream as well. Also, there is another interface for real-time subscriptions called live queries which might be more appropriate depending on the use-case: https://hasura.io/docs/latest/subscriptions/postgres/index/#types-of-subscriptions https://hasura.io/docs/latest/subscriptions/postgres/index/#... .
dagss 4y ago
This is my favorite kind of schema -- do not UPDATE Person.Name, instead INSERT into PersonNameChange. With the right indexes and lateral joins/cross applies you can make the aggregate you need when you query instead of when you write.

agrippanux 4y ago

My company is using this in production to stream messages/online presence to our Unity game clients and it works very well.

an_opabinia 4y ago
Which is it? Sounds cool.

rpd9803 4y ago

I sort of hate GraphQL. Multi-mutation syntax sucks, no select-all.. do not like.

maxpert 4y ago

While I love ingenuity of developers at Hasura (because I've been personally through these scaling challenges), I always get a gag reaction with GraphQL. I've honestly tried hard to digest it, and I can tell you at large scale where single DB won't cut it, you would either need to develop a large federation layer like [Netflix](https://netflixtechblog.com/how-netflix-scales-its-api-with-graphql-federation-part-1-ae3557c187e2 https://netflixtechblog.com/how-netflix-scales-its-api-with-...), or just rip-it out. Streaming might elevate the problem a little, but I real problem still lurks under the hood. The fact that front-end community wants to fit everything under GraphQL bothers me, because every backend developer knows that a single tool/technology is usually not the best tool for solving all problems of your life. Remember the golden words, THERE IS NO SILVER BULLET!

arcticfox 4y ago
I also have issues with GraphQL at times, but mostly because I find the syntax unintuitive. What about the concept doesn't work? It's just a syntax for queries, I'm confused why it wouldn't scale.
- eurasiantiger 4y ago
  It’s not just a syntax for queries—GraphQL is essentially a single-stop shop for entity resolution.
- lakomen 4y ago
  I've been thinking about the problem and the more I think about it, I tried many different alternatives, it's best to have plain SQL being sent for queries. The problem there is whi has the right to run which queries? So the real problem is authorization.
nrb 4y ago
If database capacity is your main concern and not independent schema deployability, then federation is overkill. You can just connect to whichever databases contain your data in your resolvers within a single service. You have to be at pretty massive scale before federation becomes necessary and by then (if ever) your frontend teams have experienced benefits that are pretty much miraculous. The reason frontend wants to fit as much as possible into it is because it's vastly better than what came before it unless you have a 95th percentile org that is really doing an outstanding job managing the API via other means.

dinvlad 4y ago

I believe they had a POC for this already a few years ago, but great to see the examples expanded, and seems like it’s more “prod-ready” now.

tirumaraiselvan 4y ago
I work at Hasura. We have 2 interfaces for real-time subscriptions: live queries and streaming. The former has been around for few years now. You can read more here: https://hasura.io/docs/latest/subscriptions/postgres/index/#types-of-subscriptions https://hasura.io/docs/latest/subscriptions/postgres/index/#...
- cpursley 4y ago
  I'm a (very satisfied) hasura user and trying to figure out which realtime interface to pick for a chat component. Can you explain the difference between the two and how/when to pick the right one?
  - altschuler 4y ago
    You would benefit from using a streaming subscription. If you use a live query, you have to manually maintain a "cursor" (ie last message you fetched) and update your graphql subscription accordingly every time a new message is received or sent. If you don't you'll be refetching the same messages repeatedly, and they'll just grow as more messages are added. With a streaming subscription this is all taken care of, you simply do something like this: 1) Load the last 50 messages (or whatever number of existing messages suits you) 2) Run your streaming subscription using the most recent of the messages from above as you cursor 3) When you send a message, do nothing and let the subscription handle getting the message you just sent

gip 4y ago

I've been using Hasura in production for small commercial projects deployed on AWS and I've been positively impressed by the stability and the speed up - it makes spinning up a graphQL backend with row-level security straightforward.

dinvlad 4y ago
Indeed, and it consumes very little memory as well, even compared to any “native” Node.js GraphQL servers.
felixyz 4y ago
Just curious: my understanding is Hasura kind of discourages using RLS in lieu of their own access control layer[1]. Did you consider pros/cons of either approach? [1] https://hasura.io/docs/latest/auth/authorization/basics/ https://hasura.io/docs/latest/auth/authorization/basics/
- exelib 4y ago
  In short, they aren't really comparable. The thing is, you use postgres RLS for DB users (or give access to an app). But hasura ACL is for app users.
  - felixyz 4y ago
    People do use RLS for app user-level access control. It has been advocated by PostgREST for years [1] and also widely practiced by the PostGraphile community [2]. Hasura distinguishes itself by not actively advocating db-level access control. From what I recall, the first versions of Hasura were created at a time when Postgres' RLS was still very slow, but it's not clear to me why they still push their own solution. (Not saying there can't be a good reason for it, just not clear to me what it is, and I'd rather default to using functionality built into the database.) [1] https://postgrest.org/en/stable/auth.html#roles-for-each-web-user https://postgrest.org/en/stable/auth.html#roles-for-each-web... [2] https://www.graphile.org/postgraphile/security/ https://www.graphile.org/postgraphile/security/

praveenweb 4y ago

We have written a post[1] on building a real-time chat app with Streaming Subscriptions on Postgres. It gives a quick overview of the architecture used and how you can leverage the API on the client side with AuthZ. There’s a live demo that you all can try out.[2] [1] https://hasura.io/blog/building-real-time-chat-apps-with-graphql-streaming-subscriptions/ https://hasura.io/blog/building-real-time-chat-apps-with-gra... [2] https://eclectic-dragon-25a38c.netlify.app/ https://eclectic-dragon-25a38c.netlify.app/ Would love to see more use cases coming out of this :)

dsandip 4y ago

FD: I work at Hasura. Seeing some feedback on GraphQL - Hasura has had support for converting templated GraphQL into RESTish endpoints (with Open API Spec docs if needed). We are planning to do the same for this streaming API as well - does anyone have good examples of existing REST/RESTish endpoints that something similar?

nicoburns 4y ago
We’ve been using hasura at work, but we’ve stopped using it for everything other than subscriptions in favour of hand written rest apis. The problem for us wasn’t really graphql itself, but the fact that the client app was determining the query. If the client could request a “named query”, that was then determined by the backend (perhaps via a web hook?)then we’d have been able to use hasura more.
- llamaLord 4y ago
  I’ve been a big hasura user for a while. Give their RESTified endpoints a go, solves this issue for you and still gives you all and access control goodness and subscriptions under one roof.
- Dystopian 4y ago
  Hasura also has controls for allow listing opertaions for that composed on the FE problem (https://hasura.io/docs/latest/security/allow-list/ https://hasura.io/docs/latest/security/allow-list/). There's also a few NPM packages for auto-generating that allow list from your project (https://www.npmjs.com/search?q=hasura%20allow%20list https://www.npmjs.com/search?q=hasura%20allow%20list -- the one I've used before was from `tallerdevs`).

benjaminwootton 4y ago

I think there’s a huge unacknowledged gap in the database industry for good streaming products. If we are building a report or dashboard that we pull up a few times a day then a pull based model where we query the database on page load is fine. For almost anything else such as an app, a microservice, an alerting system, a web page, a dashboard, we want to be able to update it in near real time for the user experience. Receiving a stream of query results is by far the easiest way to do this. Polling is obviously a poor interim solution. I think streaming will be a huge story in data over the next decade. The products are coming through now which is a start.

Already__Taken 4y ago
That's why I was watching rethinkDB but that didn't pan out.
tixocloud 4y ago
Would you not be able to solve this by having a messaging queue and getting the database to publish it’s updates? Curious what products you’ve seen as well.
- dagss 4y ago
  A message queue is a database too. So the question is: Why have two copies of your data, two products to learn and monitor and operate, write boilerplate to move data between the DBs, etc.? A "message queue" comes down to being another index on your table/set of tables ordered by a post-commit sequence number. These are things all SQL DBs have already, it just lacks a bit of exposing/packaging to be as convenient to use as a messaging queue.
lmm 4y ago
Streaming has been the story for the past couple of decades already. But once you accept the value of an event-first architecture, the benefits of traditional RDBMSes become much weaker.
- benjaminwootton 4y ago

tango12 4y ago

Has anyone come across neat tools for load-testing streaming APIs? We used https://github.com/hasura/graphql-bench https://github.com/hasura/graphql-bench and a set of scripts to monitor runtime characteristics of Hasura and Postgres, and reconciliation to make sure data was received as expected and in-order. But would love to see if there's other tools that folks have come across!

hsuperman 4y ago

I would have hoped to see some benchmark latency numbers. What does it mean to easily handle a 1M clients?

tirumaraiselvan 4y ago
It is given here: https://github.com/hasura/graphql-engine/blob/master/architecture/streaming-subscriptions.md#performance-benchmarks https://github.com/hasura/graphql-engine/blob/master/archite...
- exelib 4y ago
  It shows only connections count and CPU. No other metrics, unfortunately.

deleted 4y ago

[deleted]

atentaten 4y ago

How different is this from Supabase Realtime?

tango12 4y ago
Haven’t looked at the internals or the API, but I think for starters, Hasura is designed for working on any Postgres including ones that have existing data, especially modern Postgres offerings (crunchy, alloy db, neon, Aurora, cosmos Postgres, timescale, yugabyte)
Dystopian 4y ago
Disclaimer - I work on Hasura, so I won't comment too much on which is better. They're all similar flavors of producing realtime results - which take similar, but different, methods to their approach. My understanding (please feel free to correct me if I'm wrong): - Supabase Realtime uses WAL. - Hasura Streaming Subscriptions uses a query which will be append-only (could be a sort-by or also WAL). - Hasura Live Queries uses interval polling, refetching, and multiplexing. - Supabase uses Postgres RLS for authorization, while Hasura uses an internal RLS system which composes queries (which allows for features like the multiplexing above). - All 3 use websockets for their client communication. Supabase Realtime https://github.com/supabase/realtime#introduction https://github.com/supabase/realtime#introduction https://supabase.com/docs/guides/realtime https://supabase.com/docs/guides/realtime Hasura Streaming Subscriptions / Live Queries https://github.com/hasura/graphql-engine/blob/master/architecture/streaming-subscriptions.md https://github.com/hasura/graphql-engine/blob/master/archite... https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md https://github.com/hasura/graphql-engine/blob/master/archite...
- atentaten 4y ago
  Thanks for this. I was more interested with the differences in implementation rather than which one is better.

midislack 4y ago

How does it do on the Pi4 4GB, the universal standard benchmark system?