Postgres Change Notifications: LISTEN/NOTIFY, CDC, and Reliable Patterns

When applications need to react the moment data changes, Postgres change notifications provide several effective paths to near real-time alerts. Whether you are updating dashboards, sending push notifications, or orchestrating downstream jobs, choosing the right approach impacts latency, reliability, security, and cost. This guide explains the main options in PostgreSQL, shows working examples, and outlines best practices so your alerts are fast, durable, and safe.

Three main approaches to Postgres change notifications

PostgreSQL supports multiple ways to surface data changes. The most common are:

• LISTEN/NOTIFY with triggers (fast, lightweight, ephemeral)
• Outbox table plus scheduled polling (durable, simple to operate)
• Logical decoding and change data capture, CDC (scalable, enterprise-grade)

1) LISTEN/NOTIFY with row-level triggers

What it is: PostgreSQL has a built-in pub/sub mechanism. Sessions can LISTEN on a channel, and any session can NOTIFY that channel with a small text payload. Notifications are delivered after the transaction commits, ensuring you only react to committed changes. This method is ideal for low to moderate volumes and sub-second alerts within the same network boundary.

Key properties:

• Notify payload is text and limited to 8000 bytes.
• Notifications are ephemeral and not persisted. If no one is listening, the event is lost.
• Within a single transaction, identical channel notifications may be coalesced.
• Best to send small payloads (ideally only identifiers) and fetch details separately.

Schema and trigger example:

-- Example table\ nCREATE TABLE public.orders (
  id bigserial PRIMARY KEY,
  status text NOT NULL,
  customer_id bigint NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Trigger function that publishes change events
CREATE OR REPLACE FUNCTION public.notify_order_status()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- Only send on meaningful status change
  IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status) THEN
    PERFORM pg_notify(
      'order_events',
      json_build_object(
        'event', TG_OP,
        'id', NEW.id,
        'status', NEW.status,
        'at', now()
      )::text
    );
  END IF;
  RETURN NEW;
END; $$;

CREATE TRIGGER orders_notify
AFTER INSERT OR UPDATE OF status ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.notify_order_status();

Listening from a service:

-- psql example
LISTEN order_events;
-- When events arrive, psql prints: 
-- Asynchronous notification 'order_events' with payload '{...}'

In production, a small service typically maintains a persistent connection, LISTENs on the channel, parses the JSON payload, and forwards it via WebSockets, push, or email. Use a supervisor to auto-restart the listener process and reconnect on failures.

2) Outbox table plus polling (the outbox pattern)

What it is: Instead of sending NOTIFY, write a durable row to an outbox table inside the same transaction that changes business data. A background worker polls the outbox table on a schedule, dispatches notifications, and marks rows as processed. You can run this on a simple cron or a lightweight job runner.

Key properties:

• Durable: events are not lost if a consumer is offline.
• At-least-once delivery by design; you can deduplicate with a unique business key.
• Latency is tied to your polling interval and indexing quality.
• Friendly to read-only consumers: producers only need standard table DML.

Minimal schema and worker loop:

CREATE TABLE public.event_outbox (
  id bigserial PRIMARY KEY,
  topic text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);

-- In your business transaction
INSERT INTO public.event_outbox (topic, payload)
VALUES (
  'order_events',
  jsonb_build_object('event','UPDATE','id', NEW.id, 'status', NEW.status)
);

-- Worker query (runs every N seconds)
WITH next_events AS (
  SELECT id, topic, payload
  FROM public.event_outbox
  WHERE processed_at IS NULL
  ORDER BY id ASC
  LIMIT 500
)
SELECT * FROM next_events;

-- After dispatch succeeds
UPDATE public.event_outbox
SET processed_at = now()
WHERE id = any(array[...]);

To keep performance predictable, add indexes on (processed_at) and consider partitioning by time if the table grows large. For faster wakeups, you can combine this with NOTIFY: emit a notify when an outbox row is added so the worker wakes immediately, but still relies on the outbox for durability.

3) Logical decoding and CDC

What it is: Logical decoding streams row-level changes from WAL in commit order via a replication slot. With a plugin like pgoutput (built-in), or community plugins such as wal2json, you can feed downstream systems, queues, and push providers with low latency and high throughput. This is often used in conjunction with Kafka, Debezium, or custom consumers.

Key properties:

• Near real-time, ordered stream of committed changes.
• Durable until consumed: replication slot retains WAL preventing data loss (mind disk growth).
• Requires admin privileges to create slots; needs wal_level = logical.
• Scales well for multi-service architectures and cross-database fanout.

Minimal steps:

1. Ensure the server is configured with wal_level = logical and restart if you changed it.
2. Create a publication for the interesting tables.

CREATE PUBLICATION app_pub FOR TABLE public.orders;

Consumers can use pgoutput to subscribe, or employ tools like Debezium that manage replication slots and push changes downstream. When handling notifications off CDC, push only what consumers need (often an entity id and event type), then let consumers fetch details.

Which approach should you choose?

