HN
Today

Postgres LISTEN/NOTIFY actually scales

A common belief is that PostgreSQL's LISTEN/NOTIFY feature doesn't scale, but this post argues it absolutely can with proper optimization. The perceived bottleneck stems from a global exclusive lock taken during NOTIFY transaction commits, serializing operations and hindering throughput. By implementing a buffered and batched approach to notifications, the authors demonstrate a 20x improvement, achieving 60K writes per second and showcasing a powerful workaround for a long-standing database challenge.

23
Score
2
Comments
#3
Highest Rank
2h
on Front Page
First Seen
Jul 24, 7:00 PM
Last Seen
Jul 24, 8:00 PM
Rank Over Time
33

The Lowdown

PostgreSQL's LISTEN/NOTIFY feature, a powerful tool for low-latency notifications, streams, and pub/sub, has long suffered from a reputation for poor scalability. This article challenges that notion, dissecting the root cause of the performance bottleneck and presenting an optimized approach that significantly enhances its capabilities.

  • The core issue lies in a global exclusive lock taken by PostgreSQL during the commit phase of any transaction that calls NOTIFY.
  • This lock is essential for guaranteeing that notifications are sent in transaction commit order, requiring serialization of transactions involving notifications.
  • This serialization prevents PostgreSQL's usual performance optimizations, such as group commit, leading to a bottleneck where transactions cannot commit faster than sequentially.
  • The authors' solution involves buffering NOTIFY calls in memory and flushing them periodically in a single batch transaction, recognizing that individual notifications often serve as mere 'pings' rather than the primary source of truth.
  • To mitigate the risk of lost notifications due to crashes with buffered data, a low-frequency polling fallback is implemented for readers.
  • Benchmarking demonstrates a massive improvement, increasing throughput from 2.9K to 60K stream writes per second, with concurrent readers maintaining 15-100ms latency, proving that the database can be saturated with work rather than contention.

This optimized strategy reframes LISTEN/NOTIFY as a viable and highly performant solution for scalable real-time applications within PostgreSQL, provided its inherent locking mechanism is intelligently circumvented.