Logical Replication

Read this first if Postgres logical replication is new to you.

What is Logical Replication?

Postgres supports two types of replication:

ETL uses logical replication because decoded row changes can be sent to systems other than Postgres.

The Write-Ahead Log (WAL)

Before Postgres modifies data on disk, it first writes the change to the Write-Ahead Log (WAL). This guarantees durability: if Postgres crashes, it can replay the WAL to recover.

For logical replication, Postgres decodes the WAL back into logical changes:

ETL receives these decoded events and forwards them to downstream consumers.

WAL Level

Postgres must be configured to record enough information for logical decoding:

# In postgresql.conf
wal_level = logical

With wal_level = logical, Postgres records additional metadata needed to reconstruct row changes. Lower levels (replica, minimal) do not capture enough detail.

Publications

A publication defines which tables to replicate. Think of it as a filter that says "replicate changes from these tables."

-- Replicate specific tables
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Replicate all tables (use with caution)
CREATE PUBLICATION my_publication FOR ALL TABLES;

When you create an ETL pipeline, you specify which publication to consume. Only tables and operations selected by that publication are replicated.

What Publications Control

• Which tables: Only tables in the publication are replicated
• Which operations: You can filter to only INSERT, UPDATE, or DELETE
• Which columns (Postgres 15+): Replicate only specific columns
• Which rows (Postgres 15+): Filter rows with a WHERE clause

Replication Slots

A replication slot is a bookmark that tracks how far a consumer has read in the WAL.

Why Slots Exist

Without slots, Postgres would delete old WAL files when it no longer needs them for crash recovery. If ETL disconnects temporarily, it needs those WAL files to catch up when it reconnects.

Replication slots tell Postgres: "Don't delete WAL files until this consumer has processed them."

-- View existing slots
SELECT slot_name, confirmed_flush_lsn, active
FROM pg_replication_slots;

How ETL Uses Slots

ETL creates replication slots automatically:

The Apply Worker uses one persistent slot. Table Sync Workers create temporary slots during initial sync, then delete them.

Slot Risks

Slots prevent WAL cleanup. If ETL stops consuming because of crashes, network issues, or a slow consumer, WAL files accumulate on disk. This can fill your disk.

To mitigate this risk:

• Monitor slot lag with pg_replication_slots
• Set max_slot_wal_keep_size to limit WAL retention
• Alert when slots fall behind

See Configure Postgres for details.

The pgoutput Decoder

When Postgres decodes WAL for logical replication, it uses a decoder plugin. ETL uses pgoutput, Postgres's built-in decoder.

The decoder transforms binary WAL records into structured messages:

ETL receives these messages and converts them to events.

Why Two Phases?

ETL replicates data in two phases: initial sync and ongoing replication.

Phase 1: Initial Sync

Logical replication only captures changes. It does not know about data that existed before replication started.

So ETL first copies existing rows using Postgres's COPY command, then catches up WAL changes that arrived during the copy:

1. Create a replication slot (captures a consistent snapshot point)
2. COPY all rows from the table via write_table_rows()
3. Catch up later changes via write_events() until the table is ready

The slot ensures no changes are lost between the snapshot and ongoing replication.

Phase 2: Ongoing Replication

After initial sync, ETL begins ongoing replication. It captures subsequent changes from the WAL and delivers them to the destination:

Each change is delivered as an Event through write_events().

Large tables can spend significant time in initial sync. ETL exposes separate write_table_rows() and write_events() methods so destinations can optimize initial-copy rows and change events independently.

Replica Identity

REPLICA IDENTITY controls what old-row data Postgres includes in UPDATE and DELETE events.

-- See current setting (d=default, f=full, n=nothing, i=index)
SELECT relname, relreplident FROM pg_class WHERE relname = 'your_table';

-- Change setting
ALTER TABLE your_table REPLICA IDENTITY FULL;

Set REPLICA IDENTITY FULL when destinations need complete old rows, before/after comparison, or reliable reconstruction of unchanged TOAST columns. See Events for the exact pgoutput mapping and partial-update behavior.

LSN (Log Sequence Number)

Every position in the WAL has a unique LSN - a monotonically increasing pointer.

Format: 0/16B3748 (segment/offset)

LSNs in Events

Sequenced ETL events include a commit LSN and transaction-local ordinal:

Multiple events in the same transaction share the same commit_lsn; their tx_ordinal values distinguish their order. Relation events are connection-local metadata and do not have an event sequence key.

Persisted State

ETL persists the state needed to resume safely after a restart:

ETL stores:

The built-in PostgresStore persists to your Postgres database and runs its state-store migrations when it is created. If the pipeline reads from a read-only replica, configure store_pg_connection to point at a writable Postgres endpoint for this state. MemoryStore is for testing only - state is lost on restart. Pipeline::start() runs the ETL source migrations that install schema helpers and the DDL event trigger before replication begins. See Architecture for the worker lifecycle and Configure Postgres for production settings.