Postgres Impl Listen Notify

postgres-impl-listen-notify

Quick Reference :

LISTEN / NOTIFY is PostgreSQL's built-in publish-subscribe channel. A session runs LISTEN channel to subscribe; any session runs NOTIFY channel, 'payload' (or pg_notify('channel', 'payload')) to broadcast. The server delivers the payload asynchronously to every listening session.

It is a signalling mechanism, not a message queue. Three facts decide every design :

1. Delivery is at COMMIT. A NOTIFY inside a transaction is queued and sent only when that transaction commits; a rollback sends nothing.
2. There is no replay. A LISTEN is per-connection. A client that is not connected and listening at the moment of the NOTIFY misses the event forever. Notifications are never stored or re-sent.
3. The payload is small. A payload must be a string literal under 8000 bytes. Send a row id, not the row.

Core rules :

• ALWAYS use a dedicated, direct (non-pooled, or session-mode) connection for the listening session : transaction-mode PgBouncer breaks LISTEN.
• ALWAYS treat NOTIFY as a wakeup hint; if delivery must be guaranteed, back it with a durable queue table the listener reads after each wakeup.
• ALWAYS send a key/id in the payload and let the listener fetch the data.
• NEVER LISTEN and then sit in a long-running transaction : it blocks the server's notification-queue cleanup.

When To Use This Skill :

ALWAYS use this skill when :

• Building cache invalidation, real-time UI updates, or job-queue wakeups that should not poll the database on a timer.
• Writing an AFTER INSERT/UPDATE/DELETE trigger that broadcasts changes.
• Notifications "are not arriving" or stop working after adding PgBouncer.
• A NOTIFY fails because the payload is too large.

NEVER use this skill for :

• Guaranteed, durable, replayable delivery : use a queue table (with SELECT ... FOR UPDATE SKIP LOCKED) or an external broker.
• Logical change streaming to other databases : use logical replication (postgres-impl-logical-replication).
• Cross-process locking / coordination : use advisory locks.

Decision Trees :

LISTEN/NOTIFY or a durable queue? :

Must every consumer receive every event, even after a disconnect?
├── YES -> NOTIFY alone is wrong (no replay).
│         Use a durable queue table; NOTIFY only as a low-latency wakeup
│         so workers poll the table immediately instead of on a timer.
└── NO  -> A missed event is acceptable (cache invalidation, UI refresh).
          Plain LISTEN/NOTIFY is enough. On (re)connect, the listener
          first reads current state, then relies on notifications.

Connecting through a pooler :

Does the app connect through PgBouncer (or another transaction pooler)?
├── NO  -> direct connection: LISTEN works normally.
├── YES, pool mode = session  -> LISTEN works (connection is stable).
└── YES, pool mode = transaction or statement
        -> LISTEN BREAKS: the server connection is returned to the pool
           at COMMIT, so the listener loses its registration.
        -> Open a SEPARATE direct connection (bypass the pooler) for the
           listening session. NOTIFY senders may still use the pool.

Where does the NOTIFY belong? :

What triggers the notification?
├── A specific application action -> NOTIFY / pg_notify in application code,
│      in the same transaction as the change, so it commits atomically.
└── Any write to a table, regardless of source
       -> AFTER INSERT/UPDATE/DELETE trigger calling pg_notify().
          Catches changes from every client, including manual SQL.

Patterns :

Pattern 1 : Basic LISTEN and NOTIFY

ALWAYS : subscribe with LISTEN on the connection that will consume events, and keep that connection open. NEVER : expect a notification sent before the LISTEN committed to arrive.

-- Consumer session:
LISTEN cache_invalidation;

-- Producer session (any connection):
NOTIFY cache_invalidation, 'users';

-- Stop listening:
UNLISTEN cache_invalidation;   -- one channel
UNLISTEN *;                    -- all channels

WHY : LISTEN registers the current session as a listener and takes effect at transaction commit; the registration is cleared automatically when the session ends. A session must already be listening when the NOTIFY commits, because past notifications are never replayed.

Pattern 2 : pg_notify for dynamic channels

ALWAYS : use pg_notify(channel, payload) when the channel name or payload is not a fixed literal, for example inside a trigger or a function. NEVER : try to interpolate a variable into the NOTIFY command string.

-- NOTIFY needs literal arguments; pg_notify takes ordinary expressions.
SELECT pg_notify('row_changed_' || 'orders', '42');

