LISTEN/NOTIFY in practice: realtime features without a message broker

"The screen should update itself." It arrives in almost every custom web application brief eventually — a dispatch board that shows new orders without an F5, a warehouse screen that greys out a line the moment someone else picks it, a notification bell that means something. And the reflex answer is a new box: a broker, a realtime SaaS, a WebSocket service with its own Redis behind it.

We make that argument at the architecture level elsewhere — one section, one paragraph, the summary version. This is the long form of it, because "just use LISTEN/NOTIFY" is easy advice to give and surprisingly easy to implement in a way that quietly drops events for six months before anyone notices. The mechanism is genuinely simple. The system you build around it is where the work is.

The event already has a commit boundary — use it

Every change you want to push is a transaction that either commits or doesn't. NOTIFY is the only event mechanism in your stack that shares that boundary: subscribers are woken on commit, never before, and never at all if the transaction rolls back. No webhook, no post-save hook, no "publish after the write" in application code gets that for free — they all fire for work that might still get undone.

The simplest correct shape is a trigger that announces the row's identity and nothing else:

CREATE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify(
    'orders',
    json_build_object(
      'id', NEW.id,
      'state', NEW.state
    )::text
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_notify
  AFTER INSERT OR UPDATE OF state, assigned_to, picked_at ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Two details in there earn their place. AFTER ... UPDATE OF state, assigned_to, picked_at means a bulk UPDATE orders SET search_vector = ... doesn't wake anyone — you list the columns the UI actually reflects, and everything else stays silent. And the payload is an id plus just enough to route the message. Not the row. The 8000-byte payload cap is the reason people usually cite; the better reason is that a payload is a snapshot taken at commit time and read whenever the listener gets around to it, which is a race you don't need to have. Send "order 4182 changed", let the reader ask the database what's true now.

Why the trigger, and not the application

Doing this in application code — publish an event in the same service method that saved the row — looks cleaner and is what most teams try first. It works right up until the row changes somewhere your application isn't.

On the projects where this feature gets requested, that happens constantly. A NestJS layer writes on one path and a sync worker mirroring ERP state writes on another. A background job flips a status. A support engineer fixes a stuck row in psql at 22:00. A data migration touches ten thousand of them. A trigger is the only place in the system that sees all of those writers, because it lives below all of them. That's not a purity argument — it's the difference between a board that's live and a board that's live except when someone corrects something by hand, which is exactly when people are watching it.

One listener, not one per pod

The naive deployment has every application pod call LISTEN orders on startup. It works, and then it doesn't scale in a specific, boring way: LISTEN is a session-level thing, so a listening connection is a connection held open forever, doing nothing, and you now have pods times channels of them. Three pods and two channels is invisible. Twelve pods and eight channels is a meaningful slice of max_connections sitting idle, and every one of them wakes up on every notification only to discover the event wasn't for it.

We run a single small listener deployment instead — one process, one direct connection, whose entire job is to receive notifications and hand them to the rest of the system over the transport the app already has:

import { Client } from "pg"
 
async function listen() {
  const client = new Client({ connectionString: process.env.LISTEN_DATABASE_URL })
 
  client.on("notification", (msg) => {
    if (!msg.payload) return
    broadcast(msg.channel, JSON.parse(msg.payload))
  })
 
  client.on("error", (err) => {
    console.error("listener connection lost", err)
    client.end().catch(() => {})
    setTimeout(listen, 1_000)
  })
 
  await client.connect()
  await client.query("LISTEN orders")
  await client.query("LISTEN dispatch")
  console.log("listening")
}
 
listen()

The error handler is not boilerplate. A LISTEN connection is idle by definition, which makes it the first thing a firewall, a load balancer or a database failover drops — and node-postgres will not reconnect for you. Without that block you get a listener that runs beautifully in staging and dies silently three weeks after go-live, with the app still up and the board mysteriously frozen. Reconnect with a delay, and re-issue every LISTEN on the new connection; the subscriptions live on the session, so they die with it.

Run it as a Deployment with replicas: 1 and no autoscaler. This is one of the few workloads in our clusters where scaling out actively makes things worse.

That one process is also the one that terminates the browser connections — broadcast is an in-process fan-out to whoever is currently streaming, and the ingress routes /api/*/stream to this deployment while everything else goes to the normal app pods. It's a deliberately small service: a connection to Postgres on one side, open HTTP responses on the other, no business logic in between. Because there is exactly one replica, no client can be subscribed to a pod that didn't get the notification, and you never need sticky sessions to make that true.

The pooler breaks this before your code does

This is the one everybody hits, and it deserves more than a footnote. If your application talks to Postgres through PgBouncer in transaction pooling mode — which it should, and which CloudNativePG's Pooler resource makes the default shape of our database deployments — then LISTEN does not work. Not "works with caveats": a transaction-mode pooler hands your session's server connection to someone else the moment your transaction ends, so the connection that's registered as a listener is no longer yours, and notifications arrive at a client that isn't you.

The failure mode is the cruel kind. Nothing errors. LISTEN orders returns success. Notifications simply arrive somewhere else, sometimes, depending on which backend connection you happened to land on. Teams lose days to this because every layer reports healthy.

The fix is architectural, not a setting: the listener does not use the pooler.

# app traffic — pooled, short transactions, scales with the deployment
DATABASE_URL: postgresql://app@erp-postgres-pooler-rw:5432/erp
 
# the listener — direct to the primary, session preserved
LISTEN_DATABASE_URL: postgresql://listener@erp-postgres-rw:5432/erp

One long-lived direct connection is exactly the cost we were trying to avoid multiplying across pods, and now there's precisely one of it. It has to be the -rw service, even though the listener never writes: NOTIFY does not travel over streaming replication, so a listener parked on a replica hears nothing at all — another failure that reports perfectly healthy. Give it a role with SELECT and nothing else if that bothers you, but point it at the primary.

You can also run a second Pooler in session mode for this, and it's a legitimate answer if your platform forbids direct connections. We don't, because it adds a component to protect one connection.

Missed notifications are the design, not an edge case

Notifications are not persisted. If nobody is listening at commit time, the event is gone — there is no queue, no offset, no redelivery. Between your listener's connection dying and its reconnect one second later, every change that committed is simply never announced.

Treating that as a rare failure is the mistake. Design for it and the whole thing gets simpler, because NOTIFY stops being a message bus and becomes what it actually is: a doorbell. The truth lives in a table you can re-read.

For a board, that usually means a monotonic change log — cheap to write from the same trigger, cheap to query, trivially prunable:

CREATE TABLE order_changes (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  state    text   NOT NULL,
  at       timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX order_changes_id_idx ON order_changes (id);

The client tells the server the last change id it saw. The server replays everything after it, then goes live:

const backlog = await db
  .select()
  .from(orderChanges)
  .where(gt(orderChanges.id, lastSeenId))
  .orderBy(orderChanges.id)
  .limit(500)

Now a reconnect is a normal event rather than a data-loss event, the connection can die as often as it likes, and — the part that matters for correctness — the same change arriving twice is harmless, because every message is "row X is at version N", not "increment the counter". Idempotent by construction. If the backlog comes back at the 500-row limit, the client is far enough behind that a full refetch is cheaper than replay, and the server says so instead of paging through history.

This is also what makes the earlier advice about tiny payloads free rather than restrictive. You were never going to trust the payload anyway.

Getting it to the browser: SSE first

The transport choice is where teams reach for WebSockets by habit. For a board, a feed, a status column — anything where the server talks and the client listens — Server-Sent Events are the better default: plain HTTP, no upgrade handshake, no separate server, automatic reconnection in the browser, and a built-in Last-Event-ID header that maps onto the replay design above with no protocol of your own.

export const runtime = "nodejs"
export const dynamic = "force-dynamic"
 
export async function GET(request: Request) {
  const lastSeen = Number(request.headers.get("last-event-id") ?? 0)
 
  const stream = new ReadableStream({
    async start(controller) {
      const send = (id: number, data: unknown) =>
        controller.enqueue(
          new TextEncoder().encode(`id: ${id}\ndata: ${JSON.stringify(data)}\n\n`)
        )
 
      for (const change of await backlogSince(lastSeen)) send(change.id, change)
 
      const unsubscribe = subscribe("orders", (change) => send(change.id, change))
      const heartbeat = setInterval(() => controller.enqueue(new TextEncoder().encode(": ping\n\n")), 25_000)
 
      request.signal.addEventListener("abort", () => {
        clearInterval(heartbeat)
        unsubscribe()
        controller.close()
      })
    },
  })
 
  return new Response(stream, {
    headers: {
      "Content-Type": "text/event-stream",
      "Cache-Control": "no-cache, no-transform",
      "X-Accel-Buffering": "no",
    },
  })
}

Three lines in there exist because of infrastructure, not because of the browser. The heartbeat keeps proxies and load balancers from reaping a connection that has been silent for a slow afternoon. no-transform plus X-Accel-Buffering: no stop nginx from buffering the stream into nothing — on our ingress that's nginx.ingress.kubernetes.io/proxy-buffering: "off" and a proxy-read-timeout longer than the heartbeat, and skipping it produces the classic "it works locally, it hangs in production" bug. And runtime = "nodejs" because the listener is a TCP connection to Postgres; this is one of the routes that will never be edge, in the same way some fetches belong on the server and some don't.

Reach for WebSockets when the client genuinely needs to talk back on the same channel — collaborative editing, presence, cursors. Sending a mutation over POST and hearing about the result over SSE covers most business applications, and costs you one protocol instead of two.

On the client: patch the cache, don't refetch the world

The last mile is where a live feature gets slow. Every event triggers a refetch of the list, twenty rows change during a busy morning, and now the "realtime" board hammers the backend harder than polling would have. The event should update the cache you already have:

export function useOrderStream() {
  const queryClient = useQueryClient()
 
  useEffect(() => {
    const source = new EventSource("/api/orders/stream")
 
    source.onmessage = (event) => {
      const change = JSON.parse(event.data) as OrderChange
      queryClient.setQueryData(["orders", change.orderId], (prev) =>
        prev ? { ...prev, state: change.state } : prev
      )
      queryClient.invalidateQueries({ queryKey: ["orders", "list"], exact: true })
    }
 
    return () => source.close()
  }, [queryClient])
}

setQueryData for the row the user is looking at — instant, no request. invalidateQueries for the list, which respects the staleTime tiers we already set per data type and refetches only if the list is actually mounted. The browser's EventSource handles reconnection and resends Last-Event-ID on its own; there is no retry logic to write.

NOTIFY is not a firehose

The failure everyone eventually hits: someone imports 40,000 orders, the row-level trigger fires 40,000 times, and the listener spends four minutes forwarding events for a screen no human is reading that fast. Two mitigations, both cheap.

Coalesce at the source with a statement-level trigger, so one UPDATE produces one notification no matter how many rows it touched:

CREATE FUNCTION notify_orders_changed() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('orders', json_build_object('changed', count(*))::text)
  FROM new_rows;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_notify_stmt
  AFTER UPDATE ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION notify_orders_changed();

Combined with the change log, that's still lossless — the doorbell says "something moved", the client replays from its last id and gets every one of the 40,000. Worth knowing too: Postgres already collapses identical channel-and-payload notifications within a single transaction, which is another reason to keep payloads coarse.

Then debounce in the listener — collect ids for 100 ms and emit one batch. A human reading a dispatch board cannot perceive the difference, and it turns a burst into a single render.

The reason to bother is the one operational hazard here that isn't obvious: notifications queue in a shared, cluster-wide async buffer, and if a listening session stops draining it — a LISTEN connection stuck idle in transaction, a listener pod that's alive but wedged — that buffer fills, and then committing transactions that call NOTIFY start to block or fail, for everyone. One neglected listener can stall writes across the database. It costs nothing to watch:

SELECT pg_notification_queue_usage();

That goes on the same dashboard as the rest of the database metrics, with an alert somewhere around 0.2. It's a boring metric that sits flat at zero for years, and the one time it doesn't, it tells you precisely what is wrong — before the writes start failing and you have to work it out from the other end.

Where we'd still add the broker

None of this makes brokers wrong; it makes them a later decision, taken with numbers. We move a workload off LISTEN/NOTIFY when:

• Consumers live outside the database's blast radius. A second application, a partner integration, a Python service that shouldn't hold a connection to your primary. Notifications are an in-cluster convenience, not an integration surface.
• Delivery has to be guaranteed per consumer. The change log gives you replay for clients that ask; it doesn't give you per-consumer offsets, acks and dead-lettering. When "this event must be processed exactly once by that service" is a business requirement, that's a broker's job.
• The volume is genuinely high. Thousands of events per second, sustained, with fan-out — that's past the point where one listener connection and a shared queue is the right shape.
• We don't run the database. On a managed platform with no direct connections and an aggressive transaction pooler, fighting for a session-mode path costs more than a hosted realtime service.

For everything else — the dispatch board, the picking screen, the notification bell, the cache invalidation that keeps two pods honest — the event source is a transaction in a database you already run, already monitor and already back up. Adding a broker to carry a message across that gap buys you a second thing that can be down while the first one is fine.