LISTEN / NOTIFY - wake workers without polling the table

PostgreSQL can push a wake-up to sessions that asked for it. LISTEN channel registers interest. NOTIFY channel, ‘payload’ signals listeners — but only after the notifying transaction commits. That is not a message bus with durable history. It is a doorbell on top of your real table of work.

The problem: poll tax

Naive worker loop:

loop:
claim one pending job   -- SELECT … FOR UPDATE SKIP LOCKED
if none: sleep 200ms
else: process

When jobs are rare, you still wake the DB on a timer. With a uniform random phase, detection delay averages about half the poll interval. Lab C (analytical, 30 draws, 200 ms interval): median detect 102 ms (theory ~100). That is pure latency tax — and load — before any useful work.

The doorbell: LISTEN + NOTIFY

-- Worker session (stays open)
LISTEN til_jobs;

-- Writer / API session (same database)
BEGIN;
INSERT INTO jobs(status) VALUES ('pending');
NOTIFY til_jobs, '1';   -- payload optional, shorter than 8000 bytes by default
COMMIT;                -- listeners hear it HERE, not at NOTIFY time

Worker still claims with Day 38 after the wake. NOTIFY does not hand you the row. It says “look again.”

Commit barrier (the non-obvious rule)

Docs: signals are delivered to listeners only at transaction commit. Rollback discards them. Duplicate NOTIFY on the same channel + payload inside one transaction coalesces to one delivery.

Scenario (lab, 30 runs)	Expected	Result
BEGIN; NOTIFY; COMMIT	≥1 async notification	30/30 (median count 1)
BEGIN; NOTIFY; ROLLBACK	0 notifications	30/30 (median count 0)
Two identical NOTIFY in one txn	1 delivery (coalesce)	30/30 exactly 1

The recorded harness used a helper inside til-postgres; those harness files are not included in this repository. The listener session must stay connected after LISTEN — exiting psql drops the registration.

Diagram: doorbell vs mailbox

NOTIFY vs a real queue

Capability	LISTEN / NOTIFY	Table + SKIP LOCKED (Day 38)	External queue (Day 14)
Durable history	No — fire and forget wake	Yes — rows	Yes — broker log/queue
If no listener	Signal lost	Row waits	Message waits
Payload	Shorter than 8000 bytes	Full row / JSON	Broker limits
Cross-DB / multi-region	Same database only	Same DB	Designed for fan-out
Best use	Wake local workers	Claim work safely	Service boundaries

Race rule (docs pattern)

1. LISTEN and commit that (autocommit is fine).
2. Inspect the table for work that already exists.
3. Then wait for notifications for work that arrives later.

If you only LISTEN and sleep, a job inserted just before listen can sit until the next event — or forever if nothing else notifies. Combine with a slow safety poll if you must, but the design center is: table is truth, notify is spice.

Session lifetime and UNLISTEN

LISTEN is session state. It ends when the connection closes, the session runs UNLISTEN channel / UNLISTEN *, or you start a new session from a pool checkout that is not the listener. Do not put the listener on a short-lived request-scoped client from a connection pool — pin a dedicated process (or one sticky connection) whose only job is to wait and then kick claim loops.

LISTEN til_jobs;
-- … receive notifies …
UNLISTEN til_jobs;   -- or UNLISTEN *

Payload and queue pressure

• Payload is optional text and must be shorter than 8000 bytes in the default configuration. Prefer a job id: NOTIFY til_jobs, ‘42’, body in the row.
• Server keeps pending notify data in a queue bounded by max_notify_queue_pages. If listeners are slow or stuck, the queue can fill — transactions that NOTIFY then fail. That is another reason not to treat notify as bulk data transport.
• After a wake, drain the table (loop SKIP LOCKED) until empty; one coalesce may cover many inserts.

Worked sequence (writer + two workers)

Step	Writer	Worker A / B
1		Both LISTEN til_jobs (sessions stay up)
2		Scan once for existing pending (race rule)
3	BEGIN; INSERT job; NOTIFY; COMMIT	Both receive one async notification
4		Both run claim; SKIP LOCKED → one wins the row
5	Many inserts, one txn, many NOTIFY same payload	Often one wake (coalesce) — still loop until no rows

Lab detail

The recorded harness held an interactive psql after LISTEN, ran notifier SQL on a second backend, then counted lines containing Asynchronous notification. That matches how humans see notifies in psql. Application drivers expose the same events through their async notification APIs — still session-scoped, still after commit.

Anti-patterns

• Using NOTIFY as the job store — no replay, no SKIP LOCKED, lost if nobody listens.
• Huge payloads — put the id in the payload; keep the body in a row.
• Assuming delivery before COMMIT — other sessions must not see uncommitted notifies.
• One connection shared with a pool checkout — LISTEN is session state; use a dedicated long-lived connection for the listener.
• Ignoring transaction coalescing — two notifies in one txn may become one wake; still re-scan the table.

How it connects

• Day 14: external queues when you need durability across services.
• Day 38: claim rows after the wake — still the concurrency primitive.
• Day 30: wakes can double; handlers stay idempotent.
• Day 45: lock budgets on the claim path; notify does not replace locking.
• Day 44: backpressure is about slowing producers; notify is about timely consumers.

Interview version (60s)

“I store jobs in Postgres and claim with FOR UPDATE SKIP LOCKED. To avoid polling, workers LISTEN on a channel and the writer NOTIFYs after insert — delivery is at COMMIT, rollbacks are silent, and duplicate notifies in one txn coalesce. If no session is listening, the signal is gone, so the table remains source of truth. Lab: 30/30 commit delivers, 30/30 rollback does not, 30/30 coalesce to one. Polling every 200 ms still costs ~100 ms median detect delay even when the DB is idle.”

Quiz

Questions? Ask the agent — continue with Day 49 (Transactional Outbox). Dedy lives from Day 23 (prep inventory only here).