2026-09-27 · 8 min read
- sql
- postgresql
- notifications
LISTEN registers a PostgreSQL session for a channel, and NOTIFY schedules a small message for listening sessions. Delivery occurs after the notifying transaction commits and preserves transaction boundaries that application-level brokers may not share.
This mechanism is useful for cache invalidation, configuration refresh, and waking workers that can recover authoritative state elsewhere. It is not a durable message queue: disconnected consumers cannot replay notifications from PostgreSQL.
Define the channel and payload contract
Choose stable channel names, ownership, permissions, payload encoding, maximum accepted payload size, and versioning. Prefer a small identifier or cursor that points to database state instead of embedding a complete event.
- Keep secrets and personal data out of payloads.
- Treat channel names as SQL identifiers, not parameters.
- Version payload shapes before changing them.
- Reject malformed payloads without stopping the listener.
Own one real listener session
LISTEN state belongs to a database session. With a pool, reserve a dedicated connection for the listener or use driver support that guarantees session affinity. Commit the LISTEN transaction and keep the connection's network input loop active.
Track backend identity, database, role, channel set, last receive time, reconnect count, and driver errors. A process health check should detect a stalled socket or event loop rather than merely report that the listener task exists.
Pair signals with durable work
Write an outbox, change row, or monotonically ordered state update in the same transaction that issues NOTIFY. The notification wakes consumers; the table tells them what work exists and allows catch-up after a gap.
Use stable work identities and idempotent claims because notifications can be repeated or folded into one wake-up while several workers race. Advance a durable cursor only after the corresponding work has completed safely.
Handle queue pressure and reconnects
Long-running listener transactions can prevent notification queue cleanup. Observe queue usage, transaction age, slow consumers, connection churn, and failover. Keep callbacks short and hand substantial processing to a bounded worker queue.
After reconnect, establish LISTEN and commit it before taking the durable-state snapshot used for catch-up. Then process state newer than the saved cursor so work committed around the subscription boundary cannot disappear.
Test transaction and topology failures
Use Flashman's SQL formatter for channel and outbox queries, JSON formatter for synthetic payloads, timestamp converter for commit timelines, diff for cursor states, and UUID generator for disposable event identifiers.
Test notify commit and rollback, startup races, repeated notifications, payload boundaries, malformed messages, blocked event loops, process restart, network partitions, primary failover, pool recycling, queue pressure, permission changes, catch-up scans, and duplicate processing.