PostgreSQL LISTEN/NOTIFY — How to deliver real-time events all the way to the client using just one DB, without Redis

A while back, our team needed to add a real-time order status notification feature. At first, introducing Redis Pub/Sub seemed like the obvious move. But when we thought it through — Redis cluster operations, incident response, monitoring setup — we realized that adding a single feature would mean spinning up an entirely new piece of infrastructure. That's when the thought hit: "Wait, doesn't PostgreSQL already have Pub/Sub?" We started digging in, and ultimately never added Redis.

PostgreSQL LISTEN/NOTIFY is getting renewed attention alongside the "You Don't Need Redis, Postgres Already Has Pub/Sub" movement. The key differentiator is that it implements Pub/Sub channels inside the DB itself with no separate message broker, and if a transaction rolls back, the event is cancelled along with it. This article covers how LISTEN/NOTIFY works, the patterns for wiring it to SSE or WebSocket, and — honestly — where its limits lie.

Core Concepts

How It Works in Three Lines

LISTEN/NOTIFY is straightforward. It breaks down into three steps.

• LISTEN channel — A client (application) subscribes to a specific channel name. That DB connection stays in a waiting state until a notification arrives.
• NOTIFY channel, 'payload' — A sender (another transaction, a trigger, or another application) posts a message to the channel. The critical point is that delivery only happens when the transaction commits. If it rolls back, the NOTIFY is treated as if it never happened.
• pg_notify('channel', 'payload') — The function-form version of NOTIFY. It's essential inside triggers when you need to dynamically construct the channel name or build a JSON payload.

The "delivery only on transaction commit" part initially felt like a constraint, but it's actually a strength. Redis Pub/Sub handles DB transaction outcomes and event delivery independently, which means a message can go out even after a DB rollback. With LISTEN/NOTIFY, the event is never published on rollback, so clients can never receive a notification about an invalid state.

Transaction and Event Timing

Here you can see both scenarios at a glance — a commit delivers the notification, a rollback cancels it.

Automating with Triggers

You can emit events automatically using only a DB trigger, without touching application code. This pattern is the cleanest in production.

