Architecture

Supabase ETL uses Postgres logical replication to initially sync published tables, then replicate subsequent changes to a destination in near real time.

Overview

How It Works

ETL operates in two phases:

Phase 1: Initial Sync

When a pipeline starts, it copies existing rows from each table in the publication, then catches up changes that occurred while the copy was running. Multiple Table Sync Workers run in parallel. Each worker:

1. Locks the table and its existing descendants in ACCESS SHARE mode
2. Creates a replication slot to capture a consistent snapshot
3. Copies existing rows with Postgres COPY and sends them via write_table_rows()
4. Releases the copy locks and catches up later WAL changes via write_events() until the table is ready for the apply worker

The parent retains each copy unit's locks across all parallel chunks. Workers import its snapshot and acquire their own locks with NOWAIT. A conflict fails the incomplete attempt; the configured retry policy starts a fresh copy.

On a primary, these ordinary SELECT locks permit normal reads and writes but block ACCESS EXCLUSIVE DDL. Queued DDL can delay application queries. On a standby, they protect local reads against conflicting WAL replay, which may cancel copy sessions; they do not block DDL on the primary.

Long copies retain WAL and old row versions, and can delay concurrent index completion. See Planning long initial copies for operating costs and Initial-copy schema protection for partition scope, timeouts, and retry limits.

Copy and change data capture (CDC) are replication paths, not customer-visible phases. Initial sync uses the copy path followed by CDC catch-up; ongoing replication uses the CDC path after the table is ready.

Phase 2: Ongoing Replication

Once a table is ready, the Apply Worker handles ongoing replication from the Postgres WAL. It:

1. Receives change events (inserts, updates, deletes, truncates, and schema changes)
2. Batches events for efficiency
3. Sends batches to the destination via write_events()

Schema Changes

Schema-change handling is in public beta. A source-side event trigger captures table-schema snapshots for supported ALTER TABLE and publication column-list changes. Each pipeline combines the stored snapshot with its RELATION column metadata to select the columns it actually replicates. Destinations observe the resulting logical schema through an ordered Relation event before following rows.

Initial-copy locks protect table layout while copying; they do not resolve the known catalog-helper visibility, outdated transaction snapshots, separate capture-query races, or relation-mask ambiguity in schema-change capture and replay. See Schema Changes for the design, capture behavior, and operating guidance.

Core Components

Pipeline

The central orchestrator that manages the entire replication process. It spawns workers, coordinates state transitions, and handles shutdown.

Destination

The Destination trait receives bulk rows during initial sync and event batches during catch-up and ongoing replication. Result handles let a destination distinguish accepted work from durable work without blocking dispatch. See Extension Points for the method contract and Custom Implementations for an example.

Store

Three store traits persist the state needed to resume after restarts:

• StateStore: Tracks table state, persisted replication checkpoints, and destination table metadata
• SchemaStore: Stores versioned table schema information (columns, types, primary keys, snapshot IDs) and prunes obsolete schema versions behind persisted checkpoints while preserving the retained boundary schema and newer versions
• TableStateLifecycleStore: Prepares table-copy state, resets table states for resync, and deletes all ETL-owned state when a table leaves the publication

Use one store and its clones per pipeline to share cached state. External changes to ETL state or destination data are unsupported.

Each store trait requires CachedStore, which loads all cache domains together during startup, before workers run. See Extension Points for the cache and durability contracts.

Delivery Guarantees

ETL provides at-least-once delivery. If restarts occur, some events may be delivered more than once. This is a deliberate design choice.

Why Not Exactly-Once?

ETL does not promise exactly-once delivery across its source, store, and destinations. A failure can occur after the destination commits a write but before ETL saves the corresponding checkpoint. Destinations can use idempotent writes or deduplication to keep replay from changing the final result. ETL also limits replay through:

• Controlled shutdown: The pipeline stops intake, drains apply work, and joins tasks. Interrupted initial copies restart from a fresh snapshot. Failures return immediately and request abort of remaining owned tasks; persisted checkpoints still govern recovery
• Frequent status updates: Progress is reported to Postgres regularly, reducing the replay window after restarts

Handling Duplicates

Destinations should make writes idempotent using the source table's replica identity or primary key plus ETL's event ordering metadata. For append-style CDC tables, persist an event position derived from commit_lsn and tx_ordinal. For current-state tables, upsert by the destination's chosen row key so replayed events converge to the same state.

The commit_lsn and tx_ordinal fields on sequenced events provide stable ordering and replay detection. Destinations such as ClickHouse and BigQuery use the pair to keep replayed events ordered. See Event Types for details.

Table States

Each table progresses through these states. See Extension Points for which states are persisted across restarts.