Scaling the Outbox Pattern with PostgreSQL - Part 3: Real-time Reactivity and Maintenance

Welcome to the final part of our series on scaling the Transactional Outbox Pattern with PostgreSQL. In Part 2, we solved concurrency issues using SKIP LOCKED. Our workers can now process events in parallel without stepping on each other’s toes.

But we still have two problems left:

1. The Polling Tax: Querying the database every second is wasteful when there are no events.
2. Table Bloat: Our outbox_events table will grow infinitely over time, degrading query performance.

Let’s fix them.

The Cost of Polling

Even with SKIP LOCKED, a continuous polling interval of 1 second across 5 worker instances results in 432,000 queries per day. If your system is mostly idle at night, 99% of those queries return nothing. It’s a waste of CPU, memory, and database connections.

We need a way to say: “Hey database, let me know when you have new work.”

Push Notifications with LISTEN/NOTIFY

PostgreSQL has a built-in publish/subscribe mechanism called LISTEN and NOTIFY. We can use this to trigger our workers instantly when a new event is inserted.

First, we create a database trigger that fires a NOTIFY command whenever a new row is inserted into our outbox table:

CREATE OR REPLACE FUNCTION notify_outbox_event()
RETURNS trigger AS $$
BEGIN
  -- Notify the 'outbox_channel' with the ID of the new event
  PERFORM pg_notify('outbox_channel', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER outbox_event_trigger
AFTER INSERT ON outbox_events
FOR EACH ROW
EXECUTE FUNCTION notify_outbox_event();

Now, in our Node.js worker, we can listen for this notification:

import { Client } from 'pg'

async function setupListener() {
  const client = new Client({ connectionString: process.env.DATABASE_URL })
  await client.connect()

  // Listen to the channel
  await client.query('LISTEN outbox_channel')

  // Wait for notifications
  client.on('notification', async (msg) => {
    console.log(`Received new event notification: ${msg.payload}`)
    
    // Trigger the processOutboxQueue function from Part 2!
    await processOutboxQueue()
  })
}

Now, when a user registers, the database instantly tells the worker to process the queue. We’ve replaced latency with real-time reactivity!

The Hybrid Approach

You might be tempted to turn off the 1-second polling interval completely. Don’t.

LISTEN/NOTIFY is fire-and-forget. If your Node.js worker restarts, or there’s a temporary network hiccup at the exact moment the database fires the NOTIFY, that notification is lost forever.

The best architecture is a Hybrid Approach:

1. Use LISTEN/NOTIFY for instant, low-latency processing.
2. Keep a slow background poller (e.g., running every 30-60 seconds) as a safety net to sweep up any events that were missed or timed out.

Table Maintenance (Table Bloat)

As your system runs, the outbox_events table will fill up with processed events (status = 'PUBLISHED'). Over time, this massive table will slow down your SELECT ... FOR UPDATE SKIP LOCKED queries because the database has to scan past millions of completed rows to find the pending ones.

You have three main strategies for cleanup:

1. Soft Deletes vs. Hard Deletes: You could run a background cron job that DELETE FROM outbox_events WHERE status = 'PUBLISHED'. However, frequent DELETE operations in PostgreSQL cause “table bloat” due to MVCC (Multi-Version Concurrency Control) and require aggressive VACUUMing.

2. The “Delete on Process” approach: Instead of marking an event as 'PUBLISHED', just delete the row entirely inside the processing transaction:

   // Process event...
   // Then delete it:
   await client.query("DELETE FROM outbox_events WHERE id = $1", [event.id])
   

   This is often the most efficient approach if you don’t need to keep an audit trail of events.

3. Table Partitioning: If you do need an audit trail, use PostgreSQL Table Partitioning (e.g., partition by day or week). When a partition gets too old (e.g., older than 30 days), you can simply DROP TABLE outbox_events_2025_01, which is instantaneous and reclaims disk space cleanly without fragmentation.

Conclusion

Over this 3-part series, we’ve built a robust event-driven architecture using nothing but PostgreSQL:

• We guaranteed consistency using the Transactional Outbox Pattern within a single transaction.
• We scaled to multiple workers safely using SKIP LOCKED.
• We achieved real-time, low-latency processing with LISTEN/NOTIFY.
• We designed for long-term health with smart Cleanup Strategies.

You don’t always need a complex, heavy distributed streaming platform to build a great event-driven system. Sometimes, a well-tuned PostgreSQL database is exactly what you need.

Keep pushing forward and savor every step of your coding journey.