4 ms·

I have been developing something that provides this functionality for PostgreSQL: https://github.com/supabase/realtime https://github.com/supabase/realtime It'

I have been developing something that provides this functionality for PostgreSQL: https://github.com/supabase/realtime https://github.com/supabase/realtime It's still in very early stages (although I am using it in production for my company) It's very similar to Debezium (mentioned in another comment), but it's built with Phoenix (elixir), so great for listening via websockets. Basically the Phoenix server listens to PostgreSQL's replication functionality and converts the byte stream into JSON which it then broadcasts over websockets. This is great since you can scale the Phoenix servers without any additional load on your DB. Also it doesn't require wal2json (a Postgres extension). The beauty of listening to the replication functionality is that you can make changes to your database from anywhere - your api, directly in the DB, via a console etc - and you will still receive the changes via Phoenix. I still have to document a lot of how it works and how to use it, but if anyone is interested then I will make it a priority over the weekend

dsun180 7y ago
There is a big difference between the changestream of many SQL and noSQL databases, and what RxDB does. Having a stream of changes is useful but not the whole solution. RxDB is capable of using single document changes of a stream and recalculate the new results of an existing query. This saves you not only much IO performance but makes developing much easier. See https://rxdb.info/query-change-detection.html https://rxdb.info/query-change-detection.html
- kiwicopple 7y ago
  Yeah perhaps "same functionality" is an ambitious statement. I just saw debezium mentioned below and thought i'd throw this out in case someone reading this and it fits their needs!
- tjchear 7y ago
  I would love to use this just for the offline and query change detection capabilities alone - but the latter is currently beta and disabled by default. Is there a reason for that?
  - cryptonector 7y ago
    I do this sort of thing with triggers and table-based materializations of views.
markandrewj 7y ago
RethinkDB might be another database to look at if this is functionality which you are interested in. My only gripe with doing this sort of thing is that if you offer a public API, it becomes separate to what you are using for the application internally. There are also some GraphQL databases like Prisma that offer this sort of functionality.
- kiwicopple 7y ago
  I actually used Firebase at the start but this implementation is so that we can migrate 100% to Postgres. I agree though, Rethink is awesome
  - markandrewj 7y ago
    Just out of curiosity, are you moving off of Firebase because of technical limitations, or because of pricing?
    - kiwicopple 7y ago
      Mainly because I want to keep my team small so it’s easier if we all commit to Postgres. But We also hard a hard time with Firebase’s filtering on sub collections (for their new Firestore)
      - markandrewj 7y ago
        Fair enough.
  - com2kid 7y ago
    Make this good enough and you could very well cut into a huge chunk of Firebase's business model. I'd seriously love to have query subscriptions, especially if the library is robust enough for use on mobile. Ine large differentiator of Firebase's DB offerings is the subscription functionality, mobile and web, but using that means buying into NoSQL. A proper subscribable SQL database would be amazing. Of course the conflict resolution on writes, and the local caching is another key benefit Firebase offers. Not sure how that could be done with a real relational DB!
rch 7y ago
Did you evaluate gRPC or gRPC-Web when considering your websockets implementation? I'm using gRPC in other languages/frameworks and I'd really like to migrate one of those to Elixir in the near future, but I'm curious to know if anyone else has given it a try. There's a prominent warning on the repo: https://github.com/elixir-grpc/grpc https://github.com/elixir-grpc/grpc
- kiwicopple 7y ago
  I didn’t try, to be honest i think the implementation of this might be beyond my elixir capabilities. But I’ll check it out - thanks for the link!
pbowyer 7y ago
Interesting, thanks for sharing how you're doing it. Is there any mileage in doing this with triggers? I have a _very_ legacy system which needs caching adding. Rather than dig through the code to invalidate the cache every time a record is updated/deleted in 20+ tables, I am thinking that being able to listen to the SQL executed and invalidate the cache based on the tables involved would be a clean approach. But not found any way to make that possible - yet.
- kiwicopple 7y ago
  Yeah I also used triggers at the start. There are 2 things that this implementation achieve over triggers. 1. Triggers have an 8000 byte limit. I ran against these limits pretty quickly 2. You need to attach the trigger each time you create a new table. With this you can set and forget
  - anarazel 7y ago
    There's no 8k limit with triggers? Could you expect on what you mean by that? One of the motivating factors for not using triggers is that the implied overhead is very significant. By logging changes separately which triggers the write volume is roughly doubled, and the overhead of insertions is much much higher (a lot of fast/bulk path can't be used, the trigger processing needs to be performed).
    - kiwicopple 7y ago
      Sorry I meant I was using triggers to sent a NOTIFY which has an 8k byte limit. Very true what you mention about trigger overhead. Also you don’t get guaranteed atomicity
  - numtel 7y ago
    I wrote a node.js package a few years ago that gets past the 8000 bytes by sending multiple notifications. https://github.com/numtel/pg-live-select https://github.com/numtel/pg-live-select
- ronmex 7y ago

dsun180 7y ago

There is a big difference between the changestream of many SQL and noSQL databases, and what RxDB does. Having a stream of changes is useful but not the whole solution. RxDB is capable of using single document changes of a stream and recalculate the new results of an existing query. This saves you not only much IO performance but makes developing much easier. See https://rxdb.info/query-change-detection.html https://rxdb.info/query-change-detection.html

kiwicopple 7y ago
Yeah perhaps "same functionality" is an ambitious statement. I just saw debezium mentioned below and thought i'd throw this out in case someone reading this and it fits their needs!
tjchear 7y ago
I would love to use this just for the offline and query change detection capabilities alone - but the latter is currently beta and disabled by default. Is there a reason for that?
- cryptonector 7y ago
  I do this sort of thing with triggers and table-based materializations of views.

markandrewj 7y ago

RethinkDB might be another database to look at if this is functionality which you are interested in. My only gripe with doing this sort of thing is that if you offer a public API, it becomes separate to what you are using for the application internally. There are also some GraphQL databases like Prisma that offer this sort of functionality.

kiwicopple 7y ago
I actually used Firebase at the start but this implementation is so that we can migrate 100% to Postgres. I agree though, Rethink is awesome
- markandrewj 7y ago
  Just out of curiosity, are you moving off of Firebase because of technical limitations, or because of pricing?
  - kiwicopple 7y ago
    Mainly because I want to keep my team small so it’s easier if we all commit to Postgres. But We also hard a hard time with Firebase’s filtering on sub collections (for their new Firestore)
    - markandrewj 7y ago
      Fair enough.
- com2kid 7y ago
  Make this good enough and you could very well cut into a huge chunk of Firebase's business model. I'd seriously love to have query subscriptions, especially if the library is robust enough for use on mobile. Ine large differentiator of Firebase's DB offerings is the subscription functionality, mobile and web, but using that means buying into NoSQL. A proper subscribable SQL database would be amazing. Of course the conflict resolution on writes, and the local caching is another key benefit Firebase offers. Not sure how that could be done with a real relational DB!

rch 7y ago

Did you evaluate gRPC or gRPC-Web when considering your websockets implementation? I'm using gRPC in other languages/frameworks and I'd really like to migrate one of those to Elixir in the near future, but I'm curious to know if anyone else has given it a try. There's a prominent warning on the repo: https://github.com/elixir-grpc/grpc https://github.com/elixir-grpc/grpc

kiwicopple 7y ago
I didn’t try, to be honest i think the implementation of this might be beyond my elixir capabilities. But I’ll check it out - thanks for the link!

pbowyer 7y ago

Interesting, thanks for sharing how you're doing it. Is there any mileage in doing this with triggers? I have a _very_ legacy system which needs caching adding. Rather than dig through the code to invalidate the cache every time a record is updated/deleted in 20+ tables, I am thinking that being able to listen to the SQL executed and invalidate the cache based on the tables involved would be a clean approach. But not found any way to make that possible - yet.

kiwicopple 7y ago
Yeah I also used triggers at the start. There are 2 things that this implementation achieve over triggers. 1. Triggers have an 8000 byte limit. I ran against these limits pretty quickly 2. You need to attach the trigger each time you create a new table. With this you can set and forget
- anarazel 7y ago
  There's no 8k limit with triggers? Could you expect on what you mean by that? One of the motivating factors for not using triggers is that the implied overhead is very significant. By logging changes separately which triggers the write volume is roughly doubled, and the overhead of insertions is much much higher (a lot of fast/bulk path can't be used, the trigger processing needs to be performed).
  - kiwicopple 7y ago
    Sorry I meant I was using triggers to sent a NOTIFY which has an 8k byte limit. Very true what you mention about trigger overhead. Also you don’t get guaranteed atomicity
- numtel 7y ago
  I wrote a node.js package a few years ago that gets past the 8000 bytes by sending multiple notifications. https://github.com/numtel/pg-live-select https://github.com/numtel/pg-live-select
ronmex 7y ago