CDC Modes

pg_trickle captures changes from source tables using Change Data Capture (CDC). Two mechanisms are available: row-level triggers and WAL-based logical decoding. Understanding both helps you choose the right setting for your workload.

Quick decision guide

How trigger-based CDC works

When you create a stream table, pg_trickle installs three AFTER row-level triggers on every source table:

AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW

Each trigger fires synchronously within the user's transaction and writes one row per changed row to a buffer table (pgtrickle_changes.changes_<oid>). The buffer row is in the same transaction as the user's change — if the transaction rolls back, the buffer row also disappears.

User transaction:
  INSERT INTO orders …
    → trigger fires
    → INSERT INTO pgtrickle_changes.changes_12345 (op, row_data)
  COMMIT
        │
        ▼
  Scheduler picks up buffer rows → computes delta → refreshes stream table

Write-side cost: approximately 2–15 µs per changed row, depending on row width and table size. This is added directly to the user transaction's commit latency.

How WAL-based CDC works

WAL-based CDC uses PostgreSQL's built-in logical decoding to capture changes asynchronously from the write-ahead log, eliminating trigger overhead entirely.

User transaction:
  INSERT INTO orders …
  COMMIT  (no trigger overhead)
        │
        ▼
  WAL written to disk
        │
        ▼
  pg_trickle WAL decoder background worker
  calls pg_logical_slot_get_changes()
        │
        ▼
  Decoded changes written to pgtrickle_changes.changes_<oid>
        │
        ▼
  Scheduler refreshes stream table

The change capture is decoupled from the user transaction. Users see no added latency on commits.

Trade-off: WAL decoding introduces a small additional replication lag (typically < 1 second). Changes committed by the user are visible to the stream table slightly later than with triggers.

Prerequisites for WAL-based CDC

1. wal_level = logical in postgresql.conf
2. Sufficient replication slots: max_replication_slots ≥ (number of tracked source tables) + existing slots
3. Source table has REPLICA IDENTITY DEFAULT (primary key) or REPLICA IDENTITY FULL
4. PostgreSQL 18.x (required for the pg_trickle extension)

The auto mode: transparent transition

The default cdc_mode = 'auto' starts with triggers and automatically upgrades to WAL-based CDC when the prerequisites are met.

TRIGGER ──► TRANSITIONING ──► WAL
   ▲                           │
   └───────── (fallback) ──────┘

Transition lifecycle

1. TRIGGER — pg_trickle installs row-level triggers on the source table.
2. When wal_level = logical becomes available, pg_trickle starts the transition:
   • Creates a publication (pgtrickle_cdc_<oid>) and replication slot (pgtrickle_<oid>)
   • Sets the source's CDC state to TRANSITIONING
   • Both the trigger and WAL decoder write to the buffer (deduplication happens at refresh)
3. WAL — once the WAL decoder confirms it has caught up, the trigger is dropped.
4. Fallback — if the transition times out or errors (e.g. wal_level reverts to replica), the slot and publication are dropped and CDC reverts to triggers.

The transition is transparent — stream tables remain current throughout and there is no window of data loss.

Configuring CDC mode

Global setting

In postgresql.conf:

pg_trickle.cdc_mode = 'auto'     # default: start with triggers, upgrade to WAL
pg_trickle.cdc_mode = 'trigger'  # always use triggers; never create replication slots
pg_trickle.cdc_mode = 'wal'      # require WAL; error if wal_level != logical

Apply without restart:

ALTER SYSTEM SET pg_trickle.cdc_mode = 'auto';
SELECT pg_reload_conf();

Per-stream-table override

Override for a single stream table at creation time:

SELECT pgtrickle.create_stream_table(
    'public.order_totals',
    $$SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id$$,
    cdc_mode => 'wal'    -- force WAL for this table's sources only
);

Or after the fact:

SELECT pgtrickle.alter_stream_table('public.order_totals', p_cdc_mode => 'trigger');

The per-table override is stored in pgtrickle.pgt_stream_tables.requested_cdc_mode and takes precedence over the global GUC.

Checking the current CDC mode

-- Per-stream-table CDC state for all sources
SELECT source_table, cdc_mode, pending_rows, buffer_bytes
FROM pgtrickle.change_buffer_sizes()
ORDER BY source_table;

-- Full health check including WAL lag
SELECT * FROM pgtrickle.check_cdc_health();

-- Which triggers are installed
SELECT source_table, trigger_type, trigger_name, present, enabled
FROM pgtrickle.trigger_inventory()
ORDER BY source_table;

check_cdc_health() returns one row per source table with:

Enabling WAL-based CDC

If you start with cdc_mode = 'trigger' and later want to switch to WAL:

Step 1 — Configure PostgreSQL

# postgresql.conf
wal_level = logical
max_replication_slots = 20    # allow enough slots for all tracked sources

Requires a PostgreSQL restart:

pg_ctl restart -D $PGDATA

Step 2 — Set the GUC

