5 ms·

Show HN: Realtime Postgres

62 points by kiwicopple 7y ago

kiwicopple 7y ago
Hey HN, this is an Elixir server (Phoenix) that allows you to listen to changes in your database via websockets. Basically the Phoenix server 1. listens to PostgreSQL's native replication functionality (WAL stream) 2. converts the byte stream into JSON 3. it then broadcasts over websockets I wrote this originally to replace Firebase's firestore database, which I wasn't too pleased with. I needed the realtime functionality for messaging inside my apps. Thought the community here might like it. Postgres is an amazing database - with realtime functionality I was able to consolidate everything into one database. Would love feedback. There are a few (many?) bugs and a lot of things to iron out, but I'm working with some close friends to make it awesome.
- shadowsun7 7y ago
  I know I’m late to the party, but I’ve recently come around to the idea of using apps that sit in front of Postgres to provide an API for free. This seems like an amazing project! I mean, I knew PostgREST existed, but I didn’t realise think it would be possible to make something similar that worked for the real-time use case. Brilliant stuff!
  - kiwicopple 7y ago
    I really love PostgREST. This was my early approach to realtime, using PostgREST for CRUD operations: https://paul.copplest.one/blog/nimbus-tech-2019-04.html#tech-stack https://paul.copplest.one/blog/nimbus-tech-2019-04.html#tech... Not much is different now except I don't use NOTIFY anymore due to the 8000 byte payload limitation (and the reason why I made the elixir server)
    - numtel 7y ago
      I got around the 8000 byte limit by sending multiple notifications in this NPM package: https://github.com/numtel/pg-live-select/blob/master/lib/trigger.tpl.sql#L42 https://github.com/numtel/pg-live-select/blob/master/lib/tri... Reading the replication log is probably faster though. MySQL has row-based replication too and there's clients in most languages if you want to support it on your saas. Do you have plans to support changes to specific SELECT queries or keep it at the row level?
      - kiwicopple 7y ago
        That's quite clever, I never actually thought of that. Guessing from your username, this is your package? It's very cool Yes, we will allow filtering on the listener, just shipping early. Next steps, we will only filter on the Primary keys - something like supabase.from('users:id.eq.1') - but eventually we will support advanced filtering like this: https://supabase.io/docs/library/get#filtering https://supabase.io/docs/library/get#filtering
1_over_n 7y ago
This is a great idea, lovely to see something with so much potential utility being turned into a functional product.
ahachete 7y ago
Just a quick precision, if I'm not mistaken: this project is not decoding the (version, architecture dependent) WAL stream as it seems to be implied by https://news.ycombinator.com/item?id=22114603 https://news.ycombinator.com/item?id=22114603 But it rather uses logical decoding with the pgoutput plugin (which was introduced in Postgres 10, so I'm guessing it doesn't support out of the box earlier versions). This approach makes total sense, though. Apart from that, reading the documentation I don't see how app.supabase will interface with my source database. Does it need to be open for connections from supabase, or is supabase also a database hosting service, or an agent is installed alongside the source database?
- kiwicopple 7y ago
  Yes you're right about the logical decoding, thanks for clarifying and I'll make sure I update the docs. For Supabase (when we finally have sign ups), the idea is that you can BYO postgres, or we will host. If BYO, you would have to set wal_level = logical, and create a publication for us to listen to (eg: CREATE PUBLICATION supabase_realtime FOR ALL TABLES). We will host the middleware which will give realtime and restful api. The idea is that you focus on your database schema and we handle the rest. Still early days though. We are just trying to find our feet, including figuring out whether this is even something people would want
  - ahachete 7y ago
    Great. May you ellaborate on the BYO concept? How do you bring it? Or you will just ask Postgres owners to install an agent which will then connect from customers' premises to your middleware? BTW hosting your own Postgres service is a huge project on its own. Take this into consideration.
    - kiwicopple 7y ago
      For the BYO - all we need is the database connection string. We will then host an elixir server + nginx. This doesn’t need any agent on the database itself. I’ll add a diagram to to website, I think I can make this a lot clearer. > hosting your own Postgres service is a huge project Yes, you’re right. We will only do it if it looks like our potential customers are leaving because they don’t know how to do it themselves. Even then, we will just wrap an existing managed service to start with. We’d like to make Postgres as simple to use as Firebase (for newbie database users)