Approach	Latency	Durability	Operational complexity	Best for
LISTEN/NOTIFY	Sub-second	Ephemeral	Low	Low to medium volume, in-app reactions, quick alerts
Outbox + polling	Low to seconds (interval-based)	Durable (table-backed)	Low to medium	Auditability, retries, simple infra
Logical decoding (CDC)	Near real-time	Durable (replication slot)	Medium to high	High throughput, many consumers, event streaming

Design guidelines for robust Postgres change notifications

1) Keep payloads small

• Send minimal fields, ideally just ids and an event type. Fetch full records as needed.
• For LISTEN/NOTIFY, 8000 bytes is the absolute payload limit; staying well below improves reliability.

2) Make delivery idempotent

• Include a stable identifier (id, logical sequence, or LSN) so consumers can deduplicate.
• Persist a last-processed watermark per consumer to avoid double-processing.

3) Handle backpressure

• For LISTEN/NOTIFY, do not attempt to encode large batches in a single payload. Notify once per change or for a small group and let consumers pull details.
• For outbox, cap batch sizes and use an exponential backoff if downstream is slow.
• For CDC, monitor replication lag and WAL retention; alert if slots fall behind.

4) Respect transaction boundaries

• Triggers fire within a transaction, but NOTIFY is delivered only on commit. Test with rollbacks to ensure no phantom alerts.
• When creating compound changes, consider emitting a single event that captures the overall result rather than a flurry of row-level events.

5) Secure by design

• Restrict who can create triggers and functions. Avoid SECURITY DEFINER unless absolutely necessary and audit where used.
• Do not leak sensitive data in payloads. Enforce row-level security for follow-up fetches.
• If a listener bridges to external networks, terminate TLS and store credentials securely.

6) Test high-volume scenarios

• Simulate spikes: mass updates can generate large bursts of notifications.
• Measure end-to-end latency: database event to user-visible notification.
• Load-test with realistic payload sizes and consumer concurrency.

End-to-end example: order status alerts with LISTEN/NOTIFY

Suppose you need a real-time alert when an order transitions to 'shipped'. You can combine a lightweight trigger with a small listener service that forwards the alert to users.

1. Trigger sends a compact JSON payload to channel 'order_events' whenever status changes. (See prior trigger example.)
2. Listener service runs close to the database and forwards to a push provider or email.

# Pseudo Python (psycopg 3.x) listener
import asyncio, json, psycopg

async def main():
    async with await psycopg.AsyncConnection.connect('postgresql://...') as conn:
        await conn.execute('LISTEN order_events')
        print('listening on order_events')
        while True:
            notify = await conn.notifies.get()  # waits for events
            evt = json.loads(notify.payload)
            if evt.get('status') == 'shipped':
                # downstream call should be idempotent
                send_push(evt['id'], 'Your order has shipped')

asyncio.run(main())

Why this works well:

• Latency is sub-second: commit triggers immediate NOTIFY delivery.
• The listener has trivial logic and can be replicated for HA.
• If you need stronger guarantees, combine with an outbox table for retries.

Advanced patterns

Outbox plus NOTIFY hybrid

Write to the outbox inside the transaction and also issue a NOTIFY with just the outbox id. If a listener is online, it wakes instantly, fetches the durable outbox row, and dispatches. If offline, the outbox row remains for later processing. This balances quick wakeups with reliability.

Tenant-aware channels

For multi-tenant systems, split channels by tenant or project (for example, 'order_events_tenant_42') so listeners only receive relevant events. On the consumer side, enforce per-tenant authentication and authorization before forwarding a notification.

From CDC to fan-out

When the system grows, CDC can feed a message bus. Consumers subscribe to specific topics (orders, customers), transform events, and publish to WebSockets, email, SMS, or mobile push. Monitoring replication slots, WAL disk usage, and consumer lag is essential to keep the pipeline healthy.

Troubleshooting and common pitfalls

• No events arriving: Verify that the listener session executed LISTEN and that triggers are attached to the correct tables. Check that the transaction commits.
• Payload too large: Reduce payload fields or move details to a subsequent SELECT by id. For batch updates, emit one event per record or a compact range event.
• Duplicate deliveries: Use idempotent downstream handlers and store a processed marker keyed by event id or LSN.
• High CPU on updates: Narrow triggers to specific columns using UPDATE OF and add WHEN predicates so you only emit events when needed.
• Falling behind with CDC: Investigate slow consumers, increase parallelism, or scale infrastructure. Keep an eye on replication slot lag and WAL retention.

Cheat sheet: picking the right tool

• If you need the fastest alerts and can tolerate occasional loss when consumers are down, choose LISTEN/NOTIFY.
• If you need durable, replayable notifications with simple operations, choose outbox + polling.
• If you need high-throughput, ordered change streams across many services, choose CDC.
• For many teams, a hybrid works best: outbox for durability, NOTIFY for instant wakeups.

Pro tip: regardless of approach, structure events around business actions (order.shipped) rather than raw row diffs. This makes notifications easier to consume and less brittle as schemas evolve.

If you want a lightweight way to turn Postgres queries into mobile-friendly alerts without wiring up your own listener and push service, the iOS app Query Alert can connect with read-only access and notify you when results change.