Schema Changes

Schema-change support is in public beta and is being expanded incrementally. The current implementation is intentionally conservative: the source-side event trigger captures a rich PostgreSQL-shaped snapshot, while ETL currently models well-understood column changes: adds, drops, renames, and column default and nullability changes, plus publication column-list changes for tables the running pipeline already tracks. A few known edge cases remain.

Built-in destination support varies by destination DDL capabilities. BigQuery, ClickHouse, DuckLake, and Snowflake apply supported schema changes automatically; Iceberg is deprecated for new deployments and does not support schema-change DDL.

Short Version

For published permanent tables, ETL currently models these ALTER TABLE changes:

When several attributes of the same logical column change at once, ETL groups them into one column change with multiple modifications. For example, renaming a column and changing its default in one ALTER TABLE statement is treated as a single logical column change.

How It Works

ETL installs a PostgreSQL ddl_command_end event trigger named supabase_etl_ddl_message_trigger. When an ALTER TABLE statement or supported ALTER PUBLICATION change affects a published permanent table, the trigger emits a transactional logical message with prefix supabase_etl_ddl.

PostgreSQL does not pass user-defined arguments to an event-trigger function. The function reads TG_TAG and the object addresses returned by pg_event_trigger_ddl_commands(), then resolves the post-DDL catalogs:

• For ALTER TABLE, the object address identifies the table. The message has no publication name because a physical table change applies to every publication containing that table.
• For per-table ALTER PUBLICATION changes, the object address identifies a surviving pg_publication_rel row, which links the explicitly named table to its publication. This path does not expand a named partition root into its effective leaves.
• For publication-level ALTER PUBLICATION changes, the object address identifies a pg_publication row. The trigger does not parse which parameter changed; it expands the publication's complete post-command effective table set through pg_publication_tables.

The publication name is therefore optional in the payload for table DDL but required for publication DDL. Logical decoding exposes custom messages to a slot independently of pgoutput's table filtering, so ETL accepts an ALTER PUBLICATION message only when its resolved name exactly matches the pipeline's configured publication. A missing name fails closed.

That message is internal plumbing. Destinations do not receive it directly. Instead, ETL:

1. Parses the schema-change message.
2. Stores a new versioned table schema using a composite snapshot ID ordered by commit LSN and then message LSN.
3. Invalidates the in-memory relation state for that table.
4. Waits for PostgreSQL pgoutput to emit a fresh RELATION message before the next row event for that table.
5. Sends destinations a public Event::Relation with the new ReplicatedTableSchema.

Snapshot IDs display both LSNs as decimal unsigned 64-bit values in commit_lsn:message_lsn form. Their ordering is the numeric tuple ordering of those two components, not the lexical ordering of the displayed string.

ALTER PUBLICATION ... DROP TABLE intentionally emits no schema snapshot for the removed table because it is no longer part of the publication. PostgreSQL 14 may still send an empty BEGIN/COMMIT pair for that transaction; PostgreSQL 15 and later suppress empty logical-replication transactions. The empty pair contains no schema or row event and does not affect snapshot ordering. See the PostgreSQL 15 release notes and the upstream change.

For a table already known to the pipeline, a supported publication change creates a new snapshot ID even when the full physical table schema is unchanged. The following RELATION message supplies the current publication and identity masks. This gives successive mask changes distinct snapshot IDs and preserves their ordering. Multiple schema messages sharing one commit LSN are ordered by their message LSN. A newly published table that the running pipeline does not know is ignored until startup publication reconciliation creates its table state and initial sync.

ALTER PUBLICATION support boundary

The trigger's ability to observe an ALTER PUBLICATION command is not the same as runtime support for that publication change. The trigger emits schema snapshots; it does not add or remove ETL table state, start an initial sync, or change which relation OIDs the running apply worker owns.

