Graph Data Modeling

MERGE vs CREATE for idempotent upserts

Two Cypher clauses write nodes, and picking the wrong one is the single most common cause of a graph that drifts on re-run. CREATE always inserts a new node — fast, unconditional, and duplicating on the second execution. MERGE matches-or-creates — idempotent by design, but only race-safe and fast when a backing uniqueness constraint stands behind its match key. This guide draws the line between them: when you need the idempotent upsert semantics of MERGE, when unconditional CREATE is still the correct and faster choice, and exactly why a MERGE without a constraint quietly degrades into a full label scan that can still produce duplicates under concurrency. It is the write-time decision that sits underneath the whole constraint lifecycle.

Prerequisites

The core distinction

CREATE is unconditional. It allocates a new node every time it runs, regardless of what already exists, so it is the fastest possible write — no lookup, no match, just an insert. That speed is exactly why it duplicates: run the same CREATE twice and you get two nodes. MERGE is conditional. It first searches for a node matching its pattern; if one exists it binds to it, otherwise it creates one. That match is what makes re-execution converge on the same graph — the definition of an idempotent upsert — but the search is only cheap when it can seek an index instead of scanning every node of the label.

cypher
// CREATE — unconditional insert. Two runs, two :Event nodes. Correct ONLY when
// each row is a guaranteed-new, immutable fact that should never be deduplicated.
CREATE (e:Event {event_id: $id, occurred_at: datetime($ts), kind: $kind});
cypher
// MERGE — match-or-create anchored on the STABLE identity key. Two runs, one
// :Customer node. ON CREATE fires only on first insert; ON MATCH on every
// subsequent run. Splitting them keeps immutable fields set-once.
MERGE (c:Customer {customer_id: $id})
  ON CREATE SET c.created_at = datetime($ts), c.status = $status
  ON MATCH  SET c.updated_at = datetime($ts), c.status = $status;

The ON CREATE SET / ON MATCH SET split is the mechanism that makes MERGE a true upsert rather than a blunt overwrite. Properties that must be written once and never changed — a creation timestamp, an origin marker — go under ON CREATE. Properties that reflect the latest state go under ON MATCH, or under a plain SET after the MERGE when they should apply in both cases. Anchor the MERGE pattern on the immutable key only; putting a mutable property inside the MERGE {…} map means a changed value fails to match the existing node and creates a duplicate — the classic drift bug dissected in implementing idempotent migration scripts for Neo4j.

Why MERGE without a constraint duplicates

MERGE guarantees idempotency only when its match is atomic, and atomicity comes from the backing uniqueness constraint’s index-level write lock. Without that constraint, MERGE does a NodeByLabelScan to check existence — O(n) in the label’s size — and, worse, two concurrent transactions can each run that scan, each find no match, and each insert. The result is duplicate identity keys that the constraint would have prevented. The constraint is therefore not an optimization you add later; it is what makes the MERGE correct under concurrency in the first place.

Choosing CREATE vs MERGE, and why MERGE needs a constraint Flowchart. The Write box feeds a decision "Same input can run twice?". The no branch reaches a CREATE outcome labelled unconditional fast insert, correct for guaranteed-new immutable events. The yes branch reaches a second decision "Uniqueness / NODE KEY constraint online?". Its yes branch reaches a MERGE outcome labelled seeks index, race-safe and fast. Its no branch reaches a warning outcome labelled label scan, duplicates under concurrency, with a corrective arrow back to create the constraint first. no yes yes no create constraint first Write a node Same input runs twice? CREATE unconditional insert new immutable event Constraint online? MERGE seeks index race-safe & fast MERGE (unsafe) label scan duplicates concurrently

The performance gap widens with scale. A CREATE is constant-cost no matter how large the label grows, because it never looks anything up. An index-backed MERGE is close behind — one logarithmic index seek per row — so with the constraint in place the idempotency you gain is nearly free. A constraint-free MERGE, by contrast, degrades linearly: at ten thousand nodes the scan is unnoticeable, at fifty million it dominates the load and every additional row makes it worse. That is why the constraint must exist and be ONLINE before the first MERGE, not added afterward to a table already full of duplicates it can no longer be created over. When upserting in bulk, keep the same shape and batch it with UNWIND $rows AS row MERGE (…), so a single index-backed transaction handles thousands of rows instead of one round trip each.

Validation & verification

Prove the MERGE is index-backed, not scanning. EXPLAIN over the upsert should show a NodeUniqueIndexSeek at the leaf; a NodeByLabelScan with a Filter is the fingerprint of a missing or not-yet-online constraint:

cypher
EXPLAIN
MERGE (c:Customer {customer_id: $id})
  ON CREATE SET c.created_at = datetime($ts)
RETURN c.customer_id;   // want NodeUniqueIndexSeek, not NodeByLabelScan + Filter

Then prove idempotency behaviourally. Count the label, run the identical MERGE batch a second time, and re-count — a true upsert leaves the total unchanged, whereas a CREATE doubles it:

cypher
// Run before and after a second execution of the same input; the count must not move.
MATCH (c:Customer) RETURN count(c) AS total;

Edge cases & gotchas

1. MERGE on a mutable property. Anchoring on a value that changes between runs — MERGE (c:Customer {customer_id: $id, status: $status}) — means a changed status fails to match the existing node and inserts a duplicate. Anchor on the immutable key alone and apply the rest with SET:

cypher
// RIGHT — identity is the stable key; mutable state is applied after the match.
MERGE (c:Customer {customer_id: $id}) SET c.status = $status;

2. Using CREATE for reference data that re-loads. Loading lookup or dimension data with CREATE duplicates it on every pipeline re-run. Any record that can be reprocessed needs MERGE; reserve CREATE for append-only, immutable facts — audit events, ledger entries, sensor readings — where each row is genuinely new and should never be deduplicated.

3. Relationship MERGE without bound endpoints. MERGE (a)-[:OWNS]->(b) without first binding a and b on their keys can create phantom endpoint nodes. Merge both endpoints on their stable keys first, then merge the edge between the bound variables:

cypher
MERGE (c:Customer {customer_id: $cid})
MERGE (a:Account {account_id: $aid})
MERGE (c)-[:OWNS]->(a);   // one edge no matter how many replays

This decision is one thread of Constraint Lifecycle Management; the uniqueness constraint that makes MERGE safe is the same object whose lifecycle that reference governs.

Related pages