Does sync mode matter in CDC over postgres?

Yes, sync mode does matter when using CDC with Postgres, though in a specific way. How Sync Mode Interacts with CDC Source-side reading is handled by CDC regardless of sync mode — changes are read from the Write-Ahead Log (WAL) via Debezium, so you don't need a cursor field like

updated_at

. This part is the same across sync modes when CDC is enabled. [Postgres CDC tutorial] Destination-side writing is where the sync mode choice matters significantly: • Incremental | Append — Recommended for CDC. Only changed rows are synced and appended to the destination. This lets you capture inserts, updates, and deletions (via the

_ab_cdc_deleted_at

meta field). [Postgres CDC tutorial] • Incremental | Append + Deduped — Also works well with CDC. It appends raw changes and additionally maintains a deduplicated final table that mirrors the current state of the source, along with a history (SCD) table. [CDC synchronization tutorial] • Full Refresh — While technically usable, it is not recommended with CDC. Full Refresh syncs the entire source table on every run, which defeats the purpose of CDC (capturing only changes). The CDC process is the same with or without Full Refresh, but you lose the efficiency benefits. [Postgres CDC tutorial] Key Consideration: Tables Without Primary Keys For CDC, incremental sync is only supported for tables with primary keys. Tables without primary keys are limited to Full Refresh sync modes in CDC mode. [Postgres troubleshooting] Summary 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai Difference in Incremental | Append vs Incremental | Append + Deduped from cdc's mechanics standpoint?

From a CDC mechanics standpoint, both modes read changes the same way from the source (via the transaction log), but they differ significantly in how they write to the destination: Incremental | Append • Every CDC event (INSERT, UPDATE, DELETE) is appended as a new row to the destination table. • No deduplication occurs — if a record is updated multiple times, you'll find multiple copies of the same primary key in the final table. • DELETE events are represented by a row with

_ab_cdc_deleted_at

populated, but the old row is not removed — it just sits alongside the new tombstone row. • No primary key is required for this mode. [Incremental Append] Incremental | Append + Deduped • CDC events are first written to a temp table, then merged into the final table via a

MERGE

statement keyed on the primary key. [CDC Handling] • The final table contains only one row per primary key, reflecting the latest state. • Hard deletes are handled natively: when

_ab_cdc_deleted_at IS NOT NULL

, the corresponding row is physically deleted from the final table via the MERGE logic:

MERGE INTO final_table AS target
USING deduped AS source
ON target.pk = source.pk
WHEN MATCHED AND source._ab_cdc_deleted_at IS NOT NULL THEN DELETE
WHEN MATCHED AND source.cursor > target.cursor THEN UPDATE ...
WHEN NOT MATCHED AND source._ab_cdc_deleted_at IS NULL THEN INSERT ...

• A primary key is required for deduplication to work. [Incremental Append+Deduped] Summary In short, Append gives you a full audit log of every CDC event, while Append + Deduped gives you a clean, current-state view of the source with actual deletions applied. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements