bethel-nz
/Making a Go application reactive with PostgreSQL
/essay7 min read
/essay7 min read

September 15, 2025

Making a Go application reactive with PostgreSQL

A way for a Go app to react to PostgreSQL changes without running a separate notification service.

I wanted a product to feel reactive without turning notifications into a platform of their own. The system needed three things: storage that kept notifications until a user could read them, a signal that woke connected users when something changed, and a transport that exposed both to a client.

That separation was useful. A standalone service was not.

I did not have hundreds of thousands of active users or enough notification traffic to justify operating a standalone service. I still wanted the product to feel reactive: write something once, let a connected interface update immediately, and allow someone who was offline to find the same notification later.

Then I revisited PostgreSQL's native LISTEN and NOTIFY commands. The database already had the signal primitive I needed: it could remain the durable store, emit a small message after a successful write, and wake a Go process without polling or introducing another service.

The resulting system is intentionally small:

  1. The application inserts a notification row.
  2. An AFTER INSERT trigger calls pg_notify().
  3. PostgreSQL delivers that signal when the transaction commits.
  4. One dedicated Go connection listens for it.
  5. An in-process hub wakes subscribers for the affected user.
  6. The client reads the durable notification from PostgreSQL.

It is closer to a reactive application than a message broker.

Store the fact, signal the change

The most important decision was not putting the whole notification system inside the NOTIFY payload. PostgreSQL notifications are ephemeral and deliberately small. The table stores the notification; the message only says which stored fact changed.

sql
1CREATE TABLE notifications (
2id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
3user_id uuid NOT NULL,
4kind text NOT NULL,
5title text NOT NULL,
6body text NOT NULL,
7read_at timestamptz,
8created_at timestamptz NOT NULL DEFAULT now()
9);
10
11CREATE OR REPLACE FUNCTION signal_notification_insert()
12RETURNS trigger AS $$
13BEGIN
14PERFORM pg_notify(
15 'notification_events',
16 json_build_object(
17 'id', NEW.id,
18 'user_id', NEW.user_id
19 )::text
20);
21
22RETURN NEW;
23END;
24$$ LANGUAGE plpgsql;
25
26CREATE TRIGGER notification_inserted
27AFTER INSERT ON notifications
28FOR EACH ROW EXECUTE FUNCTION signal_notification_insert();

The application still performs one ordinary insert. It does not insert and then separately publish to Redis, call a WebSocket manager, or hope a second network operation succeeds.

An AFTER trigger runs within the transaction, while PostgreSQL waits until commit to deliver the notification. A rolled-back insert therefore produces neither a visible row nor a signal telling the interface to fetch one.

This is the useful “success layer”: once a listener receives the message, it can assume the referenced row committed.

LISTEN owns one database connection

LISTEN is session-scoped, so it should not run through the ordinary query pool. The listener owns one connection for its entire lifetime and uses it for no unrelated work.

go
1type notificationEvent struct {
2 ID string `json:"id"`
3 UserID string `json:"user_id"`
4}
5
6func listen(ctx context.Context, dsn string, hub *Hub) error {
7 conn, err := pgx.Connect(ctx, dsn)
8 if err != nil {
9 return fmt.Errorf("connect listener: %w", err)
10 }
11 defer conn.Close(context.Background())
12
13 if _, err := conn.Exec(ctx, "LISTEN notification_events"); err != nil {
14 return fmt.Errorf("listen: %w", err)
15 }
16
17 for {
18 notification, err := conn.WaitForNotification(ctx)
19 if err != nil {
20 if ctx.Err() != nil {
21 return ctx.Err()
22 }
23 return fmt.Errorf("wait for notification: %w", err)
24 }
25
26 var event notificationEvent
27 if err := json.Unmarshal([]byte(notification.Payload), &event); err != nil {
28 continue
29 }
30
31 hub.Publish(event.UserID, event)
32 }
33}

This listener cannot use transaction pooling. A pooler may move ordinary queries between backend sessions, but a LISTEN subscription belongs to the specific session that created it. If that connection closes, the process must reconnect and subscribe again.

