问HN：你是如何在生产环境中使用PostgreSQL的LISTEN/NOTIFY功能的？
最近有一个HN讨论主题：“Postgres的LISTEN/NOTIFY不具备可扩展性”，
链接：https://news.ycombinator.com/item?id=44490510
我们正在努力改善PostgreSQL中LISTEN/NOTIFY的可扩展性。为了指导这项工作，我希望更好地了解它在实际系统中的使用情况（或曾经的使用情况）。哪些方面运作良好？哪些方面存在问题？
当前的实现存在一些已知的可扩展性瓶颈：
1. **惊群效应**：
一个NOTIFY会唤醒当前数据库中**所有**正在监听的后端，即使这些后端并没有监听被通知的频道。当许多监听者各自监听自己的频道（例如，在作业队列中）时，这种做法效率低下。
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