The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Listen on a channelLISTEN order_events;View examples
Stop one listenerUNLISTEN order_events;View examples
Stop all listenersUNLISTEN *;View examples
Send a literal payloadNOTIFY order_events, 'order:8421';View examples
Send a dynamic payloadSELECT pg_notify('order_events', json_build_object('id', 8421)::text);View examples
Notify from a triggerCREATE TRIGGER orders_notify AFTER INSERT ON app.orders FOR EACH ROW EXECUTE FUNCTION app.notify_order();View examples
Persist an outbox eventINSERT INTO app.event_outbox (topic, aggregate_id, payload) VALUES ('order.created', 8421, '{"version":1}'::jsonb);View examples
Claim pending eventsSELECT event_id FROM app.event_outbox WHERE published_at IS NULL ORDER BY event_id FOR UPDATE SKIP LOCKED LIMIT 100;View examples
Identify this backendSELECT pg_backend_pid();View examples
List active registrationsSELECT * FROM pg_listening_channels();View examples
Measure queue occupancySELECT pg_notification_queue_usage();View examples
Inspect queue capacitySHOW max_notify_queue_pages;View examples
Find old listener transactionsSELECT pid, xact_start, state FROM pg_stat_activity WHERE backend_type = 'client backend' AND xact_start IS NOT NULL ORDER BY xact_start;View examples
Version an event hintSELECT pg_notify('order_events', json_build_object('v', 1, 'event_id', 9001)::text);View examples

LISTEN and NOTIFY provide low-latency, transaction-aware hints between sessions connected to one PostgreSQL database. They are deliberately lightweight: delivery is not durable, payloads are small, disconnected consumers miss events, and channels are not authorization boundaries. Use notifications to wake consumers that recover authoritative work from tables, keep listener transactions short, monitor the shared queue, and design reconnection, failover, and duplicate handling before production rollout.

Step by step

Detailed examples

01

Establish the listener before taking the initial snapshot

LISTEN and UNLISTEN take effect only when their transaction commits, and a transaction that used either LISTEN or NOTIFY cannot be prepared for two-phase commit. A new listener has an unavoidable startup boundary: commit LISTEN first, query the durable state in a new transaction, then treat subsequent notifications as prompts to query again. A listener receives notifications only between transactions, so long transactions increase latency and can prevent queue cleanup. Registrations disappear on disconnect and must be restored after reconnect or failover.

Register, commit, and then establish durable state
BEGIN;
LISTEN order_events;
COMMIT;

-- Start a new transaction after registration is active.
SELECT event_id, aggregate_id, payload
FROM app.event_outbox
WHERE event_id > 9000
ORDER BY event_id;

Note: The initial query can overlap with early notifications. Deduplicate by durable event_id rather than assuming exactly-once delivery.

Back to quick reference ↑
02

Publish compact commit-coupled wake-up hints

NOTIFY events created inside a transaction are delivered only if it commits. Identical channel-and-payload pairs in one transaction can be folded into one event, while distinct payloads retain their order. NOTIFY accepts only a literal payload; pg_notify is safer for computed values and should receive parameters rather than interpolated SQL. The default payload limit is under 8000 bytes. A statement-level trigger can collapse high-volume row changes into a single wake-up, while a row trigger is appropriate only when the resulting notification rate and duplicate folding are understood.