At startup, ETL calls pg_get_publication_tables() to load the same effective relation identities PostgreSQL uses for pgoutput. New identities enter initial sync and identities no longer returned by PostgreSQL have their ETL state purged. This startup process does not rename, merge, or delete destination tables created for an earlier root or leaf identity.

The important public boundary is:

... -> internal DDL message -> Relation(new schema) -> Insert/Update/Delete ...

Destinations should treat Event::Relation as the point where the active schema changes for following row events.

Relation is an ordered event, not a batch boundary. ETL batches calls to write_events() based on size and time, so a single destination batch may contain zero, one, or many schema changes, including multiple relation events for the same table.

Destination-Specific DDL Behavior

ETL has one shared schema-change signal, but DDL behavior is implemented per destination. A destination may choose to apply DDL automatically, reject a schema change, or require operator handling.

For every built-in destination, a relation whose snapshot ID and replication mask exactly match the applied destination metadata is idempotent. BigQuery, ClickHouse, DuckLake, and Snowflake currently reject an older snapshot, or the same snapshot with a different replication mask, instead of attempting to infer schema ordering from later row events. A relation has no DML sequence key of its own, so destination row-replay deduplication does not prove that reverse or ambiguously ordered DDL is safe. Recovering from either rejection currently requires resynchronizing the table. This comparison applies only to metadata already marked Applied; it neither defines nor initiates recovery from an interrupted Applying state.

See Destinations for the canonical maturity status and broader limitations of every built-in implementation.

Default Backfills

When a source table adds a replicated column with a default, PostgreSQL can make pre-existing source rows read as though they already contain that default. ETL deliberately avoids physical destination backfills for those existing rows. Built-in destinations avoid operations such as UPDATE, MERGE, CTAS/swap rewrites, or ClickHouse MATERIALIZE COLUMN because those operations can rewrite large tables, block replication progress, and create destination-specific cost spikes.

Instead, destinations apply the schema change in the cheapest safe form they support:

• BigQuery adds the column as nullable, then sets supported literal default metadata for future writes.
• ClickHouse may expose default values for pre-existing rows through default metadata, but ETL does not issue MATERIALIZE COLUMN.
• DuckLake uses add-time initial default metadata for supported defaults; its schema evolution does not rewrite data files.
• Snowflake uses ADD COLUMN ... DEFAULT for supported literal defaults. Snowflake exposes default values for existing rows and does not document this as a physical row rewrite, but defaults created this way cannot later be dropped.

As a result, pre-existing destination rows might not match PostgreSQL's historical ADD COLUMN ... DEFAULT view unless the destination has a metadata-only initial-default mechanism and the source default is supported. This does not mean future replicated tuples lose their default values: PostgreSQL sends evaluated column values in row data after the relation change, and ETL writes those values normally. The limitation is only that unsupported defaults are not installed as destination schema default metadata, and existing destination rows are not rewritten by ETL.

Supported Column Defaults

Column defaults are best-effort metadata translations, not a PostgreSQL expression evaluator. ETL reads the source default from PostgreSQL's pg_get_expr output, parses only deterministic literal defaults, and asks each destination whether that parsed default can be rendered safely in that destination's SQL dialect.

If a default is not in the supported subset, ETL skips the destination default metadata with a warning. Replication does not fail, and future tuples still carry evaluated values from PostgreSQL. This is intentional: runtime-generated defaults can evaluate at different times or under different session settings in different systems, so installing a similar-looking destination default can create silent mismatches. ETL can add support for specific additional defaults later when their semantics can be preserved for the destination.

The shared parser currently recognizes only these source default shapes:

The parser is intentionally conservative. These PostgreSQL defaults are examples of unsupported expressions:

default nextval('users_id_seq')
default 'a' || 'b'
default lower('USER' || '_ID')
default concat('a', 'b')
default md5('x')
default random()
default clock_timestamp()
default now()
default current_timestamp
default current_user
default gen_random_uuid()
default uuid_generate_v4()
default timezone('UTC', now())
default array['a', 'b']
default (select 'x')
default current_setting('app.tenant_id')
default 1e6
default interval '1 day 2 hours'

