In this lesson, you will learn how to turn your PostgreSQL database into a real-time message broker using the built-in LISTEN and NOTIFY mechanism.
The Event-Driven Bus
Most database interactions are "pull-based": an application asks for data, and the database returns it. However, sometimes you need the database to "push" data to your application the moment something changes. LISTEN/NOTIFY provides a lightweight, asynchronous signaling mechanism that bypasses the need for constant polling or expensive table scans.
When a session executes NOTIFY channel, payload, it places a message on an internal shared-memory queue. Any other database session that has previously executed LISTEN channel will receive that message as soon as they reach their next transaction boundary (or idle state).
Practical Implementation
Imagine you have an application that needs to invalidate a cache whenever a user updates their profile. Instead of the application checking the database every few seconds, you can use a trigger:
-- 1. Create a trigger function to send the notification
CREATE OR REPLACE FUNCTION notify_user_update() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('user_updates', 'ID:' || NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 2. Attach it to your table
CREATE TRIGGER trg_user_changed
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION notify_user_update();
On the application side, your backend worker simply maintains an open connection and executes LISTEN user_updates;. Once the UPDATE statement commits, the database pushes the ID string directly to your listener, allowing you to purge the specific cache entry immediately.
Important Internals
It is crucial to remember that NOTIFY is asynchronous. The message is only delivered after the transaction that triggered it successfully commits. If the transaction rolls back, the notification is discarded. Additionally, these messages are not transactional logs; they are "fire-and-forget." If your listener is disconnected, it will not receive past notifications. Therefore, this mechanism is best used for signaling (telling an app to "go do something") rather than reliable message delivery for mission-critical audit trails.
Key Takeaway: LISTEN/NOTIFY transforms your database into a real-time event publisher, allowing your application to respond instantly to data changes without the overhead of polling.