LISTEN and NOTIFY From Go for Live Database Updates

Polling the database for changes works and wastes effort: every client asks every second whether anything happened, and the answer is nearly always no. PostgreSQL has a built in alternative, LISTEN and NOTIFY, a small publish and subscribe system inside the server. A session says LISTEN orders, another says NOTIFY orders, 'payload', and the listener is woken immediately. It is a good fit for cache invalidation, waking workers, live dashboards and anything where "something changed" is the message. It has sharp edges, and this article runs each of them on Go 1.25.6 with pgx and PostgreSQL 18.6.

It belongs to the database cornerstone. The pgx specific API is the subject of pgx Listen and Notify, and waking queue workers with it is part of a Postgres job queue.

The basics

A channel is just a name. Any session can send to it with NOTIFY channel, 'text' or select pg_notify('channel', 'text'), and any session that ran LISTEN channel receives the text and the sender's process id. Three properties decide how you use it:

• Delivery happens at commit. A notification sent inside a transaction waits until the transaction commits. If it rolls back, nothing is sent.
• It is not stored. The server holds notifications in a queue only until each listening session has read them. A session that is not connected gets nothing, later or ever.
• The payload is text under 8000 bytes. It is not a place for data.

The lab

The program creates a table with a trigger that sends a notification for every insert, starts a listener, and runs five experiments.

package main

