Why can adding pg_notify() to every write transaction on a busy PostgreSQL primary cap write throughput, and how do you keep the notification cost down?
answer
- one lock for all notifiers
- held through the commit flush
- every listener in the database woken
- notify only on real change
- one coarse signal per transaction
basics
~20 sEach transaction that notifies takes one server-wide lock before committing and holds it until the commit completes, so notifying commits run one at a time. Notify only when listeners care, and send fewer, coarser signals per transaction.
solid answer
~50 sAt pre-commit, a transaction with pending notifications takes a heavyweight `AccessExclusiveLock` on a special object, database OID 0, so queue entries appear in commit order. The lock is released only after the commit finishes, including the WAL flush and any synchronous-standby wait, so **all notifying commits on the server are serialized**; waiters show as `wait_event` `object` under `Lock`. Non-notifying transactions are unaffected. In PostgreSQL 18 each notifying commit also signals every listening backend in that database that is not caught up, whatever channel it listens on. To keep the cost down: add a trigger `WHEN` clause so no-op updates do not notify, use a statement-level trigger with a constant payload so a bulk update yields one entry, rely on identical payloads folding, batch writes into fewer transactions, and keep one listener connection per application server.
code
sql · 13 linesCREATE FUNCTION notify_price_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
PERFORM pg_notify('price_changed', NEW.sku);
RETURN NULL;
END;
$$;
CREATE TRIGGER price_changed_row
AFTER UPDATE OF amount ON price
FOR EACH ROW
WHEN (OLD.amount IS DISTINCT FROM NEW.amount)
EXECUTE FUNCTION notify_price_change();go deeper
Know that NOTIFY is not free: every notifying commit does extra shared work on the server.
Explain that notifying commits take one server-wide lock to keep commit order, and that identical payloads fold within a transaction.
Diagnose Lock waits on object from notifiers, tie the hold time to commit latency, and apply conditional or statement-level triggers.
Judge when per-write signalling should give way to coarser signals or a WAL-based change stream, given write volume and latency targets.
## The lock every notifier takes When a transaction that has executed `NOTIFY` or `pg_notify()` reaches pre-commit, it acquires a **heavyweight `AccessExclusiveLock` on a special object: database OID 0**. The lock guarantees that queue entries appear in commit order, so an uncommitted transaction can never sit ahead of a committed one and block delivery. It is taken before the commit record is written and released only once the commit has completed, which includes: - the WAL flush of the commit record whenever `synchronous_commit` requires one; - any wait for a synchronous standby to confirm the commit. The consequence is that **all notifying commits on the server run one at a time**, across every database. Concurrent transactions that do not notify are not affected. A waiting notifier shows in `pg_stat_activity` with `wait_event_type` `Lock` and `wait_event` `object`, and in `pg_locks` as a lock of locktype `object`. ## Why it caps throughput Ordinary concurrent commits overlap: several backends can be inside their commit at once and their WAL flushes can be combined. Notifying commits cannot overlap, because each one waits for the previous notifier's entire commit, flush and standby round trip included. Notifying throughput therefore tends towards **one commit per commit latency**. Fast local storage gives a high ceiling; a synchronous standby across a network lowers it sharply, and every notifier above the ceiling queues on the lock while holding its own row locks. ## Costs on the listening side In PostgreSQL 18, after each notifying commit the server signals **every listening backend in that database** that is not already caught up, whatever channels it listens on. Each woken backend reads the new entries and filters by channel itself. Listeners in other databases are signalled only when they fall far behind. So: 1. Many listener connections multiply the wake-ups per commit. 2. Many notifications per transaction mean more queue pages to write and read. 3. Large payloads fill queue pages faster. ## Keeping the cost down | Technique | Effect | |---|---| | `WHEN (OLD.amount IS DISTINCT FROM NEW.amount)` on a row trigger | no-op updates send nothing, so they take no lock unless something else notifies | | Statement-level trigger with a constant payload | one queue entry per transaction instead of one per row | | Identical payloads within a transaction | folded into one automatically | | Batching price updates into fewer transactions | fewer serialized notifying commits | | One listener connection per application server | fewer wake-ups per commit | | Payload = key, never the whole row | smaller queue entries | Coarser signals trade precision for cost: a listener that receives `batch` instead of a SKU has to reload more of its cache. For cache invalidation that is usually the right trade, because a reload on the application side is cheap compared with serializing every write on the database. ## When it is the wrong tool If the change stream is high volume and every change matters to a consumer, notifying from each write is the wrong shape. Change streams read from WAL through logical decoding do not take this per-commit lock; that mechanism belongs to replication. `NOTIFY` stays the right tool for low-rate wake-ups such as *prices changed, refresh your cache*. ## Telling it apart from a full queue This bottleneck and a full notification queue are easy to confuse, but their evidence differs: - **Lock serialization:** commit latency of notifying transactions rises under concurrency, sampled `pg_stat_activity` rows show `Lock` / `object` waits, and `pg_notification_queue_usage()` stays near zero. - **Full queue:** commits fail outright with SQLSTATE `54000`, the log shows the `NOTIFY queue is N% full` warning, and usage is high because a listener stopped reading. A quick experiment confirms the first: run the same write load with the notifying trigger disabled and compare commit rates. ## What a strong answer contains - The lock on database 0, taken at pre-commit and held through the commit. - That it serializes notifiers across the whole server, not per channel or per database. - That flush latency and synchronous replication lengthen each hold. - That 18 wakes every listener in the database per notifying commit. - Concrete mitigations: conditional triggers, statement-level triggers, folding and batching. A candidate who answers *NOTIFY is just a message, it costs nothing* has not run it under write load.
- Does the PostgreSQL notify commit lock block transactions that never call NOTIFY?No. Only transactions with pending notifications take it at pre-commit, so ordinary commits proceed in parallel. The bottleneck is limited to the notifying subset, but if every write path fires a notifying trigger, that subset is all writes.
- Why does a synchronous standby make the PostgreSQL notify lock hurt more?The lock is released only after the commit completes, and with synchronous replication the commit waits for the standby's confirmation. Every notifying transaction therefore holds the server-wide lock for a network round trip, and the next notifier waits behind it.
- Each channel has its own lock, so notifiers on different channels never wait.
- The notify lock is held only while entries are copied into the queue.
- In PostgreSQL 18 a commit wakes only listeners on the notified channel.
- Switching pg_notify() to the NOTIFY statement removes the commit serialization.
- Raising notify_buffers lets notifying commits run in parallel.
Assumes PostgreSQL 18.6; how listeners are woken is an implementation detail that the unreleased 19 beta narrows to sessions listening on the notified channel.