Show HN: Realtime Postgres
github.com
github.com
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.
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)
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?
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
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?
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
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.
> 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)
Then this means that the database should be publicly accessible. Many would not accept this from a security perspective (regardless of the use of SSL and even certificate authentication only).
That’s the beauty of opensource though. If we end up just building a useful product that everyone hosts themselves, I’m happy with that.
Btw, these are great questions. Thanks - they will really help me to improve how I have explained it on the website
One question: Does this have any access control built in? Otherwise, anyone could change anything in database from browser.
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)
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
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) although admittedly it's a bit easier on Firebase.