Building a PostgreSQL WAL‑Based CDC System: Principles and Engineering Implementation

Introduction

When a row in a database changes, downstream systems such as Elasticsearch, Redis, Kafka, or audit logs often need the change. All these scenarios rely on a single capability: continuously capturing INSERT, UPDATE, and DELETE events and reliably delivering them. This article starts from the challenges of ensuring no data loss, ordered changes, correct consumer recovery, and seamless bulk‑to‑incremental handoff, then walks through the principles and engineering of PostgreSQL WAL‑based CDC, contrasting it with the more familiar MySQL binlog CDC.

Why Choose Log‑Based CDC

The most straightforward way to keep downstream stores in sync is to write to them directly after the database write, but this fails when the downstream write crashes while the database commit succeeds. Log‑based CDC avoids this double‑write problem. Four CDC approaches are compared:

Application‑level delivery – carries business semantics but suffers from double‑write consistency issues.

Query polling – simple but adds latency and requires soft‑delete columns.

Database triggers – real‑time and transactional but invasive and increases write amplification.

Log parsing – low‑invasion, low‑latency, and complete coverage, though the consumer must handle more complex decoding.

Log parsing has become the mainstream CDC method because the database already records all committed changes in order.

PostgreSQL vs MySQL CDC Overview

Both databases provide a transaction log (MySQL binlog, PostgreSQL WAL) that can be used for CDC, but their semantics differ. PostgreSQL requires logical decoding and an output plugin, while MySQL’s binlog already contains row‑level events. Key differences include log basis (crash recovery vs replication), configuration parameters (e.g., wal_level=logical vs log_bin=ON), decoding mechanisms, output formats, data range handling, progress tracking, and log retention policies.

Four Core Concepts

Publication : declares which tables and operations are to be streamed.

Replication Slot : a server‑side bookmark that records the consumer’s progress (restart_lsn and confirmed_flush_lsn).

LSN (Log Sequence Number) : the position in the WAL, e.g., 16/B374D848, used to order and locate changes.

REPLICA IDENTITY : controls which old values are retained for UPDATE/DELETE, with settings DEFAULT, USING INDEX, FULL, or NOTHING.

These concepts together describe a logical replication flow: Publication defines the capture scope, LSN marks each change, Replication Slot stores the consumer’s position, and REPLICA IDENTITY determines the before‑image available to downstream systems.

Logical Replication Protocol

CDC consumers connect via the replication protocol and use a COPY ‑based streaming protocol. Important messages include: IDENTIFY_SYSTEM – obtains system ID, timeline, and current WAL position. START_REPLICATION SLOT … LOGICAL <lsn> – begins streaming from a slot at a given LSN. XLogData – carries decoded logical changes.

Primary keepalive and standby status updates for heartbeat and progress reporting.

When using the built‑in pgoutput plugin, the consumer must parse messages such as Begin, Relation, Insert, Update, Delete, and Commit. Transactions are emitted in commit order; PG 14+ can split large transactions into stream fragments.

Engineering Implementation – Minimal Example

First configure the instance (e.g., wal_level = logical, max_replication_slots = 10, max_wal_senders = 10) and reload. Then create a sample database, table, and replication user:

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

