Why We Ditched Redis Pub/Sub for Postgres LISTEN/NOTIFY

A few months ago, we inherited a multi-tenant SaaS control plane that was running a Redis cluster solely to broadcast resource state changes to a fleet of WebSocket servers. In theory, this is a standard architecture. In production, it introduced unnecessary operational overhead.

We encountered connection leaks during rolling deployments, silent subscription failures when nodes restarted, and extra costs for a managed Redis instance that sat at low CPU utilization. To address this, we migrated the pub/sub system to PostgreSQL's native LISTEN and NOTIFY channels.

The Dual-Write Problem

In the original stack, every time a tenant updated a device configuration in PostgreSQL, the API worker had to write to the database and then publish an event to Redis. If either operation failed due to a network partition, the WebSockets fell out of sync. Fixing this requires complex transactional outbox patterns.

By moving the event publication to a PostgreSQL trigger inside the database transaction, we guaranteed atomic updates. If the database transaction commits, the event is dispatched. If it rolls back, no event is sent.

Setting up the Postgres Trigger

We defined a SQL function to generate a minimal JSON payload, staying well below the 8000-byte channel limit, and registered a trigger on the target table:

CREATE OR REPLACE FUNCTION notify_device_update()
RETURNS trigger AS $$
DECLARE
  payload json;
BEGIN
  payload = json_build_object(
    'id', NEW.id,
    'tenantId', NEW.tenant_id,
    'status', NEW.status
  );
  PERFORM pg_notify('device_updates', payload::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER device_update_trigger
AFTER UPDATE ON devices
FOR EACH ROW
EXECUTE FUNCTION notify_device_update();

Implementing the Persistent Connection Listener in Node.js

Because Postgres is process-based, we cannot have every serverless API execution run a persistent LISTEN query. Instead, we run a dedicated background daemon process that maintains a single connection, parses the payloads, and forwards them to the client broker. If your infrastructure requires high-performance databases, check out our data engineering solutions.

import { Client } from 'pg';

async function startEventListener() {
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000
  });

  await client.connect();
  await client.query('LISTEN device_updates');

  client.on('notification', (msg) => {
    try {
      const payload = JSON.parse(msg.payload);
      wsBroker.broadcastToTenant(payload.tenantId, payload);
    } catch (err) {
      console.error('Failed to parse pg_notify payload:', err);
    }
  });
}

Reconnection Strategies with Exponential Backoff

In a production system, database connections can drop due to network partitions, database maintenance, or server restarts. A basic reconnect loop can overwhelm your database with connection attempts. To prevent this, we implement exponential backoff with jitter:

async function reconnect(client, attempt = 0) {
  const delay = Math.min(1000 * Math.pow(2, attempt) + Math.random() * 1000, 30000);
  console.log(`Attempting database reconnection in ${delay}ms...\n`);
  
  setTimeout(async () => {
    try {
      await client.connect();
      await client.query('LISTEN device_updates');
      console.log('✓ Successfully reconnected database listener.');
    } catch (err) {
      console.error('Reconnection failed:', err);
      await reconnect(client, attempt + 1);
    }
  }, delay);
}

Trade-offs and Limitations

While this migration simplified our stack, Postgres LISTEN/NOTIFY is not a fit for all workloads:

• The 8000-Byte Payload Limit: Payloads must be small. If you need to send large payloads, notify only the ID and status, letting workers fetch full records only if required.

• Connection Overhead: Each active subscriber consumes a real Postgres connection slot. This requires a dedicated broker process.

• No Message Persistence: If your listener is offline during a write, it misses the message. We address this by running a sync query on startup.