Optimized Cypher patterns for ingestion

How data is written in Cypher® affects both performance and correctness at scale. This page covers the patterns worth getting right before running imports.

`MERGE` or `CREATE`?

In general, MERGE is the safer option when ingesting data, but it is worthwhile knowing how it compares to CREATE, as there are situations when the latter could be useful.

CREATE creates new entities without any lookup. This makes it fast, since there is no existing data to check against, but it also means running the same import twice produces duplicate nodes (unless constraints are created to prevent such duplication). Use CREATE when:

Loading into a fresh database, with data known to be clean.
IDs are guaranteed to be unique, and the import will not be re-run.

MERGE checks whether a matching entity already exists before deciding whether to create one. This makes it safe for incremental imports and re-runs, but it comes at a cost: every MERGE is a lookup. Without an index backing that lookup (see Index-backed key constraints and type constraints), Neo4j does a full label scan across every existing node of that label, for every single row. On a file with 100,000 rows, that is 100,000 full scans. Use MERGE when:

The database already has data, and nodes might already exist.
The import might be re-run; MERGE is idempotent, CREATE is not.
Loading incrementally from a regularly updated source.

However, even though MERGE is idempotent, it has no guarantee of uniqueness. If two rows have the same ID but a slight property difference (a trailing space, a type mismatch, a casing difference) MERGE will create two separate nodes instead of matching the existing one. In the case of unintended value type differences, this can be solved with property type constraints.

Additionally, without a property uniqueness/key constraint on identifying properties, MERGE has nothing to lock onto for brand-new entities, meaning that two concurrent transactions can both find no match and both create identical entities.

It is, therefore, highly recommended to set a schema before ingesting data. For more information, see Schema and indexes.

Load relationships after nodes

The same logic for loading nodes applies to relationships: MERGE is usually the safer option, use CREATE only when the data is clean and will not be re-run.

Relationships should only be created after their start and end nodes have been ingested, as a separate, later step. Running node loading and relationship loading as two separate statements guarantees the nodes are already committed by the time the relationship step runs. If both were done in the same query, the relationship pattern would attempt to resolve nodes and create the relationship all at once, with no guarantee the nodes exist first. As a general rule when loading relationships, MATCH the previously created start and end nodes before merging the relationship between them:

MATCH pre-loaded start and end nodes before loading relationships

MATCH (a:Account {id: row.fromId})
MATCH (b:Account {id: row.toId})
MERGE (a)-[:TRANSFER]->(b)

In other words, avoid including start and end nodes in the same MERGE creating the relationship:

Avoid including start and end nodes in the relationship MERGE

MERGE (a:Account {id: row.fromId})-[:TRANSFER]->(b:Account {id: row.toId})

This second example lets MERGE create a or b if either is missing, rather than only connecting existing nodes, undermining the point of loading relationships as a separate step at all. Verify node counts before running the relationship import, since a missing node means the row is silently skipped, not an error.

Keep `MERGE` patterns concise

A common mistake is to merge on patterns that are too wide. Consider the following scenario:

customers.csv

customerId,name,email,city
1,Alice Johnson,[email protected],London
2,Bob Smith,[email protected],Manchester
3,Carol White,[email protected],Birmingham

MERGE on pattern of excessive length

CREATE CONSTRAINT customer_id IF NOT EXISTS
  FOR (customer:Customer) REQUIRE customer.customerId IS NODE KEY;

LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
WITH row.customerId AS customerId,
     row.name AS name,
     row.city AS city
CALL (*) {
  MERGE (customer:Customer {customerId: customerId, name: name, city: city})
}

customerId is the identifying key, covered by a node key constraint and its backing index. However, name and city are not constrained and therefore not indexed. Including them in the MERGE pattern can cause two problems.

First, Neo4j uses the index to find candidates by customerId, then must additionally filter on name and city in memory. Since neither property is indexed, this adds unnecessary overhead on every row.

Second, consider a re-run where the source data has been updated; a customer’s city has changed from London to Manchester. MERGE looks for a node matching all three properties exactly. It finds the expected customerId by index but the city property no longer matches, so no exact match is found. MERGE therefore attempts to create a new node with the customerId, at which point the node key constraint throws a constraint violation error.

