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 all existing rows using Postgres's COPY command:

1. Create replication slot (captures consistent snapshot point)
2. COPY all rows from the table
3. Begin ongoing replication from the snapshot point

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 data Postgres includes in UPDATE and DELETE events.

The Problem

When a row is updated or deleted, downstream systems need enough old-row information to identify which source row changed.

The important nuance is that PostgreSQL does not always send an old-side tuple for UPDATE. Under key-based replica identity, it only sends a key image when it is needed. For DELETE, PostgreSQL sends an old-side tuple whenever the delete is publishable.

This means replica identity is both a PostgreSQL logging rule and a consumer contract for downstream consumers. It determines whether an event contains enough old-row data to match an existing row, detect key changes, or compare before-and-after values.

Settings

-- 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;

Impact on ETL

ETL preserves PostgreSQL's old-row semantics in update and delete events:

pub old_table_row: Option<OldTableRow>

• Some(OldTableRow::Key(row)) means PostgreSQL sent only the replica-identity columns, normalized into replicated table-column order.
• Some(OldTableRow::Full(row)) means PostgreSQL sent the full old row.
• None means PostgreSQL did not send an old-side tuple for that update. This is normal under DEFAULT or USING INDEX when PostgreSQL determines no old-side image is required.

For FULL, PostgreSQL sends a full old row for every published update and delete. For DELETE, valid pgoutput messages always include either a full old row or a key image. REPLICA IDENTITY NOTHING, and DEFAULT on a table without a primary key, do not produce update/delete events when those actions are published; the source statement is rejected instead. The Rust event API keeps the old-row fields optional at the boundary, but those None cases are broader than the PostgreSQL pgoutput shapes described here.

TOAST adds one more wrinkle. PostgreSQL can mark unchanged toasted update values as UnchangedToast instead of resending the value. ETL can reconstruct those values only if the old-side row image contains them, so tables with toasted columns can produce partial update rows unless they use REPLICA IDENTITY FULL or the missing values are present in a logged key image.

If you need old values for auditing, comparison, complete replacement rows, or reliable reconstruction of unchanged toasted columns, set REPLICA IDENTITY FULL on those tables. If a consumer only needs stable key values, DEFAULT with a primary key or USING INDEX is usually enough, but update events will not always include old_table_row.

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.