Skip to Content

UPSERT (ON CONFLICT) for Idempotent Pipelines

When to use

Re-running a feature pipeline should not produce duplicates or errors — same source, same destination, same result.

Analogy

ON CONFLICT is a librarian who, when a book with the same ISBN arrives, replaces the old shelf copy with the new one instead of stacking them.

Data-flow diagram

  INSERT INTO features (user_id, metric, ts)
       VALUES $values
  ON CONFLICT (user_id, ts)            -- existing row? UPDATE here.
       DO UPDATE SET metric = EXCLUDED.metric;

       |  conflict      no conflict
       v                v
   UPDATE row         INSERT row
   (set new fields)   (use VALUES)

Deep explanation

UPSERT = INSERT with ON CONFLICT (cols) DO UPDATE / DO NOTHING. Runs are idempotent: rerunning the pipeline with the same data produces the same final state. ON CONFLICT requires a unique index or primary key on the conflict columns — Postgres can’t decide ‘is this row a duplicate’ without one. DO NOTHING is the cheap, throwaway option; DO UPDATE is for keeping latest-wins semantics on feature tables. DESIGN AHEAD: declare your natural key at the table design phase, not after the first duplicate crash.

Examples

Example 1

-- 11a: simple latest-wins feature UPSERT
INSERT INTO user_features (user_id, window_end, metric, value)
VALUES (42, '2026-01-31', 'click_count', 17)
ON CONFLICT (user_id, window_end, metric)
DO UPDATE SET value = EXCLUDED.value;
 
-- requires: UNIQUE (user_id, window_end, metric) on user_features

Standard pattern for offline feature stores: pipeline reruns are safe; each row is keyed by (user_id, window_end, metric).

Example 2

-- 11b: DO NOTHING for append-only dedup (e.g. event log)
INSERT INTO processed_events (event_id, payload, processed_at)
VALUES (:event_id, :payload, NOW())
ON CONFLICT (event_id) DO NOTHING;

DO NOTHING for idempotent event de-dupe without recomputing — perfect for at-least-once delivery.

Example 3

-- 11c: server-side computation on conflict (e.g. recompute running count)
INSERT INTO user_credit_ledger (user_id, event_seq, balance)
VALUES (42, next_event_seq, 100)
ON CONFLICT (user_id)
DO UPDATE SET balance = user_credit_ledger.balance + EXCLUDED.balance;

ON CONFLICT DO UPDATE can do server-side arithmetic: keep a running balance, count, or vector update.

Common mistake

Missing unique index on the conflict columns — Postgres raises a syntax error explaining it. Forgetting EXCLUDED: when DO UPDATE references the new value, use EXCLUDED.col to grab the INSERT-side column, not the literal. Another trap: DO UPDATE NOT resetting updated_at — concurrency becomes invisible.

Key takeaway

Always declare a UNIQUE index covering the natural key; ON CONFLICT DO UPDATE for latest-wins feature rows; DO NOTHING for append-only idempotency; EXCLUDED.col references the would-be INSERT row; rerun pipelines freely.

Production Failure Playbook

Failure scenario 1: missing-unique-index

Failure scenario 2: do-update-vs-do-nothing-mixed-up