PostgreSQL LISTEN/NOTIFY
Introduction
Have you ever needed your application to react immediately when data changes in your PostgreSQL database? Traditional approaches involve polling the database at regular intervals, which is inefficient and creates unnecessary load. PostgreSQL offers a better solution with its built-in LISTEN/NOTIFY mechanism.
LISTEN/NOTIFY is a simple yet powerful pub/sub (publish/subscribe) system that enables real-time communication between database sessions. It allows one session to send notifications to other sessions that are actively listening for specific events, all within the database itself.
In this tutorial, we'll explore how to use LISTEN/NOTIFY to build responsive applications that can react instantly to database changes.
Understanding the Basics
The LISTEN/NOTIFY mechanism consists of three key SQL commands:
LISTEN: Subscribe to a specific notification channelNOTIFY: Send a notification to a specific channelUNLISTEN: Unsubscribe from a notification channel
Here's a simple diagram showing how it works:
Let's break down how this works:
- Session B registers interest in a specific channel by executing
LISTEN channel_name - Session A sends a notification using
NOTIFY channel_name, 'optional message' - PostgreSQL delivers this notification to all sessions (including Session B) that are listening on that channel
- When Session B is done listening, it can use
UNLISTEN channel_nameto stop receiving notifications
Basic Usage Examples
Let's start with some simple examples to see LISTEN/NOTIFY in action. Open two separate PostgreSQL sessions (we'll call them Session A and Session B).
Example 1: Basic Notification
In Session B (the listener):
LISTEN my_channel;
In Session A (the notifier):
NOTIFY my_channel;
Session B will receive a notification that looks like this:
Asynchronous notification "my_channel" received from server process with PID 5678.
Example 2: Notification with Payload
In Session B:
LISTEN data_updated;
In Session A:
NOTIFY data_updated, 'User record with ID 42 was updated';
Session B receives:
Asynchronous notification "data_updated" with payload "User record with ID 42 was updated" received from server process with PID 5678.
The payload allows you to send additional information along with the notification (limited to 8000 bytes in PostgreSQL).
Practical Applications
Now that we understand the basics, let's look at some practical applications of LISTEN/NOTIFY.
Application 1: Cache Invalidation
When data changes in the database, you often need to invalidate cached data in your application. LISTEN/NOTIFY provides an elegant solution:
-- Create a function that sends a notification when products are updated
CREATE OR REPLACE FUNCTION notify_product_update() RETURNS TRIGGER AS $$
BEGIN
NOTIFY product_cache_channel, 'product:' || NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create a trigger that calls this function after an update
CREATE TRIGGER product_updated
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION notify_product_update();
Then in your application code, you would listen for these notifications and invalidate the corresponding cache entry when notifications arrive.
Application 2: Real-time Chat System
LISTEN/NOTIFY can be used to build a simple chat system:
-- Create a function to send a chat message
CREATE OR REPLACE FUNCTION send_chat_message(
channel_name text,
username text,
message text
) RETURNS void AS $$
BEGIN
-- Store the message in the database
INSERT INTO chat_messages (channel, username, message, created_at)
VALUES (channel_name, username, message, NOW());
-- Notify all listeners about the new message
NOTIFY chat_updates, json_build_object(
'channel', channel_name,
'username', username,
'message', message,
'time', NOW()
)::text;
END;
$$ LANGUAGE plpgsql;
Your application would call this function to send messages and listen on the chat_updates channel to receive them.
Application 3: Background Worker Coordination
LISTEN/NOTIFY is excellent for coordinating multiple worker processes:
-- Queue a job for processing
INSERT INTO job_queue (job_type, parameters, status)
VALUES ('send_email', '{"to": "[email protected]", "subject": "Hello"}', 'pending')
RETURNING id;
-- Notify workers about the new job
NOTIFY job_queue_channel, 'new_job';
Worker processes would listen on job_queue_channel and check for new jobs when they receive a notification.
Using LISTEN/NOTIFY in Applications
Let's see how to use LISTEN/NOTIFY from various programming languages:
Node.js with node-postgres
const { Client } = require('pg');
async function setupNotifications() {
// Create a dedicated client for listening
const client = new Client({
connectionString: 'postgresql://user:password@localhost:5432/dbname'
});
await client.connect();
// Listen for notifications
await client.query('LISTEN data_changes');
// Set up notification handler
client.on('notification', (msg) => {
console.log('Notification received:', msg.channel);
console.log('Payload:', msg.payload);
// Process the notification...
});
console.log('Listening for notifications...');
}
setupNotifications().catch(err => console.error(err));
Python with psycopg2
import select
import psycopg2
import psycopg2.extensions
# Connect to the database
conn = psycopg2.connect("dbname=mydb user=postgres")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
# Create a cursor and subscribe to the channel
cur = conn.cursor()
cur.execute("LISTEN data_changes;")
print("Waiting for notifications...")
while True:
# Check if there's a notification to be processed
if select.select([conn], [], [], 5) == ([], [], []):
# Timeout occurred
continue
# Process any pending notifications
conn.poll()
while conn.notifies:
notify = conn.notifies.pop()
print(f"Got notification: {notify.channel}, {notify.payload}")
# Process the notification...
Java with JDBC
import java.sql.*;
public class PostgresNotificationListener {
public static void main(String[] args) throws Exception {
// Connect to the database
Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/mydb", "postgres", "password");
// Create a statement for listening
Statement stmt = conn.createStatement();
// Start listening
stmt.execute("LISTEN data_changes");
// Create a listener for notifications
PGConnection pgConn = conn.unwrap(org.postgresql.PGConnection.class);
// Main loop to process notifications
while (true) {
// Check for notifications (this is a blocking call)
org.postgresql.PGNotification[] notifications = pgConn.getNotifications(1000);
if (notifications != null) {
for (org.postgresql.PGNotification notification : notifications) {
System.out.println("Channel: " + notification.getName());
System.out.println("Payload: " + notification.getParameter());
// Process the notification...
}
}
}
}
}
Best Practices and Limitations
When using LISTEN/NOTIFY, keep the following best practices and limitations in mind:
Best Practices
-
Use dedicated connections for listening: Create a separate database connection for handling notifications so that your main application queries are not affected.
-
Keep payloads small: Notification payloads are limited to 8000 bytes. For larger data, send a reference (like an ID) and have the listeners query for the complete data if needed.
-
Handle reconnections: If the database connection is lost, your application needs to reconnect and re-establish LISTEN statements.
-
Use JSON for structured data: When sending complex data, format it as a JSON string for easy parsing.
Limitations
-
No guaranteed delivery: If a client is disconnected when a notification is sent, it will not receive the notification later when it reconnects.
-
No persistence: Notifications are transient and not stored anywhere.
-
No across-database notifications: LISTEN/NOTIFY operates within a single database.
-
No automatic load balancing: If multiple listeners are on the same channel, all will receive the same notification. Your application needs to handle coordination.
Common Patterns and Antipatterns
Patterns
Trigger-based notification: Use database triggers to automatically send notifications when data changes:
CREATE OR REPLACE FUNCTION notify_data_change() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('data_changes', json_build_object(
'table', TG_TABLE_NAME,
'action', TG_OP,
'id', CASE WHEN TG_OP = 'DELETE' THEN OLD.id ELSE NEW.id END
)::text);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_changed
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION notify_data_change();
Antipatterns
Overuse of notifications: Sending too many notifications can overwhelm both PostgreSQL and client applications. Use them judiciously for events that truly need real-time handling.
Relying on notifications for critical operations: Due to their transient nature, don't use LISTEN/NOTIFY for operations that must be guaranteed to happen.
Summary
PostgreSQL's LISTEN/NOTIFY mechanism is a powerful feature that enables real-time communication between database sessions and applications. It provides a lightweight alternative to traditional polling approaches and can significantly improve the responsiveness of your applications.
Key benefits include:
- Low latency notifications of database events
- Reduced database load by eliminating unnecessary polling
- Simple implementation of pub/sub patterns directly in PostgreSQL
- Integration with database triggers for automatic event generation
While LISTEN/NOTIFY has some limitations, it's an excellent tool for many real-time scenarios like cache invalidation, chat systems, and worker coordination.
Additional Resources
Here are some resources to deepen your understanding of LISTEN/NOTIFY:
Exercises
-
Create a simple notification system that sends a notification whenever a new user registers in your application.
-
Implement a cache invalidation system using LISTEN/NOTIFY for a table of your choice.
-
Build a simple chat application where messages are stored in PostgreSQL and distributed to connected clients using LISTEN/NOTIFY.
-
Create a job queue system where workers pick up tasks based on notifications rather than polling the database.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)