Real-Time Without a Message Broker: Leveraging PostgreSQL LISTEN and NOTIFY with WebSockets - ITFROMZERO - Share tobe shared! Is your infrastructure overwhelmed by relentless polling and bulky message brokers? Leverage PostgreSQL's built-in LISTEN/NOTIFY alongside WebSockets to build a sleek, transaction-safe, and resource-efficient real-time notification system.

Table of Contents

The Polling Nightmare and the Real-Time Challenge

At 2:00 AM, PagerDuty goes off relentlessly. The monitoring dashboard shows the 8-vCPU production server spiking at 98% CPU utilization. The culprit isn’t hard to find: 15,000 clients firing HTTP polling requests every 2 seconds to check for new orders. To make matters worse, the adjacent RabbitMQ cluster just got OOMKilled due to memory saturation. The entire infrastructure is groaning under immense pressure just to deliver one thing: instant order notifications.

When tackling instant data synchronization from the database to the UI, backend engineers typically choose one of three approaches:

Short Polling or Long Polling: Clients send an HTTP GET request to the server every 2–3 seconds. This is the easiest to implement, but it wastes massive amounts of resources.
Dedicated Message Broker (RabbitMQ, Kafka, Redis Pub/Sub): A dedicated intermediary broker manages queues and distributes messages. Whenever the database changes, the backend emits an event to the broker, which workers then consume and push out via WebSockets.
PostgreSQL LISTEN/NOTIFY with WebSockets: Leveraging the native Pub/Sub engine already built into Postgres. The database fires an event across an internal channel whenever new data arrives. The backend maintains a single LISTEN connection, receives the payload, and broadcasts it directly to clients via WebSockets.

Weighing the Pros and Cons of Each Architecture

Having optimized numerous high-load systems, I’ve broken down the real-world trade-offs of each solution:

1. Traditional Polling

Pros: The easiest to implement. On the frontend, a simple setInterval calling fetch() gets the job done.
Cons: A massive waste of bandwidth. In practice, over 95% of requests return empty responses. During peak flash sales, millions of incoming requests quickly exhaust the database connection pool (50–100 connections), dragging the entire system down.

2. Dedicated Message Brokers (RabbitMQ, Redis)

Pros: Impressive scalability. Capable of handling hundreds of thousands of messages per second with flexible routing and robust backpressure handling.
Cons: High operational overhead. You need to provision clusters, configure monitoring and alerting, and maintain extra infrastructure. The most insidious trap is the dual-write issue: right after the database commits, the application server abruptly crashes before it can publish the event to RabbitMQ. As a result, the record is stored in the database, but users never receive any notification.

3. PostgreSQL LISTEN and NOTIFY

Pros:
- Zero Infrastructure Overhead: No additional containers or intermediary server clusters to manage. The PostgreSQL instance you already use for CRUD acts as your broker.
- Transactional Integrity Guaranteed: The NOTIFY signal is only dispatched when the transaction successfully executes COMMIT. If the transaction rolls back (ROLLBACK), the notification is automatically discarded. You will never send phantom messages.
- Extremely Low Resource Consumption: The listener utilizes a non-blocking persistent connection that consumes only around 2–5MB of backend RAM.
Cons:
- Hard payload limit of 8,000 bytes. This is ample space for a JSON payload containing IDs and metadata fields, but you cannot pack large objects into it.
- No message persistence. If the Node.js server restarts at the exact millisecond Postgres dispatches a NOTIFY, that message is gone forever.
- Connection Pooler Pitfalls: This mechanism requires session-level connections. If you run PgBouncer in transaction pooling mode, the LISTEN command will not work. The solution is to connect your listener directly to Postgres or create a dedicated pool running in session mode.

When Should You Use PostgreSQL LISTEN/NOTIFY?

If you are building an exchange with millions of concurrent users or processing terabytes of logs daily, investing in Kafka or RabbitMQ is essential. Conversely, for the vast majority of small-to-medium applications, B2B SaaS platforms, admin dashboards, internal order notification bells, or export progress bars (under 500–1,000 events/sec), spinning up a Redis or RabbitMQ cluster simply introduces unnecessary points of failure.

Postgres LISTEN/NOTIFY shines brightest when you need a lean architecture. It guarantees that users are notified only after data has safely hit the disk, all while keeping cloud infrastructure costs to an absolute minimum.

