Skip to content

CONCEPT Cited by 3 sources

Logical replication

Definition

Logical replication is a replication mode in which the primary database emits a stream of row-level change events (insert / update / delete with primary-key and column values), instead of replicating opaque storage-layer artefacts like physical pages or WAL byte-ranges ("physical replication"). Consumers of the stream can materialise the changes into any compatible target — another database of the same or different kind, a Kafka topic, a search index, a data lake, an event consumer — because the payload is semantic rather than storage-format-specific.

In Postgres, logical replication is enabled by setting wal_level=logical and creating a publication (the set of tables being replicated) and a replication slot (a persistent cursor into the WAL that prevents the WAL from being recycled until the consumer has acknowledged consumption through that point).

Why it matters for system design

Logical replication is the substrate under almost every CDC pipeline in the Postgres ecosystem. Debezium's Postgres source connector subscribes to a logical replication slot and emits the row-level changes as Kafka records. That lets one Postgres feed any number of heterogeneous downstream systems without a custom trigger / polling pipeline per consumer.

Contrast with physical streaming replication (the WAL- byte-stream mode Postgres uses for hot standbys): physical replication is simpler and faster but requires binary-compatible replicas, cannot transform records in flight, and cannot feed non-Postgres destinations.

Requirements on Postgres

From Datadog's 2025-11-04 retrospective, enabling logical replication in service of a Debezium CDC pipeline requires:

  • wal_level=logical (instance-wide setting).
  • Postgres users with the correct replication permissions.
  • Replication publishers (publications) defining which tables / operations to replicate.
  • Replication slots (one per consumer) to pin WAL progress.
  • Heartbeat tables — a Debezium-specific pattern where a dummy table gets written periodically so the slot can advance LSN during quiet periods; without it, an idle slot prevents WAL recycling indefinitely (Postgres pins WAL until the slowest active slot catches up).

(Source: sources/2025-11-04-datadog-replication-redefined-multi-tenant-cdc-platform)

Relationship to WAL

Logical replication is derived from the same Postgres WAL stream that physical replication uses, but decoded into row-level change events via the logical-decoding plugin. The WAL is therefore load-bearing in two roles on a CDC-enabled Postgres:

  1. Durability of local transactions (the original purpose).
  2. Carrier of the change stream for all downstream logical consumers.

This dual role makes logical replication extra-sensitive to WAL- retention configuration: slow or stalled consumers pin WAL and can exhaust disk space on the primary.

Seen in

  • sources/2025-03-18-redpanda-3-powerful-connectors-for-real-time-change-data-capture — canonical wiki disclosure of logical replication as the Postgres CDC substrate for Redpanda Connect's postgres_cdc input connector. Canonical verbatim: "PostgreSQL captures changes at a transaction-level via logical replication, ensuring data consistency by only streaming fully committed transactions." Plus: "When creating a replication slot in PostgreSQL, the connector exports consistent snapshot of your tables and seamlessly transitions to streaming ongoing changes from that snapshot point." The replication slot mechanism is load-bearing: it provides the atomic snapshot-to-streaming transition boundary that the CDC consumer rides on. Canonical framing: logical replication's slot-owned offset durability is "integrated with Redpanda Connect's at-least-once delivery guarantees ensuring you never drop any data" — a structurally different posture from MySQL's consumer-owned binlog offset that Redpanda's mysql_cdc uses. Also canonicalises Redpanda Connect's parallel-snapshot-of-large-table capability on the Postgres side as the differentiator against stock Debezium.

  • sources/2025-11-04-datadog-replication-redefined-multi-tenant-cdc-platform — Postgres logical replication with wal_level=logical + publications + slots + heartbeat tables is the source side of Datadog's managed multi-tenant CDC platform, feeding Debezium source connectors.

  • sources/2026-04-21-planetscale-postgres-high-availability-with-cdc — canonical wiki disclosure of the substrate asymmetry that makes Postgres logical replication operationally distinct from MySQL binlog replication. Sam Lambert (PlanetScale CEO, 2025-09-12) canonicalises that logical replication rides on the physical redo WAL but tracks subscriber progress in a primary-local catalog ( logical replication slot), with Postgres 17 failover slots as partial mitigation. The asymmetry creates HA-CDC coupling: "slot progress is a single-node concern that must be coordinated across the cluster at failover time, and eligibility depends on subscriber behavior outside your control." Three explicit failure scenarios canonicalised — CDC quiet-period failover breaks the stream; fresh- pg_basebackup standbys remain ineligible until the subscriber polls; dormant physical standbys pin restart_lsn and can exhaust the primary's WAL volume. Complements Datadog's managed-CDC-platform framing above — Datadog's post is about running logical replication well at scale with first-party subscribers; this post is about the structural HA trade-offs when subscribers are outside operator control.

  • sources/2023-11-08-zalando-patching-the-postgresql-jdbc-driver — canonical wiki disclosure of the client-driver side of logical replication. Zalando's 2023-11 post canonicalises logical replication's wire protocol from the subscriber driver's perspective — specifically the distinction between Replication messages (carrying row-level change events) and KeepAlive messages (carrying current server LSN with no change-event payload). The post's contribution is making pgjdbc ack KeepAlive- reported LSNs to advance the slot when no Replication events are pending — the KeepAlive-LSN- advancement mechanism, shipped in pgjdbc 42.7.0. Addresses the runaway WAL-growth failure mode that surfaces at fleet scale (systems/zalando-postgres-event-streams runs hundreds of logical-replication slots on Debezium Engine).

  • — canonical wiki extension: logical replication as a general-purpose real-time pub/sub substrate, not just the CDC-pipeline carrier. Nick Van Wiggeren (PlanetScale SVP Engineering) builds a bidirectional 15 fps video chat by inserting 25–40 KB JPEGs into a video_frames BYTEA column on a $5 PlanetScale Postgres, with a ~400-line Node.js WebSocket relay (pg-relay) consuming a logical replication slot on the same database and forwarding each row's JPEG bytes to the recipient's browser. Canonical framing verbatim: "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 same relay + publication simultaneously carries chat, user-presence, and call-state transitions on one transport — canonicalised on the wiki as logical replication as pub/sub and the database-as- real-time-message-broker pattern. The post also canonicalises the two rejected alternatives that define the primitive's fit: LISTEN/NOTIFY's 8 KB payload limit rules it out for media, and unlogged tables skip the WAL → invisible to every subscriber → back to polling. Canonical statement that the WAL is the stream.

Last updated · 542 distilled / 1,571 read