It is, therefore, recommended to keep MERGE pattern concise, limited only to the identifying keys.

MERGE on concise pattern, keep non-identifying writes outside of MERGE

LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
WITH row.customerId AS customerId,
     row.name AS name,
     row.city AS city
CALL (*) {
  MERGE (customer:Customer {customerId: customerId})
  SET customer.name = name,
      customer.city = city
}

Always wrap Cypher ingestions in a CALL subquery

Even for small files, it is recommended to wrap write operations in a CALL subquery. Without it, the planner may introduce an Eager operator that forces the entire dataset to be buffered in memory before execution can continue. CALL subqueries avoids this potentially costly step by processing and committing rows separately which means the planner never needs to hold the full dataset in memory at once. To inspect the plan before loading data, prepend your load-statement with EXPLAIN. This is a recommended step before ingesting data of any size.

Example 1. Inspecting how a CALL subquery affects the plan of an import

staff.csv

id,name,age,joined,skills
1,Alice,33,2021-03-15,"Cypher, Python, Java"
2,Bob,30,2020-07-01,"Rust, Go"
3,Carol,42,2019-11-22,"Cypher, Java, Scala"

Create key constraint

CREATE CONSTRAINT person_id_key IF NOT EXISTS
FOR (p:Person) REQUIRE p.id IS KEY;

Data ingestion without CALL subquery: plan includes Eager operator

EXPLAIN
LOAD CSV WITH HEADERS FROM 'file:///staff.csv' AS row
MERGE (person:Person {id: row.id})
SET person.name = row.name
MERGE (skill:Skill {name: row.skills})

Query plan

+--------------------+----+---------------------------------------------------------+----------------+---------------------+
| Operator           | Id | Details                                                 | Estimated Rows | Pipeline            |
+--------------------+----+---------------------------------------------------------+----------------+---------------------+
| +ProduceResults    |  0 |                                                         |             10 |                     |
| |                  +----+---------------------------------------------------------+----------------+                     |
| +EmptyResult       |  1 |                                                         |             10 |                     |
| |                  +----+---------------------------------------------------------+----------------+                     |
| +Apply             |  2 |                                                         |             10 |                     |
| |\                 +----+---------------------------------------------------------+----------------+                     |
| | +Merge           |  3 | CREATE (skill:Skill {name: row.skills})                 |             10 |                     |
| | |                +----+---------------------------------------------------------+----------------+                     |
| | +Filter          |  4 | skill.name = row.skills                                 |              5 |                     |
| | |                +----+---------------------------------------------------------+----------------+                     |
| | +NodeByLabelScan |  5 | skill:Skill                                             |            100 | Fused in Pipeline 4 |
| |                  +----+---------------------------------------------------------+----------------+---------------------+
| +Eager             |  6 | read/set conflict for property: name (Operator: 7 vs 4) |             10 | In Pipeline 3       |
| |                  +----+---------------------------------------------------------+----------------+---------------------+
| +SetProperty       |  7 | person.name = row.name                                  |             10 |                     |
| |                  +----+---------------------------------------------------------+----------------+                     |
| +Apply             |  8 |                                                         |             10 |                     |
| |\                 +----+---------------------------------------------------------+----------------+                     |
| | +MergeUniqueNode |  9 | UNIQUE person:Person(id) WHERE id = row.id,             |             10 | Fused in Pipeline 2 |
| |                  +----+---------------------------------------------------------+----------------+---------------------+
| +LoadCSV           | 10 | row                                                     |             10 | In Pipeline 1       |
+--------------------+----+---------------------------------------------------------+----------------+---------------------+

Data ingestion with CALL subquery: plan does not include Eager operator

EXPLAIN
LOAD CSV WITH HEADERS FROM 'file:///staff.csv' AS row
WITH row.id AS id, row.name AS name, row.skills AS skills
CALL (id, name, skills) {
    MERGE (person:Person {id: id})
    SET person.name = name MERGE (skill:Skill {skill: skills})
}