sheerun 7y ago
As far as I am aware none of popular managed PostgreSQL providers provide access to WAL
- kiwicopple 7y ago
  Not sure about the others but we are using this with AWS RDS in production. This should work with any provider that allows replication
- ahachete 7y ago
  As I mentioned on a comment above (https://news.ycombinator.com/item?id=22115752 https://news.ycombinator.com/item?id=22115752) it doesn't really require the WAL but rather the logical decoding stream. This is offered by some of the PostgreSQL-aaS.
zubairq 7y ago
This is pretty good. I built an open source project on GitHub that listened to table changes in Postgres a few years back and could relay them back to the UI
deleted 7y ago
[deleted]
jatins 7y ago
Interesting project! One question: Does this have any access control built in? Otherwise, anyone could change anything in database from browser.
- kiwicopple 7y ago
  Good question. We will be adding a static API key which is set as an ENV variable. For our hosted service we put this behind an nginx instance that has more robust access control. We’d prefer to keep it this way so the realtime server can remain as simple as possible (and completely stateless)
  - jatins 7y ago
    Ah, okay! My question more on the lines of application level access control. As in how to ensure one user can only modify certain data in a table? For example assuming a social networking site, if entire database is exposed one user could update the profile of other user knowing their user ID. I think firebase used to have a concept of security rules for such things
    - kiwicopple 7y ago
      Aha, gotcha - this elixir server is for subscribing only. For all other CRUD operations I would very highly recommend PostgREST. And yes, Firebase still has that concept. You can achieve the same in postgres with Row Level Security (https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html) although admittedly it's a bit easier on Firebase.
- mildmustard 7y ago
  Yea, I'd be really interested in this. Part of using Firebase Realtime DB was that you can use Firebase Auth with it for access control and set rules on the database based on it.

62 points by kiwicopple 7y ago

Hey HN, this is an Elixir server (Phoenix) that allows you to listen to changes in your database via websockets. Basically the Phoenix server 1. listens to PostgreSQL's native replication functionality (WAL stream) 2. converts the byte stream into JSON 3. it then broadcasts over websockets I wrote this originally to replace Firebase's firestore database, which I wasn't too pleased with. I needed the realtime functionality for messaging inside my apps. Thought the community here might like it. Postgres is an amazing database - with realtime functionality I was able to consolidate everything into one database. Would love feedback. There are a few (many?) bugs and a lot of things to iron out, but I'm working with some close friends to make it awesome.

shadowsun7 7y ago
I know I’m late to the party, but I’ve recently come around to the idea of using apps that sit in front of Postgres to provide an API for free. This seems like an amazing project! I mean, I knew PostgREST existed, but I didn’t realise think it would be possible to make something similar that worked for the real-time use case. Brilliant stuff!
- kiwicopple 7y ago
  I really love PostgREST. This was my early approach to realtime, using PostgREST for CRUD operations: https://paul.copplest.one/blog/nimbus-tech-2019-04.html#tech-stack https://paul.copplest.one/blog/nimbus-tech-2019-04.html#tech... Not much is different now except I don't use NOTIFY anymore due to the 8000 byte payload limitation (and the reason why I made the elixir server)
  - numtel 7y ago
    I got around the 8000 byte limit by sending multiple notifications in this NPM package: https://github.com/numtel/pg-live-select/blob/master/lib/trigger.tpl.sql#L42 https://github.com/numtel/pg-live-select/blob/master/lib/tri... Reading the replication log is probably faster though. MySQL has row-based replication too and there's clients in most languages if you want to support it on your saas. Do you have plans to support changes to specific SELECT queries or keep it at the row level?
    - kiwicopple 7y ago
      That's quite clever, I never actually thought of that. Guessing from your username, this is your package? It's very cool Yes, we will allow filtering on the listener, just shipping early. Next steps, we will only filter on the Primary keys - something like supabase.from('users:id.eq.1') - but eventually we will support advanced filtering like this: https://supabase.io/docs/library/get#filtering https://supabase.io/docs/library/get#filtering

1_over_n 7y ago

This is a great idea, lovely to see something with so much potential utility being turned into a functional product.

ahachete 7y ago

Just a quick precision, if I'm not mistaken: this project is not decoding the (version, architecture dependent) WAL stream as it seems to be implied by https://news.ycombinator.com/item?id=22114603 https://news.ycombinator.com/item?id=22114603 But it rather uses logical decoding with the pgoutput plugin (which was introduced in Postgres 10, so I'm guessing it doesn't support out of the box earlier versions). This approach makes total sense, though. Apart from that, reading the documentation I don't see how app.supabase will interface with my source database. Does it need to be open for connections from supabase, or is supabase also a database hosting service, or an agent is installed alongside the source database?

kiwicopple 7y ago
Yes you're right about the logical decoding, thanks for clarifying and I'll make sure I update the docs. For Supabase (when we finally have sign ups), the idea is that you can BYO postgres, or we will host. If BYO, you would have to set wal_level = logical, and create a publication for us to listen to (eg: CREATE PUBLICATION supabase_realtime FOR ALL TABLES). We will host the middleware which will give realtime and restful api. The idea is that you focus on your database schema and we handle the rest. Still early days though. We are just trying to find our feet, including figuring out whether this is even something people would want
- ahachete 7y ago
  Great. May you ellaborate on the BYO concept? How do you bring it? Or you will just ask Postgres owners to install an agent which will then connect from customers' premises to your middleware? BTW hosting your own Postgres service is a huge project on its own. Take this into consideration.
  - kiwicopple 7y ago
    For the BYO - all we need is the database connection string. We will then host an elixir server + nginx. This doesn’t need any agent on the database itself. I’ll add a diagram to to website, I think I can make this a lot clearer. > hosting your own Postgres service is a huge project Yes, you’re right. We will only do it if it looks like our potential customers are leaving because they don’t know how to do it themselves. Even then, we will just wrap an existing managed service to start with. We’d like to make Postgres as simple to use as Firebase (for newbie database users)

sheerun 7y ago

As far as I am aware none of popular managed PostgreSQL providers provide access to WAL

kiwicopple 7y ago
Not sure about the others but we are using this with AWS RDS in production. This should work with any provider that allows replication
ahachete 7y ago
As I mentioned on a comment above (https://news.ycombinator.com/item?id=22115752 https://news.ycombinator.com/item?id=22115752) it doesn't really require the WAL but rather the logical decoding stream. This is offered by some of the PostgreSQL-aaS.

zubairq 7y ago

This is pretty good. I built an open source project on GitHub that listened to table changes in Postgres a few years back and could relay them back to the UI

deleted 7y ago

[deleted]

jatins 7y ago

Interesting project! One question: Does this have any access control built in? Otherwise, anyone could change anything in database from browser.

kiwicopple 7y ago
Good question. We will be adding a static API key which is set as an ENV variable. For our hosted service we put this behind an nginx instance that has more robust access control. We’d prefer to keep it this way so the realtime server can remain as simple as possible (and completely stateless)
- jatins 7y ago
  Ah, okay! My question more on the lines of application level access control. As in how to ensure one user can only modify certain data in a table? For example assuming a social networking site, if entire database is exposed one user could update the profile of other user knowing their user ID. I think firebase used to have a concept of security rules for such things
  - kiwicopple 7y ago
    Aha, gotcha - this elixir server is for subscribing only. For all other CRUD operations I would very highly recommend PostgREST. And yes, Firebase still has that concept. You can achieve the same in postgres with Row Level Security (https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html) although admittedly it's a bit easier on Firebase.
mildmustard 7y ago
Yea, I'd be really interested in this. Part of using Firebase Realtime DB was that you can use Firebase Auth with it for access control and set rules on the database based on it.