Skippr Docs

Appearance

How PostgreSQL Handles CDC Replay Safety ​

May 2026

PostgreSQL replay safety depends on a staged apply path that checks row freshness before every upsert.

Short Answer ​

PostgreSQL handles CDC replay safety with a staging-then-apply pattern. Skippr loads incoming changes into a staging table and then uses INSERT ... ON CONFLICT with a WHERE clause that only updates the target row when the staging _skippr_order_token is newer than the one already stored.

Deletes are protected through companion tombstone tables. A replayed insert for a deleted business key only wins if its order token is newer than the delete token that was already recorded, which blocks ghost resurrection after retries.

Why Teams Struggle with This ​

This matters because PostgreSQL is familiar enough that teams sometimes assume normal upserts are sufficient. They are not. A plain ON CONFLICT without a freshness check can happily replace current state with an older replay if the same key arrives again.

  • Staging is part of the correctness model, not disposable plumbing.
  • A replay-safe upsert needs both a business key and an order-token comparison.
  • Delete history has to stay visible somewhere or replayed inserts become hard to reason about.
  • The destination table should be the stable object that downstream SQL reads.

How Skippr Handles It ​

Skippr makes the PostgreSQL contract explicit by creating the _skippr_order_token TEXT column and the skippr_tombstones companion automatically for CDC-managed tables. The staging table exists to bulk load efficiently while preserving the newer-wins rule during apply.

A concrete example: inventory sku = A1 is updated from quantity = 4 to quantity = 6, then a retry replays the older quantity = 4 change. The ON CONFLICT apply step compares tokens and leaves quantity = 6 in place because that row is newer.

  • PostgreSQL CDC uses a staging table plus INSERT ... ON CONFLICT apply pattern.
  • The update branch is guarded by _skippr_order_token comparison.
  • Tombstone tables prevent older inserts from reviving deleted rows.
  • Bulk loading and replay safety live in the same destination flow.

What the First Useful Version Looks Like ​

PostgreSQL replay safety is less about a special feature name and more about disciplined apply logic.

If the staging path, key choice, and newer-wins comparison are clear, retries stop being dangerous.