CREATE DATABASE appdb;
\connect appdb
CREATE TABLE public.products (
    id bigint PRIMARY KEY,
    name text NOT NULL,
    price numeric(12,2) NOT NULL,
    attributes jsonb,
    updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.products REPLICA IDENTITY FULL;
CREATE USER cdc_user REPLICATION LOGIN PASSWORD 'replace-with-a-secret';
GRANT CONNECT ON DATABASE appdb TO cdc_user;
GRANT USAGE ON SCHEMA public TO cdc_user;
GRANT SELECT ON TABLE public.products TO cdc_user;

Define a publication and a logical slot:

CREATE PUBLICATION cdc_pub FOR TABLE public.products;
SELECT * FROM pg_create_logical_replication_slot('demo_slot', 'pgoutput');

Insert, update, and delete rows to see the generated changes. For debugging, the test_decoding plugin can be used to read human‑readable output:

SELECT * FROM pg_logical_slot_get_changes('demo_slot', NULL, NULL);

After testing, drop the slot with SELECT pg_drop_replication_slot('demo_slot');.

Snapshot & Incremental Hand‑off

Logical replication only provides incremental changes from the slot’s retained WAL. The first CDC run must therefore:

Read a consistent snapshot of the existing data (the baseline).

Consume incremental changes from a known LSN.

The boundary is established by creating a replication slot with EXPORT_SNAPSHOT, which returns a consistent_point (the LSN to start streaming from) and a snapshot_name. Multiple workers can then open read‑only REPEATABLE READ transactions, set the snapshot with SET TRANSACTION SNAPSHOT '…', and scan the tables in parallel. Once all snapshot rows are loaded, the consumer starts streaming from consistent_point, guaranteeing no gaps.

Correctness Concerns in Production

Key engineering issues and their handling principles:

LSN acknowledgment timing : only report Commit.end_lsn after the downstream has successfully applied the whole transaction.

Concurrent processing and safe watermarks : advance the confirmed LSN only when all earlier transactions are complete; apply back‑pressure when the in‑flight window is full.

Row ordering : use schema.table.primary_key as a stable routing key.

Unchanged TOAST handling : treat unchanged large objects as semantic “unchanged” rather than NULL.

Large transactions : limit in‑flight data and rely on PG 14+ stream‑fragment support.

Oversized rows : chunk them, use external storage (claim‑check), or re‑fetch from the source.

DDL and schema evolution : capture DDL via event triggers or schema‑history tables and apply them in the correct order.

At‑least‑once delivery is typical; downstream systems must be idempotent (e.g., UPSERT by primary key combined with transaction LSN).

Message Model Design

A unified event schema distinguishes snapshot and incremental records by an op field. Example JSON:

{
  "key": "public.products:42",
  "source": "production-pg",
  "op": "UPDATE",
  "commit_lsn": "16/B374D848",
  "event_index": 1,
  "xid": 123456,
  "commit_ts": "2026-07-23T10:00:00Z",
  "schema": "public",
  "table": "products",
  "primary_key": {"id": 42},
  "before": {"price": 100},
  "after": {"price": 99},
  "unchanged_toast": ["attributes"]
}

The design emphasizes stable routing keys, comparable positions, transaction traceability, explicit before‑image semantics, schema‑compatible evolution, and a single format for both snapshot and incremental events.

Open‑Source Tools & Clients

Several OSI‑approved tools can consume PostgreSQL CDC, including Debezium, Apache Flink CDC, SeaTunnel, xataio/pgstream, ConduitIO/conduit, PeerDB, Sequin, and language‑specific clients such as jackc/pglogrepl (Go), PostgreSQL JDBC PGReplicationStream (Java), psycopg2 (Python), supabase/etl (Rust), and kibae/pg‑logical‑replication (Node.js). For debugging, pg_recvlogical, test_decoding, and wal2json are handy.

Monitoring, Failure Recovery & Release Checklist

Production monitoring focuses on three aspects: consumer lag, slot‑retained WAL size, and recovery correctness. Example query to inspect slot state:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Key metrics include slot activity, retained WAL volume, confirmed LSN progression, end‑to‑end latency percentiles, consumer in‑flight counts, retry/back‑pressure stats, and primary‑instance WAL disk usage.

Recovery steps must verify the target cluster, timeline, slot validity, WAL availability, and that confirmed_flush_lsn matches expectations before resuming consumption. A pre‑deployment checklist covers instance parameters, user permissions, REPLICA IDENTITY settings, snapshot‑incremental integration tests, LSN advancement only after downstream confirmation, handling of unchanged TOAST, DDL, large transactions, and alerting for slot size, latency, and disk pressure.

Conclusion

PostgreSQL WAL‑based CDC turns low‑level log records into a reliable, ordered event stream. Logical decoding extracts row‑level semantics, Publication defines the capture scope, LSN marks positions, Replication Slot tracks consumer progress, and REPLICA IDENTITY controls the before‑image for UPDATE/DELETE. Proper snapshot handling eliminates data gaps, and production‑grade correctness hinges on committing LSN only after downstream success, preserving order, and implementing idempotent downstream processing. Compared with MySQL binlog CDC, PostgreSQL’s main operational difference is the need to monitor slot‑retained WAL rather than binlog expiration. A well‑designed CDC pipeline therefore guarantees no missing data, safe recovery points, and convergence to the correct state even under retries, delays, or out‑of‑order delivery.