Real-Time Data with PostgreSQL: Using LISTEN/NOTIFY for WebSockets

Modern web applications increasingly demand real-time data updates to provide responsive and dynamic user experiences. While traditional polling mechanisms have been the go-to solution for years, they’re inefficient and resource-intensive. PostgreSQL offers a powerful alternative through its native pub/sub capabilities with the LISTEN/NOTIFY mechanism, which pairs elegantly with WebSockets to deliver true real-time data flow.

Understanding PostgreSQL’s LISTEN/NOTIFY System

At its core, PostgreSQL’s LISTEN/NOTIFY is an asynchronous communication mechanism that enables database-driven events. This built-in feature allows applications to:

1. Subscribe to notifications through database connections
2. Broadcast events when data changes occur
3. Deliver payloads with relevant information about the changes

This system operates on three key commands:

• LISTEN: Registers a connection as a listener for a specific channel
• NOTIFY: Sends a notification to all listeners on a specified channel
• UNLISTEN: Removes a listener from a channel

The WebSocket Connection

WebSockets provide persistent connections between clients and servers, allowing bidirectional data flow without the overhead of repeated HTTP requests. When combined with PostgreSQL’s LISTEN/NOTIFY mechanism, you create a powerful pipeline:

1. Database changes trigger notifications through PostgreSQL
2. Server application receives these notifications and processes them
3. WebSockets transmit the relevant updates to connected clients
4. Client applications update their UI without page refreshes

Implementation Architecture

1. Database Setup

First, we need to create triggers that will notify about relevant data changes:

sql-- Create a function to notify about changes
CREATE OR REPLACE FUNCTION notify_data_change() RETURNS TRIGGER AS $$
BEGIN
    -- Convert the new row to JSON and send as payload
    PERFORM pg_notify(
        'data_changes',
        json_build_object(
            'table', TG_TABLE_NAME,
            'action', TG_OP,
            'data', row_to_json(NEW)
        )::text
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply the trigger to tables you want to monitor
CREATE TRIGGER products_notify_trigger
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE PROCEDURE notify_data_change();

2. Server-Side Implementation (Node.js with pg and ws)

Next, we’ll set up a Node.js server that listens for PostgreSQL notifications and forwards them to connected WebSocket clients:

javascriptconst { Pool } = require('pg');
const WebSocket = require('ws');

// Create PostgreSQL connection pool
const pool = new Pool({
  connectionString: 'postgresql://user:password@localhost:5432/database'
});

// Set up WebSocket server
const wss = new WebSocket.Server({ port: 8080 });

// Store connected clients
const clients = new Set();

// WebSocket connection handler
wss.on('connection', (ws) => {
  // Add new client to the set
  clients.add(ws);
  
  ws.on('close', () => {
    // Remove client when connection closes
    clients.delete(ws);
  });
});

// Set up database listener connection
(async () => {
  // Dedicated connection for listening
  const client = await pool.connect();
  
  try {
    // Listen for notifications on the data_changes channel
    await client.query('LISTEN data_changes');
    
    // Event handler for notifications
    client.on('notification', (msg) => {
      const payload = JSON.parse(msg.payload);
      
      // Broadcast to all connected WebSocket clients
      clients.forEach(client => {
        if (client.readyState === WebSocket.OPEN) {
          client.send(JSON.stringify(payload));
        }
      });
    });
    
    console.log('Listening for PostgreSQL notifications...');
  } catch (error) {
    console.error('Error setting up notification listener:', error);
    client.release();
  }
})();

3. Client-Side Implementation

Finally, implement WebSocket handling in your frontend application:

javascript// Establish WebSocket connection
const socket = new WebSocket('ws://localhost:8080');

// Handle incoming messages
socket.addEventListener('message', (event) => {
  const data = JSON.parse(event.data);
  
  // Handle different types of updates
  switch (data.table) {
    case 'products':
      updateProductsUI(data);
      break;
    case 'orders':
      updateOrdersUI(data);
      break;
    // Handle other tables as needed
  }
});

// UI update function example
function updateProductsUI(data) {
  if (data.action === 'INSERT') {
    addProductToUI(data.data);
  } else if (data.action === 'UPDATE') {
    updateProductInUI(data.data);
  } else if (data.action === 'DELETE') {
    removeProductFromUI(data.data.id);
  }
}

Performance Considerations

When implementing LISTEN/NOTIFY for real-time applications, consider these performance aspects:

1. Connection management: Maintain dedicated connections for notifications to prevent blocking regular queries
2. Payload size: Keep notification payloads small, containing only essential information
3. Channel specificity: Use multiple channels for different types of updates to reduce processing overhead
4. Client filtering: Consider sending all updates and letting clients filter what’s relevant rather than managing subscriptions server-side

Scaling the Architecture

As your application grows, you might face scaling challenges:

Connection Pooling

Each PostgreSQL connection listening for notifications takes resources. To handle many WebSocket clients:

javascript// Group notifications by type and broadcast only once per event
const uniqueNotifications = new Map();

// Process notifications in batches
setInterval(() => {
  if (uniqueNotifications.size > 0) {
    clients.forEach(client => {
      // Send relevant notifications to each client
      // ...
    });
    uniqueNotifications.clear();
  }
}, 50); // 50ms batching interval

Multi-Server Setup

For distributed systems:

1. Use Redis or other pub/sub systems as an intermediate layer
2. Have PostgreSQL notify a single listener that forwards to Redis
3. Allow multiple WebSocket servers to subscribe to Redis channels

Advanced Patterns

Selective Data Access

Control what data each client receives based on permissions:

javascript// When a client connects, store their access permissions
ws.on('connection', (ws, req) => {
  // Authenticate and determine user permissions
  const userPermissions = authenticateUser(req);
  
  // Store permissions with the WebSocket connection
  ws.userPermissions = userPermissions;
  clients.add(ws);
});

// Filter notifications based on permissions
client.on('notification', (msg) => {
  const payload = JSON.parse(msg.payload);
  
  clients.forEach(client => {
    if (client.readyState === WebSocket.OPEN) {
      // Check if client has access to this data
      if (hasAccess(client.userPermissions, payload)) {
        client.send(JSON.stringify(payload));
      }
    }
  });
});

Reliable Delivery

Ensure clients don’t miss updates during disconnections:

1. Assign version numbers or timestamps to updates
2. When clients reconnect, they provide their last received version
3. Send any missed updates before resuming real-time notifications

Conclusion

PostgreSQL’s LISTEN/NOTIFY mechanism, when combined with WebSockets, creates a powerful foundation for real-time web applications. This approach offers several advantages over traditional methods:

• Efficiency: No polling overhead, updates only when data changes
• Simplicity: Leverages built-in database capabilities without complex middleware
• Robustness: PostgreSQL’s reliability underpins your real-time communication
• Scalability: Can be adapted for increasingly complex applications

By implementing this architecture, you can deliver responsive, real-time experiences to your users while maintaining a clean separation of concerns in your application stack.

Whether you’re building collaborative tools, dashboards, monitoring systems, or modern web applications, this PostgreSQL-driven approach to real-time updates offers an elegant solution that scales with your needs.