Postgres CDC

Ingest changes from Postgres into Firebolt through logical replication, from source setup to a continuously updated table.

Nightly Feature

This feature is currently in private preview. Contact [e-mail] to request early access.

Firebolt connects to your Postgres database and keeps a CDC table continuously up to date with it: every insert, update, and delete on the source arrives within seconds, with nothing to deploy in between. Under the hood, the connection is native Postgres logical replication.

This page walks through preparing the source, setting up the stream and table, and day-to-day operations, with the advanced details at the end.

Prepare the source database

On the Postgres side you need:

1. Logical WAL. Set wal_level = logical (requires a restart).

2. A replication role. The connecting user needs the REPLICATION attribute and SELECT on the tables you capture:

   CREATE ROLE firebolt_cdc WITH LOGIN REPLICATION PASSWORD '********';
   GRANT SELECT ON public.orders TO firebolt_cdc;
   

3. A publication covering the table:

   CREATE PUBLICATION firebolt_cdc FOR TABLE public.orders;
   

   Keep its default publish option and publish the whole table: CREATE STREAM rejects a publication that leaves out insert, update, delete or truncate, or that filters the table’s rows with WHERE, since the stream would miss those changes without an error.

4. Slot headroom. Each stream takes one replication slot. Size max_replication_slots and max_wal_senders accordingly.

5. Replica identity. The default (primary key) identity is sufficient: deletes and updates carry the key. Set REPLICA IDENTITY FULL on the table if updates can leave large out-of-line values unchanged (see TOAST columns).

If a prerequisite is not met, CREATE STREAM fails with an error that names it and, where there is one, the fix.

A replication slot pins WAL on the source until its consumer acknowledges it. While ingestion is running this is a bounded window; if you suspend ingestion for a long period or stop querying a stream, the source retains WAL for the slot and its disk usage grows. Drop streams you no longer consume.

Create the location

The location stores the connection details, credentials, and the publication name.

CREATE LOCATION pg_prod WITH (
    SOURCE      = 'POSTGRES',
    HOST        = 'pg.example.internal',
    PORT        = '5432',
    DATABASE    = 'app',
    USER        = 'firebolt_cdc',
    PASSWORD    = '********',
    PUBLICATION = 'firebolt_cdc'
);

Connections to any host other than localhost use TLS, verified against the operating system trust store of the Firebolt nodes. There is no connection-level option to disable verification or point at a CA file; to use a private certificate authority, install its certificate into the nodes’ system trust store. See CREATE LOCATION (Postgres) for the full parameter reference.

Create the stream

A stream binds to one source table and declares the columns to capture, mapped to Firebolt types. Column definitions are required for Postgres streams; there is no schema inference from the source.

CREATE STREAM orders_changes (
    order_id    BIGINT,
    customer_id BIGINT,
    amount      NUMERIC(12, 2)
)
TABLE = 'public.orders'
LOCATION = 'pg_prod';

If the table stores large TEXT, BYTEA, or JSON values, read TOAST columns before creating the stream.

CREATE STREAM connects to the source, checks the table and the publication, and creates the replication slot. The slot’s starting position is recorded so that a later snapshot backfill and the change feed line up with no gap. No data is copied at create time.

Renaming or retyping a captured column on the source requires dropping and recreating the stream; there is no ALTER STREAM for Postgres streams.

Support for upstream schema evolution, so that source column changes flow through without recreating the stream, is planned for a future release.

Create the CDC table and start ingestion

CREATE CDC TABLE orders (
    order_id    BIGINT,
    customer_id BIGINT,
    amount      NUMERIC(12, 2),
    PRIMARY KEY (order_id) NOT ENFORCED
);

ALTER CDC TABLE orders ATTACH STREAM orders_changes;
ALTER CDC TABLE orders RESUME;

The PRIMARY KEY ... NOT ENFORCED clause names the merge key: the columns that identify a row across its lifetime, normally the source table’s primary key. On RESUME, the ingest worker backfills the table from a source snapshot and then applies the change feed continuously. Semantics, merge modes, and lifecycle commands are covered in CDC tables.

Monitor progress through information_schema.cdc_ingests:

SELECT table_name, state, phase, apply_count, live_lag_ms, last_error
FROM information_schema.cdc_ingests;

Operations

• Delivery and recovery. The replication slot’s consumed position advances only after the Firebolt transaction that read the batch commits. After an Engine restart, run RESUME again. The new worker resumes from the slot’s confirmed position; a batch that was read but not committed is redelivered, and the sequence-guarded apply makes the redelivery a no-op. The net effect is exactly-once application.
• Engine restart. Workers are not recreated automatically. Run ALTER CDC TABLE ... RESUME on the Engine where ingestion should continue.
• Source TRUNCATE reloads the table. A truncate of the captured table on the source cannot be applied as row changes, so the ingest reloads the CDC table from a fresh snapshot of the source and continues from there. The reload is atomic: queries see the table as it was before the truncate until the reload commits, and then the fresh snapshot. Meanwhile information_schema.cdc_ingests shows the truncate in last_error; if the reload itself fails, last_error shows why and the table keeps its earlier rows until a retry succeeds. Once reloads have failed for at least 10 attempts over at least 4 hours by default, the attachment stops in state = 'error' and RESUME rejects it. A truncate of another table in the publication does not affect the stream. CREATE STREAM rejects the two cases that would keep a truncate from the stream: a publication whose publish option leaves out truncate, and a partitioned source table, whose truncate of a single partition is not sent through the root. A publication changed after CREATE STREAM goes undetected.
• DROP STREAM drops the replication slot(s) on the source. If the source is unreachable, the stream is still dropped and the error message names the leftover slot so you can remove it with SELECT pg_drop_replication_slot('<slot>').
• One consumer per slot. A stream’s change feed has a single consuming reader. The CDC table’s ingest worker is that consumer; use consume => false for ad-hoc reads alongside it.

Limitations

• Captured column types must be primitive (no STRUCT).
• Source DDL does not carry over to the CDC table. Adding, dropping, renaming, or reordering columns of the source table stops ingestion permanently at the next change: information_schema.cdc_ingests shows state = 'error' with the reason in last_error, and the table keeps what it applied before. Changes the source committed just before the schema change can be missing too, when they reach Firebolt in the same batch as the first change after it. To recover, run ALTER CDC TABLE ... DETACH STREAM, drop the stream (which releases its replication slot), create it again with the source’s current columns, attach it to a new CDC table with those columns, and run RESUME. The old table cannot take the new stream, because a CDC table cannot change its columns.
• ALTER STREAM is not supported for Postgres streams.

Advanced

The sections below cover details that most setups never need: inspecting the raw change feed, partitioned sources, and how large out-of-line values behave.

Read raw change events

READ_STREAM returns the decoded change feed. Use consume => false to peek without advancing the slot; peeks are isolated and repeatable, so they are safe for inspection at any time. A read that reaches a truncate of the table on the source fails, peeks included.

SELECT "$op", "$commit_ts", order_id, customer_id, amount
FROM READ_STREAM(STREAM orders_changes, consume => false);

Each change row carries the declared columns plus pseudo-columns (reference them double-quoted):

Pseudo-column	Type	Description
$op	TEXT	Operation: I insert, U update, D delete, R snapshot row. A source truncate fails the read rather than producing a row.
$lsn	BIGINT	WAL position of the change, packed into a 64-bit integer.
$commit_lsn	BIGINT	WAL position of the enclosing transaction’s commit.
$commit_ts	TIMESTAMPTZ	Commit timestamp of the enclosing transaction.
$xid	BIGINT	Source transaction ID.
$stream_row_number	BIGINT	1-based position of the row within this statement’s read, in delivery order.
$stream_read_seq	BIGINT	Number of the consuming read that returned the row: the same on every row of one read, and increasing with every consuming read. Selecting it with consume => false raises an error.

Changes appear in commit order, and uncommitted transactions are never surfaced. Under the default replica identity, a delete carries only the key columns (other columns are NULL), and a primary-key update decomposes into a delete of the old key and an update with the new one.

READ_SNAPSHOT returns a full snapshot of the source table instead of the change feed. The CDC table’s initial backfill uses it; you rarely call it directly.

Partitioned source tables

CREATE STREAM rejects a partitioned table, one created with PARTITION BY. A publication sends a partitioned table’s changes under its leaf partitions, or with publish_via_partition_root under the table itself but without a TRUNCATE of a single partition, so a stream on the table would miss changes. A table that other tables INHERITS from is rejected for the same reason: reading it includes its children’s rows, but their changes are published under the children’s own names.

A single leaf partition can be streamed like any other table, as long as the publication does not set publish_via_partition_root, which sends the leaf’s changes under the partitioned table’s name.

TOAST columns

Postgres stores large values (roughly 2 KB and up, in TEXT, BYTEA, JSON, and similar columns) out of line in TOAST storage, and logical replication omits such a value from an update’s row image when the update leaves it unchanged. The value is then simply not in the change feed. Inline-sized values are unaffected; declaring TEXT columns is fine, and most tables never hit this.

The SUPPORT_TOASTED_VALUES stream option controls what happens when an omitted value is encountered:

• FALSE (default). The source keeps its default replica identity. If an update omits an unchanged out-of-line value, ingestion stops with an error naming the column, rather than writing a wrong value into the mirror. The table keeps its last consistent state, and the error message carries the remediation.
• TRUE. The source table must use REPLICA IDENTITY FULL, which makes every update carry the full old row; Firebolt recovers unchanged values from it. If the identity is not actually FULL, ingestion stops with an error pointing at the missing ALTER TABLE.

For a table whose large columns are updated in place (so unchanged out-of-line values occur), configure both sides together. On the source Postgres database:

ALTER TABLE public.orders REPLICA IDENTITY FULL;

And in Firebolt:

CREATE STREAM orders_changes (
    order_id BIGINT,
    status   TEXT,
    payload  TEXT
)
TABLE = 'public.orders'
SUPPORT_TOASTED_VALUES = TRUE
LOCATION = 'pg_prod';

REPLICA IDENTITY FULL is a property of the source table, not of the stream: it controls what Postgres writes into the WAL at update time, which every replication consumer then shares, so it cannot be set per slot. It increases WAL volume on the source because every update and delete logs the full old row; that is the cost of exact mirroring for out-of-line values. Leave the option off when your updates always rewrite the large columns or the values stay inline.