Emit a versioned key from a controlled trigger
CREATE FUNCTION app.notify_order()
RETURNS trigger
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = pg_catalog, app
AS $$
BEGIN
  PERFORM pg_notify(
    'order_events',
    json_build_object('v', 1, 'order_id', NEW.order_id)::text
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_notify
AFTER INSERT ON app.orders
FOR EACH ROW EXECUTE FUNCTION app.notify_order();

Note: A trigger firing on a logical-replication subscriber depends on its trigger replication mode. Do not assume notifications are forwarded or re-emitted across clusters.

Back to quick reference ↑
03

Pair lossy notifications with a durable transactional outbox

Notifications are not retained for disconnected clients and carry no acknowledgment or replay cursor. For work that must survive crashes, insert an immutable outbox row in the same transaction as the business change, then send only its key or a generic wake-up. Consumers resume from durable state, claim bounded batches with row locks, perform idempotent external work, and record completion. FOR UPDATE SKIP LOCKED improves worker concurrency but intentionally hides currently locked rows, so periodically retry older unprocessed events and alert on age.

Write state, outbox record, and notification atomically
BEGIN;

UPDATE app.orders
SET status = 'paid', paid_at = clock_timestamp()
WHERE order_id = 8421 AND status = 'pending';

INSERT INTO app.event_outbox (topic, aggregate_id, payload)
SELECT 'order.paid', order_id, jsonb_build_object('v', 1, 'order_id', order_id)
FROM app.orders
WHERE order_id = 8421 AND status = 'paid';

SELECT pg_notify('order_events', 'wake');
COMMIT;

Note: Add an application idempotency key or unique transition constraint so retries do not create duplicate durable events.

Back to quick reference ↑
04

Integrate notifications with the client event loop and reconnect path

A client must keep a dedicated connection alive, consume protocol input, drain all pending notifications, and avoid connection pools that silently replace or share the listener session. libpq applications wait on PQsocket, call PQconsumeInput, then drain PQnotifies and free each result. Many drivers expose equivalent APIs. Heartbeat the connection, reconnect with backoff, reissue LISTEN, repeat the durable catch-up query, and ignore a notification whose sender PID equals the listener PID only when application semantics make that safe.

Verify registrations and identify self-notifications
SELECT pg_backend_pid() AS listener_pid;
LISTEN order_events;
SELECT channel
FROM pg_listening_channels() AS channels(channel)
ORDER BY channel;

Note: LISTEN registrations are session-local and invisible to other sessions. Health checks should exercise the actual dedicated listener connection.

Back to quick reference ↑
05

Keep transactions short and monitor queue pressure

PostgreSQL stores undelivered notifications in a shared queue. A listener sitting in a long transaction can prevent cleanup; once the queue fills, notifying transactions fail at commit. For predictable performance, alert well before saturation using pg_notification_queue_usage, investigate old listener transactions, and bound publisher rate and payload size. PostgreSQL 18 exposes max_notify_queue_pages, a startup-only capacity setting; increasing it is not a substitute for fixing stalled consumers and requires version-aware deployment and restart planning.

Correlate occupancy with transaction age
SELECT pg_notification_queue_usage() AS queue_fraction;

SELECT pid, usename, application_name, client_addr, state,
       clock_timestamp() - xact_start AS transaction_age
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 20;

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name = 'max_notify_queue_pages';

Note: pg_stat_activity does not identify which sessions executed LISTEN. Correlate application_name, connection ownership, logs, and listener health telemetry.

Back to quick reference ↑
06

Treat channels as public hints within one database

Any database user can observe notifications on a known channel, and PostgreSQL provides no per-channel GRANT. Never place secrets, authorization decisions, or complete private records in payloads; publish opaque durable keys and enforce table privileges or row-level security when consumers fetch details. Quote dynamic identifiers through client APIs or avoid dynamic channels entirely. LISTEN/NOTIFY is database-local and is not carried by physical or logical replication as an application event bus. After promotion, reconnect to the new primary, re-register, and reconcile the durable outbox; evaluate behavior on every supported PostgreSQL major version.

Publish a non-secret, versioned durable key
SELECT pg_notify(
  'order_events',
  json_build_object('v', 1, 'event_id', 9001)::text
);

REVOKE ALL ON app.event_outbox FROM PUBLIC;
GRANT SELECT (event_id, topic, aggregate_id, payload)
ON app.event_outbox TO event_consumer;

Note: Column grants do not override row-level security. Test the consumer role directly and keep payload fields safe for every role that can connect to the database.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: LISTENpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: NOTIFYpostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: Asynchronous Notification with libpqpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: System Administration Functionspostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback