Postgres CDC

​

Prepare the source database

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;
   
4. Slot headroom. Each stream takes one replication slot (one per leaf partition for partitioned sources). 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).

​

Create the location

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

​

Create the stream

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

​

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
) FROM STREAM orders_changes;

ALTER CDC TABLE orders RESUME;

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 a crash or engine restart, the 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. Ingestion resumes automatically for every CDC table whose ingest is enabled; no manual RESUME is needed after a restart.
• Source TRUNCATE halts ingestion. A truncate on the source is not applied. Ingestion stops at that point in the feed and the table keeps its pre-truncate state, so an operational mistake on the source does not silently empty the analytical mirror. Recreate the CDC table to re-baseline from a fresh snapshot.
• 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 is not replicated. Adding a column on the source is invisible until you recreate the stream with the new column; dropping a captured column breaks ingestion.
• ALTER STREAM is not supported for Postgres streams.

​

Advanced

​

Read raw change events

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

​

Partitioned source tables

• One dedicated publication per leaf. Each leaf partition needs a publication that covers exactly that leaf (CREATE PUBLICATION orders_p0 FOR TABLE public.orders_p0;). CREATE STREAM verifies this and names the missing publication in its error message.
• The partition key must be part of the CDC table’s merge key. Postgres routes a row’s changes to the leaf its partition key selects, so requiring the partition key inside the merge key guarantees all changes for a given key flow through one slot, in order. CREATE CDC TABLE enforces this and rejects a key that could split across slots.

​

TOAST columns

• 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.

ALTER TABLE public.orders REPLICA IDENTITY FULL;

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