7 ms·
Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, bu
by nicoburns 5y ago
Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, but it's poll based and not very efficient. It's a super-useful feature for keeping UIs in sync with database state.
- XCSme 5y agoCan't you add something like this to MySQL using triggers or some similar system?
- spiffytech 5y agoHasura evaluated Postgres' listen/notify feature to power their subscriptions, but chose polling instead: https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md https://github.com/hasura/graphql-engine/blob/master/archite... > Listen/Notify: Requires instrumenting all tables with triggers, events consumed by consumer (the web-server) might be dropped in case of the consumer restarting or a network disruption. It was substantially non-trivial for them to implement subscriptions that were both robust and efficient using this approach. Many applications need the extra step on top of listen/notify of relaying subscriptions to an untrusted client (e.g., a browser). I'd like to see more DBs bake that feature in, like RethinkDB did.
- nicoburns 5y agoThe Superbase (https://github.com/supabase/realtime https://github.com/supabase/realtime) approach is really interesting. It listens to the logical replication stream. Makes a lot of sense to me. Unfortunately our postgres instances hosted on heroku don't expose this, so I've been unable to try it out.
- vaughan 5y agoHasura discusses why they chose polling here (https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md#when-do-we-refetch https://github.com/hasura/graphql-engine/blob/master/archite...). > WAL: Reliable stream, but LR slots are expensive which makes horizontal scaling hard, and are often not available on managed database vendors. Heavy write loads can pollute the WAL and will need throttling at the application layer. Would like to hear Supabase's response to this.
- kiwicopple 5y agoHasura's approach is great and there are just tradeoffs between approaches. Sure, slots (can be) expensive; GCP doesn't offer slots but all the others do as far as I know (and I see that as a GCP issue, not a Supabase issue); a lot of writes can definitely fill the WAL. Stepping back, the main comment I can give is that we each approach problems with a slightly different philosophy. Hasura tackles problems using middleware and Supabase tackles problems using the database. Both are fine. Our approach is important (to us) as we evolve the product. For example, we are adding Row Level Security to our Realtime instance. Because it all sits in the database it's essentially just a Postgres extension, minimizing chatter between a middleware server and the database. Also because it's at the "bottom of the stack", other vendors/integrations can make use of the functionality we provide. (supabase cofounder)
- vlasky 5y agoI'm the defacto maintainer of the Meteor MySQL integration and the Node.js package mysql-live-select. These implement pub/sub and reactive queries using the MySQL binary log as the event source: https://github.com/vlasky/meteor-mysql https://github.com/vlasky/meteor-mysql https://github.com/vlasky/mysql-live-select https://github.com/vlasky/mysql-live-select
- deleted 5y ago[deleted]
- pornel 5y agoPostgres has LISTEN and NOTIFY. People build DIY pub-sub with this.
- nicoburns 5y agoSure, but it's a very manual process. I want to be able to write an artbitrary SQL query (or subset of SQL query), and have the subscription aspect "just work".
- acjohnson55 5y agoMaterialize.io does something like this, although in a separate system from Postgres, and I'm not sure it's possible to push live queries to a client.
- alerighi 5y agoWhy can't you do that with triggers? By the way subscriptions seems a good idea in theory, in practice polling is often the best solution. There are not a lot of applications where you need the data that real time (I mean with a latency that is less of a couple of seconds), and for that applications you should build something custom. Subscriptions to work are based on websockets that have its problems, and also requires a constant connection to the server. The application then needs to handle the incoming data properly, another thing that is not entirely obvious. Another thing is that most of the times you don't have a 1:1 mapping between the database schema and the API (and if you do, you shouldn't, since a change of the internal database representation will require a change of the API and will break clients). You have in practice an application server in between. Well is that application server that can send subscriptions to clients. And it can do that without subscribing to updates on database tables (assuming that the tables are only changed by the application itself, that seems reasonable). The application when updates the data can publish it to whatever subscription system it wants to notify clients.
- vaughan 5y ago> and for that applications you should build something custom. Given the spirit of the original post, I think avoiding "build something custom" is the point. It should be a toggle in the database. > you don't have a 1:1 mapping between the database schema and the API (and if you do, you shouldn't, since a change of the internal database representation will require a change of the API and will break clients) I'd argue this is a huge problem. When DB and API get out of sync it creates so much complexity, especially when working in a relational model, and things become impossible to change, or make client caching really difficult. > The application when updates the data can publish it to whatever subscription system it wants to notify clients. This is so much complexity though, compared with the experience of Firebase. Good for keeping more devs employed though.
- alerighi 5y ago> This is so much complexity though, compared with the experience of Firebase. Good for keeping more devs employed though. The solution of Google is not less complex. Rather the complexity is hidden, that if all goes well is good, when things start to not work as expected it means weeks spent in debugging and trying to work around the issue. And we are not even talking about the possibility that Google changes API or closes down the service entirely, or it makes it more expensive, the so called vendor lock-in. At the other side a custom solution takes more time to develop initially, but then is entirely under your control, if something doesn't work you know how to fix it because you built it, you are not bounded to a particular platform, and there is not the possibility that Google decides to change the API in an incompatible way and you have to do extra work just to make things work as they did before, you decide when and if to update the software.
- kiwicopple 5y agoWe built a real-time server to solve this using Postgres Write Ahead Log: https://github.com/supabase/realtime https://github.com/supabase/realtime. We also have some neat stuff coming with integrated Row Level Security. Disclosure (Supabase cofounder)
- gigatexal 5y agoAlas RethinkDB was supposed to do this but the project imploded. It’s been OSSed I think though.
- dkh 5y agoYes, and as a happy early adopter with many projects running off it, I was very happy about this. However the pace of development since then has been absolutely glacial and seemingly without direction. It's truly a shame.
- vaughan 5y agoI feel like Rethink would have done a lot better if it were started closer to today. Funnily enough, in 2009 it was originally an SSD-optimized storage engine for MySQL. The dev experience and API were great. It was the "relational mongodb". Though no one feels daring enough to part from PSQL as a backend these days. With all the cloud deployment options and optimizations in PSQL, it's hard to justify writing your own db engine, but for a great real-time experience, that's what needs to happen.
- rishav_sharan 5y agoHave you tried Postgres Notify? Its the native event subscription. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html