Ask HN：你们在生产环境中如何使用/使用过PostgreSQL的LISTEN/NOTIFY功能？
1 分•作者: JoelJacobson•10 个月前
最近，Hacker News 上有一个帖子：
“Postgres LISTEN/NOTIFY 无法扩展”，
https://news.ycombinator.com/item?id=44490510
我们目前正在努力改进 PostgreSQL 中 LISTEN/NOTIFY 的可扩展性。为了指导这项工作，我想更好地了解它在实际系统中的使用方式（或曾经的使用方式）。哪些方面表现良好？哪些方面不尽如人意？
目前的实现存在一些已知的可扩展性瓶颈：
1. 惊群问题：
NOTIFY 会唤醒当前数据库中 <i>所有</i> 监听的后端，即使是那些没有监听通知通道的后端。当许多监听器各自监听自己的通道时（例如在作业队列中），这会降低效率。
2. 提交锁争用：
NOTIFY 操作在事务提交时会通过一个重量级锁进行序列化。当许多事务并行发送通知时，这可能会成为瓶颈。
如果您在生产环境中使用过 LISTEN/NOTIFY，我很乐意听到：
* 您的用例是什么？
* 每个客户端是否监听自己的通道，或者它们是否共享通道？
* 有多少个监听后端进程？
* 有多少个并行 NOTIFY？
* 您是否发送有效载荷？如果是，有多大？
* 什么对您来说效果很好？什么不好？
* 您是否遇到了任何可扩展性限制？
非常感谢您的反馈！
/Joel
查看原文
Recently there was an HN thread:
"Postgres LISTEN/NOTIFY does not scale",
https://news.ycombinator.com/item?id=44490510<p>We're now working on improving the scalability of LISTEN/NOTIFY in PostgreSQL, and
to guide that work, I'd like to better understand how it's used (or was used) in
real-world systems. What works well? What doesn't?<p>The current implementation has some known scalability bottlenecks:<p>1. Thundering Herd Problem:
A NOTIFY wakes up <i>all</i> listening backends in the current database, even those
not listening on the notified channel. This is inefficient when many
listeners are each listening to their own channels (e.g. in job queues).<p>2. Commit Lock Contention:
NOTIFY operations are serialized behind a heavyweight lock at transaction commit.
This can become a bottleneck when many transactions send notifications in parallel.<p>If you've used LISTEN/NOTIFY in production, I'd love to hear:<p>- What is/was your use case?<p>- Does each client listen on its own channel, or do they share channels?<p>- How many listening backend processes?<p>- How many NOTIFYs in parallel?<p>- Are you sending payloads? If so, how large?<p>- What worked well for you? What didn't?<p>- Did you hit any scalability limits?<p>Feedback much appreciated, thanks!<p>/Joel