zlacker

For pub/sub, I would recommend against using PostgreSQL if you're doing it at any kind of scale because LISTEN ties up one connection completely and Postgres connections are very expensive compared to a redis connection.

>>pilif+(OP)
I was looking for your comment here, thank you. I hope they solve this problem somehow in a future release.

>>pilif+(OP)
The other issue with LISTEN/NOTIFY is that a client will miss any notifications sent while it is not running (e.g. due to crash, update, etc.). An alternative would be logical replication and change data capture (CDC), as provided for Postgres by Debezium for instance. That way, any consumers won't miss events during downtimes, as they'll continue to read from the replication slot from the last offset they had processed.

Disclaimer: I work on Debezium

>>gunnar+I
isnt this also the case for redis pubsub?

>>gunnar+I
Could you explain a little more about this (Debezium is awesome btw)? So after creating the proper replication slots, you set up change data capture via Debezium, then you listen to the CDC stream from the original DB you are connected to, correct?

This sounds like it might be vulnerable to dead consumers causing WAL to pile up without the right settings/periodic checks.

>>superm+a2
I'd need to check, but I think so.

Which is probably why if you don't want loss if consumers go down a proper queue system (RabbitMQ, ActiveMQ, Amazon SQS, Kafka if you don't care about ordering between partitions) is the way I'd go.

>>gunnar+I
This is by design and very common with pub/sub.

>>gunnar+I
Then use streams.

>>superm+a2
For pub/sub yes that's correct. For full info though: Redis later added streams (in 5.x) for the don't-wan't-to-miss case: https://redis.io/topics/streams-intro

>>gunnar+I
then what's the point of this feature as part of the db, if it doesn't do persistance?

>>hardwa+d3
> then you listen to the CDC stream from the original DB you are connected to, correct?

Yes, exactly.

> This sounds like it might be vulnerable to dead consumers causing WAL to pile up without the right settings/periodic checks.

There are some subtleties around this indeed. You should monitor backlog of replication slots, so to identify inactive consumers as you say. Also, there are some corner cases you need to take care of: when listening to changes from a low-traffic database on the same PG host that also has a high-traffic database, the replication slot may not be acknowledged often enough. Debezium mitigates this by optionally writing changes to a dummy heartbeat table in the low-traffic database, so to advance the replication slot.

In Postgres 13 there's also a new option "max_slot_wal_keep_size" which limits WAL size retained by a replication slot. This prevents unbounded WAL growth, at the risk of consumers to miss events if they are down for too long.

All in all, proper monitoring and alerting is key.

>>pilif+(OP)
> LISTEN ties up one connection completely

I've seen this twice in this thread, but I don't know what that means. Can you explain a bit?

>>postgr+pZ
When a client connects to Postgres, Postgres creates a new process just for that client. Separate processes are great for isolation but it means Postgres connections are a bit expensive (this is an active area of improvement). Postgres really starts to struggle once you a have a few thousand connections, even if those connections aren’t doing anything.

The common workaround is to use a connection pooler like PGBouncer so that clients reuse connections. This approach doesn’t work for LISTEN because typically a client will listen for its entire lifecycle so you can’t share connections in a pool.

>>sa46+e91
You need one connection per worker thread, and realistically you would only have one worker per cpu core. So how many LISTENing connections do you really need?

>>taffer+Nf1
You generally have more than one database connection per thread. As an example, consider Node.js which acts like a single threaded process. You’d probably want to be able to handle more than 1 database query concurrently since network latency tends to dominate OLTP requests.

How you setup LISTEN and NOTIFY is app dependent. In a multi-tenant database, you could have 1 NOTIFY channel per tenant.

As you scale, you probably do something that listens in a smarter way, maybe 1 Go channel per client with a single LISTEN instead of 1 database connection per client. The downside is that now the app code is responsible for tenant isolation instead of the database.

>>sa46+e91
> This approach doesn’t work for LISTEN because typically a client will listen for its entire lifecycle so you can’t share connections in a pool.

But you only need one connection for LISTEN per database, total. So I'm confused why this is made out to be a big issue.

>>sa46+nm1
> [...] since network latency tends to dominate OLTP requests.

You are absolutely right about that. However, in this case we are talking about background workers. I would argue that background workers, such as an image resizing worker, are typically CPU-bound and do not perform high-volume OLTP queries. Therefore, a background worker does not require multiple database connections per thread as a web server would.