React to new orders instantly with PostgreSQL LISTEN/NOTIFY
Short answer: Instead of asking the database "anything new?" every few seconds, have PostgreSQL tell your app. A trigger on the orders table calls pg_notify('new_order', id) when a row is inserted; your app keeps one connection that has run LISTEN new_order and reacts as soon as the message arrives, for example by updating staff screens or starting a job. Send only an ID in the message, read the full row from the table, and on every reconnect check for anything you missed, because notifications sent while you weren't listening are not stored for you.
Why polling becomes a problem
Polling is the usual first version: a loop that queries SELECT ... WHERE created_at > last_check every few seconds. It works, but:
- each staff screen, worker or service runs its own loop, so the database answers the same question over and over
- shortening the interval for faster updates multiplies the load
- lengthening it makes kitchen screens, dispatch boards or order alerts feel sluggish
LISTEN/NOTIFY replaces the loop with a message the database sends when something actually happens.
How it works
PostgreSQL's documentation describes NOTIFY as sending a notification event, with an optional payload string, to every client session that has previously executed LISTEN on the same channel in the same database. It calls it a simple way for processes using the same database to communicate.
Two pieces:
- A listener. Your app opens a dedicated connection and runs
LISTEN new_order;. The LISTEN reference explains that every session listening on that channel is notified wheneverNOTIFY new_orderis run. - A sender. Something runs
NOTIFY new_order, '1042';or, more flexibly,SELECT pg_notify('new_order', '1042');. The docs note thatpg_notifyis easier when channel names or payloads aren't fixed strings.
Let a trigger send it
The NOTIFY documentation suggests using a trigger on the table, so notifications happen automatically when rows change and nobody forgets to send one:
CREATE FUNCTION notify_new_order() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('new_order', NEW.id::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_notify AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();
Orders created by the website, the admin screen, an import or a manual fix all trigger the same message.
Rules that shape how you use it
The PostgreSQL docs spell out several behaviours that matter in practice:
- Delivery happens after commit. If the insert is part of a transaction, listeners hear nothing until it commits, and nothing at all if it rolls back. That's what you want: no alerts for orders that never existed.
- Payloads are small. In the default configuration the payload must be shorter than 8000 bytes. The docs recommend putting larger data in a table and sending the record's key.
- Duplicates can be folded. Identical notifications on the same channel within one transaction may be delivered once. Distinct payloads are always delivered separately.
- Order is preserved within a transaction, and across transactions in commit order.
- A stuck listener can block cleanup. Notifications wait in a queue until every listening session has processed them. The docs warn that a session that listens and then stays inside a long transaction prevents cleanup, and if the queue fills, transactions calling NOTIFY fail at commit. Warnings appear in the log when it's half full.
What it doesn't do
LISTEN/NOTIFY is a doorbell, not a mailbox:
- If your listener is disconnected, it doesn't receive notifications sent in the meantime. On every connect or reconnect, query the table for rows you haven't processed yet.
- It isn't a job queue. If three workers listen, all three hear every message. Use the notification to wake workers, and claim jobs from a table with a proper locking pattern, or use a real queue.
- Connection poolers can interfere. Listening needs a long-lived session. Poolers in transaction mode don't keep that, so give the listener a direct connection.
Testing it by hand
You can try the whole thing in two database sessions before writing any app code. In the first, run LISTEN new_order;. In the second, insert a test order and commit. Most command-line clients print the notification in the first session, with the channel and payload, the next time you run a command there. Then try inserting inside a transaction and rolling back: no notification should appear. Finally, disconnect the listener, insert another order, reconnect and confirm your catch-up query finds it. These three checks cover most of the surprises teams hit in production.
Getting the update to staff screens
The database notifies your server, not people's browsers. A typical setup: the listener receives new_order 1042, loads order 1042, and pushes it to connected staff screens over WebSockets or server-sent events, or sends an alert to a team chat. The database does no polling, and the screens update within moments of the order being saved.
A worked example
An illustrative Galway pharmacy's click-and-collect system had staff tablets that checked for new orders every ten seconds, and a background worker that did the same for label printing. With several tablets open, the database spent much of its time answering "anything new?". The developer added an insert trigger calling pg_notify, one listener process in the app server that pushed new orders to tablets and woke the label worker, and a catch-up query on reconnect. Orders now appear on tablets almost immediately, and the polling queries are gone.
FAQ
Is LISTEN/NOTIFY reliable enough for orders?
As a signal, yes. Keep the order itself in the table and always catch up on reconnect, so a missed signal doesn't mean a missed order.
Can I send the whole order as JSON in the payload?
Small payloads work, within the size limit, but sending the ID and reading the row is safer and always current.
Does this work with managed PostgreSQL services?
Usually yes, as long as your app can hold a direct, long-lived connection.
Do MySQL or SQLite have this?
Not in the same built-in form. Use a queue, a change-data-capture tool or polling there.
If your app polls for new orders or jobs and is getting heavier, describe the setup and we'll suggest a notification design.