Nov 18, 20268 min read

Postgres LISTEN/NOTIFY: Real-Time Events From Your Database

How LISTEN and NOTIFY work in Postgres, what they guarantee and what they drop, a trigger that announces row changes, listeners in Go and Node.js, and when to move the events to a durable Pub/Sub topic.

Illustration for Postgres LISTEN/NOTIFY: Real-Time Events From Your Database

Postgres has a built-in way to tell connected clients that something changed: one session runs LISTEN order_status, and any transaction that runs pg_notify('order_status', ...) wakes it up when that transaction commits. With a trigger on the table, every status change announces itself without the application code remembering to.

It is also easy to lean on it for more than it promises. Notifications are not stored, so a listener that is restarting misses them, payloads must stay under 8000 bytes, and a connection pooler in transaction mode silently breaks LISTEN. Those limits decide whether it is the right tool or a shortcut that loses events.

This guide covers what NOTIFY guarantees, a trigger that announces order status changes, and listeners in Go and Node.js. It starts with Encore, using the listener to keep a per-instance cache correct across replicas.

LISTEN/NOTIFY at a glance

LISTEN/NOTIFYA Pub/Sub topic (SNS + SQS, GCP Pub/Sub)
DeliveryTo every session listening at commit timeTo every subscription, stored until acknowledged
If the listener is downThe notification is lostThe message waits, with retries
PayloadText, under 8000 bytes by defaultUp to the broker limit, typically kilobytes to megabytes
TransactionalYessent only on commitNot with your database, unless you use an outbox
Extra infrastructureNone beyond PostgresA broker per environment
Good forCache invalidation, waking workers, live updatesEvents other services must not miss

How do you use LISTEN/NOTIFY with Encore?

Encore

Encore brings infrastructure from code: the Postgres database, its migrations and the service that listens to it are declared in Go, encore run starts Postgres locally, and a deploy provisions RDS or Cloud SQL in your own AWS or GCP account. The listener uses the database's underlying pgx pool through sqldb.Driver, so it holds a dedicated connection rather than borrowing one per query.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create orders-app --example=hello-world$ cd orders-app && mkdir -p orders/migrations

A trigger that announces changes

The migration creates the table and a trigger that calls pg_notify with the order ID and the new status whenever a row is inserted or its status changes, so a status change made by a script or another service is announced too:

-- orders/migrations/1_orders.up.sql CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'placed', updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE FUNCTION notify_order_status() RETURNS trigger AS $$ BEGIN PERFORM pg_notify('order_status', json_build_object('id', NEW.id, 'status', NEW.status)::text); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER order_status_changed AFTER INSERT OR UPDATE OF status ON orders FOR EACH ROW EXECUTE FUNCTION notify_order_status();

Listen and invalidate

Each instance keeps a small in-memory cache of order statuses. A goroutine started from the service struct's initService holds one connection with LISTEN order_status and evicts any order that changes, so a status updated on one replica is not served stale from another. The Shutdown method stops the listener when Encore drains the instance:

// orders/orders.go package orders import ( "context" "encoding/json" "errors" "sync" "time" "encore.dev/beta/auth" "encore.dev/beta/errs" "encore.dev/rlog" "encore.dev/storage/sqldb" "github.com/jackc/pgx/v5/pgxpool" ) var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"}) //encore:service type Service struct { mu sync.RWMutex cache map[int64]string // order id -> status, local to this instance cancel context.CancelFunc wg sync.WaitGroup } func initService() (*Service, error) { ctx, cancel := context.WithCancel(context.Background()) s := &Service{cache: map[int64]string{}, cancel: cancel} s.wg.Add(1) go s.listen(ctx) return s, nil } // Shutdown is called by Encore when the instance stops. func (s *Service) Shutdown(force context.Context) { s.cancel() s.wg.Wait() } type change struct { ID int64 `json:"id"` Status string `json:"status"` } // listen holds one connection with LISTEN order_status and evicts changed orders // from this instance's cache. It reconnects with backoff if the connection drops. func (s *Service) listen(ctx context.Context) { defer s.wg.Done() pool := sqldb.Driver[*pgxpool.Pool](db) backoff := time.Second for ctx.Err() == nil { err := s.listenOnce(ctx, pool) if ctx.Err() != nil { return } rlog.Warn("order listener stopped, reconnecting", "err", err, "in", backoff) select { case <-time.After(backoff): case <-ctx.Done(): return } backoff = min(backoff*2, 30*time.Second) } } func (s *Service) listenOnce(ctx context.Context, pool *pgxpool.Pool) error { conn, err := pool.Acquire(ctx) if err != nil { return err } // Close instead of Release: the session still has LISTEN active. defer func() { conn.Hijack().Close(context.Background()) }() if _, err := conn.Exec(ctx, "LISTEN order_status"); err != nil { return err } // Anything cached before LISTEN started may be stale, so start empty. s.mu.Lock() clear(s.cache) s.mu.Unlock() for { n, err := conn.Conn().WaitForNotification(ctx) if err != nil { return err } var c change if err := json.Unmarshal([]byte(n.Payload), &c); err != nil { rlog.Error("bad notification payload", "payload", n.Payload) continue } s.mu.Lock() delete(s.cache, c.ID) s.mu.Unlock() } } type Order struct { ID int64 `json:"id"` Status string `json:"status"` } //encore:api auth method=GET path=/orders/:id func (s *Service) Get(ctx context.Context, id int64) (*Order, error) { customer, _ := auth.UserID() s.mu.RLock() status, ok := s.cache[id] s.mu.RUnlock() if ok { var owner string err := db.QueryRow(ctx, `SELECT customer_id FROM orders WHERE id = $1`, id).Scan(&owner) if err != nil || owner != string(customer) { return nil, &errs.Error{Code: errs.NotFound, Message: "order not found"} } return &Order{ID: id, Status: status}, nil } err := db.QueryRow(ctx, `SELECT status FROM orders WHERE id = $1 AND customer_id = $2`, id, string(customer)).Scan(&status) if errors.Is(err, sqldb.ErrNoRows) { return nil, &errs.Error{Code: errs.NotFound, Message: "order not found"} } else if err != nil { return nil, err } s.mu.Lock() s.cache[id] = status s.mu.Unlock() return &Order{ID: id, Status: status}, nil } type StatusParams struct { Status string `json:"status"` } //encore:api auth method=POST path=/orders/:id/status func (s *Service) SetStatus(ctx context.Context, id int64, p *StatusParams) (*Order, error) { customer, _ := auth.UserID() switch p.Status { case "placed", "paid", "shipped", "cancelled": default: return nil, &errs.Error{Code: errs.InvalidArgument, Message: "unknown status"} } // The trigger sends the notification when this transaction commits. res, err := db.Exec(ctx, `UPDATE orders SET status = $2, updated_at = now() WHERE id = $1 AND customer_id = $3`, id, p.Status, string(customer)) if err != nil { return nil, err } if res.RowsAffected() == 0 { return nil, &errs.Error{Code: errs.NotFound, Message: "order not found"} } return &Order{ID: id, Status: p.Status}, nil }

