Event-driven architectures are a cornerstone of modern web development, enabling scalable and responsive applications. In the Node.js and TypeScript ecosystem, leveraging databases for inter-process communication is a common pattern. PostgreSQL's LISTEN/NOTIFY feature often comes to mind for real-time eventing. However, a common pitfall is treating NOTIFY as a reliable message queue, which it fundamentally isn't. As highlighted in a recent article, "Postgres NOTIFY Is a Doorbell, Not a Queue," NOTIFY messages can be lost if no listener is active at the time of notification. This article delves into building a resilient event-driven worker system using PostgreSQL, combining the low-latency signaling of NOTIFY with the robustness of a transactional job queue, ensuring no task is ever lost, even when workers crash.
The Pitfall of Pure NOTIFY
The LISTEN/NOTIFY mechanism in PostgreSQL is excellent for signaling. When a NOTIFY command is executed, all currently listening clients for that channel receive a notification. The critical word here is currently. If your worker process isn't running, or if it crashes and restarts, any NOTIFY messages sent during its downtime are simply gone. This makes NOTIFY unsuitable for scenarios where message delivery guarantees are paramount, such as processing user-initiated tasks, background jobs, or critical data synchronization.
Consider a scenario where a user uploads a large file, and a backend service needs to process it asynchronously. If you rely solely on NOTIFY to trigger a worker, and that worker happens to be restarting or experiencing an outage, the notification about the new file will be lost, and the file will never be processed. This leads to silent failures and a poor user experience.

