patternsqlpostgresqlTip
LISTEN/NOTIFY for lightweight real-time events between processes
Viewed 0 times
LISTEN NOTIFYpg_notifypubsubreal-time eventstrigger notificationasync
Problem
Polling a database table repeatedly to detect new rows or state changes wastes connection time and adds unnecessary load. Many developers do not know PostgreSQL has built-in async pub/sub.
Solution
Use LISTEN/NOTIFY for event-driven communication:
-- Publisher (any session or trigger):
NOTIFY channel_name, 'optional payload up to 8000 bytes';
-- From a trigger:
CREATE OR REPLACE FUNCTION notify_new_order() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('orders', row_to_json(NEW)::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_notify_order
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();
-- Subscriber (separate connection):
LISTEN orders;
-- Then call PQnotifies() in libpq or equivalent in your driver
-- Publisher (any session or trigger):
NOTIFY channel_name, 'optional payload up to 8000 bytes';
-- From a trigger:
CREATE OR REPLACE FUNCTION notify_new_order() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('orders', row_to_json(NEW)::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_notify_order
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();
-- Subscriber (separate connection):
LISTEN orders;
-- Then call PQnotifies() in libpq or equivalent in your driver
Why
pg_notify delivers messages asynchronously over existing database connections. It is transactional: notifications are only sent if the transaction commits, preventing phantom events from rolled-back transactions.
Gotchas
- Payload is limited to 8000 bytes; send an ID and let the subscriber fetch details
- Notifications are not persisted; a subscriber that is down misses events
- The subscriber must be in an idle state between LISTEN calls to receive events; connection poolers in transaction mode break this
- Use pg_notification_queue_usage() to monitor backpressure
Code Snippets
NOTIFY with JSON payload and queue monitoring
-- Check pending notification queue pressure:
SELECT pg_notification_queue_usage();
-- Send a notification manually:
SELECT pg_notify('cache_invalidate', '{"table":"products","id":42}');Context
When building lightweight real-time pipelines or cache invalidation systems backed by PostgreSQL
Revisions (0)
No revisions yet.