Postgres Realtime: Listen to Database Changes in Your App

To get Postgres realtime updates into an app, you need something that turns database changes into events and pushes them to clients over a websocket. Postgres gives you two building blocks: LISTEN/NOTIFY, a lightweight message channel, and logical replication, a stream of every insert, update and delete. A realtime server sits on top, filters the changes per user, and delivers them to browsers and mobile apps.

This article explains both building blocks, when each fits, and how a managed realtime layer handles the part that is easy to get wrong: making sure each user only receives the rows they are allowed to see.

#Option 1: LISTEN and NOTIFY

NOTIFY sends a message on a named channel. Any session that ran LISTEN on that channel receives it after the sending transaction commits.

-- session A
listen order_events;

-- session B
notify order_events, '{"id": 42, "status": "shipped"}';
-- or from a trigger:
select pg_notify('order_events', json_build_object('id', new.id, 'status', new.status)::text);

A trigger makes it automatic:

create function public.notify_order() returns trigger language plpgsql as $$
begin
  perform pg_notify('order_events',
    json_build_object('op', tg_op, 'id', new.id, 'status', new.status)::text);
  return new;
end $$;

create trigger orders_notify
  after insert or update on public.orders
  for each row execute function public.notify_order();

It is simple and built in, but know its limits:

• Payloads are small. The PostgreSQL docs set the default maximum payload at 8,000 bytes. Send ids, not whole rows.
• No delivery guarantee. If the listener is disconnected when the notification fires, it is gone. There is no replay.
• Needs a dedicated connection. LISTEN does not work through a transaction-mode pooler. Use a direct or session-mode connection.
• No per-user filtering. Every listener on the channel gets every message. You build authorization yourself.

LISTEN/NOTIFY is great for server-side workers ("a new job is ready, go check the queue") and cache invalidation. It is a poor fit for sending data straight to thousands of end users.

#Option 2: Logical replication

Logical replication decodes the write-ahead log into row-level changes. A client creates a replication slot, subscribes to a publication, and receives every insert, update and delete for the published tables, in commit order. Tools like Debezium build change data capture pipelines on it.

-- requires wal_level = logical
create publication app_changes for table public.orders, public.messages;

Strengths:

• Every committed change, with full row data.
• Ordered and resumable: a slot remembers how far the consumer got.
• No triggers needed on your tables.

Costs:

• A slot that is not consumed holds back WAL, and disk usage grows until the consumer catches up or the slot is dropped.
• For UPDATE and DELETE, the old row contains only the primary key unless you set replica identity full on the table.
• You still need a server that turns the stream into websocket messages and applies authorization.

#The missing piece: authorization

Here is the part that tutorials skip. If your chat app streams messages changes to clients, user A must not receive user B's private messages. With LISTEN/NOTIFY or raw logical replication, the database has no idea who is listening.

The robust answer is to check each change against the same rules the API uses. If you already have Row Level Security policies on the table, the realtime layer can evaluate them for each subscriber and send the change only to users whose policies allow them to select that row. That way there is one source of truth for "who can see what". See Postgres Row Level Security.

#Option 3: A managed realtime layer

Backends built on the open-source Postgres stack (StitchDB, Supabase and others) run a realtime server that reads logical replication for you, applies RLS, and delivers changes over websockets. The client API is the same across them.

On StitchDB it takes two steps.

#1. Publish the table

Every project has a built-in publication, stitchdb_realtime. Add the tables you want to stream, either with Enable Realtime in Studio or with SQL:

alter publication stitchdb_realtime add table public.messages;

#2. Subscribe from the client

import { createClient } from 'stitchdb-js'

const stitchdb = createClient('https://<project-ref>.p.stitchdb.com', anonKey)

stitchdb
  .channel('room-42')
  .on(
    'postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'messages', filter: 'room_id=eq.42' },
    (payload) => addMessage(payload.new)
  )
  .on(
    'postgres_changes',
    { event: 'DELETE', schema: 'public', table: 'messages' },
    (payload) => removeMessage(payload.old.id)
  )
  .subscribe()

event is INSERT, UPDATE, DELETE or *. Filters support eq, neq, lt, lte, gt, gte and in. The SDK connects to wss://<project-ref>.p.stitchdb.com/realtime/v1/ and reconnects automatically.

For UPDATE and DELETE, payload.old holds only the primary key unless you enable full replica identity:

alter table public.messages replica identity full;

That makes the WAL larger for that table, so turn it on only where you need the old values.

#Security

Each subscriber only gets rows their policies let them select. A published table with RLS off sends every row to every subscriber, and Studio's Advisors warn about that. Before you publish a table, make sure it has RLS on and a select policy:

alter table public.messages enable row level security;

create policy "room members read messages" on public.messages
  for select to authenticated
  using (exists (
    select 1 from public.room_members m
    where m.room_id = messages.room_id and m.user_id = auth.uid()
  ));

Full docs: Postgres changes.

#Broadcasting from SQL

Sometimes you do not want raw row changes on the wire. You want a shaped event: "order 42 shipped", with only the fields the client needs. On StitchDB, realtime.send publishes a message to a channel from SQL, for example in a trigger:

create function public.notify_order() returns trigger language plpgsql as $$
begin
  perform realtime.send(
    jsonb_build_object('id', new.id, 'status', new.status),
    'order-update',  -- event
    'orders',        -- channel
    false            -- public channel
  );
  return new;
end $$;

create trigger order_updated
  after update on public.orders
  for each row execute function public.notify_order();

Clients listening on the orders channel for order-update receive the payload. This is lighter than streaming the whole table, and you choose exactly what leaves the database. See Broadcast from the database.

#Choosing a Postgres realtime approach

#Limits to plan around

Realtime has per-plan limits. On StitchDB, concurrent realtime clients are 200 on Free, 500 on Pro, 2,000 on Scale and 10,000 on Enterprise, with per-second message and join limits as well. Connections above the concurrent limit are refused and clients that send too fast are rate limited. See Realtime limits. Above the included amounts, peak connections and messages are billed when the spend cap is off.

Also plan for reconnects. Mobile clients drop connections constantly. When a client reconnects, refetch the current state with a normal query, then resume listening. Do not assume you received every event while offline.

async function resync() {
  const { data } = await stitchdb.from('messages').select('*').eq('room_id', 42).order('id')
  setMessages(data)
}

#FAQ

#Is LISTEN/NOTIFY enough for a chat app?

For a prototype, maybe. For production you need delivery to many clients, authorization and reconnect handling, which a realtime server gives you.

#Do realtime subscriptions respect Row Level Security?

On StitchDB, yes: each subscriber receives only rows their policies allow them to select.

#Does realtime add load to the database?

Logical decoding and per-subscriber policy checks cost some CPU. Publish only the tables you need and filter subscriptions.

#Can I get the old row on update?

Yes, with alter table ... replica identity full. Otherwise only the primary key is included for updates and deletes.

Add live updates to your app in minutes: create a free project, publish a table, and open the Realtime inspector in Studio.