LISTEN/NOTIFY: transactional wake-up calls

The outbox relay polls its table; polling trades latency for load. PostgreSQL ships the fix: NOTIFY — a pub/sub doorbell built into the database and, the part that matters for this chapter, wired into transactions. The manual: "if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed". A notification can never announce data that isn't committed yet — or data that never committed at all.

A listener you can see

One honest wrinkle: Bun's SQL client can't receive notifications, so the listener in this scenario is a psql subprocess — the same client you'd use to eavesdrop on a channel in production. It also demonstrates a real property of the protocol: the client only notices notifications when it talks to the server, hence the periodic poke:

/**
 * Bun.sql has no async-notification API, so the listener is a psql subprocess —
 * the same client you'd use to eavesdrop on a channel in production.
 * ponytail: assumes the docker-compose stack from this repo (run from its root).
 */
class Listener {
  private proc = Bun.spawn(
    ["docker", "compose", "exec", "-T", "postgres", "psql", "-U", "postgres", "-X", "-q", "-A", "-t"],
    { stdin: "pipe", stdout: "pipe" },
  );
  private out = "";

  static async start(channel: string): Promise<Listener> {
    const l = new Listener();
    l.drain();
    l.proc.stdin.write(`LISTEN ${channel};\nSELECT 'listening';\n`);
    const deadline = Date.now() + 15_000;
    while (!l.out.includes("listening")) {
      if (Date.now() > deadline) throw new Error("psql listener failed to start");
      await Bun.sleep(25);
    }
    return l;
  }

  private async drain() {
    for await (const chunk of this.proc.stdout) this.out += new TextDecoder().decode(chunk);
  }

  /** psql only checks for notifications around command execution — poke it, then look. */
  private poke() {
    this.proc.stdin.write("SELECT 1;\n");
  }

  /** Wait for the next notification psql prints. */
  async next(): Promise<{ channel: string; payload: string; pid: number }> {
    const deadline = Date.now() + 10_000;
    while (Date.now() < deadline) {
      const m = this.out.match(
        /Asynchronous notification "(\w+)" with payload "([^"]*)" received from server process with PID (\d+)\./,
      );
      if (m) {
        this.out = this.out.slice(m.index! + m[0].length);
        return { channel: m[1]!, payload: m[2]!, pid: Number(m[3]) };
      }
      this.poke();
      await Bun.sleep(50);
    }
    throw new Error("no notification arrived within 10s");
  }

  /** Assert that nothing has been delivered — poke for a while and check. */
  async nothingYet(ms = 700): Promise<void> {
    const deadline = Date.now() + ms;
    while (Date.now() < deadline) {
      this.poke();
      await Bun.sleep(50);
    }
    if (this.out.includes("Asynchronous notification")) {
      throw new Error(`expected silence, got: ${this.out}`);
    }
  }

  stop() {
    this.proc.kill();
  }
}

Three claims, one run

const listener = await Listener.start("orders");
t.note("A psql process is attached and ran LISTEN orders; — a separate client, not one of our sessions.");

const [me] = await A`SELECT pg_backend_pid() AS pid`;

await A`BEGIN`;
await A`INSERT INTO orders VALUES (1, 'alice')`;
await A`NOTIFY orders, 'order 1 placed'`;
await listener.nothingYet();
t.note("The listener hears nothing — the notification is queued inside A's transaction.");

await A`COMMIT`;
const n = await listener.next();
eq(n.channel, "orders");
eq(n.payload, "order 1 placed");
eq(n.pid, Number(me!.pid)); // sent by A's backend
t.note(
  `The listener wakes up: Asynchronous notification "orders" with payload "order 1 placed" received from server process with PID pid(A).`,
);

t.note("A rolled-back NOTIFY never happened at all:");
await A`BEGIN`;
await A`NOTIFY orders, 'order 2 placed'`;
await A`ROLLBACK`;
await listener.nothingYet();
t.note("Silence — order 2's notification died with its transaction.");

t.note("Identical notifications in one transaction are de-duplicated:");
await A`BEGIN`;
await A`NOTIFY orders, 'order 3 placed'`;
await A`NOTIFY orders, 'order 3 placed'`;
await A`COMMIT`;
const once = await listener.next();
eq(once.payload, "order 3 placed");
await listener.nothingYet();
t.note("Two NOTIFYs went in, one notification came out.");

listener.stop();

A psql process is attached and ran LISTEN orders; — a separate client, not one of our sessions.

A> SELECT pg_backend_pid() AS pid;
  pid   
--------
 pid(A) 
(1 row)

A> BEGIN;
BEGIN

A> INSERT INTO orders VALUES (1, 'alice');
INSERT 0 1

A> NOTIFY orders, 'order 1 placed';
NOTIFY

The listener hears nothing — the notification is queued inside A's transaction.

A> COMMIT;
COMMIT

The listener wakes up: Asynchronous notification "orders" with payload "order 1 placed" received from server process with PID pid(A).

A rolled-back NOTIFY never happened at all:

A> BEGIN;
BEGIN

A> NOTIFY orders, 'order 2 placed';
NOTIFY

A> ROLLBACK;
ROLLBACK

Silence — order 2's notification died with its transaction.

Identical notifications in one transaction are de-duplicated:

A> BEGIN;
BEGIN

A> NOTIFY orders, 'order 3 placed';
NOTIFY

A> NOTIFY orders, 'order 3 placed';
NOTIFY

A> COMMIT;
COMMIT

Two NOTIFYs went in, one notification came out.

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The de-duplication is documented behavior, not an accident: "If the same channel name is signaled multiple times with identical payload strings within the same transaction, only one instance of the notification event is delivered to listeners".

A few caveats before you lean on it. NOTIFY is a doorbell, not a mailbox: a notification goes to sessions listening right now, so a relay that was down while the doorbell rang must still find the event afterwards. That's the whole reason the combo is outbox + NOTIFY — the outbox row is the durable fact, and the notification only says "check the outbox", its payload can even be empty.

Two more edges are worth knowing. Delivery waits for COMMIT, so notifications from a long transaction arrive late; the manual's advice matches chapter 4's, that "applications using NOTIFY for real-time signaling should try to keep their transactions short". And LISTEN and two-phase commit don't mix, a preview of the next-door lesson: "A transaction that has executed LISTEN cannot be prepared for two-phase commit".

Put together, that makes NOTIFY the outbox relay's wake-up call rather than its memory: fire it in the same transaction that writes the outbox row, LISTEN in the relay, and fall back to polling when no one is listening. The notification itself is disposable — it rings for whoever is home and then vanishes — so every durability guarantee stays in the outbox table, exactly where the previous lesson put it.

Further reading

• PostgreSQL docs: NOTIFY
• PostgreSQL docs: LISTEN
• MySQL has no LISTEN/NOTIFY — its outbox lesson covers polling and binlog CDC instead