5 ms·

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

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

ralusek 7y ago
Is this better than postgres's native NOTIFY?
- kiwicopple 7y ago
  I actually started with NOTIFY but the payload has a limit of 8000 bytes. Postgres throws an error for anything longer.
- manigandham 7y ago
  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.
  - jtdev 7y ago
    Like...a trigger?
    - Tostino 7y ago
      Yeah...triggers can have significant overhead though while sending a WAL stream to another server which will process it is super low overhead.
hashhar 7y ago
Look at embedded Debezium. https://debezium.io/docs/embedded/ 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.
- kiwicopple 7y ago
  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
  - bobbydreamer 7y ago
    Why replace Firebase ?
    - kiwicopple 7y ago
      Consolidating our codebase - most of our infra was already built on top of Postgres and we were just using Firebase for realtime chat.
  - gunnarmorling 7y ago
    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
tyre 7y ago
Do you know of anything like this for SQLite? I have a need for exactly this. Are there similar triggers?
- deleted 7y ago
  [deleted]
- kiwicopple 7y ago
  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
adamfeldman 7y ago
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-triggers.md https://github.com/hasura/graphql-engine/blob/master/event-t...
- kiwicopple 7y ago
  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
frafra 7y ago
Similar, using triggers, Python, aiohttp + asyncpg: https://github.com/frafra/postgresql2websocket/ https://github.com/frafra/postgresql2websocket/
- fjp 7y ago
  Any reason for your choice of asyncpg vs aiopg? Curious as I have worked with both
pythonaut_16 7y ago
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.

ralusek 7y ago

Is this better than postgres's native NOTIFY?

kiwicopple 7y ago
I actually started with NOTIFY but the payload has a limit of 8000 bytes. Postgres throws an error for anything longer.
manigandham 7y ago
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.
- jtdev 7y ago
  Like...a trigger?
  - Tostino 7y ago
    Yeah...triggers can have significant overhead though while sending a WAL stream to another server which will process it is super low overhead.

hashhar 7y ago

Look at embedded Debezium. https://debezium.io/docs/embedded/ 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.

kiwicopple 7y ago
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
- bobbydreamer 7y ago
  Why replace Firebase ?
  - kiwicopple 7y ago
    Consolidating our codebase - most of our infra was already built on top of Postgres and we were just using Firebase for realtime chat.
- gunnarmorling 7y ago
  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

tyre 7y ago

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

deleted 7y ago
[deleted]
kiwicopple 7y ago
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

adamfeldman 7y ago

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-triggers.md https://github.com/hasura/graphql-engine/blob/master/event-t...

kiwicopple 7y ago
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

frafra 7y ago

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

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

pythonaut_16 7y ago

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.