Streaming XML Into PostgreSQL Without a DOM Using COPY

The standard advice for loading a large XML file into a database has two halves, and both are correct: do not build the tree, and do not insert row by row. Together they turn a job that takes minutes and gigabytes into one that takes seconds and a few dozen megabytes. This post shows the combination in about thirty lines, with the numbers, and explains the single trap I fell into while measuring it.

The target

Orders have a scalar part and a repeating part, so two tables:

CREATE TABLE xo_orders (id int PRIMARY KEY, status text, customer text, placed date);
CREATE TABLE xo_lines  (order_id int, sku text, qty int, price numeric(10,2));

Every order element becomes one row in xo_orders, and every line inside it becomes a row in xo_lines. This is the usual shape of a flattening (flattening repeating XML elements into rows). The loader's job is to walk the file once and write both tables as the records go by.

The loader

import sys, time
import psycopg
from lxml import etree

path = sys.argv[1]
DSN = "host=pg dbname=jl user=postgres password=secret"
conn, conn2 = psycopg.connect(DSN), psycopg.connect(DSN)
cur = conn.cursor()

with cur.copy("COPY xo_orders (id,status,customer,placed) FROM STDIN") as co, \
     conn2.cursor().copy("COPY xo_lines (order_id,sku,qty,price) FROM STDIN") as cl:
    for _, el in etree.iterparse(path, events=("end",), tag="order"):
        oid = int(el.get("id"))
        co.write_row((oid, el.get("status"), el.findtext("customer"), el.findtext("placed")))
        for line in el.iter("line"):
            cl.write_row((oid, line.get("sku"), int(line.get("qty")), line.findtext("price")))
        el.clear()
        while el.getprevious() is not None:
            del el.getparent()[0]
conn.commit()
conn2.commit()

Four parts do the work. iterparse with tag="order" yields each order once its closing tag has been read, so the whole record is available and nothing else is held. cur.copy(...) opens PostgreSQL's COPY ... FROM STDIN protocol, and write_row sends one row into it, with psycopg converting Python values to text. The el.clear() and sibling deletion keep the tree small. And the commit at the end makes both tables appear together or not at all, which is the behavior you want from a load.

On the 400,000-order file:

orders=400000 lines=1199909 sum=3001978835.00 time=6.5s peak_rss=44MB

Both tables were filled in 6.5 seconds with 44 MB of process memory. The sum is computed in SQL (sum(qty*price)) after the load and matches the total computed in Python while benchmarking, which is the cheapest end-to-end check that no record was lost or loaded twice.

What it replaces

The naive version, which is what many first attempts look like, parses the whole tree and inserts each row with its own INSERT:

root = etree.parse(path).getroot()
for o in root:
    cur.execute("INSERT INTO xo_orders VALUES (%s,%s,%s,%s)", (...))
    for line in o.iter("line"):
        cur.execute("INSERT INTO xo_lines VALUES (%s,%s,%s,%s)", (...))
conn.commit()

I ran that on the 100,000-order file (32 MB, a quarter of the data) because the large one would have taken too long:

naive DOM + row INSERT: time=38.5s peak_rss=519MB

For a quarter of the rows, it took six times as long and 12 times the memory. Scaled to the full file, the naive loader would need about 150 seconds and 2 GB. Two costs are stacked. The DOM holds the tree (519 MB at this size). And each execute is a network round trip to the server, with parsing and planning of the statement, around 300,000 times. COPY removes the second cost by streaming rows in one command, and iterparse removes the first.

The trap: two COPYs on one connection

My first version of this script used cur.copy for the orders and cur.connection.cursor().copy for the lines, on the same connection. It hung. A PostgreSQL connection runs one command at a time, and an open COPY occupies it until it finishes, so the second COPY waits for the first, which waits for the loop, which waits for the second. The script never completed, and nothing reported an error.

There are three ways out:

• Two connections, as in the loader above. Simple, and each COPY streams independently. The cost is that the two tables are committed in separate transactions, so a failure between the two commits can leave one table loaded. Commit them as close together as possible, load into staging tables and swap, or accept that a failed load is rerun from scratch into truncated tables.

• One COPY into a staging table, with a row for each order line carrying the order's fields, then split with SQL. Less code in Python, more in SQL, and the staging table is bigger.

• Buffer the child rows in memory per batch of orders, and flush the parent COPY, then the child COPY, per batch. This keeps one connection and one transaction at the price of batching code.

For a one-off load the two-connection version is the simplest. For a repeating pipeline, load into new tables inside one transaction and rename, or use a staging schema.

Hardening the loader

A bad value stops a COPY completely. One price of N/A in a numeric column aborts the statement with an error and rolls back everything it loaded. That is the right behavior for clean data and a bad one for a feed you do not control. Options:

Load as text, cast later. Make the staging columns text, load everything, then insert into the typed table with a query that handles failures: INSERT ... SELECT ... WHERE price ~ '^[0-9]+(\.[0-9]+)?$', with a second query that copies the rejects into a table for review. PostgreSQL 17 and later can also skip and log bad rows in COPY itself with ON_ERROR ignore, which is worth a look if you only need to survive rejects.

Validate in the loop. Convert and check each value in Python and write rejects to a file. This is slower per row and gives better error messages, with the record id and the XML element.

Check counts. Compare the number of records in the file with the number in the table. A script that streams cannot count first without reading twice, but grep -c '<order ' on the file is fast enough.

Also decide how to handle an interrupted run. With a single transaction the partial load rolls back. With two, use TRUNCATE at the start so the rerun begins from empty, as the benchmark script does.

Related reading

This post is part of parsing big XML files fast. The same pattern for product feeds is in loading a product feed XML into PostgreSQL, and the generic version in loading large XML files into a database. The COPY protocol is documented in the PostgreSQL manual and its psycopg API in the psycopg 3 COPY guide. Look at a sample record with the XML formatter.

When streaming into PostgreSQL is not the right loader

The XML lives in the database as a document. If you want to query the XML in place, store it in an xml column and use xpath() or XMLTABLE (shredding XML nodes with XMLTABLE and storing XML in PostgreSQL). That is a different design.

The file is small. Under a few megabytes, a DOM and executemany is shorter code and the speed does not matter.

You need transformation the database does better. If most of the work is joins and aggregation, load raw text columns first and do the rest in SQL.

The target is not PostgreSQL. MySQL has LOAD DATA, SQLite has transactions around executemany, and DuckDB reads files directly. The streaming half still applies; the sink changes.