Published on

Postgres LISTEN/NOTIFY: Build Real-Time Scalable Systems

Postgres LISTEN and NOTIFY are built-in commands that allow your database to send real-time alerts to external applications whenever specific events occur. By using this mechanism, you can reduce server load by 90% compared to traditional polling, as your application only reacts when the database pushes a notification. This setup enables you to build instant features like live chat, real-time dashboards, or automated cache clearing with just a few lines of SQL and code.

How does the LISTEN/NOTIFY mechanism work?

The LISTEN and NOTIFY system works like a radio broadcast within your database. One part of your system acts as the broadcaster (NOTIFY), sending out a message on a specific "channel" or topic name. Other parts of your system act as listeners (LISTEN), tuning into that specific channel to wait for updates.

When a NOTIFY command is executed, Postgres checks for any active sessions that have run a LISTEN command on that channel. If it finds any, it sends the notification to those specific connections immediately. This happens asynchronously (meaning the database doesn't wait for the listener to finish its work before moving on), which keeps your database fast.

You can even include a "payload" (a small string of text or JSON data) with your notification. This allows you to send specific details, like the ID of a new user or the status of a payment, directly to your application. Because this is built directly into the core of Postgres, you don't need to install extra message brokers like Redis for simple real-time tasks.

Why is this better than polling?

Polling is the process where your application asks the database "Is there new data?" every few seconds. This creates constant traffic and wastes CPU (Central Processing Unit - the brain of your computer) cycles even when nothing has changed. As your user base grows, thousands of simultaneous "Are we there yet?" requests can slow your database to a crawl.

LISTEN/NOTIFY changes this relationship to a "push" model. Your application opens a single, long-lived connection and sits quietly until the database speaks. This significantly reduces the overhead on your database server.

We have found that using this native feature simplifies your tech stack because you have one less piece of infrastructure to manage. Instead of setting up a separate message queue, you use the database you already own. It ensures that your application stays perfectly in sync with the actual data sitting in your tables.

What do you need to get started?

Before writing code, ensure your environment is ready for modern development. You will need a basic understanding of SQL (Structured Query Language - the language used to talk to databases).

Prerequisites:

  • PostgreSQL 18 or 19: Ensure you are using a current version to take advantage of the latest performance improvements.
  • Python 3.14+: We will use Python for the listener script due to its excellent library support.
  • Psycopg3: This is the modern database adapter (a tool that lets Python talk to Postgres) that supports asynchronous operations.
  • A Database Client: Tools like pgAdmin or DBeaver help you run SQL commands manually for testing.

How do you set up a basic notification?

You can test this functionality right now using two different query windows in your database tool. This helps you see the "broadcast" and "reception" happening in real-time without any complex code.

Step 1: Open a connection and start listening. In your first SQL window, run the following command:

-- This tells Postgres we want to hear messages on the 'task_updates' channel
LISTEN task_updates;

What you should see: The database will return a success message like "LISTEN". It is now waiting.

Step 2: Open a second window and send a notification. In a new, separate SQL window, run this command:

-- This sends a message to the 'task_updates' channel with a text payload
NOTIFY task_updates, 'A new task has been assigned!';

What you should see: The second window will report that the command was successful.

Step 3: Check your first window. Go back to your first window and check the "Messages" or "Output" tab. You will see an asynchronous notification received from the server. Don't worry if it doesn't pop up as a giant alert; most database tools show these in the logs or a specific notifications pane.

How do you automate notifications with Triggers?

Manually running NOTIFY is fine for testing, but in a real system, you want the database to alert you automatically when data changes. We do this using a Trigger (a function that runs automatically when a specific event, like an INSERT or UPDATE, happens in a table).

Step 1: Create a sample table.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT,
    customer_name TEXT
);

Step 2: Create a Trigger Function. This function (a reusable block of code) will be executed by the database.

CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS trigger AS $$
BEGIN
  -- We convert the new row data into JSON and send it as a notification
  -- pg_notify is the function version of the NOTIFY command
  PERFORM pg_notify('order_event', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: Attach the Trigger to the table.

CREATE TRIGGER order_update_trigger
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Now, every time you add a new order, Postgres will automatically broadcast the details on the order_event channel.

How do you write a Python listener?

To make this useful, your application needs to "hear" these messages and act on them. Using psycopg in Python is one of the most reliable ways to handle this.

Step 1: Install the library. Run this in your terminal:

pip install "psycopg[binary]"

Step 2: Create the listener script. Save this as listener.py.

import psycopg
import json

# Connection string to your database
conn_info = "dbname=your_db user=your_user password=your_pass host=localhost"

def start_listening():
    # Connect to the database
    with psycopg.connect(conn_info, autocommit=True) as conn:
        # Start listening to our specific channel
        conn.execute("LISTEN order_event")
        print("Waiting for order updates...")

        # A generator that waits for notifications
        gen = conn.notifies()
        for notify in gen:
            # notify.payload contains the JSON string from our trigger
            data = json.loads(notify.payload)
            print(f"New Order Received! ID: {data['id']}, Customer: {data['customer_name']}")

if __name__ == "__main__":
    start_listening()

Step 3: Run the script. Open your terminal and run python listener.py. It will sit and wait. Then, go to your SQL tool and insert a row:

INSERT INTO orders (status, customer_name) VALUES ('pending', 'Alice');

Your Python script will instantly print the order details. It is normal to feel a bit of excitement when you see the text appear in your terminal the moment you hit "run" in your SQL editor.

What are the common gotchas to avoid?

While LISTEN/NOTIFY is powerful, there are a few things that can trip up beginners. Understanding these early will save you hours of debugging.

  • Transactional Limits: Notifications are only sent after a transaction (a group of database operations that succeed or fail together) is committed. If your transaction fails and rolls back, the notification is never sent. This prevents your application from reacting to data that doesn't actually exist.
  • Payload Size: The message payload has a limit (usually 8,000 bytes). If you try to send a massive JSON object with thousands of fields, it will fail. Instead, send just the ID of the row and have your application fetch the full details if needed.
  • Connection Drops: If your Python script loses its internet connection, the "LISTEN" command is lost. You must include logic in your code to reconnect and run the "LISTEN" command again if the connection breaks.
  • Queue Overflow: If you send notifications faster than your listener can process them, they can pile up. However, for most small to medium projects, Postgres handles this with ease.

Next Steps

Now that you have built a working real-time notification loop, you can start integrating this into larger projects. You might try connecting this to a WebSocket (a way to send data to a web browser in real-time) so your website updates without refreshing.

You could also explore how to use this for "Cache Invalidation." This is when you tell your application to clear its temporary memory because the data in the database has changed.

For more detailed guides, visit the official Postgres documentation.

Read the Listennotify Documentation