Postgres LISTEN/NOTIFY does not scale (recall.ai)

Article summary

The article discusses how the LISTEN/NOTIFY feature in Postgres does not scale well under heavy concurrent write workloads, causing a global lock on the entire database and leading to downtime. The authors discovered this issue while investigating downtime in their Postgres database and found that the global lock was acquired during COMMIT queries when a transaction had previously issued a NOTIFY. They migrated away from LISTEN/NOTIFY and instead tracked the logic at the application layer, resolving the issue. The problem has since been fixed in the Postgres core.

Main themes

• Postgres LISTEN/NOTIFY scalability
• database-level constraints vs application-level logic
• transactional outbox pattern
• WAL-based database changes
• trigger performance and scalability
• business logic enforcement
• database locking mechanisms

What commenters say

• Some commenters suggested executing NOTIFY in its own connection outside of the transaction to avoid the global lock, but this approach may lose transactional guarantees.
• Others argued that triggers and stored procedures can be used to enforce business logic and constraints, but may have their own performance and scalability issues.
• There was disagreement on whether triggers are slow or just hide extra work, with some arguing that they can be a useful tool for enforcing constraints.
• The use of a transactional outbox pattern was proposed as a potential solution to the scalability issue, but it may require additional complexity and deduplication mechanisms.
• Some commenters noted that relying on the application layer to handle business logic can be more opaque and prone to errors than using database-level constraints.
• The idea of using Postgres's WAL for database changes was suggested as an alternative approach, potentially offering better performance and scalability.
• There was discussion on the tradeoffs between using database-level constraints and application-level logic, with some arguing that the latter can be more flexible but also more error-prone.