Bulk Loading & Import

patinaDB supports offline CLI import and online bulk loading. The three online paths below are versioned (recorded in the engram log) and, on a cluster, replicated. Offline CLI import writes the datastore directly without engrams.

Bulk upsert (large files, match-or-create)

POST /mgmt/upsert, /mgmt/upsert-edges, and /mgmt/upsert-graph resolve and apply a whole columnar file (CSV / Parquet / Arrow) — or a coupled JSON node+edge subgraph — as one changeset, per-row match-or-create against a match key, index-accelerated. A chunk_rows field splits a very large load into many bounded, independently-committed engrams so peak memory (and Raft entry size) stays O(chunk) instead of O(rows).

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert \
  -H 'content-type: application/json' \
  -d '{"db":"default","file":"/srv/import/people.csv","label":"Person","match":["email"]}'

The file path is confined to the server’s filesystem sandbox (see below); this is the fastest, largest-scale online path — see Bulk Upsert for the full request shape, merge/conflict policies, and chunking guidance.

Offline CLI import

patinadb import loads node and relationship CSV files into a database while it is offline. This direct datastore operation does not create import engrams or establish a replicated history baseline. Do not use it against a running server or treat a successful current-graph read as proof of history or replica recovery. Node headers identify :ID, :LABEL, and property columns; relationship headers identify :START_ID, :END_ID, :TYPE, and property columns. A property header may declare a scalar type (name:int, active:boolean, score:double) or one of the typed-list forms below. CSV quoting follows the CSV reader’s standard quoting rules, so a JSON array cell containing commas must itself be CSV-quoted, for example:

:ID,:LABEL,tags:string[]
u1,Person,"[""red"",""blue, green""]"

For typed-list properties, the cell contains a JSON array:

An empty cell means the property is absent. [] stores an empty list, while [""] stores a one-element list containing the empty string. JSON null elements are allowed. Array order and duplicate elements are retained; string contents, including leading or trailing whitespace, are preserved. The cell must be a JSON array whose non-null elements match the declared type. Nested arrays, objects, malformed JSON, and values of another element type are rejected.

Typed-list declarations apply to CSV import; typed Arrow List columns remain unsupported. This does not establish a CSV/Arrow export-import round-trip contract.

Scalar string properties (string, char, an untyped header, or an unrecognised type suffix) preserve the CSV cell exactly, including leading and trailing whitespace. A zero-length cell means the property is absent; a whitespace-only cell is a present string containing that whitespace. Earlier importer behavior trimmed scalar strings and treated whitespace-only cells as absent. Re-importing such files with this behavior can therefore change stored scalar string values or add a previously absent whitespace-only property. This is an intentional migration behavior; scalar numeric, Boolean, date, and datetime parsing retains its prior surrounding-whitespace handling.

For a :LABEL cell, semicolon-separated labels form the complete label set: the first is primary and later unique labels are secondary. Whitespace around label names is ignored; empty names (including A;;B or A;) are rejected. A later row for the same ID replaces the previous set; a single primary label clears existing secondary labels. This also applies to Arrow/Parquet label columns. Replacement does not imply deletion of omitted properties.

The importer retains its existing durable batch and interrupted-import recovery contracts. It does not make an entire multi-file import rollback-atomic.

Online: LOAD CSV

LOAD CSV streams rows from a CSV file into a running query as a row source — like UNWIND, but from a file. It goes through the normal write path, so each load is versioned (an engram) and, on a cluster, replicated.

LOAD CSV WITH HEADERS FROM 'file:///data/people.csv' AS row
CREATE (:Person {id: toInteger(row.id), name: row.name})

• WITH HEADERS makes each row a map keyed by header (row.name); without it each row is a list of string cells (row[0]).
• Cells are strings — coerce with toInteger / toFloat / toBoolean. A coercion may sit directly in a CREATE/MERGE pattern property (as above); a row[i] list-index expression still needs a WITH stage first.
• FIELDTERMINATOR '<c>' overrides the , delimiter. http(s):// URLs are rejected; only file:// and bare/relative paths are read.

The full clause reference is in Cypher Support → Loading CSV.

The un-chunked memory cliff

A bare LOAD CSV … CREATE streams the source row by row, but buffers all resolved write operations into one transaction (on the server, one Raft entry) before committing — an O(rows) memory cliff for a large load. A LOAD CSV … RETURN row read query streams end to end and has no such cliff.