Three details carry the correctness here. The connection is closed rather than returned to the pool when the listener exits, since that session still has LISTEN active. The cache is cleared every time listening starts, because anything changed while the listener was disconnected produced a notification nobody received. And the backoff keeps a database failover from turning into a reconnect storm.

We ran the trigger and the listener loop against Postgres 17: an insert and an update each produced one notification, two identical pg_notify calls inside one transaction were delivered once, and an update in a rolled-back transaction produced none.

When an event has to survive a restart, the same service can publish to an Encore Pub/Sub topic instead, which a deploy provisions as SNS and SQS on AWS or Pub/Sub on GCP, with retries and a dead-letter queue. The transactional outbox package writes those messages in the same transaction as the row, which keeps the property LISTEN/NOTIFY had for free. An agent asked to "make this event durable" changes a declaration in Go and runs it locally, instead of adding a broker to a separate infrastructure repository.

Best fit: Go services on Postgres that want cache invalidation or live updates across replicas from database changes, with a path to durable Pub/Sub in the same codebase.

How do you use LISTEN/NOTIFY without a framework?

Postgres with pgx or node-postgres

With plain pgx, the listener is the same loop on a connection you open from DATABASE_URL. With node-postgres, use a dedicated Client rather than a Pool, because a pooled client would release the session that registered LISTEN:

// listener.ts import { Client } from "pg"; export async function listen(onChange: (id: number, status: string) => void) { const client = new Client({ connectionString: process.env.DATABASE_URL }); client.on("notification", (msg) => { if (msg.channel !== "order_status" || !msg.payload) return; const { id, status } = JSON.parse(msg.payload); onChange(id, status); }); client.on("error", (err) => { console.error("listener connection failed", err); process.exitCode = 1; // let the process manager restart it, or reconnect here }); await client.connect(); await client.query("LISTEN order_status"); return client; }

A few limits apply wherever the listener runs. A NOTIFY payload must be shorter than 8000 bytes, so send IDs, not documents. Notifications queue in a shared area of 8 GB in a standard installation, and a session that sits in a long transaction while listening holds that queue from being cleaned up, as the Postgres docs warn. PgBouncer in transaction pooling mode does not support LISTEN, since the feature table lists it as session-mode only, so the listener needs a direct connection or a session pool. Locally you need a Postgres for every developer with the same trigger applied, and in production one listener connection per instance counts against max_connections.

Best fit: teams already on Postgres who want lightweight change signals without another service, and who can treat missed notifications as a cache miss rather than lost data.

How to choose

Use LISTEN/NOTIFY when the event is a hint, such as "this row changed, refresh it" or "there is new work in the jobs table", and the truth stays in a table that a reconnecting listener can read. Use a Pub/Sub topic when another service must process every event even if it was down, and an outbox when the event must match a database commit exactly. Pick Encore when you want both in one Go codebase, with the database, the topic and the listener running locally under encore run and provisioned in your own AWS or GCP account.

Frequently asked questions

How does LISTEN/NOTIFY work in Postgres?

A session runs LISTEN channel_name, and any session that runs NOTIFY channel_name, 'payload' or SELECT pg_notify('channel_name', 'payload') sends an event to every session listening on that channel. Notifications sent inside a transaction are delivered only if and when the transaction commits.

Is Postgres LISTEN/NOTIFY reliable?

Yes. For sessions that are connected and listening. It is not durable: a listener that is disconnected, restarting or not yet listening misses the notification, and nothing is stored for later. Use it to wake up or invalidate, and keep the state itself in a table that a reconnecting listener can read.

What is the payload size limit for pg_notify?

In the default configuration the payload must be shorter than 8000 bytes. Send an ID and a small amount of context, and have the listener read the full row from the table when it needs more.

Does LISTEN/NOTIFY work with PgBouncer?

No. It does not work in transaction pooling mode: LISTEN registers on a server session and the next transaction from the same client may run on a different server connection. Give the listener its own direct or session-pooled connection, and keep using the pool for normal queries.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders