
Show HN: Realtime Postgres - kiwicopple
https://github.com/supabase/realtime
======
kiwicopple
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
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
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...](https://paul.copplest.one/blog/nimbus-
tech-2019-04.html#tech-stack)

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
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/tri...](https://github.com/numtel/pg-live-
select/blob/master/lib/trigger.tpl.sql#L42)

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

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

~~~
ahachete
> For the BYO - all we need is the database connection string.

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

~~~
kiwicopple
We will also provide an IP address that the user can whitelist so they don’t
have to make it completely public. But I would understand if even that is not
enough for some.

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

------
jatins
Interesting project!

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

~~~
kiwicopple
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
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
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.

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

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

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

~~~
ahachete
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.

