Comment by kiwicopple | Hacker News Reader

Comment by kiwicopple | Hacker News Reader

For anyone just looking for the pub/sub functionality, I have been developing something that provides the functionality for PostgreSQL: https://github.com/supabase/realtime

It's an Elixir server (Phoenix) that allows you to listen to changes in your database via websockets.

Basically the Phoenix server listens to PostgreSQL's replication functionality, converts the byte stream into JSON, and then broadcasts over websockets. 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 websockets.

I'd have to dig into their implementation to verify, but the main benefit over OP is scalability - i believe their implementation is tightly coupled to postgres, but with this you could scale the Elixir servers without any additional load on your DB

It's still in very early stages, although I am using it in production at my company and will work on it full time starting Jan

Reading the WAL is interesting. I've been other tools [1] accomplish similar ends using Postgres triggers

[1]: https://github.com/hasura/graphql-engine/blob/master/event-t...

Hasura is an amazing tool. I tried with triggers + NOTIFY at the start, which was how Hasura does it (at least last I looked). I ran into the limitations of NOTIFY, which can only have a 8000 byte payload.

The recommended resolution online was just to send a NOTIFY with a Primary key or identifier, then to query the database for the resource that changed. I didn't like this approach because it required more load on the database.

One other advantage of using the WAL is that you don't have to set up then triggers everytime you set up a table. You just set and wal_level to "logical" on the database and then all your tables will be included in the stream replication

Is this better than postgres's native NOTIFY?

I actually started with NOTIFY but the payload has a limit of 8000 bytes. Postgres throws an error for anything longer.

NOTIFY just sends a pub/sub message. You still need a trigger or something else to actually call it on updates, and it has payload size limits.

Like...a trigger?

Yeah...triggers can have significant overhead though while sending a WAL stream to another server which will process it is super low overhead.

Do you know of anything like this for SQLite? I have a need for exactly this. Are there similar triggers?

It's a good question - I don't know the specifics of their implementations, but there are SQLite adapters for RxDB and PouchDB which both have a focus on realtime functionality. YMMV but I found them both pretty good for small implementations

Similar, using triggers, Python, aiohttp + asyncpg: https://github.com/frafra/postgresql2websocket/

Any reason for your choice of asyncpg vs aiopg? Curious as I have worked with both

Look at embedded Debezium. https://debezium.io/docs/embedded/

Very similar to what you are doing but instead of Websockets you can decide where to send your data. The core-debezium sends it to Kafka.

I took a lot of inspiration from Debezium - at the time they required the wal2json plugin (not sure if that's still the case?). I didn't have an option to install the plugin and I mostly wanted a websocket-friendly implementation to replace Firebase.

And if I'm 100% honest I just wanted to make something i thought was cool

Why replace Firebase ?

Consolidating our codebase - most of our infra was already built on top of Postgres and we were just using Firebase for realtime chat.

By now the Debezium Postgres connector also works with pgoutput (coming with Postgres by default as of PG 10 and later).

Disclaimer: working on Debezium

This is pretty cool.

Mnesia (one of the databases built into Erlang) has a similar ability to subscribe to its tables that I've used for realtime updates in some demo projects.

Reply on news.ycombinator.com