How to Implement Pub Sub in PostgreSQL: Simple Guide

In PostgreSQL, you implement pub-sub using the LISTEN and NOTIFY commands. NOTIFY sends a notification to all clients listening on a specific channel via LISTEN. This allows asynchronous message passing inside the database.

Syntax

The pub-sub mechanism in PostgreSQL uses two main commands:

• LISTEN channel_name; — This command registers the current session to listen for notifications on the specified channel.
• NOTIFY channel_name, 'payload'; — This sends a notification with an optional payload string to all sessions listening on that channel.

When a notification is sent, all sessions that executed LISTEN on that channel receive it asynchronously.

sql

LISTEN channel_name;

NOTIFY channel_name, 'your message here';

Example

This example shows how one session listens for notifications and another sends them. The listener waits for notifications on channel news. The notifier sends a message to that channel.

sql

-- Session 1: Listener
LISTEN news;

-- Wait for notifications (in a client or application, this waits asynchronously)

-- Session 2: Notifier
NOTIFY news, 'Hello subscribers!';

Output

Asynchronous notification received on channel "news" with payload "Hello subscribers!"

Common Pitfalls

Some common mistakes when using PostgreSQL pub-sub:

• Not calling LISTEN before expecting notifications.
• Expecting notifications to persist after the session ends; notifications are transient.
• Using large payloads in NOTIFY which has a size limit (usually 8000 bytes).
• Not handling notifications asynchronously in client applications, causing missed messages.

Always ensure your client properly waits for notifications and reconnects if needed.

sql

/* Wrong: Sending NOTIFY without any listener active */
NOTIFY updates, 'Update 1';

/* Right: First LISTEN, then NOTIFY */
LISTEN updates;
NOTIFY updates, 'Update 1';

Quick Reference

Command	Description
LISTEN channel_name;	Start listening for notifications on a channel.
NOTIFY channel_name, 'payload';	Send a notification with optional message to listeners.
pg_notify(channel_name, payload)	Function form of NOTIFY for use in SQL functions.
pg_listening_channels()	View channels the current session is listening on.

Key Takeaways

Use LISTEN to subscribe to a channel and NOTIFY to send messages in PostgreSQL.

Notifications are asynchronous and only delivered to active sessions listening on the channel.

Payload size in NOTIFY is limited; keep messages small.

Clients must handle notifications asynchronously to avoid missing messages.

Pub-sub in PostgreSQL is useful for lightweight inter-process communication inside the database.