LISTEN Carefully: How NOTIFY Can Trip Up Your Database
summary
A trigger that runs NOTIFY on every commit obtains a cluster-wide access-exclusive lock on pg_database to serialize delivery, causing surprising lock timeouts and accidental serialization under load. The team replaces immediate pg_notify with an unlogged notification_queue table, uses pg_try_advisory_xact_lock to let one sender batch and emit notifications, logs queue length in ~0.5% of transactions (modulo 199), and tests at 4x production traffic — result: lock timeouts disappear and throughput more than triples.
AI-written summary · about
chapters (12)
- 0:00Music & introduction
- 0:53Speaker background and context
- 1:35What LISTEN/NOTIFY actually does
- 3:30How publisher-subscriber flow works
- 6:36Production architecture with triggers and Redis
- 8:07Symptoms: lock timeouts and query slowdowns
- 10:02Root cause: global lock and accidental serialization
- 13:17Initial fixes and why they failed
- 14:43Queue-based async notification redesign
- 18:12Advisory locks and batched notifications
- 22:11Testing at scale and observed improvements
- 24:39Key lessons and practical takeaways
related talks
44mPushy Postgres and Python
30mCall me back, Postgres
30mQueues in PostgreSQL
48mUnderstanding and Reducing Application Contention
23mRethinking Message Queues: PostgreSQL's Role
42mAnatomy of Table-Level Locks in PostgreSQL
37mCommon Possible Preventative Measures to Avoid Locking Issue in PostgreSQL
25mWaiting for Commit