Unsupported defaults are skipped because translating arbitrary PostgreSQL expressions would require both a PostgreSQL parser and a destination-specific expression translator. Most destinations do not accept arbitrary PostgreSQL expressions as column defaults, and even similar-looking SQL can have different volatility, time zone, type coercion, or evaluation semantics. Skipping the destination schema default does not drop actual row values emitted by PostgreSQL.

Destination support may be narrower than parser support:

When changing a default from one supported expression to an unsupported expression, destinations that can safely remove defaults drop the old supported default to avoid leaving stale destination behavior behind. Snowflake is the exception for defaults introduced by ADD COLUMN ... DEFAULT, because Snowflake does not allow those defaults to be dropped safely.

Diff Semantics

ETL stores schemas in PostgreSQL column ordinal order (pg_attribute.attnum) and computes destination schema diffs over replicated columns only.

The current diff rules are:

• Same ordinal position, different name: column rename.
• Same ordinal position, different default expression: column default change.
• Same ordinal position, different nullability: detected schema metadata change.
• Old ordinal position missing from the new schema: column drop.
• New ordinal position missing from the old schema: column add.

This matches PostgreSQL's normal behavior for simple column operations: renames keep the same attnum, dropped columns disappear from the visible schema, and newly added columns receive new ordinal positions.

Destination Handling

Custom destinations should handle schema changes in write_events() by watching for Event::Relation.

A practical flow is:

1. Iterate through the batch in order, treating each relation event as a possible schema transition for that table.
2. Flush any buffered rows/events for the old schema before processing the relation event.
3. Compare the old destination schema with the relation event's new ReplicatedTableSchema.
4. Mark destination metadata as Applying if the destination needs recovery bookkeeping for the DDL transition.
5. Apply supported destination DDL for adds, drops, renames, and default changes.
6. Mark destination metadata as Applied only after the destination schema is actually ready for following row events.
7. Process following row events with the new schema.

The built-in BigQuery, ClickHouse, DuckLake, and Snowflake destinations follow this shape: they mark destination schema metadata as Applying, apply the supported DDL operations, then mark the schema as Applied. Because destination DDL is not always transactional, recovery is destination-specific. ClickHouse and DuckLake can retry an interrupted Applying operation. When an arriving relation drives that retry, it must exactly match the snapshot ID and replication mask recorded as the target; DuckLake startup recovery can instead reconstruct that exact target from durable schema state. BigQuery does not automatically repair Applying schema-change metadata, and Snowflake only automatically retries interrupted initial setup, not an interrupted schema change. Other interrupted states require resynchronization.

Other destination modules may support a narrower schema-change surface. Treat Event::Relation as the stable ETL contract, then check the destination's status and implementation before relying on automatic destination DDL.

Supported Scope

The source event trigger intentionally observes a broad schema snapshot, but ETL currently supports the simplest safe cases:

• ALTER TABLE ... ADD COLUMN for replicated columns.
• ALTER TABLE ... DROP COLUMN for replicated columns.
• ALTER TABLE ... RENAME COLUMN for replicated columns.
• ALTER TABLE ... ALTER COLUMN ... SET DEFAULT where the destination supports setting compatible default metadata.
• ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT where the destination supports removing default metadata safely.
• Multi-subcommand ALTER TABLE statements composed of those simple changes.
• Changes to published permanent tables only.

The trigger ignores temporary tables, unpublished tables, generated columns, dropped-column catalog tombstones, extension-owned DDL, and non-logical-WAL databases.

Known Beta Limitations

These behaviors are not full destination DDL semantics yet:

• Only ALTER TABLE and supported ALTER PUBLICATION changes are captured by the ETL DDL trigger today.
• Type changes, constraint changes, identity changes, and replica-identity changes may be visible in the emitted snapshot, but they are not yet interpreted as destination DDL operations.
• Table create/drop/rename operations are outside the current schema-change contract. Publication membership and table cleanup remain separate pipeline lifecycle concerns.
• Live publication membership, row-filter, operation-setting, publication-name, and publish_via_partition_root changes are unsupported. Although the source trigger may emit schema snapshots for some of these commands, it does not dynamically reconcile table ownership or migrate destination table identity.
• If a table-sync worker decodes a DDL or publication-column change during catch-up but receives no following relation before its handover boundary, it cannot construct the complete decoder required by SyncDone. The DDL message supplies the physical schema, but only the relation supplies the exact publication and replica-identity masks for that WAL position. ETL cannot safely reuse older masks or read newer catalog state, so this correctness edge case fails the table sync closed. Retry or resynchronize the table after schema activity has settled.
• A drop and re-add is not treated as a rename. It becomes a drop plus an add because PostgreSQL assigns a new ordinal position to the new column.
• Destination defaults are best-effort metadata translations. Unsupported defaults are skipped with a warning instead of failing replication. This does not remove values PostgreSQL emits in future row events; it only means the destination schema default metadata is not set. If a previously supported default becomes unsupported, ETL removes the old destination default where the destination supports that operation so stale behavior is not left behind. Snowflake default changes on existing columns are skipped with a warning because ALTER COLUMN SET DEFAULT is documented only for existing sequence defaults, and defaults introduced by ALTER TABLE ADD COLUMN ... DEFAULT cannot be dropped safely.
• Runtime-generated defaults are intentionally unsupported as destination schema defaults. Examples include now(), clock_timestamp(), gen_random_uuid(), random(), sequence defaults, and session-dependent expressions such as current_user. Those expressions can evaluate at different times or under different session settings in each destination. PostgreSQL still sends evaluated values for future row events, but existing destination rows are not physically backfilled by ETL. Avoid runtime-generated defaults for replicated schema changes when exact historical values matter.
• ADD COLUMN ... DEFAULT semantics differ by destination. ETL intentionally avoids destination DDL that rewrites all existing rows. BigQuery leaves pre-existing destination rows null for newly added defaulted columns, while ClickHouse, DuckLake, and Snowflake can expose supported add-time defaults without ETL issuing a materialization rewrite. Snowflake only receives add-column defaults for source defaults that can be rendered as Snowflake literals.
• BigQuery applies ongoing-replication DROP NOT NULL changes so future source NULL values remain writable. BigQuery cannot change an existing NULLABLE column to REQUIRED, so PostgreSQL enforces later SET NOT NULL changes while the destination column remains nullable. Other built-in destinations currently leave ongoing-replication nullability changes unchanged. Newly added columns remain nullable where the destination requires that for historical rows.
• The trigger payload includes current_query for debugging only. It can contain literals and multiple statements, so it must not be treated as replayable DDL.
• BigQuery, ClickHouse, DuckLake, and Snowflake reject stale or ambiguously ordered relation schemas instead of rewinding. An older snapshot could drive reverse DDL that drops newer columns and their data. An equal snapshot with a different replication mask is also rejected: supported publication column-list changes always receive a new composite snapshot ID, so the conflicting masks have no ordering with which to choose a safe winner. The pipeline fails with a schema-rewind error and the affected table must be resynchronized.
• Sessions can set supabase_etl.skip_ddl_log = 'true' as an emergency opt-out while recovering a system. DDL executed with that setting enabled is not logged for ETL.

Event Ordering

Schema changes are transactional logical messages, so they appear in WAL order relative to row changes. ETL updates its stored schema when it decodes the DDL message, then waits for the next RELATION message to rebuild the runtime replication and identity masks for row decoding.

This matters for custom destinations: row events after a relation event should be decoded and written using that relation event's schema. Row events before it belong to the previous schema.