The Resilient Job Queue Pattern
The solution lies in treating your database table itself as the authoritative job queue. NOTIFY then acts as a "doorbell" – a lightweight signal to wake up a worker, prompting it to check the actual queue for new tasks. This hybrid approach combines the immediate responsiveness of NOTIFY with the persistence and transactional guarantees of your database.
Here's the core idea:
- Job Persistence: All tasks are inserted into a dedicated
jobstable in PostgreSQL with astatuscolumn (e.g.,pending,processing,completed,failed). - Notification Trigger: After a job is inserted, a
NOTIFYcommand is issued to alert workers about new tasks. - Worker Logic: Workers continuously listen for
NOTIFYevents. Upon receiving a notification, or periodically polling, they query thejobstable forpendingtasks. - Atomic Task Acquisition: Workers use
SELECT ... FOR UPDATE SKIP LOCKEDto atomically acquire a pending job, preventing multiple workers from processing the same task. - Robust Processing: If a worker crashes mid-processing, the job's status can be reset to
pending(orfailedwith retry logic) after a timeout, ensuring it's picked up by another worker.
Implementing the Pattern with Node.js and TypeScript
Let's outline a simplified implementation using Node.js, TypeScript, and a PostgreSQL client.
1. The jobs Table Schema
CREATE TABLE jobs ( id SERIAL PRIMARY KEY, payload JSONB NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, processed_at TIMESTAMP WITH TIME ZONE ); -- Function to notify on new jobs CREATE OR REPLACE FUNCTION notify_new_job() RETURNS TRIGGER AS $$ BEGIN PERFORM pg_notify('new_job_channel', NEW.id::text); RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger to call the function after insert CREATE TRIGGER new_job_trigger AFTER INSERT ON jobs FOR EACH ROW EXECUTE FUNCTION notify_new_job();
This schema includes a payload for job data and a status to manage its lifecycle. The new_job_trigger automatically sends a NOTIFY signal on the new_job_channel whenever a new job is inserted.
2. The Job Producer (e.g., in a Next.js API route or Nest.js service)
import { Pool } from 'pg'; const pool = new Pool({ connectionString: process.env.DATABASE_URL, }); export async function createJob(data: any) { const client = await pool.connect(); try { const res = await client.query( 'INSERT INTO jobs(payload) VALUES($1) RETURNING id', [data] ); console.log(`Job created with ID: ${res.rows[0].id}`); // The trigger will handle the NOTIFY } finally { client.release(); } } // Example usage: // createJob({ type: 'email', recipient: '[email protected]', subject: 'Hello' });
When createJob is called, a new record is inserted into the jobs table. The PostgreSQL trigger then automatically emits the NOTIFY signal.
3. The Resilient Worker
This is where the magic happens. The worker will LISTEN for notifications but also periodically POLL the database to catch any jobs missed during downtime.
import { Pool } from 'pg'; const workerPool = new Pool({ connectionString: process.env.DATABASE_URL, }); const POLL_INTERVAL_MS = 5000; // Poll every 5 seconds async function processJob(jobId: number, payload: any) { console.log(`Processing job ${jobId}:`, payload); // Simulate work await new Promise(resolve => setTimeout(resolve, Math.random() * 3000)); console.log(`Finished job ${jobId}`); } async function fetchAndProcessJobs() { let client; try { client = await workerPool.connect(); await client.query('BEGIN'); const result = await client.query( `SELECT id, payload FROM jobs WHERE status = 'pending' ORDER BY created_at ASC FOR UPDATE SKIP LOCKED LIMIT 1` ); if (result.rows.length > 0) { const job = result.rows[0]; console.log(`Worker acquired job ${job.id}`); await client.query( 'UPDATE jobs SET status = $1, processed_at = NOW() WHERE id = $2', ['processing', job.id] ); await client.query('COMMIT'); // Commit the acquisition immediately try { await processJob(job.id, job.payload); await workerPool.query('UPDATE jobs SET status = $1 WHERE id = $2', ['completed', job.id]); } catch (error) { console.error(`Error processing job ${job.id}:`, error); await workerPool.query('UPDATE jobs SET status = $1 WHERE id = $2', ['failed', job.id]); } } else { await client.query('ROLLBACK'); // No job acquired, rollback the BEGIN } } catch (err) { console.error('Error fetching/processing jobs:', err); if (client) await client.query('ROLLBACK'); } finally { if (client) client.release(); } } async function startWorker() { const listenerClient = await workerPool.connect(); listenerClient.query('LISTEN new_job_channel'); listenerClient.on('notification', async (msg) => { console.log('Received NOTIFY:', msg.payload); // Immediately check for jobs upon notification await fetchAndProcessJobs(); }); listenerClient.on('error', (err) => { console.error('Listener client error:', err); // Implement re-connection logic here for production }); // Initial check and periodic polling to catch missed notifications await fetchAndProcessJobs(); setInterval(fetchAndProcessJobs, POLL_INTERVAL_MS); console.log('Worker started, listening for new jobs...'); } startWorker();
This worker implementation demonstrates several key aspects:
LISTEN new_job_channel: Subscribes to the PostgreSQL notification channel.FOR UPDATE SKIP LOCKED: This crucial clause ensures that when multiple workers try to pick up jobs simultaneously, only one worker successfully locks and retrieves a particularpendingjob. Other workers trying to acquire the same locked row are skipped, preventing deadlocks and race conditions. This is essential for horizontally scaling workers.- Transactional Acquisition: The job's status is updated to
processingwithin a transaction and committed before the actualprocessJoblogic runs. This ensures that if the worker crashes immediately after acquisition but before processing, the job is marked asprocessing, allowing for potential retry mechanisms or manual intervention based on aprocessed_attimestamp. - Periodic Polling: The
setInterval(fetchAndProcessJobs, POLL_INTERVAL_MS)ensures that even if aNOTIFYmessage is missed (e.g., during worker restart), the worker will eventually pick up the pending jobs during its regular poll cycle. - Robust Error Handling: If
processJobfails, the job's status is updated tofailed, allowing for inspection and retries.
Beyond the Basics: Considerations for Production
While the core pattern is robust, a production-ready system requires more:
- Idempotency: Ensure your job processing logic can be safely re-run multiple times without side effects, as jobs might be retried.
- Dead Letter Queues (DLQ): For jobs that consistently fail, move them to a separate DLQ for manual inspection rather than infinite retries.
- Monitoring and Alerting: Track job statuses, processing times, and failed jobs. Integrate with your monitoring stack.
- Concurrency Limits: Implement logic to limit the number of concurrent jobs a single worker instance processes to prevent resource exhaustion.
- Graceful Shutdown: Ensure workers can complete their current job before shutting down when receiving
SIGTERMsignals (e.g., from Docker orchestrators). - Backoff Strategies: When retrying failed jobs, use exponential backoff to avoid overwhelming external services or the database.
- Docker Integration: Containerize your worker service using Docker. This provides consistent environments and simplifies deployment and scaling.
Conclusion
PostgreSQL's LISTEN/NOTIFY is a powerful tool, but understanding its limitations is key to building truly resilient systems. By recognizing NOTIFY as a "doorbell" rather than a guaranteed message queue, and pairing it with a transactional job table using FOR UPDATE SKIP LOCKED and periodic polling, you can construct a robust, scalable, and fault-tolerant event-driven worker architecture. This approach, applicable across various full-stack applications built with Node.js, TypeScript, and modern web frameworks, ensures that your critical background tasks are always processed, even in the face of unexpected worker outages. This hybrid pattern provides the best of both worlds: low-latency signaling and rock-solid persistence, paving the way for highly reliable asynchronous operations.