"PostgreSQL LISTEN/NOTIFY: Realtime Backend Without a Queue"
"The pg_notify trigger websocket realtime push pattern — trigger JSON payloads, Node and Python listeners, dedicated connections, and reconnect handling."
You need "when a row changes, push it to connected clients" — and the usual answer is to bolt on Redis Pub/Sub or Kafka. But Postgres already ships a built-in pub/sub primitive: LISTEN / NOTIFY . A trigger fires pg_notify() on row changes, your backend holds one listening connection, and it pushes events to browsers over WebSocket. This is the pg_notify trigger websocket realtime push pattern, start to finish: the trigger, the Node.js listener, the Python variant, and the production rules (dedicated connection, reconnects, payload limits) that separate a demo from a system. Last updated: October 4, 2026 What you'll learn Why LISTEN/NOTIFY beats polling for realtime updates Writing a trigger that sends JSON payloads with pg_notify() A Node.js listener ( pg client) that pushes to browsers over WebSocket The Python/FastAPI listener variant (psycopg/asyncpg) Why the LISTEN connection must be dedicated (and breaks under PgBouncer transaction mode) Reconnect handling and the read-then-subscribe race Reliability limits: 8000-byte payloads, commit-time delivery, no listener = lost message Why Not Polling: NOTIFY vs Polling Realtime Updates Polling is SELECT * FROM orders WHERE updated_at > $1 every 2 seconds per client. It works until it doesn't: with 500 connected clients you're running 15,000 queries a minute to mostly learn "nothing changed", and every poll is a round trip that competes with real workload. NOTIFY inverts it — the database tells *you* when something changed, so idle clients cost zero queries. Latency drops from the poll interval to milliseconds, and database load becomes proportional to actual change, not to client count. If your "realtime" feature currently polls, this is the upgrade. Step 1 — The Trigger: pg_notify with a JSON Payload…