Indexing XML Columns in PostgreSQL Without Wrecking Inserts

XML stored in a database column is a document the database does not understand until you tell it where to look. A query that digs a value out of every document with xpath() is a scan of the whole table, and it gets slower as the table grows. The remedy is an index on the thing you extract. It works, it has a price on every write, and there is a variant that is both clearer and no costlier. This post measures all three.

The setup

A table with one xml column and a lookup by a value inside the document:

CREATE TABLE xdocs (id serial PRIMARY KEY, doc xml);
-- each doc looks like:
-- <order id="7" status="paid"><customer>customer-42</customer><total>19.90</total></order>

SELECT count(*) FROM xdocs
WHERE (xpath('/order/customer/text()', doc))[1]::text = 'customer-42';

xpath() returns an array of xml values. The [1] takes the first, and ::text casts it so it can be compared with a string. This is the standard way to compare a value from a document (shredding XML nodes with XMLTABLE shows the alternative for extracting several columns).

No index: a scan

Without an index the planner has no choice:

query no index: count=105 245 ms
Aggregate  (cost=2511.29..2511.30 rows=1 width=8)
  ->  Seq Scan on xdocs  (cost=0.00..2509.92 rows=549 width=0)
        Filter: (((xpath('/order/customer/text()'::text, doc, '{}'::text[]))[1])::text = 'customer-42'::text)

It reads every row and parses every document to evaluate the XPath, 50,000 times, for 245 ms. At 5 million rows, the same query takes about 25 seconds. The Filter line shows the whole expression evaluated per row. Also note that the plan estimates 549 matching rows, and there are 105: without statistics on the extracted value, the planner guesses a default selectivity.

Option 1: an expression index

PostgreSQL has no default B-tree support for the xml type, since XML values have no ordering, so you cannot write CREATE INDEX ON xdocs (doc). You can index an expression that produces an indexable type:

CREATE INDEX xdocs_cust ON xdocs (((xpath('/order/customer/text()', doc))[1]::text));
ANALYZE xdocs;

Rerun the same query:

query with expr index: count=105 1 ms
Aggregate  (cost=282.12..282.13 rows=1 width=8)
  ->  Bitmap Heap Scan on xdocs  (cost=5.03..281.88 rows=96 width=0)
        Recheck Cond: (((xpath('/order/customer/text()'::text, doc, '{}'::text[]))[1])::text = 'customer-42'::text)

The lookup now takes 1 ms and the estimate is close to the real count (96 against 105), because ANALYZE gathered statistics on the indexed expression. The condition must match the index definition exactly: same XPath string, same [1], same cast. A query that writes the XPath with a space or a different quoting, or compares ->> of something else, does not use it and silently goes back to scanning. Copy the expression from one place, or wrap it in an immutable SQL function and index and query the function.

The price: slower writes

An index is work done at write time. Loading the same 50,000 documents with COPY:

load 50,000 docs, no index: 0.21s
load 50,000 docs, with index: 0.58s  (2.8x)

Every insert now evaluates the XPath, parses the document, and updates the B-tree. The load takes 2.8 times as long. For a table that is loaded in bulk and read often, that is a good trade. For a table that takes thousands of inserts per second and is queried rarely, the index is a cost with no reader. Decide by the ratio of writes to reads, and by whether the lookups are frequent enough to justify the maintenance. Each extra expression index adds the same penalty again.

Option 2: a generated column

A cleaner design extracts the value once, on write, into a real column, and indexes that column:

ALTER TABLE xdocs ADD COLUMN customer text
  GENERATED ALWAYS AS (((xpath('/order/customer/text()', doc))[1])::text) STORED;
CREATE INDEX xdocs_cust2 ON xdocs (customer);

load with generated column + btree: 0.58s
query generated col: count=105 1 ms

The numbers are the same as the expression index: 0.58 seconds to load, 1 ms to query. The gain is in readability and safety. Queries say WHERE customer = 'customer-42', so there is no way to mistype the expression, the planner collects ordinary column statistics, and the column is visible in SELECT, in joins and in tools. The cost is storage for the extra column, a few bytes per row, in the table. Generated columns need PostgreSQL 12 or later.

If you need many extracted values, you are building a relational table next to the document. At that point consider storing the document and shredding it to proper tables on load (streaming XML into PostgreSQL without a DOM), keeping the original only if you need it for audit.

Choosing

When every query touches the same two or three fields, the generated column wins. When the set of queried fields changes often, keep the documents and add expression indexes only for the ones that prove hot, which you can see in pg_stat_statements.

Checking the index is used

An index you cannot see being used is a guess. After creating it, run the query under EXPLAIN (ANALYZE, BUFFERS) and read the plan: you want an Index Scan or Bitmap Heap Scan on the index name, not a Seq Scan, and the actual row count close to the estimate. Check again after a few days of production traffic, because a plan that uses the index on a table of 50,000 rows may switch to a scan when statistics say most rows match.

Then watch the other side. pg_stat_user_indexes shows how often each index was scanned. An expression index with a scan count of zero after a month is write cost buying nothing, and dropping it speeds up every insert. For a table that grows quickly, also keep an eye on index size next to table size. An index on a text expression can be a large fraction of the table, and bloat from updates adds to it.

One more PostgreSQL detail: the expression in an index must be immutable. xpath() qualifies, and so does a cast to text. A function that depends on the session, such as one using now() or a setting, is rejected when you create the index.

Related reading

This post belongs to parsing big XML files fast. The basics of the column type are in storing XML in PostgreSQL, and the cost question in indexing XML columns and what it costs. For whether to keep XML in the database at all, see should you store XML in a database column. The functions are documented in the PostgreSQL manual on XML functions and expression indexes. Test an XPath in the XPath tester.

When an XML column index is the wrong fix

You query many different paths. Indexing each one multiplies the write cost. Shred the document into columns or use a full-text approach for free-form search.

The table is write-heavy. If you load far more than you read, skip the index and accept the occasional scan, or build it on a read replica.

The document is large. Parsing a megabyte per row at write time for an index is very expensive. Extract the few fields at the application layer and store them next to the document.

You need range or text search inside values. A B-tree on the extracted text supports equality and prefix ranges. Search inside text needs a text-search index on the extracted text, not on the XML.