4 ms·

This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expen

This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Also, mitigation strategies like PgBouncer cannot be used with this approach (nor can scale out solutions like CitusDB I don't think). Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres - this has improved in 14), this would be a very viable approach.

javajosh 5y ago
>LISTEN ties up a connection Wait, what?? I can't keep doing things with my connection after I issue a LISTEN? That doesn't seem right! I would assume it would isomorphic to how unix-y bg programs will occasionally write to the console (although I see how this might be hard to deal with at the driver level). Now I will have to go check.
- gurjeet 5y ago
  You (the application) can certainly go on to issue other database commands. See the docs [1]. > With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received. [1]: https://www.postgresql.org/docs/current/sql-listen.html https://www.postgresql.org/docs/current/sql-listen.html
gurjeet 5y ago
Supabase's Realtime [1] is one of the solutions that can help with that. Although it doesn't exactly let you LISTEN at scale, but it allows the applications to be notified on changes in the database. > Listen to changes in a PostgreSQL Database and broadcasts them over WebSockets [1]: https://github.com/supabase/realtime https://github.com/supabase/realtime Disclosure: I'm a Supabase employee.
- seanwilson 5y ago
  How many connected users does this scale to roughly?
  - cpursley 5y ago
    Considering that it's Elixir/Erlang, presumably millions: https://phoenixframework.org/blog/the-road-to-2-million-websocket-connections https://phoenixframework.org/blog/the-road-to-2-million-webs...
- killingtime74 5y ago
  Does Supabase have HA or failover yet? Asked a few months ago and got no answer
- cpursley 5y ago
  I like Supabase's approach over pub/sub. One of the big advantages is they listen to the Postgres WAL which overcomes the 8000 bytes limitation[1] of the notify approach. And Elixir is especially well suited for this type of workload. I actually extracted out much of the Supabase Realtime library so that I could work with the data directly in Elixir[2] [1]: https://github.com/supabase/realtime#why-not-just-use-postgresqls-notify https://github.com/supabase/realtime#why-not-just-use-postgr... [2]: https://github.com/cpursley/walex https://github.com/cpursley/walex
runeks 5y ago
> Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Scalability is always limited, no matter which solution you choose. This article argues that the scalability limit for this particular solution is acceptable for most people to begin with: > It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.

javajosh 5y ago

>LISTEN ties up a connection Wait, what?? I can't keep doing things with my connection after I issue a LISTEN? That doesn't seem right! I would assume it would isomorphic to how unix-y bg programs will occasionally write to the console (although I see how this might be hard to deal with at the driver level). Now I will have to go check.

gurjeet 5y ago
You (the application) can certainly go on to issue other database commands. See the docs [1]. > With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received. [1]: https://www.postgresql.org/docs/current/sql-listen.html https://www.postgresql.org/docs/current/sql-listen.html

gurjeet 5y ago

Supabase's Realtime [1] is one of the solutions that can help with that. Although it doesn't exactly let you LISTEN at scale, but it allows the applications to be notified on changes in the database. > Listen to changes in a PostgreSQL Database and broadcasts them over WebSockets [1]: https://github.com/supabase/realtime https://github.com/supabase/realtime Disclosure: I'm a Supabase employee.

seanwilson 5y ago
How many connected users does this scale to roughly?
- cpursley 5y ago
  Considering that it's Elixir/Erlang, presumably millions: https://phoenixframework.org/blog/the-road-to-2-million-websocket-connections https://phoenixframework.org/blog/the-road-to-2-million-webs...
killingtime74 5y ago
Does Supabase have HA or failover yet? Asked a few months ago and got no answer
cpursley 5y ago
I like Supabase's approach over pub/sub. One of the big advantages is they listen to the Postgres WAL which overcomes the 8000 bytes limitation[1] of the notify approach. And Elixir is especially well suited for this type of workload. I actually extracted out much of the Supabase Realtime library so that I could work with the data directly in Elixir[2] [1]: https://github.com/supabase/realtime#why-not-just-use-postgresqls-notify https://github.com/supabase/realtime#why-not-just-use-postgr... [2]: https://github.com/cpursley/walex https://github.com/cpursley/walex

runeks 5y ago

> Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Scalability is always limited, no matter which solution you choose. This article argues that the scalability limit for this particular solution is acceptable for most people to begin with: > It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.