Append, upsert, or delete: write modes and what they cost

Three modes, one config field

A sink's write_mode says how rows land in the destination:

• append (default) — add rows. No reconciliation, no key lookup — the fastest path.
• upsert — insert-or-update by key, so re-running a load is idempotent.
• delete — apply deletes (via a marker), for full CDC mirroring.

Upsert and delete need a key: they require auto-mapped columns and a declared key set, so the destination knows what "the same row" means.

Append has a fast lane

Within append, the Postgres sink offers two write methods: multi-row insert (default) and copy. COPY … FROM STDIN is a streaming bulk load and is meaningfully faster than multi-row INSERT for bulk append — in our Postgres → Postgres benchmark the COPY path led the field.

The cost is where the work is

Append streams: faucet hands the destination bytes and it writes them. Upsert/merge is different — the destination now has to match keys, update existing rows, and maintain indexes for every batch. On a warehouse a MERGE can be several times slower than an append, and that cost lives in the warehouse, not in faucet. Worth knowing when you read a benchmark: an append number is the ceiling; a real upsert pipeline sits below it, bounded by the target's reconciliation cost.

Takeaway

Use append (with COPY where available) when you're loading fresh data — it's the fast lane. Reach for upsert/delete when you need idempotent re-runs or a true mirror, and budget for the destination doing more work. faucet gives you the choice and refuses the incoherent combinations.