WHY : NOTIFY requires its channel and payload to be string literals. pg_notify(text, text) is the function equivalent and accepts any expression, which is what makes it usable from PL/pgSQL triggers.

Pattern 3 : Trigger-driven change notifications

ALWAYS : broadcast row changes with an AFTER row-level trigger that calls pg_notify, so changes from every client are caught. NEVER : put the pg_notify in a BEFORE trigger or rely only on application code (it misses manual SQL and other services).

CREATE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
  -- Send only the id; the listener fetches the row itself.
  PERFORM pg_notify('order_changed',
                    COALESCE(NEW.id, OLD.id)::text);
  RETURN NULL;                    -- AFTER trigger ignores the return value
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify_order_change
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_order_change();

WHY : the trigger fires inside the writing transaction, so the notification is queued and delivered exactly when (and only if) that transaction commits. A rolled-back change sends nothing, which keeps listeners consistent.

Pattern 4 : Send a key, never the data

ALWAYS : put a short identifier in the payload and let the listener query the current row. NEVER : pack row data or JSON documents into the payload : the limit is 8000 bytes and a NOTIFY over it fails.

-- Good: a key well under the limit.
SELECT pg_notify('order_changed', '42');

-- Bad: a full document risks exceeding 8000 bytes and is stale on arrival.
-- SELECT pg_notify('order_changed', row_to_json(o)::text);

WHY : the payload must be a string literal shorter than 8000 bytes in the default configuration. Sending only the key also avoids delivering stale data : by the time the listener reacts, the row may have changed again, so it should read the latest state itself.

Pattern 5 : Dedicated listener connection

ALWAYS : give the listening session its own long-lived connection, direct or through a session-mode pool. NEVER : run LISTEN on a connection borrowed from a transaction-mode pool.

Producers (web requests, jobs) ---> PgBouncer (transaction mode) ---> Postgres
Listener process              ---> direct connection (no pooler) ---> Postgres
                                   LISTEN order_changed;  (held open)

WHY : a transaction-mode pooler returns the server connection to the pool at every COMMIT/ROLLBACK. LISTEN is session state bound to one physical server connection, so after the next transaction the listener is silently no longer registered. A direct or session-mode connection stays bound, so the registration survives.

Pattern 6 : Consume notifications in the client

ALWAYS : keep one connection open and let the driver hand you notifications asynchronously or via a blocking wait; on each wakeup, read current state. NEVER : busy-poll the connection in a tight loop.

// node-postgres: a dedicated Client (not a pooled connection).
const client = new Client({ /* direct connection */ });
await client.connect();
client.on('notification', (msg) => {
  // msg.channel, msg.payload -> fetch the row, refresh cache, wake a worker.
});
await client.query('LISTEN order_changed');

WHY : the server pushes notifications, but client libraries surface them either as an event or through a blocking wait on the socket. The connection must stay open and dedicated to listening; sharing it with normal query traffic risks transactions that delay or hide notifications. See references/examples.md for psycopg and libpq equivalents.

Anti-Patterns :

(Full cause + symptom + fix in references/anti-patterns.md)

• LISTEN through a transaction-mode PgBouncer pool : registration lost, notifications silently never arrive.
• Treating NOTIFY as guaranteed delivery : no replay, a disconnected client misses events permanently.
• Payload over 8000 bytes : the NOTIFY fails.
• Expecting a notification from a rolled-back transaction : nothing is sent.
• A LISTEN session held inside a long-running transaction : blocks notification-queue cleanup; once full, every NOTIFY fails at commit.
• NOTIFY in a transaction that is then prepared for two-phase commit : the transaction cannot be prepared.

Reference Links :

• references/methods.md : LISTEN / NOTIFY / UNLISTEN / pg_notify syntax, delivery + ordering semantics, queue limits.
• references/examples.md : Working SQL and client-side samples (node-postgres, psycopg, libpq), version-annotated.
• references/anti-patterns.md : Anti-patterns with cause, symptom, fix-pattern.

See Also :

• postgres-impl-logical-replication : durable, replayable change streaming when missed events are unacceptable.
• postgres-impl-advisory-locks : cross-process coordination primitives.
• postgres-impl-vacuum-bloat : long-running transactions also starve VACUUM, the same root cause as a blocked notification queue.
• Vooronderzoek section : §21 (PROPOSE-ADD postgres-impl-listen-notify), §13 (PgBouncer transaction-mode session-state incompatibility).
• Official docs : https://www.postgresql.org/docs/17/sql-notify.html , https://www.postgresql.org/docs/17/sql-listen.html