import (
	"context"
	"fmt"
	"log"
	"strings"
	"sync"
	"time"

	"github.com/jackc/pgx/v5"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

// listen collects every notification on a channel until ctx ends.
type listener struct {
	mu   sync.Mutex
	got  []string
	conn *pgx.Conn
	done chan struct{}
}

func startListener(ctx context.Context, channel string) *listener {
	conn, err := pgx.Connect(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	if _, err := conn.Exec(ctx, "listen "+channel); err != nil {
		log.Fatal(err)
	}
	l := &listener{conn: conn, done: make(chan struct{})}
	go func() {
		defer close(l.done)
		for {
			n, err := conn.WaitForNotification(ctx)
			if err != nil {
				return
			}
			l.mu.Lock()
			l.got = append(l.got, n.Payload)
			l.mu.Unlock()
		}
	}()
	return l
}

func (l *listener) stop() {
	l.conn.Close(context.Background())
	<-l.done
}

func (l *listener) take() []string {
	time.Sleep(150 * time.Millisecond)
	l.mu.Lock()
	defer l.mu.Unlock()
	out := l.got
	l.got = nil
	return out
}

func main() {
	ctx := context.Background()
	pub, err := pgx.Connect(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pub.Close(ctx)
	for _, q := range []string{
		"drop table if exists orders615",
		"create table orders615 (id bigserial primary key, item text not null)",
		`create or replace function notify_order615() returns trigger language plpgsql as $$
		 begin
		   perform pg_notify('orders615', json_build_object('id', new.id, 'item', new.item)::text);
		   return new;
		 end $$`,
		"create trigger orders615_notify after insert on orders615 for each row execute function notify_order615()",
	} {
		if _, err := pub.Exec(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	l := startListener(ctx, "orders615")

	// 1. A trigger turns each insert into a notification.
	for _, item := range []string{"book", "pen", "lamp"} {
		_, _ = pub.Exec(ctx, "insert into orders615 (item) values ($1)", item)
	}
	fmt.Println("1. three inserts delivered:", l.take())

	// 2. Notifications are sent at commit: a rolled back transaction sends nothing.
	tx, _ := pub.Begin(ctx)
	_, _ = tx.Exec(ctx, "insert into orders615 (item) values ('ghost')")
	_ = tx.Rollback(ctx)
	fmt.Println("2. after a rollback:", l.take())

	// 3. Identical payloads on one channel inside one transaction are delivered once.
	tx, _ = pub.Begin(ctx)
	for i := 0; i < 3; i++ {
		_, _ = tx.Exec(ctx, "select pg_notify('orders615', 'same')")
	}
	_, _ = tx.Exec(ctx, "select pg_notify('orders615', 'other')")
	_ = tx.Commit(ctx)
	fmt.Println("3. three identical notifies and one different, delivered:", l.take())

	// 4. The payload limit.
	_, err = pub.Exec(ctx, "select pg_notify('orders615', $1)", strings.Repeat("x", 9000))
	fmt.Println("4. a 9000 byte payload:", err)

	// 5. Notifications are not stored: a listener that is away misses them.
	l.stop()
	_, _ = pub.Exec(ctx, "insert into orders615 (item) values ('while away')")
	l = startListener(ctx, "orders615")
	fmt.Println("5. listener reconnected, delivered:", l.take())
	var missed int
	_ = pub.QueryRow(ctx, "select count(*) from orders615 where id > 3").Scan(&missed)
	fmt.Println("   rows inserted after the first three (the catch-up query would find them):", missed)
	l.stop()
}

1. three inserts delivered: [{"id" : 1, "item" : "book"} {"id" : 2, "item" : "pen"} {"id" : 3, "item" : "lamp"}]
2. after a rollback: []
3. three identical notifies and one different, delivered: [same other]
4. a 9000 byte payload: ERROR: payload string too long (SQLSTATE 22023)
5. listener reconnected, delivered: []
   rows inserted after the first three (the catch-up query would find them): 1

The listener is an ordinary pgx.Conn. It runs listen orders615 and then loops on conn.WaitForNotification(ctx), which blocks until a notification arrives, the context ends or the connection fails. That connection does nothing else. Use a dedicated connection and not one from a pool. A pooled connection is returned and reused, and the LISTEN state belongs to the session, so a subscription made through db.Exec is attached to whichever connection the pool happened to pick and disappears with it. With database/sql, either use pgx.Connect for the listener as here, or db.Conn and hold the connection for the life of the program.

1. A trigger turns writes into messages

The trigger function calls pg_notify('orders615', json_build_object('id', new.id, 'item', new.item)::text). Three inserts delivered three notifications, in order, with payloads like {"id" : 1, "item" : "book"}. The application that inserts does not know anyone is listening, which is the point: any writer, including psql and a migration, produces the same events. Put the notification in a trigger when every change must be announced, and in application code when only some should be.

2. Rollbacks send nothing

A transaction inserted a row and rolled back. The listener received nothing. Because delivery waits for commit, a listener never sees a state that was undone, and it can safely read the row after being told about it. This is the property that makes notifications usable for cache invalidation: you are never told about uncommitted data.

3. Identical payloads are merged

Inside one transaction the program sent the payload same three times and other once. The listener received [same other]. PostgreSQL drops repeated notifications with the same channel and the same payload in one transaction. So a notification means "something changed", never "this happened N times". A counter built from notification counts will be wrong. If every event matters individually, put a unique id in each payload, or better, treat the notification as a wake up and read the rows.

4. The size limit

A 9000 byte payload failed with ERROR: payload string too long (SQLSTATE 22023). The limit is 8000 bytes with the default build. Do not squeeze data in. Send the key, {"id": 41}, and let the listener query the row. That also removes the problem of a stale payload: the listener reads current data.

5. Missed messages

The listener was stopped, an insert happened, and a new listener was started. It received []: nothing. One row, inserted while the listener was away, was never announced to it. A dropped connection, a deploy or a database failover all create such gaps, and the server will never replay them.

Design for it. The reliable pattern is to treat NOTIFY as a hint and keep the table as the source of truth:

1. On start and after every reconnect, run a catch-up query for everything newer than the last id you processed.
2. On each notification, run the same query again rather than trusting the payload.
3. Also run it on a timer, every 30 seconds or so, to cover anything lost.

Done this way, a lost notification costs latency and not correctness. The same design is why the outbox relay polls even if it also listens.

Operating it

• Reconnects. WaitForNotification returns an error when the connection dies. The loop must reconnect with backoff, run listen again, and then do the catch-up query. Subscribe first and query second, so nothing slips through the gap between them.
• A slow listener can hurt the server. Notifications queue in shared memory, 8GB by default, until every listener has read them. A listener that stops reading while holding its connection open eventually fills the queue, and then NOTIFY fails at commit. Always drain notifications promptly and monitor the queue with pg_notification_queue_usage().
• A long open transaction in a listening session can delay cleanup of the queue; keep listener connections free of transactions.
• Poolers. PgBouncer in transaction pooling mode cannot carry LISTEN, since the session changes per transaction. Give listeners a direct connection or a session pooled one.
• Fan out. Every listening session costs a connection. A hundred services each holding one is fine; ten thousand browsers each holding one is not. Listen in one process and push to clients over WebSocket or server sent events.
• Throughput. NOTIFY takes a global lock briefly at commit, so a very high rate of notifying transactions serialises. Notify per batch, not per row, when you insert thousands of rows.

When to use something else

LISTEN and NOTIFY fit low volume signalling inside one database. They do not fit guaranteed delivery, replay, or consumers that are often offline; use a table and polling, the outbox pattern, or a broker for those. For change capture of every row, logical replication reads the write ahead log and misses nothing; a pgx logical replication client shows the heavier option. The commands are documented in the PostgreSQL manual under NOTIFY and LISTEN, including the merging and size rules observed above.