What your CDC pipeline does when a primary key changes

When an UPDATE changes a row's primary key, some CDC pipelines leave the old row behind in the destination. Whether yours does depends on three things: whether the source puts the old key in the change event, whether your tool turns the update into a delete plus an insert, and whether the destination applies that delete.

You can run a check for your database using the guide at the end of the article in less than 10 minutes.

How Primary Key Changes Create Orphan Rows in CDC

This week I ran one UPDATE through two CDC pipelines to see what each would do with it. The source was a three-row MySQL table:

sql
UPDATE customers SET id = 10 WHERE id = 1;

Alright, so what happened? The source table still has three rows. One of the destinations had four, and Ada was in it twice, as id 1 and as id 10.

Almost every UPDATE a CDC pipeline sees is boring, but the ones that change a primary key are different, and nothing in the pipeline flags them. Nothing failed and no alert fired, the destination just stopped agreeing with the source. Apps that rewrite keys are more common than you'd expect (an ORM that regenerates ids, a merge script, a one-off data fix somebody ran on a Friday).

How MySQL Binlog Events Record Primary Key Updates

Now let's take a look at what MySQL writes for that UPDATE. It's one row event in the binlog, with the old row (the WHERE image) and the new row (the SET image):

sql
UPDATE shop.customers
WHERE
@1=1  @2='Ada'  @3='ada@example.com'  @4='free'
SET
@1=10 @2='Ada'  @3='ada@example.com'  @4='free'

I'll call the old row the before-image. If the connector forwards this as a plain update at the new key, a destination that stores rows by key merges on id = 10, finds no row with that key, and inserts one. Nothing ever touches id = 1.

That's the orphan. The before-image says the old key was 1, so the tool had what it needed, but the destination only sees a row arriving at key 10.

Upserting on the new key is the easy first approach. It's right for inserts and ordinary updates, which is how it survives for years, and it's what produced the four-row table above. The table at the top shows the old key can reach the connector in every case, so the question is what the connector does with it.

How CDC Tools Handle Primary Key Changes: Delete and Insert

Okay, so the obvious fix is to turn the update into a delete plus an insert. Debezium does this at the source, and its MySQL and Postgres connector docs say that for a primary key change the connector emits a DELETE for the old key and a CREATE for the new key, in place of the UPDATE, with message headers (__debezium.newkey and __debezium.oldkey) that link the two. Estuary's MySQL capture has done the same since late 2024. For the same UPDATE, it writes a delete document at id 1 (_meta/op: d, carrying the old values) and then an insert at id 10 (_meta/op: c).

That's the right idea, but it can still go wrong in four places.

1. Incorrect old primary key: A delete built from the wrong old key hits the wrong row. More on this one below.
2. Event Ordering: The delete has to land before the insert if a key can be reused. Debezium sends records to Kafka by key, and Kafka orders records only within a partition. In my run the delete and tombstone for key 1 landed in partition 2, and the create for key 10 landed in partition 0. Nothing came out of order in any of my scenarios, but nothing guarantees that across partitions. When a key is reused (a new row takes the old key), the delete and the new insert share a key and therefore a partition, so that case stays ordered.
3. Downstream delete behavior: A destination in soft-delete mode, a history table, or an append-only table may treat the synthetic delete as an event to record instead of a row to remove. Estuary's Postgres materialization marks deletes by default (_meta/op = d, the row stays) and removes the rows only with hardDelete: true.
4. Sink delete configuration: Debezium's JDBC sink doesn't delete rows on DELETE or tombstone events unless you set delete.enabled=true (which needs a primary key mode that reads the record key). With the default, the delete arrives and nothing happens.

Debezium vs. Estuary: Primary Key Change Test Results

I put one MySQL table behind two pipelines and compared each destination with the source after three scenarios. The source used the default FULL row image. Debezium 3.0.8 ran with Kafka and its JDBC sink, once with delete.enabled=false (the default) and once with true. Estuary's MySQL capture fed two Postgres materializations, one with the default soft delete and one with hardDelete: true. The scenarios were:

• Plain key change. The UPDATE above.
• Key swap. Two rows trade ids inside one transaction, through a temporary key (2 to -1, 3 to 2, -1 to 3).
• Key reuse. One row moves from id 3 to 31, and a new row takes id 3, in one transaction.

Alright, now the results. After each scenario I compared every row, on all four columns, between the source and each destination. The table counts rows left behind (rows that no longer exist at the source):

Estuary's default is a soft delete, so the old row stays in the table with _meta/op = d. I counted only live rows (_meta/op <> 'd'). If you query that table directly, filter on _meta/op, or set hardDelete: true to remove the rows.

• Debezium with delete.enabled=true and both Estuary variants matched the source every time. The only pipeline that left rows behind was Debezium's default sink, because of one setting.
• The swap left behind a row you never wrote. The temporary key (-1) exists for a fraction of a second inside the transaction, and the binlog records it. It's invisible in the source and permanent in a destination that never applies deletes.
• Key reuse hides the problem. The new row overwrote the old row at that key, so there was nothing left to orphan, even in the sink that leaves orphans elsewhere.

So what does this mean for us? Mostly that one default setting decided the outcome, and that a transaction can leave behind rows you never wrote.

For more on operational and technical issues with Debezium in production, see our guide to Debezium CDC pain points.

Do MySQL FULL Row Images Prevent CDC Orphan Rows?

