Comment by kiwicopple | Hacker News Reader

Comment by kiwicopple | Hacker News Reader

I have been developing something that provides this functionality for PostgreSQL: 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

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

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!

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?

I do this sort of thing with triggers and table-based materializations of views.

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.

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

Just out of curiosity, are you moving off of Firebase because of technical limitations, or because of pricing?

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)

Fair enough.

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!

Thanks for the comments. I agree, the conflict resolution of FB on mobile is pretty awesome.

FYI we are using this on our mobile (react native) in production, so I have no doubts it will be robust enough with a bit more polish

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.

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

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).

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

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

You can use apache ignite as a caching layer over your db. (Or as a db itself). You can then register a ContinuousQuery to listen for changes. A grpc service can be stood up to stream these changes to other clients, even front end ones (via grpc-web).

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

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!

Reply on news.ycombinator.com