PostgreSQL Logical Replication and CDC Explained

When an application changes a row, analytics systems, search indexes, and other services often need to learn about that change without repeatedly querying the production database. PostgreSQL logical replication provides one way to expose committed table changes as a continuous stream. This explainer is for developers and data engineers who need to understand change data capture (CDC), the PostgreSQL components behind it, and the operational risks to plan for.

What Is PostgreSQL Logical Replication and CDC?

Change data capture is a pattern for identifying and delivering inserts, updates, and deletes from a source system as they occur. Instead of periodically copying every row, a CDC pipeline forwards only the changes since its previous position.

PostgreSQL’s built-in logical replication is one implementation. A publisher selects tables and exposes their row changes; a subscriber connects, copies an initial table snapshot, then applies subsequent changes. The feature is designed for PostgreSQL-to-PostgreSQL replication, but its logical decoding foundation can also feed external CDC connectors such as Debezium.

Logical replication is different from physical replication, which copies PostgreSQL’s underlying data pages and write-ahead log (WAL) to maintain a standby server. Logical replication works with table-level changes and can select published tables. PostgreSQL describes the built-in model in its logical replication documentation and the lower-level change stream in its logical decoding documentation.

Why Use CDC Instead of Repeated Queries?

An analytics job can poll a table for rows whose updated_at value is newer than its last run. That is straightforward, but it creates extra queries, depends on a reliable watermark, and can miss changes when timestamps or transaction timing are handled incorrectly. Polling also has difficulty representing hard deletes unless the application records tombstones.

CDC reads the database’s change history instead. It can capture deletes as well as inserts and updates, and consumers can process the changes independently. That makes it useful for keeping a warehouse, search index, cache, or downstream service close to the source’s state without adding a synchronous call to every application write.

CDC is not a replacement for every integration pattern. A small table with infrequent changes may be simpler to poll, while synchronous application events may be preferable when a business workflow needs an immediate response. The right choice depends on freshness, completeness, source load, and the consequences of a delayed or repeated event.

How Logical Replication Works

The basic data path is:

Application transaction -> PostgreSQL WAL -> logical decoding -> publication -> subscriber or CDC connector

1. A transaction changes rows and commits. PostgreSQL records the changes in WAL.
2. A logical decoding output plugin turns relevant WAL records into a logical representation. The built-in pgoutput plugin is used by PostgreSQL’s native logical replication.
3. A publication selects the tables and operations to expose.
4. A replication slot tracks how far a consumer has read. It lets PostgreSQL retain WAL that the consumer still needs.
5. A native subscription or external connector reads the stream and applies or emits changes.

For a native subscription, PostgreSQL first takes a consistent copy of the published tables and then continues with changes that occurred during and after that copy. Subsequent changes are applied at the subscriber in commit order for that subscription. The subscriber does not automatically receive the source table definitions or sequence values; schema changes and sequence state need separate handling.

These methods solve different problems. In particular, physical replication is useful for a standby but does not directly provide a portable row-change event to an analytics consumer.

The replication connection itself runs over TCP. RFC 9293 specifies TCP’s reliable, ordered byte-stream service; that transport guarantee does not make a downstream database write, event handler, or external side effect happen exactly once. Consumers still need durable progress tracking and safe retry behavior.

Key Components and Operational Concepts

• WAL and wal_level: Logical decoding requires wal_level to be set to logical on the publisher. WAL is the source of the change stream, not a separate event database.
• Publication: A named set of tables and operations, such as inserts, updates, and deletes. Publications do not copy table DDL.
• Replication slot: A persistent consumer position on the publisher. If a consumer stops advancing, PostgreSQL may retain WAL needed by that slot and use up disk space.
• Replica identity: PostgreSQL needs a row identifier to describe updates and deletes. The default is the primary key. A table without a suitable key may need REPLICA IDENTITY FULL, which can increase the amount of old-row data logged and the work required to identify rows.
• Subscription or connector: A native subscription applies changes to another PostgreSQL database. A connector such as Debezium reads logical changes and converts them into events for other systems, often through Kafka.
• Schema and event contract: Consumers must handle column additions, type changes, deletes, and versioning deliberately. A connector can serialize changes, but teams still own compatibility and consumer behavior.

Real-World Uses

Analytics ingestion: A CDC consumer can load inserts, updates, and deletes into a warehouse or data lake. For storage and processing patterns around those destinations, see the data lake architecture guide.

