Native staged bulk loader

A multi-worker loader for large N-Triples files. It runs in four phases (STAGE → DICT → RESOLVE → INDEX), commits each one before the next begins, and sizes itself to the host. It loaded the complete 8.2-billion-triple Wikidata "truthy" dump into one PostgreSQL instance.

What it does

pgrdf.load_turtle_staged_run(path TEXT, graph_id BIGINT, n_workers INT DEFAULT 0) → JSONB
CALL pgrdf.load_turtle_staged(path TEXT, graph_id BIGINT, n_workers INT DEFAULT 0)

Loads the N-Triples file at path (on the database server) into the graph, using a pool of background workers. n_workers => 0 (the default) sizes the pool to the host. The function returns a report; the procedure runs the same load as a CALL.

Phase	What it does
STAGE	Parses the file into an unlogged staging table, in parallel across the workers.
DICT	Finds the distinct terms and assigns dictionary ids with set-based SQL that spills to disk, so memory stays bounded however many terms there are.
RESOLVE	Joins the staged triples against the dictionary to produce the encoded quads.
INDEX	Builds the SPO, POS and OSP indexes concurrently, one worker per index.

On a preloaded server, load_turtle hands N-Triples files to this loader on its own (when no base_iri is given). Call it directly when you want the report.

Rules

• N-Triples only. One complete triple per line, absolute IRIs, no prefixes. Convert Turtle to N-Triples first with any RDF toolkit. A Turtle file is not refused: it reports "ok": true with "triples": 0 and loads nothing.

• An empty database. The staged loader needs a database into which nothing has been loaded yet. Otherwise it loads nothing and reports why:

  {"ok": false, "job_id": 23, "reason": "dictionary already populated — staged loader requires an empty dict; caller should use the combined path", "fallback": true, "n_workers": 4}

  Use load_turtle for further loads into the same database.

• Not inside a transaction block. Because it commits per phase, it refuses inside BEGIN … COMMIT with a message saying so ("the staged loader commits per phase and cannot run inside a transaction block; call it as a single statement …"). Run it with autocommit on, and check your driver's setting.

• pgRDF must be preloaded. Background workers only start when pgRDF is in shared_preload_libraries (a restart, not a reload); see Install.

• Malformed lines are skipped, not fatal. Compare triples in the report with the number of lines in your file.

Example

A small N-Triples file on the database server, /tmp/people.nt (with the Docker container from Install, write it locally and docker cp people.nt pgrdf:/tmp/people.nt):

<http://example.org/alice> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://xmlns.com/foaf/0.1/Person> .
<http://example.org/alice> <http://xmlns.com/foaf/0.1/name> "Alice" .
<http://example.org/alice> <http://xmlns.com/foaf/0.1/knows> <http://example.org/bob> .
<http://example.org/bob> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://xmlns.com/foaf/0.1/Person> .
<http://example.org/bob> <http://xmlns.com/foaf/0.1/name> "Bob" .

Load it into a fresh database:

SELECT pgrdf.add_graph('http://example.org/people');

SELECT jsonb_pretty(pgrdf.load_turtle_staged_run(
  '/tmp/people.nt', pgrdf.graph_id('http://example.org/people')));

{
    "ok": true,
    "quads": 5,
    "job_id": 22,
    "triples": 5,
    "phase_ms": {
        "dict": 34.835133,
        "index": 6.99406,
        "stage": 10.156799,
        "resolve": 7.679558
    },
    "n_workers": 4,
    "dict_terms": 9
}

quads equal to triples confirms every staged triple arrived in the graph. phase_ms gives the time per phase, in milliseconds. Timings and job_id vary from run to run.

The procedure form prints the same report as a notice:

CALL pgrdf.load_turtle_staged('/tmp/people.nt', pgrdf.graph_id('http://example.org/people'));
-- NOTICE:  pgrdf staged load: {"ok": true, "quads": 5, "job_id": 24, "triples": 5, "phase_ms": {…}, "n_workers": 4, "dict_terms": 9}

Why you'd use it

• Project managers — a billion-scale graph loads into one PostgreSQL instance you already know how to back up, monitor and secure. No second system to operate.
• Data scientists — load the full source graph once, then query it with SPARQL and SQL in place.
• Operators — each phase commits, and the loader logs the settings it chose, so a long load is observable.
• Ontologists — every distinct literal survives, including each language and datatype variant of the same text (see below).

Settings

All are ordinary settings you can SET in the session; only the preload needs a restart. The defaults complete a full-scale load on stock PostgreSQL.

pgrdf.staged_resolve_strategy — index (default) | hash | auto

The join method RESOLVE uses. The result is identical whichever you choose; only the plan and the temporary disk use differ.

Value	Behaviour
index (default)	Index nested loop. Low temporary disk use; the setting the full-scale load was validated with.
hash	Hash join. At billions of rows it can spill terabytes to temporary files.
auto	No forcing; the PostgreSQL planner chooses.

Any other value refuses with 22023 and lists the accepted ones.

pgrdf.staged_temp_tablespaces — where temporary spill goes

At the largest scale, temporary files can reach terabytes. By default they go to the data disk. Set this to a tablespace name (or a comma-separated list) to send them to a roomier volume:

SET pgrdf.staged_temp_tablespaces = 'fast_scratch';

Empty (the default) uses the server's own temp_tablespaces.

Self-tuning

Each phase's work_mem, maintenance_work_mem and parallelism are set from the host's RAM and core count. The chosen values are written to the server log, for example:

LOG:  staged self-tune: MemTotal=7.7GiB nproc=4 work_mem=165MB maintenance_work_mem=990MB max_parallel_workers=4 temp_tablespaces=server-default

The same loader runs on a laptop-sized machine and on a 128-core server; the 8.2-billion-triple load is a ceiling, not a requirement.

Every distinct literal is kept

The dictionary is keyed on a literal's full identity (value, datatype and language), not on its text alone. "Berlin"@en, "Berlin"@de, "1"^^xsd:integer and "1" each get their own id. At Wikidata scale this keeps the whole multilingual and typed-literal space of the source.

Benchmark — the full Wikidata graph

The complete Wikidata "truthy" N-Triples dump, loaded into a single PostgreSQL instance:

Measured	Result
Triples loaded	8,199,708,346 (none dropped)
Distinct dictionary terms	1,801,847,593
On-disk size	~2.0 TB (heap 729 GB + indexes 1,448 GB)
Indexes	full SPO / POS / OSP
Literal identity	"Berlin" kept in 268 distinct languages

Host	Ingest time	Rate
128-vCPU cloud VM, 1 TiB RAM	4 h 53 m	~466 K triples/s
64-vCPU cloud VM, 503 GiB RAM, 3.4 TB disk	~10.3 h	~221 K triples/s

On the 128-vCPU machine the phases took STAGE 14 min · DICT 1 h 51 m · RESOLVE 2 h 00 m (index strategy) · INDEX 32 min. The 64-vCPU run shows the same load completing with default settings on half the cores and a 3.4 TB disk: the index resolve strategy keeps temporary files small enough to fit.

Raw ingest, not reasoning

These figures are for loading only. Wikidata "truthy" statements are direct assertions, so there is nothing to infer. For a full load → reason → query pipeline, see the LUBM benchmark in Pillar 3 · Inference.

See also

• Bulk ingest — the loader family and how to choose.
• Load Turtle from disk — the front door that hands N-Triples to this loader.
• Hexastore + dictionary — the layout the staged loader writes.