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.