Correction (issue #1060, measured 2026-08-21): the O(rows) figure above holds for the plain example shown — a CREATE with no MATCH at all. The equally ordinary foreign-key-lookup import idiom —

LOAD CSV WITH HEADERS FROM 'file:///data/orders.csv' AS row
MATCH (c:Customer {id: toInteger(row.customer_id)})
CREATE (c)-[:PLACED]->(:Order {id: toInteger(row.order_id)})

— is not O(rows). Before the #1060 fix, LOAD CSV could never seed the planner’s index-seek restructure (issue #429/#367) the way a top-level UNWIND can, so MATCH (c:Customer {id: …}) fell back to a full label scan over every :Customer vertex, repeated once per CSV row — O(R · rows) where R is the distinct-customer count, not O(rows). Measured on a hub-with-N-leaves fixture (R = distinct parents, N = rows per parent): the real LOAD CSV path cost the same order of magnitude, ~3.9 GB peak RSS, as a synthetic double-UNWIND cross-join at R = 25, N = 20 000 — see crates/patinadb-bench/examples/existence_isolate_probe.rs (modes crossjoin_build / loadcsv_build) for the harness and crates/patinadb/src/execution/load_csv_tests.rs’s loadcsv_seed_restructure_matches_unoptimised_reference for the fix’s differential proof.

The fix makes LOAD CSV a seed candidate like UNWIND, so the shape above now seeks when the compared value is a bare CSV field (row.customer_id with Customer.id also stored as a string, no coercion).

Correction (issue #1060 follow-up 2, 2026-08-22): the paragraph above, as first written, said the coerced form (toInteger(row.customer_id)) — the exact shape in the example at the top of this section — stayed a “known, separate, still-open limitation”. That is no longer true. A second #1060 follow-up taught the binding_seek mechanism (#429/#367) to seek through a CypherLit::Func value too, for a small, deliberately narrow allowlist of pure coercions: toInteger, toFloat, toString, toBoolean — exactly the four this manual already recommends for coercing a CSV cell (see “Cells are strings” above). So the foreign-key example at the top of this section, written exactly as shown, now seeks. The residual limitation is narrower than before, not gone: a coercion or transform OUTSIDE that four-function allowlist (toLower, abs, a +/arithmetic expression, …) still does not seed a seek, even where the function itself is pure — the allowlist is deliberately kept small (each addition is its own auditable decision, not “obviously fine, might as well”; see plan::expr::SEEK_SAFE_SCALAR_FUNCS’s doc comment for the reasoning) — and a value-dependent expression referencing a binding the match pattern itself introduces (not yet bound at seek time) still declines safely rather than seeking a wrong key. Gate: execution::in_seek_and_compound_range_tests:: unwind_seed_func_allowlist_each_coercion_seeks_and_matches_reference (all four coercions) and execution::load_csv_tests:: loadcsv_seed_func_tointeger_coercion_matches_unoptimised_reference (the real LOAD CSV path, this exact idiom).

CALL { … } IN TRANSACTIONS (below) bounds peak memory to one chunk regardless of whether a given write shape seeks — do not read chunking as a substitute for the seek when a shape falls outside the allowlist above.

Online + chunked: CALL { … } IN TRANSACTIONS

Wrapping the write subquery in CALL { … } IN TRANSACTIONS chunks the load into many small, independently-committed transactions — the online answer to the RAM cliff. Peak memory drops to one chunk, and on a cluster each chunk replicates as one small Raft entry instead of one unbounded one.

LOAD CSV WITH HEADERS FROM 'file:///data/more_people.csv' AS row
CALL {
  WITH row
  CREATE (:Reader {id: row.id, name: row.name, age: toInteger(row.age)})
} IN TRANSACTIONS OF 2 ROWS

Loading a 5-row file OF 2 ROWS produces ⌈5/2⌉ = 3 chunks — and three separate engrams, one per committed chunk:

MATCH (r:Reader) RETURN count(r) AS n
-- n: 5
CALL patinadb.engrams() YIELD id RETURN count(id) AS n
-- n: 3 (for this one load, on a fresh database)

• Each chunk is its own commit ⇒ its own engram, so batched ingest is versioned and time-travellable. A later chunk sees the writes committed by earlier chunks (read-your-writes).
• OF <n> ROW[S] sets the chunk size (default 1000).
• ON ERROR — FAIL (default) aborts the whole statement on a failing chunk; CONTINUE skips it and commits the rest; BREAK stops after it, keeping earlier chunks. A skipped/failed chunk changes nothing — it is resolved against a throwaway copy of the graph, so a partial chunk is never left behind.
• Crash-atomic per chunk: a crash between chunks recovers to a chunk boundary. The graph and the engram log always agree.
• The CALL { … } IN TRANSACTIONS must be the final clause of the query, and it cannot run inside an explicit Bolt BEGIN … COMMIT transaction.

On the server the driver proposes one client_write per chunk, so a 250-row load OF 100 ROWS advances the applied index by 3, not 1 — bounded Raft entries, read-your-writes preserved across chunks on the leader.

Server file-I/O sandbox

Reading a file:// URL, or writing with the export procedures, touches the server’s filesystem. On the server both are deny-by-default and gated by two layers:

1. Directory sandbox — --allow-csv-dir <dir> (reads) and --allow-export-dir <dir> (writes) whitelist directories; unset means every file access is refused. Paths are canonicalized, so .. traversal and symlink escapes are rejected.
2. Authorize by effect — any query that does file I/O is raised to require the global Admin role, because file access is a host-level capability, not a graph-data one. A per-database Writer cannot read or write host files.

/mgmt/upsert and its siblings confine their file path through the same --allow-csv-dir sandbox and are admin-only by virtue of living under /mgmt/. See Authentication & TLS → Cypher-driven file I/O.

Which path should I use?

• A large columnar file (or subgraph document) already on the server → POST /mgmt/upsert/upsert-edges/upsert-graph (fastest, index-accelerated match-or-create, chunkable).
• Live ingest into a running / clustered database from a streamed CSV → LOAD CSV … CALL { … } IN TRANSACTIONS OF n ROWS (versioned, replicated, bounded memory).
• A small load, or you need the CSV as a read source → plain LOAD CSV … CREATE / LOAD CSV … RETURN row.

Deferred index-build memory (#1398)

Index rebuilding externally merges all sorted runs before filling the index. It limits each spill run by both key count (PATINADB_INDEX_BUILD_SORT_KEYS, default 8,000,000) and retained key allocation bytes (PATINADB_INDEX_BUILD_SORT_BYTES, default 67,108,864 = 64 MiB). The byte accounting includes each key’s allocated capacity and vector descriptors and sequence bookkeeping. Both limits must admit a key into the current run. Zero or invalid values use the defaults. This applies to vertex/edge value, compound and edge-sorted rebuilds.

These are run-buffer limits, not a total process-memory limit. Allocator overhead, spare vector capacity, backend cache/transactions and other import buffers consume additional memory. A single key larger than the byte budget is flushed on its own; it is not rejected or silently omitted. The existing pending-build marker and terminal durability barrier remain unchanged. Size the total process/cgroup budget separately, including file cache, and compare finalization as well as insertion time.

Sorted-spool filling has a separate pair of limits: PATINADB_SORTMERGE_FILL_BATCH (default 200,000 entries) and PATINADB_SORTMERGE_FILL_BYTES (default 67,108,864 = 64 MiB of retained key/value allocations and descriptors). The fill stops a batch when either limit is reached; a single oversized entry is applied alone. Zero or invalid byte values use the default. This bounds the sorted consumer batch, not the producer’s external-sort spools (PATINADB_SORTMERGE_RUN_BYTES), the entire import, or all snapshot buffers. The shared sorted fill also serves snapshot installation; its surrounding atomic installation scope and crash-recovery markers are unchanged.

The offline importer no longer switches between inline and deferred loading. All imports use sorted mutation spools, including re-imports and skipped input reference checks. Existing stores apply bounded sorted chunks with incremental value, compound and edge-sorted index maintenance. Each chunk preserves properties absent from its input and removes superseded index keys before its terminal durability barrier; it does not rebuild unrelated indexes. Fresh whole-load builds retain their pending marker and derive indexes from final primary values when repeated input IDs prevent trusting precomputed value-index keys. The retired PATINADB_IMPORT_SORTMERGE and PATINADB_DEFERRED_INDEX_MIN_ROWS selectors no longer choose an alternative implementation. Index rebuild scratch files use private temporary directories under TMPDIR; size that disk for spilled keys.

Incremental delta transactions

Small input buffers do not necessarily imply small redb transactions. Updating scattered keys can modify many existing B-tree pages. When those dirty pages outgrow the write cache, early writeback can add I/O before the final durability barrier. Sorting the keys alone does not prevent this.

Existing-store sorted deltas therefore also use an estimated-work budget. Automatic sizing uses 1/32 of the configured redb cache, capped at 64 MiB, with a minimum charge of 16 KiB per mutation. This is a heuristic, not a bound on dirty pages, RSS or I/O, and does not promise faster imports for every workload. Fresh dense index construction and snapshot filling use their fill limits. Direct snapshot buffers also obey PATINADB_SORTMERGE_FILL_BYTES: a small record count can still retain large property values. This includes historical reconstruction for background tag snapshots, not just learner installation. Smaller delta chunks keep the same deferred-commit and terminal-sync contract; they do not make partially applied imports a supported recovery state.

PATINADB_SORTMERGE_DELTA_WORK_BYTES overrides this heuristic with a positive integer byte budget. Leave it unset initially. Reduce it when profiling shows write-cache churn during incremental import; raise it only when matched tests show that extra small transactions cost more than they save. The ordinary PATINADB_SORTMERGE_FILL_BYTES and PATINADB_SORTMERGE_FILL_BATCH caps remain independent upper limits. Raising the work budget does not bypass them.

For example, a configured 128-MiB redb cache selects a 4-MiB work budget. With the minimum charge, that admits at most 256 mutations per transaction; wider keys or values reduce the count. This is the policy’s arithmetic, not a measured optimal batch size. Set environment overrides before opening the database.

Compare the same database image, input and total RAM limit in both arms. Include terminal sync in wall time, check contents and indexes after reopen, and record CPU time, actual read/write bytes and I/O pressure. A lower byte count alone is not a latency improvement. Leave RAM for the OS page cache and other database buffers; enlarging redb’s cache inside an unchanged cgroup can create pressure elsewhere. Neither tuning knob replaces the process memory limit.