When preparing initial data for notification systems or catalogs, engineers often need to convert configuration files. Whenever I need to quickly convert CSV to JSON for database imports, I often use toolcraft.app/en/tools/data/csv-to-json — it runs entirely in the browser without sending data to external servers, ensuring complete privacy.

Hands-on Implementation: PostgreSQL + Node.js + WebSocket

Let’s dive into the code. The entire workflow breaks down into four straightforward steps.

Step 1: Create the Trigger and Function in PostgreSQL

Create an orders table, a function that bundles the data into JSON and calls pg_notify, and trigger it on AFTER INSERT.

-- 1. Create the orders table
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Create the trigger function to dispatch NOTIFY
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS TRIGGER AS $$
DECLARE
    notification_payload JSON;
BEGIN
    -- Package minimal data (avoid exceeding the 8,000-byte limit)
    notification_payload = json_build_object(
        'id', NEW.id,
        'customer_name', NEW.customer_name,
        'total_amount', NEW.total_amount,
        'status', NEW.status,
        'created_at', NEW.created_at
    );

    -- Send the payload to 'new_order_channel'
    PERFORM pg_notify('new_order_channel', notification_payload::text);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Attach the trigger to the orders table
DROP TRIGGER IF EXISTS trg_orders_notify ON orders;
CREATE TRIGGER trg_orders_notify
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION notify_new_order();

Step 2: Build the Node.js Backend to Listen to LISTEN and Broadcast via WebSockets

On the backend, install the two popular libraries pg and ws:

npm install pg ws

Initialize the WebSocket server and open a dedicated Postgres connection to maintain the LISTEN command:

// server.js
const { Client } = require('pg');
const { WebSocketServer } = require('ws');

// 1. Initialize the WebSocket server on port 8080
const wss = new WebSocketServer({ port: 8080 });
wss.on('connection', (ws) => {
    console.log('[WebSocket] Client connected.');
    ws.on('close', () => console.log('[WebSocket] Client disconnected.'));
});

function broadcast(data) {
    wss.clients.forEach((client) => {
        if (client.readyState === 1) { // 1 = OPEN
            client.send(JSON.stringify(data));
        }
    });
}

// 2. Open a dedicated connection to PostgreSQL (avoid sharing the CRUD pool)
const pgClient = new Client({
    connectionString: process.env.DATABASE_URL || 'postgresql://postgres:password@localhost:5432/production_db'
});

async function startListening() {
    try {
        await pgClient.connect();
        console.log('[PostgreSQL] Connected to database successfully.');

        // Subscribe to channel notifications
        await pgClient.query('LISTEN new_order_channel');
        console.log('[PostgreSQL] Listening for NOTIFY on channel: new_order_channel');

        // Handle events when Postgres sends a notification
        pgClient.on('notification', (msg) => {
            try {
                const payload = JSON.parse(msg.payload);
                broadcast({
                    event: 'ORDER_CREATED',
                    data: payload
                });
            } catch (err) {
                console.error('Error parsing JSON payload:', err);
            }
        });

        pgClient.on('error', (err) => {
            console.error('[PostgreSQL Error]', err);
            process.exit(1); // Allow process manager (Docker/PM2) to restart the connection
        });
    } catch (error) {
        console.error('Failed to connect to PostgreSQL:', error);
        setTimeout(startListening, 5000);
    }
}

startListening();

Step 3: Receive Real-Time Notifications on the Frontend

On the web frontend, establish a WebSocket connection to the server and update the UI as soon as a message arrives:

// client.js
const socket = new WebSocket('ws://localhost:8080');

socket.onopen = () => {
    console.log('Connected to WebSocket server.');
};

socket.onmessage = (event) => {
    const response = JSON.parse(event.data);
    
    if (response.event === 'ORDER_CREATED') {
        const order = response.data;
        console.log('New order:', order);
        // Replace alert with a Toast notification in production UI
        alert(`Order #${order.id} from ${order.customer_name} ($${order.total_amount}) has been created!`);
    }
};

socket.onerror = (err) => {
    console.error('WebSocket connection error:', err);
};

Step 4: Verify the Results

Test the setup by opening the psql console and inserting a sample record:

INSERT INTO orders (customer_name, total_amount) 
VALUES ('Nguyen Van A', 450.00);

The moment the transaction commits, the trigger executes pg_notify. In under 10ms, the browser displays the notification without sending a single polling request. Not a single extra megabyte of RAM spent on Redis, database CPU remains below 5%, and you can finally sleep soundly without worrying about PagerDuty going off.