Postgres LISTEN/NOTIFY actually scales
4 hours ago
- LISTEN/NOTIFY has a reputation for not scaling due to a global lock, but it can be optimized for high throughput.
- Basic streaming design uses a streams table with LISTEN/NOTIFY for low-latency notification instead of polling.
- Initial implementation with trigger on NOTIFY bottlenecked at 2.9K writes/sec because of an exclusive lock during commit.
- The exclusive lock serializes commits of transactions with NOTIFY, preventing group commit and causing poor performance.
- Optimization buffers notifications in memory and flushes them in batches, reducing lock contention.
- A fallback polling mechanism is added to handle potential loss of notifications from process crashes.
- Benchmark shows 60K writes/second on a single Postgres server with 15-100ms latency, a 20x improvement.
- All benchmark code is available on GitHub, and DBOS offers tools for durable execution with Postgres.