Postgres NOTIFY as a WebSocket Delivery Mechanism
LISTEN/NOTIFY is an underutilized real-time delivery primitive: no broker, no extra infrastructure, durable via the underlying table.
The problem
Real-time event delivery to WebSocket clients is easy when all clients are connected to the same process. In a scaled or multi-process system, a FastAPI instance has no direct view of which clients are connected to the proxied Node.js WS server running on a different port. Calling ws_manager.send_to_client() in-process drops the event on the floor if the target isn't connected to that process.
The approach
Postgres LISTEN/NOTIFY solves the fan-out problem without adding a message broker. A trigger function fires on every INSERT into the delivery table:
CREATE OR REPLACE FUNCTION notify_message() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('message', json_build_object(
'id', NEW.id,
'recipient', NEW.recipient,
'sender', NEW.sender,
'subject', NEW.subject
)::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER message_notify
AFTER INSERT ON messages
FOR EACH ROW EXECUTE FUNCTION notify_message();The Node.js WS server holds a dedicated Postgres client with LISTEN message. When the notification fires, it fetches the full row by id (keeping the NOTIFY payload under the 8KB cap) and fans it out to all registered WebSocket clients. The Python backend never needs to know the WS server exists — it just does a normal DB insert.
The key insight: the NOTIFY payload is intentionally minimal (just the id). The real content is always fetched from the table. This means the NOTIFY is just a wake-up call, not a data carrier. You never hit the 8KB limit, and you always get the freshest row state.
What I learned
Postgres NOTIFY is underutilised as a real-time delivery primitive. It requires no additional infrastructure, survives reconnects (the subscriber just re-executes LISTEN), and gives you durable delivery via the underlying table without a separate message broker. The two-path design — fast in-process send for hot paths, INSERT-triggered NOTIFY for durability — lets you have both low latency and reliability without choosing between them.