Search and cache synchronization: Changes to product or account tables can update a search index or cache without having each application request update both systems synchronously. Consumers should be idempotent so a retry does not create duplicate effects.

Database migration: Logical replication can keep selected tables synchronized while a team prepares a new PostgreSQL instance. Cutover still requires a plan for writes, lag, sequences, schema compatibility, and rollback; replication alone does not make a migration transparent.

Event-driven integration: A connector can publish row changes to a retained event stream for multiple independent consumers. The Apache Kafka event streaming guide explains how those consumers manage offsets, replay, and delivery semantics.

Getting Started with a Local PostgreSQL CDC Example

This small Docker Compose setup runs a publisher and subscriber on one machine. It is for learning only: the shared local password, open ports, and lack of persistent volumes are not production settings. Install Docker with Compose, then save this as compose.yaml:

services:
  publisher:
    image: postgres:17
    environment:
      POSTGRES_USER: cdc
      POSTGRES_PASSWORD: localdev
      POSTGRES_DB: app
    command:
      - postgres
      - -c
      - wal_level=logical
      - -c
      - max_replication_slots=10
      - -c
      - max_wal_senders=10
    ports:
      - "5432:5432"

  subscriber:
    image: postgres:17
    environment:
      POSTGRES_USER: cdc
      POSTGRES_PASSWORD: localdev
      POSTGRES_DB: app
    ports:
      - "5433:5432"

Start both databases and wait until they report as running:

docker compose up -d
docker compose ps

On the publisher, create a table, a dedicated login for the replication connection, and a publication. The password below is only for this disposable local setup:

CREATE TABLE public.orders (
  id bigint PRIMARY KEY,
  status text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE ROLE cdc_repl WITH LOGIN REPLICATION PASSWORD 'localdev';
GRANT CONNECT ON DATABASE app TO cdc_repl;
GRANT USAGE ON SCHEMA public TO cdc_repl;
GRANT SELECT ON public.orders TO cdc_repl;

CREATE PUBLICATION app_changes FOR TABLE public.orders
  WITH (publish = 'insert, update, delete');

On the subscriber, create the matching table first; logical replication transfers row data, not its schema. Then create the subscription:

CREATE TABLE public.orders (
  id bigint PRIMARY KEY,
  status text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE SUBSCRIPTION app_changes
  CONNECTION 'host=publisher port=5432 dbname=app user=cdc_repl password=localdev'
  PUBLICATION app_changes
  WITH (copy_data = true);

Run each SQL block in a psql session connected to the named service, for example docker compose exec publisher psql -U cdc -d app. Once the subscription is ready, insert a row on the publisher and query the subscriber:

docker compose exec publisher psql -U cdc -d app \
  -c "INSERT INTO public.orders (id, status) VALUES (101, 'created');"

docker compose exec subscriber psql -U cdc -d app \
  -c "SELECT id, status, updated_at FROM public.orders;"

To check the subscriber’s view of replication progress, run:

SELECT subname, received_lsn, latest_end_lsn
FROM pg_stat_subscription;

On the publisher, inspect logical slots and their activity:

SELECT slot_name, active, restart_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

In a real deployment, provision the required replication settings before enabling a consumer, protect the replication connection with TLS and restricted network access, and use credentials with only the required privileges. Changes to settings such as wal_level require a PostgreSQL restart. Monitor slot activity and retained WAL: a disconnected consumer can cause disk usage to grow even while the database continues serving application traffic.

Common Misconceptions

“Logical replication copies the whole database.”

A publication selects tables and operations; it does not automatically copy every database object. The initial table copy can be enabled for a subscription, but DDL, sequences, and other objects need their own migration process.

“A replication slot is just a checkpoint.”

A slot records consumer progress and protects the WAL still needed from being recycled. If the consumer is abandoned or broken, the retained WAL can fill the publisher’s disk. Monitor slots and remove only those that are confirmed obsolete.

“CDC guarantees exactly-once business processing.”

Transporting a change and committing a side effect are separate operations. A consumer can retry after a crash, so downstream handlers should use stable keys, deduplication, or idempotent upserts. Exactly-once behavior must be defined across the whole system boundary, not inferred from the presence of a replication slot.

Related Articles

• Compare logical, physical, and other approaches in database replication patterns.
• See how change streams fit into data lake ingestion and architecture.
• Review retention, partitioning, and consumer progress in Apache Kafka event streaming.

Changelog

• Initial publication. Last updated: October 1.