-- Applies only to INSERT/UPDATE, so NEW always exists.
-- To handle DELETE events, reference OLD instead of NEW.
CREATE OR REPLACE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify(
    'orders_channel',
    json_build_object(
      'op', TG_OP,
      'id', NEW.id,
      'status', NEW.status
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_after_change
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

It's best to put only the ID and minimal metadata in the payload. The hard limit is 8,000 bytes, so trying to pack the full record into the payload will hit that ceiling fast. In practice, many teams send only a signal that "this record changed" along with the ID, and have subscribers re-query the DB directly for the actual data.

Key Libraries by Language

Practical Application

The Architecture Big Picture First

Understanding the overall flow before looking at code makes everything much easier to follow.

The backend maintains a single dedicated connection to PostgreSQL in a LISTEN wait state, and when an event arrives it pushes it down to connected clients via SSE or WebSocket. Keeping this separate from the connection pool is the architectural cornerstone here. If you attach LISTEN to a connection borrowed from the pool, the subscription state gets tangled when that connection is returned and reused.

Python + FastAPI + SSE Example

The most commonly used combination. asyncpg handles the LISTEN wait, and FastAPI's StreamingResponse delivers the SSE.

import asyncio
import asyncpg
import json
from fastapi import FastAPI
from fastapi.responses import StreamingResponse
 
app = FastAPI()
 
DATABASE_URL = "postgresql://user:password@localhost/mydb"
 
async def get_listen_connection():
    return await asyncpg.connect(DATABASE_URL)
 
async def event_generator(order_id: int):
    conn = await get_listen_connection()
    queue: asyncio.Queue = asyncio.Queue()
 
    # Named _conn to distinguish from conn in the outer scope
    async def handle_notification(_conn, pid, channel, payload):
        data = json.loads(payload)
        if data.get("id") == order_id:
            await queue.put(payload)
 
    await conn.add_listener("orders_channel", handle_notification)
 
    try:
        while True:
            try:
                payload = await asyncio.wait_for(queue.get(), timeout=30.0)
                yield f"data: {payload}\n\n"
            except asyncio.TimeoutError:
                # SSE keep-alive: ping to maintain the connection
                yield ": ping\n\n"
    finally:
        await conn.remove_listener("orders_channel", handle_notification)
        await conn.close()
 
@app.get("/orders/{order_id}/events")
async def order_events(order_id: int):
    return StreamingResponse(
        event_generator(order_id),
        media_type="text/event-stream",
        headers={
            "Cache-Control": "no-cache",
            # Nginx-specific header. For other proxies
            # (Caddy, HAProxy, AWS ALB, etc.), check their own
            # SSE buffering disable settings separately.
            "X-Accel-Buffering": "no",
        }
    )

Make sure the try/except TimeoutError is inside the while True. If it's outside, the generator sends one ping every 30 seconds and then exits, causing the SSE stream to disconnect periodically.

Node.js + pg-listen Example

If you need auto-reconnect, pg-listen is the convenient choice.

import createSubscriber from "pg-listen";
import { Pool } from "pg";
 
const databaseUrl = process.env.DATABASE_URL;
 
// Dedicated LISTEN connection - separate from the general pool
const subscriber = createSubscriber({ connectionString: databaseUrl });
 
// Track WebSocket clients with a Set
const wsClients = new Set();
 
subscriber.notifications.on("orders_channel", (payload) => {
  console.log("Order change detected:", payload);
  for (const client of wsClients) {
    if (client.readyState === WebSocket.OPEN) {
      client.send(JSON.stringify(payload));
    }
  }
});
 
subscriber.events.on("error", (error) => {
  console.error("LISTEN connection error:", error);
  // pg-listen will automatically attempt to reconnect
});
 
await subscriber.connect();
await subscriber.listenTo("orders_channel");
 
// Keep a separate pool for general queries
const pool = new Pool({ connectionString: databaseUrl });

Multi-Tenant Pattern

A pattern for isolating events per tenant in SaaS by appending the tenant ID to the channel name.

CREATE OR REPLACE FUNCTION notify_tenant_event() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify(
    'tenant_' || NEW.tenant_id::text,
    json_build_object(
      'event', TG_OP,
      'table', TG_TABLE_NAME,
      'id', NEW.id
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

channel_name = f"tenant_{tenant_id}"
await conn.add_listener(channel_name, handle_notification)

However, one tension to be aware of: PostgreSQL wakes up all LISTEN subscribers on that instance whenever a NOTIFY fires, regardless of how many channels exist. As the number of tenants grows into the thousands, the per-tenant channel pattern amplifies this inefficiency directly. If you expect a large number of tenants, it's worth considering a single channel with payload-side filtering instead of channel isolation — or check the scalability limits discussed below before committing.

Preventing Message Loss with the Outbox Pattern

LISTEN/NOTIFY does not persist messages. Notifications that arrive while a subscriber is redeploying or disconnected are simply lost. The outbox pattern is a practical solution for environments where this non-persistence is a problem.

CREATE TABLE outbox (
    id BIGSERIAL PRIMARY KEY,
    channel TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    processed_at TIMESTAMPTZ
);
 
-- NOTIFY is used only as a "signal"; actual data is stored in the outbox
CREATE OR REPLACE FUNCTION notify_with_outbox() RETURNS trigger AS $$
DECLARE
  payload JSONB;
BEGIN
  payload := json_build_object('op', TG_OP, 'id', NEW.id);
  INSERT INTO outbox (channel, payload) VALUES ('orders_channel', payload);
  PERFORM pg_notify('orders_channel', '{"type":"new_event"}');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

When a subscriber receives the signal, it pulls unprocessed events from the outbox.

async def process_outbox(conn):
    # SKIP LOCKED prevents duplicate processing in multi-instance environments
    rows = await conn.fetch("""
        SELECT id, channel, payload
        FROM outbox
        WHERE processed_at IS NULL
        ORDER BY id
        FOR UPDATE SKIP LOCKED
    """)
    for row in rows:
        await handle_event(row["channel"], row["payload"])
        await conn.execute(
            "UPDATE outbox SET processed_at = NOW() WHERE id = $1",
            row["id"]
        )
 
async def handle_notification(_conn, pid, channel, payload):
    async with pool.acquire() as conn:
        await process_outbox(conn)

With this setup, NOTIFY serves only as a "new event available" signal, while subscribers fetch the actual events from the outbox table. Backlogged events can be retrieved after reconnecting, greatly reducing concerns about message loss.

Pros and Cons

Side-by-Side Comparison

Throughput figures vary significantly by workload and hardware, so measure in your own environment for accurate assessment.

Strengths, Clearly Stated

• No event on rollback: Notifications for failed operations can never occur. This is a guarantee Redis Pub/Sub structurally cannot provide, because it handles DB transactions and event delivery independently.
• Zero additional infrastructure: PostgreSQL alone covers Pub/Sub. Operational complexity and cost are noticeably reduced.
• Low latency: Memory-based processing with no disk I/O. Response time is substantially better compared to periodic polling.
• Implementation simplicity: Two lines of SQL and your existing DB driver are all you need to get started.

Limitations, Clearly Stated

When Not to Use It

A post titled "Postgres LISTEN/NOTIFY does not scale" sparked a discussion with hundreds of comments on Hacker News. The core issue surfaced in analyses by the Recall.ai and PgDog teams is that inserting into the NOTIFY queue acquires a global lock, creating a bottleneck in high-write environments. The inefficiency of waking all LISTEN subscribers — regardless of the channel they care about — was also called out.

The three criteria that ultimately determine fit are:

Common Mistakes Seen in Practice

Mixing LISTEN connections into the connection pool: If you attach LISTEN to a connection borrowed from the pool, the subscription state gets tangled when that connection is returned and reused. The dedicated LISTEN connection must always be managed separately, outside the pool.

Putting the full record in the payload: Ignoring the 8,000-byte limit and stuffing large JSON into the payload will trigger ERROR: payload string too long. The error surfaces immediately so you'll catch it quickly, but getting the design wrong early is a pain to fix later.

Forgetting proxy buffering: When a reverse proxy buffers responses while serving SSE, real-time delivery breaks completely. With Nginx, add X-Accel-Buffering: no to the response headers. For other proxies (Caddy, HAProxy, AWS ALB, etc.), check each proxy's own streaming buffering disable settings.

Closing Thoughts

PostgreSQL LISTEN/NOTIFY is the best fit when events are in the hundreds per second or fewer, listeners number in the hundreds or fewer, and some message loss is acceptable. You avoid adding Redis, and the guarantee that no event is published on a transaction rollback is a differentiator no other Pub/Sub solution can structurally match.

On the other hand, if you need thousands of events per second, guaranteed message durability, or thousands of concurrent listeners, Kafka or NATS is the right call. Forcing LISTEN/NOTIFY into that role and hitting the global lock problem later makes for a much harder migration.

References

• PostgreSQL Official Docs — NOTIFY
• PostgreSQL Official Docs — LISTEN
• PostgreSQL Official Docs — libpq Asynchronous Notification
• How to Use Listen/Notify for Real-Time Updates in PostgreSQL — OneUptime (2026.01)
• I Replaced Redis with PostgreSQL (And It's Faster) — DEV Community
• You Don't Need Redis, Postgres Already Has Pub/Sub — Prisma Blog
• Postgres LISTEN/NOTIFY does not scale — Recall.ai
• Postgres LISTEN/NOTIFY does not scale — Hacker News Discussion (2025.07)
• Scaling Postgres LISTEN/NOTIFY — PgDog
• PostgreSQL LISTEN/NOTIFY for Real-Time Multi-Tenant Events — Ugur Aslim
• Use Postgres LISTEN/NOTIFY + Server-Sent Events — Atomic Object
• Building Real-Time Log Streaming with PostgreSQL LISTEN/NOTIFY — DEV Community
• Real-Time Communication with PostgreSQL LISTEN/NOTIFY and FastAPI — Medium
• pg-listen — GitHub (Node.js)
• pgxlisten — GitHub (Go)
• asyncpg-listen — PyPI (Python)
• Neon Guides — Pub/Sub with LISTEN/NOTIFY
• LISTEN/NOTIFY: Automatic Client Notification — Cybertec PostgreSQL
• All the Ways to Do Change Data Capture in Postgres — Sequin Blog
• Building a Notification Engine with PostgreSQL Listen/Notify and Next.js — DoHost (2026.06)