A small Go hub is enough

The in-process hub does not store notification history. PostgreSQL already does that. It only keeps the channels belonging to currently connected users and removes them when those users disconnect.

go
1type Hub struct {
2 mu sync.RWMutex
3 subs map[string]map[chan notificationEvent]struct{}
4}
5
6func (h *Hub) Subscribe(userID string) (<-chan notificationEvent, func()) {
7 h.mu.Lock()
8 defer h.mu.Unlock()
9
10 ch := make(chan notificationEvent, 1)
11 if h.subs[userID] == nil {
12 h.subs[userID] = make(map[chan notificationEvent]struct{})
13 }
14 h.subs[userID][ch] = struct{}{}
15
16 cancel := func() {
17 h.mu.Lock()
18 defer h.mu.Unlock()
19 delete(h.subs[userID], ch)
20 close(ch)
21 if len(h.subs[userID]) == 0 {
22 delete(h.subs, userID)
23 }
24 }
25
26 return ch, cancel
27}
28
29func (h *Hub) Publish(userID string, event notificationEvent) {
30 h.mu.RLock()
31 defer h.mu.RUnlock()
32
33 for ch := range h.subs[userID] {
34 select {
35 case ch <- event:
36 default:
37 // The row is durable; a slow client can catch up from PostgreSQL.
38 }
39 }
40}

This is where my design differs from keeping a large buffered channel for every possible user. Memory grows with active subscriptions, not with the total number of accounts. The buffer is only a wake-up slot, not a second notification database.

Dropping a signal for a slow subscriber is acceptable because the signal carries no unique state. The client can query unread rows and converge on the same result.

Join the snapshot before the live tail

A reactive endpoint needs both history and live changes. Querying first and subscribing second leaves a gap: a notification can commit between those operations and never reach the open connection.

The safer order is to subscribe first, read the current rows second, then begin forwarding signals:

go
1events, cancel := hub.Subscribe(userID)
2defer cancel()
3
4items, err := store.ListUnreadNotifications(ctx, userID)
5if err != nil {
6 return err
7}
8
9if err := stream.Send("snapshot", items); err != nil {
10 return err
11}
12
13for {
14 select {
15 case <-ctx.Done():
16 return ctx.Err()
17 case event := <-events:
18 item, err := store.GetNotification(ctx, event.ID, userID)
19 if err != nil {
20 continue
21 }
22 if err := stream.Send("notification", item); err != nil {
23 return err
24 }
25 }
26}

The transport can be long polling or Server-Sent Events when the client wants a continuous one-way stream. The storage and signal boundaries do not change.

Where the lightweight version stops

PostgreSQL LISTEN/NOTIFY is not a queue. Notifications are not replayable, there is no acknowledgement, and a listener that is offline at commit time misses the signal. Payloads must also stay below PostgreSQL's limit, which is another reason to publish an ID rather than a full record.

For in-app notifications, the table makes those constraints manageable. A reconnect reads unread rows, so a missing signal delays the interface but does not erase the notification.

Email is different. Once sending mail became part of the design, I needed work that could survive a provider outage and retry without an open browser or database listener. That path eventually belonged in Redis Streams, where a worker could acknowledge completion, reclaim abandoned messages, and dead-letter repeated failures.

That is a different article. The useful lesson from this smaller version is that “reactive” does not automatically mean Kafka, a dedicated notification service, or even Redis. If the durable state is already in PostgreSQL, LISTEN/NOTIFY can be the small signal that connects a committed row to a live Go application.

If events became a broader coordination layer inside the process, I would take cues from Tailscale's event bus. It connects typed publishers and subscribers, gives clients an ordered view of published events, and includes tooling for inspecting the bus. That machinery makes sense when many components communicate through events and ordering becomes part of the contract. Here, each signal only invalidates one user's database-backed view. A small keyed hub was easier to operate and easier to explain, and staying lean felt like the right boundary.