Well, partly. FULL row images in MySQL and REPLICA IDENTITY FULL in Postgres get the old values into the event, but you still need a tool that uses them, and the log gets bigger. In my MINIMAL test the binlog carried only the key in the old image and only the changed column in the new one, and Debezium's MySQL connector failed on it ("internal schema size 4, but row size 1"). Estuary's setup docs require FULL too, so use it.

Why CDC Needs the Complete Old Primary Key

Okay, this next part comes from our own code review. MySQL has had the delete-plus-insert behavior in Estuary since late 2024, and we added it to the Oracle capture this October (PR #5337, merged October 2). Oracle gives the connector the old row through undo SQL, and in that record a NULL looks the same as a column that didn't change. During code review the team worked through what that means for a key column that allows NULLs (real Oracle primary keys can't be NULL, but the connector lets you override the key). The reviewer's example was a table keyed on REGION and CODE:

Update (north, NULL) to (south, X7). Read naively, the before-state looks like (north, X7), because the NULL isn't visible. That's a different row, and it exists in the source. A synthesized delete for that key would remove it.

The change shipped with a guard. The connector splits an update only when the before-state is complete, and otherwise falls back to a plain update (and logs it). A synthesized delete is only as good as the old key you can prove, and on some sources you can't always prove it. Oracle only gets the old key at all when supplemental logging puts the key columns in the undo SQL.

CDC Before-Images and Data Privacy Risks

To split an update you need the old row, and the old row has every column. Many tools ship that before-image on ordinary updates too (in Debezium it's the before field, and in Estuary it's _meta/before). So a masking or redaction rule applied to a top-level field may not cover the copy inside the before-image. Check that yours does, in every place the pipeline stores documents.

Trade-offs of Handling Primary Key Changes in CDC

• FULL row images and REPLICA IDENTITY FULL mean more bytes per update in the log.
• Split events mean extra rows in history and append-only destinations, and downstream logic has to treat the pair as one change.
• Pre-images mean more sensitive data in more places.
• When the old key can't be proven (a missing before-state, NULL ambiguity, no supplemental logging), the safe behavior is a plain update, and that brings the orphan back.

Repairing orphans that already exist is its own job. Re-reading the current rows can't remove a key that no longer exists at the source, so you're deleting them by hand or rebuilding the table.

Primary Key Changes in CDC: FAQs.

1. Do all CDC tools handle key changes? - It depends on the tool, the connector and a sink setting. Test yours.
2. Will a backfill fix existing orphans? - A backfill re-reads what exists at the source now, so it won't remove old keys by itself.
3. Is it safe to ban primary key updates? - Only if you control every writer.
4. What about tables without a primary key? - Worse, since there's nothing to split on.

How to Test Your CDC Pipeline for Primary Key Changes

Alright, let's make this practical. Take a staging table with a few rows, point your pipeline at it, and run one key change:

sql
UPDATE customers SET id = 10 WHERE id = 1;

Then find the rows in the destination that aren't in the source. Export the source ids into a temporary table in the destination database (or any join-capable place), then:

sql
SELECT d.id
FROM destination.customers d
LEFT JOIN source_ids s ON s.id = d.id
WHERE s.id IS NULL;

An empty result means your pipeline handled it. If it isn't empty, check the settings that decide the outcome:

sql
-- MySQL: should be FULL
SHOW VARIABLES LIKE 'binlog_row_image';

-- Postgres: 'd' = default (old key on key change), 'f' = full row
SELECT relname, relreplident FROM pg_class WHERE relname = 'customers';

On the tool side, look for how it handles deletes at the destination (Debezium's JDBC sink needs delete.enabled=true, Estuary's Postgres materialization needs hardDelete: true if you want rows removed instead of marked). Then run the swap, to see whether a temporary key sneaks into the destination.

Test Primary Key Changes with Estuary

Estuary's MySQL CDC connector handles primary key changes by emitting delete and create events, helping prevent orphan rows in downstream tables when deletes are applied correctly.

Explore the MySQL capture documentation or start building a CDC pipeline for free.

Example CDC Events: MySQL, Debezium, and Estuary

MySQL binlog (one row event, old and new images):

sql
UPDATE `shop`.`customers`
WHERE
  @1=1 @2='Ada' @3='ada@example.com' @4='free'
SET
  @1=10 @2='Ada' @3='ada@example.com' @4='free'

Debezium (Kafka topic dbz.shop.customers, 3 partitions):

Estuary collection documents:

plaintext
{"_meta": {"op": "d"}, "id": 1,  "name": "Ada", "email": "ada@example.com", "tier": "free"}
{"_meta": {"op": "c"}, "id": 10, "name": "Ada", "email": "ada@example.com", "tier": "free"}

Merge semantics: in a destination that merges by key, the delete at id 1 is what removes the old row. A destination that only upserts never applies it, and the old row stays.

Final Takeaway: Primary Key Changes Can Silently Break CDC Consistency

A CDC pipeline can run without errors and still leave stale rows behind when a primary key changes. As our tests showed, correct replication depends on more than capturing the old and new values. The connector must handle the key change correctly, and the destination must apply the resulting deletes.

In our comparison, Debezium with deletes enabled and both Estuary configurations preserved the expected live rows. Debezium's default JDBC sink configuration left orphan rows because deletes weren't applied.

The takeaway is simple: don't assume your CDC pipeline handles primary key changes correctly. Test key updates, swaps, and reuse against the destination's actual behavior.