ALTER SYSTEM SET pg_trickle.cdc_mode = 'auto';
SELECT pg_reload_conf();

pg_trickle will automatically begin transitioning existing stream tables to WAL-based CDC over the next few scheduler ticks. No manual intervention is needed per stream table.

Step 3 — Monitor the transition

SELECT source_table, cdc_mode FROM pgtrickle.check_cdc_health();

Tables will cycle through trigger → transitioning → wal over the next 1–2 minutes depending on write volume.

Reverting to trigger-based CDC

To revert globally:

ALTER SYSTEM SET pg_trickle.cdc_mode = 'trigger';
SELECT pg_reload_conf();

pg_trickle will drop all CDC replication slots and publications on the next scheduler tick and reinstall row-level triggers. Stream tables remain current throughout — the transition is safe.

To revert a single table:

SELECT pgtrickle.alter_stream_table('public.order_totals', p_cdc_mode => 'trigger');

Trigger mode details

Statement-level vs. row-level triggers

By default, pg_trickle uses row-level AFTER triggers. On high-volume bulk inserts (e.g. INSERT INTO orders SELECT … FROM staging), row-level triggers fire once per row. You can switch to statement-level triggers to reduce overhead at the cost of coarser change capture:

pg_trickle.cdc_trigger_mode = 'statement'   # default: 'row'

Note: cdc_trigger_mode is ignored when WAL-based CDC is active.

REPLICA IDENTITY and triggers

Trigger-based CDC captures the full NEW and OLD row. For DELETE and UPDATE to capture the old row values, the source table needs a primary key or REPLICA IDENTITY FULL. Without a primary key, pg_trickle detects this and may fall back to full refresh for affected stream tables.

WAL mode details

Replication slot naming

Each tracked source table gets its own replication slot:

pgtrickle_<source_table_oid>

And a publication:

pgtrickle_cdc_<source_table_oid>

These are internal to pg_trickle and should not be modified manually.

Slot lag management

If a subscriber (or pg_trickle itself) falls behind, the replication slot holds WAL on disk until it is consumed. This can grow unboundedly if pg_trickle is stopped for an extended period.

pg_trickle monitors slot lag and warns when it exceeds pg_trickle.publication_lag_warn_bytes (default: 64 MB). In auto mode, change-buffer cleanup is paused for lagging slots to prevent data loss.

If a slot grows dangerously large while pg_trickle is down, you can drop and recreate it:

-- 1. Temporarily switch to trigger mode
ALTER SYSTEM SET pg_trickle.cdc_mode = 'trigger';
SELECT pg_reload_conf();

-- 2. Manually drop the stale slot if needed
SELECT pg_drop_replication_slot('pgtrickle_12345');

-- 3. Switch back to auto (pg_trickle recreates the slot)
ALTER SYSTEM SET pg_trickle.cdc_mode = 'auto';
SELECT pg_reload_conf();

Partitioned source tables

WAL-based CDC for partitioned tables uses publish_via_partition_root = true so that child partition changes are published under the parent table name. This matches trigger-mode behaviour and ensures the stream table sees a unified change stream.

If a table is converted to partitioned after CDC is set up, pg_trickle detects the inconsistency on the next health check and rebuilds the publication with the correct setting automatically.

Monitoring slot lag in Prometheus

If you use the Prometheus & Grafana integration, pg_trickle exports per-source slot lag as:

pgtrickle_replication_slot_lag_bytes{slot_name="pgtrickle_12345", source_table="orders"}

Set an alert at 80% of your disk space budget for WAL retention.

Performance comparison

Troubleshooting

Trigger CDC: changes not appearing

1. Verify triggers are installed:

   SELECT * FROM pgtrickle.trigger_inventory() WHERE NOT present OR NOT enabled;
   

2. If missing, rebuild:

   SELECT pgtrickle.rebuild_cdc_triggers('public.source_table');
   

WAL CDC: slot not advancing

1. Check slot lag:

   SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
   FROM pg_replication_slots WHERE slot_name LIKE 'pgtrickle_%';
   

2. Check that the pg_trickle background worker is running:

   SELECT * FROM pg_stat_activity WHERE application_name LIKE 'pg_trickle%';
   

3. Check pg_trickle.cdc_mode is set to 'auto' or 'wal'.

Stuck in TRANSITIONING state

If a source table stays in transitioning for more than a few minutes:

SELECT source_table, cdc_mode FROM pgtrickle.check_cdc_health();

The transition has a timeout (wal_transition_timeout, default: 300 s). After the timeout it falls back to triggers automatically. If it keeps failing:

1. Check wal_level = logical is still set.
2. Check max_replication_slots has not been exceeded.
3. Force revert: ALTER SYSTEM SET pg_trickle.cdc_mode = 'trigger'.

See also

• Configuration: pg_trickle.cdc_mode
• Architecture Overview — CDC architecture and WAL decoder design
• Downstream Publications — expose stream table output via logical replication
• Tutorials: What Happens on INSERT — trigger-mode CDC deep dive