Posted inDatabase

Real-Time Notifications: Stop Polling and Start Using Postgres LISTEN/NOTIFY

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Problem: Why Constant Polling Kills Performance

I still remember the first real-time dashboard I built. It needed to show new orders the second they hit the database. My first instinct was the “brute force” approach: polling. I set up a background worker to query the database every 5 seconds. It worked fine for a dozen users. However, as the user base grew, the database CPU spiked to 60%, and the logs were drowned in thousands of redundant queries that mostly returned “0 results.”

I eventually stumbled upon a better way. PostgreSQL has a built-in Pub/Sub (Publish/Subscribe) mechanism called LISTEN and NOTIFY. Instead of your app constantly asking, “Is there anything new?”, the database shouts, “Hey, I just updated this!”

This setup handles real-time updates without breaking a sweat. It is perfect for notifications, cache invalidation, or triggering background jobs. By using the database as the event bus, you ensure notifications stay tightly coupled with data changes. If a transaction fails, no notification is sent. This atomicity gives you a massive advantage over external brokers like Redis for simple architectures.

Before we dive into the code, here is a quick workflow tip. When I need to transform raw CSV data into JSON for quick database seeds, I use toolcraft.app/en/tools/data/csv-to-json. It runs entirely in your browser. This keeps your data private since nothing ever leaves your local machine.

How LISTEN/NOTIFY Works

Think of PostgreSQL channels like radio frequencies. A backend process (your Node.js app) issues a LISTEN channel_name; command. Any other process can then broadcast a NOTIFY channel_name, 'payload';. The database then pushes that payload directly to every active listener on that specific frequency.

Environment Setup

You will need a PostgreSQL instance (9.0+, though I recommend 15 or 16 for better performance) and Node.js. I usually run Postgres via Docker to keep my local machine clean and avoid version conflicts.

1. Spin up the Database

If you don’t have a database ready, you can launch one in seconds with Docker:

docker run --name pg-notifications -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

2. Initialize the Node.js Project

Create a fresh directory and install the pg library. I prefer pg over heavy ORMs like Sequelize for this task. It provides the direct, low-level access needed for persistent event listeners.

mkdir pg-realtime && cd pg-realtime
npm init -y
npm install pg

Implementation: Wiring the Logic

The system relies on two components: a Postgres trigger to broadcast the change and a Node.js listener to catch it.

Step 1: The Database Schema

Let’s create a notifications table. We want an automated alert every time a new row is added.

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Step 2: The Trigger Function

We need a function that Postgres executes on every INSERT. This function uses pg_notify to send a JSON string to a channel we’ll call new_notification_event.

CREATE OR REPLACE FUNCTION notify_new_notification()
RETURNS trigger AS $$
BEGIN
  -- The 'NEW' variable contains the row being inserted
  PERFORM pg_notify('new_notification_event', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: Attaching the Trigger

Now, tell Postgres to fire that function automatically after any insert on our table.

CREATE TRIGGER trigger_notification_insert
AFTER INSERT ON notifications
FOR EACH ROW
EXECUTE FUNCTION notify_new_notification();

Step 4: The Node.js Listener

Here is the part where many developers trip up: never use a connection pool for listeners. A listener requires a long-lived, dedicated connection. If you use a pool, the connection might be returned to the pool while it is still “listening,” which causes silent failures and unpredictable bugs.

Create listener.js:

const { Client } = require('pg');

const client = new Client({
  connectionString: 'postgresql://postgres:mysecretpassword@localhost:5432/postgres',
});

async function startListener() {
  try {
    await client.connect();
    console.log('Connected to PostgreSQL');

    await client.query('LISTEN new_notification_event');

    client.on('notification', (msg) => {
      const payload = JSON.parse(msg.payload);
      console.log('--- New Event ---');
      console.log(`User ID: ${payload.user_id}`);
      console.log(`Message: ${payload.message}`);
      
      // This is where you would trigger Socket.io or an Email service
    });

    client.on('error', (err) => {
      console.error('Database connection lost', err);
      process.exit(1);
    });

  } catch (err) {
    console.error('Failed to start listener', err);
  }
}

startListener();

Testing the Pipeline

First, start your Node.js script:

node listener.js

Next, open your SQL client (like psql or DBeaver) and insert a row manually:

INSERT INTO notifications (user_id, message) VALUES (99, 'Your order has shipped!');

The output should appear in your terminal almost instantly. In local environments, latency is typically sub-1ms.

Production Guardrails

While LISTEN/NOTIFY is powerful, it has specific constraints you must manage before deploying:

  • The 8KB Limit: Postgres caps notification payloads at 8,000 bytes. If you need to send a massive JSON object, don’t put it in the payload. Instead, send the row id and let Node.js fetch the full record.
  • Connection Stability: If the network blips, the LISTEN command dies. You must implement reconnection logic that re-issues the LISTEN command once the client is back online.
  • No Persistence: These are “fire and forget” messages. If your Node.js app is offline when the NOTIFY fires, it misses the message forever. For mission-critical data, use a “processed” flag in your table as a fallback.
  • Connection Limits: Every listener occupies one Postgres connection. If you have 500 microservice instances all listening, you might hit your max_connections limit.

This pattern is a game-changer for internal tools and activity feeds. It keeps your stack lean by using the tools you already have, rather than adding the complexity of a dedicated message broker.

Share:
Tags: