> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

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

# Postgres CDC

<Warning>
  **Private Preview Feature**

  This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
</Warning>

Firebolt connects to your Postgres database and keeps a [CDC table](/guides/change-data-capture/cdc-tables) 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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   CREATE ROLE firebolt_cdc WITH LOGIN REPLICATION PASSWORD '********';
   GRANT SELECT ON public.orders TO firebolt_cdc;
   ```

3. **A publication** covering the table:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   CREATE PUBLICATION firebolt_cdc FOR TABLE public.orders;
   ```

4. **Slot headroom.** Each stream takes one replication slot (one per leaf partition for [partitioned sources](#partitioned-source-tables)). 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](#toast-columns)).

If something is missing, `CREATE STREAM` fails with the Postgres server's original error message.

<Note>
  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.
</Note>

## Create the location

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

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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)](/reference-sql/commands/data-definition/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.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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](#toast-columns) before creating the stream.

`CREATE STREAM` connects to the source, discovers whether the table is partitioned, and creates the replication slot (or one slot per leaf partition). 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.

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

## Create the CDC table and start ingestion

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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;
```

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](/guides/change-data-capture/cdc-tables).

Monitor progress through [`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests):

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

The sections below cover details that most setups never need: inspecting the raw change feed, scaling ingestion for 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.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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, `T` truncate, `R` snapshot 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.                                                         |
| `$source_partition`  | BIGINT      | Leaf-partition ordinal for partitioned sources; `0` otherwise.                 |
| `$stream_row_number` | BIGINT      | 1-based position of the row within this statement's read, in delivery order.   |

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.

With `snapshot => true`, `READ_STREAM` returns a full snapshot of the source table (rows tagged `$op = 'R'`) instead of the change feed. The CDC table's initial backfill uses this path; you rarely call it directly.

### Partitioned source tables

A single replication slot is decoded by one `walsender` process on the source, which caps a slot's throughput at a few hundred thousand row changes per second regardless of how large the source machine is. For hot tables, Postgres declarative partitioning removes that ceiling.

When the source table is partitioned, `CREATE STREAM` discovers the leaf partitions automatically and creates one replication slot per leaf. The engine then snapshots and tails all leaves in parallel, and the decode work spreads across one `walsender` per leaf on the source. No extra syntax is involved; partitioning is detected from the table itself.

Two requirements come with this:

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

Rows from each leaf carry their leaf's ordinal in `$source_partition`, and the CDC table keeps each source partition in its own storage partition so background consolidation runs per partition, in parallel.

### 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:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER TABLE public.orders REPLICA IDENTITY FULL;
```

And in Firebolt:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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.