CONCEPT Cited by 1 source

Logical replication as pub/sub¶

Definition¶

Logical replication as pub/sub is the architectural observation that Postgres logical replication — normally deployed for cross-database replication or CDC — is already a reliable, ordered, durable pub/sub substrate that application code can subscribe to for real-time fan-out of arbitrary events. You don't need a separate broker (Kafka, NATS, Redis pub/sub, LISTEN/NOTIFY) if logical replication already carries what you need to fan out.

Mechanism¶

Set wal_level = logical in Postgres, create a PUBLICATION over the tables that should fan out, and have each subscriber open a replication slot consuming that publication. Writers continue to INSERT / UPDATE / DELETE as normal — no separate publish API call. Each commit produces row-level events delivered to every slot in commit order with full before/after column values.

The key property: one row operation → one event → delivered to every subscriber. The database write path is the publish path; the WAL is the durable log.

Why it's a real pub/sub¶

Ordered: events arrive in commit order.
Durable: the stream is the WAL, which is crash-safe and replicated to read replicas. A subscriber that disconnects and reconnects resumes from its last acknowledged LSN.
Fan-out: multiple replication slots can consume the same publication independently, each with its own progress cursor.
Heterogeneous events on one transport: chat messages, presence changes, video frames, and state transitions all flow through the same stream if the backing tables are in the publication. (Source: sources/2026-02-27-planetscale-video-conferencing-with-postgres.)
Back-pressure built in: a slow subscriber slows WAL recycling but doesn't silently drop events.

When it works¶

Events are naturally table-row-shaped (or can be INSERT-ed as such).
You need exactly-once, ordered delivery and already run Postgres.
The write rate fits what Postgres can commit without WAL growth outrunning vacuum / subscriber consumption.
A small relay process can sit between Postgres and the end clients (e.g. a Node.js WebSocket relay per patterns/websocket-relay-over-logical-replication).

When it doesn't¶

You have a write rate that saturates Postgres long before it saturates a dedicated broker (Kafka, NATS) — use the broker.
Events are short-lived and you don't want them to touch disk (fire-and-forget). Logical replication requires WAL, so every event is persisted.
You want to run on unlogged tables for write speed — unlogged writes skip the WAL and are therefore invisible to logical replication.
Payload size is large enough that per-row overhead becomes the bottleneck, and you'd rather use a blob store for the media + a small event for the pointer.

Relationship to other Postgres pub/sub primitives¶

LISTEN / NOTIFY is the obvious first choice but the concepts/listen-notify-payload-limit|8 KB payload limit and best-effort delivery make it unsuitable for media-sized events or ordering-sensitive workloads. Logical replication has no payload size limit other than the row / WAL record limit, and it guarantees ordered at-least-once delivery to a persistent slot.
Triggers + polling is the legacy answer — strictly weaker (polling latency + load + ordering guarantees that depend on read- after-write consistency).

Seen in¶

sources/2026-02-27-planetscale-video-conferencing-with-postgres — canonical wiki framing for the primitive. Nick Van Wiggeren builds a bidirectional 15 fps video-chat over a $5 PlanetScale Postgres by inserting 25–40 KB JPEGs into a video_frames table and having a Node.js WebSocket relay (pg-relay) consume a logical-replication slot on the same database; it checks each delivered row's to_id and forwards the JPEG bytes over WebSocket to the recipient. The same relay + publication simultaneously carries chat messages, user presence, and call-state transitions — "the same mechanism that pushes video frames to call participants also pushes chat messages, user presence changes, and call state transitions." Verbatim framing of the primitive: "PostgreSQL's logical replication gives us a reliable and ordered change stream. You get INSERT, UPDATE, and DELETE events for every table in the publication, delivered in commit order. This means we don't have to poll Postgres with SELECT statements from the table fast enough to render 15fps video." The post deliberately sets up the contrast: LISTEN/NOTIFY's 8 KB limit and unlogged-tables' WAL skip are both rejected alternatives that make logical replication the load-bearing choice.