Why DuckDB 2.0 is quicker

VARIANT: smaller and sooner than JSON

Everybody loves JSON. VARIANT is now a first-class knowledge kind in DuckDB, and the phrase to recollect is shredding.

Not the guitar type. Shredding in our VARIANT context means: when DuckDB writes a row group to disk, it seems at your JSON column and finds the fields that present up in most rows with the identical form of worth each time.

Here is a typical instance, one occasion out of 5 million:

{"kind": "buy", "consumer": {"id": 42, "nation": "FR", "premium": true},
 "props": {"quantity": 12.5, "foreign money": "EUR", "gadgets": 2}, "tags": ["a", "b"]}

The occasion type is at all times textual content, consumer.id is at all times a quantity, props.quantity is at all times a decimal. Those fields get pulled out into their very own actual columns underneath the hood. The uncommon fields, and the fields which can be a quantity in a single row and textual content within the subsequent, keep collectively in a binary the rest. So the constant a part of your JSON is saved like a traditional desk, and solely the messy half is saved as a blob.

JSON vs VARIANT SELECT depend(*) FROM ev WHERE kind = ‘buy’ AND consumer.nation = ‘FR’; DuckDB 1.5.5 JSON string, parsed at question time payload · saved as textual content {“kind”:”click on”, “consumer”:{“id”:51054,”nation”:”BR”},”quantity”:null} {“kind”:”click on”,“consumer”:{“id”:51054,”nation”:”BR”},”quantity”:null} {“kind”:”buy”, “consumer”:{“id”:42,”nation”:”FR”},”quantity”:12.5} {“kind”:”buy”,“consumer”:{“id”:42,”nation”:”FR”},”quantity”:12.5} {“kind”:”view”, “consumer”:{“id”:11,”nation”:”FR”},”quantity”:null} {“kind”:”view”,“consumer”:{“id”:11,”nation”:”FR”},”quantity”:null} {“kind”:”buy”, “consumer”:{“id”:8890,”nation”:”US”},”quantity”:301.2} {“kind”:”buy”,“consumer”:{“id”:8890,”nation”:”US”},”quantity”:301.2} kind · after parsing nation · after parsing click on BR buy FR view FR buy US characters learn 0 18 32 43 51 57 61 63 64 65 74 90 103 112 119 124 127 129 130 147 160 170 178 183 187 189 191 201 217 230 240 248 253 256 258 259 DuckDB 2.0 alpha VARIANT, shredded at checkpoint payload · saved as VARIANT {“kind”:”click on”, “consumer”:{“id”:51054,”nation”:”BR”},”quantity”:null} {“kind”:”click on”,“consumer”:{“id”:51054,”nation”:”BR”},”quantity”:null} {“kind”:”buy”, “consumer”:{“id”:42,”nation”:”FR”},”quantity”:12.5} {“kind”:”buy”,“consumer”:{“id”:42,”nation”:”FR”},”quantity”:12.5} {“kind”:”view”, “consumer”:{“id”:11,”nation”:”FR”},”quantity”:null} {“kind”:”view”,“consumer”:{“id”:11,”nation”:”FR”},”quantity”:null} {“kind”:”buy”, “consumer”:{“id”:8890,”nation”:”US”},”quantity”:301.2} {“kind”:”buy”,“consumer”:{“id”:8890,”nation”:”US”},”quantity”:301.2} kind consumer.id nation quantity click on 51054 BR null buy 42 FR 12.5 view 11 FR null buy 8890 US 301.2 values learn 0 2 4 6 8

The excellent case is structured logs. degree, service, latency_ms, trace_id are in each line and at all times the identical form of worth, so all of them shred. The odd additional object stays within the the rest, nonetheless queryable, simply slower. The entice: a latency_ms that’s 231 in a single line and "231ms" within the subsequent falls into the rest too. Keep worth varieties constant.

VARIANT shouldn’t be solely about pace. Text is grasping on storage as a lot as on CPU. I took the 5 million occasions and saved the identical knowledge 3 ways:

CREATE TABLE ev AS SELECT json AS payload FROM read_ndjson_objects('occasions.ndjson');

CREATE TABLE ev AS SELECT json::VARIANT AS payload FROM read_ndjson_objects('occasions.ndjson');

CREATE TABLE ev AS SELECT * FROM read_json('occasions.ndjson');

Then three queries on every: a filter on two fields, a sum of a numeric subject grouped by nation, and an inventory lookup.

SELECT depend(*) FROM ev
WHERE payload.kind::VARCHAR = 'buy' AND payload.consumer.nation::VARCHAR = 'FR';


SELECT payload.consumer.nation::VARCHAR AS nation, sum(payload.props.quantity::DOUBLE) AS quantity
FROM ev WHERE payload.kind::VARCHAR = 'buy' GROUP BY ALL ORDER BY 1;


SELECT depend(*) FROM ev WHERE list_contains(payload.tags::VARCHAR[], 'c');

What can we see right here?

• VARIANT is 2.7 occasions smaller than the JSON string, and you’ll see why with pragma_storage_info('ev'): the article is cut up into sub-columns, the occasion type is saved as a dictionary. EXPLAIN exhibits the filter pushed into the scan, so a question on payload.kind reads one sub-column.
• On the queries that contact shredded fields, a filter or a sum on a numeric sub-field, VARIANT is about 6 occasions sooner than parsing the JSON textual content and inside 20 % of the typed columns. Compared to VARIANT in 1.5.5 it’s 78 occasions sooner, as a result of 1.5.5 had the sort however not the shredding.
• The checklist question is the exception. Casting a VARIANT checklist to VARCHAR[] prices two seconds on this alpha, slower than the JSON path. Field entry is the place shredding pays as we speak; lists are usually not there but.

So the golden rule of modeling remains to be legitimate: mannequin what you recognize. The fields each question touches deserve actual columns, and selling one is 2 statements:

ALTER TABLE ev ADD COLUMN kind VARCHAR;
UPDATE ev SET kind = payload.kind::VARCHAR;

TL;DR: in case your occasions share a constant set of fields with constant worth varieties, retailer them as VARIANT relatively than a JSON string. You get a 3rd of the storage and subject queries that run like actual columns. Promote the fields each question touches to actual columns, preserve the lengthy tail within the VARIANT, and keep away from checklist casts in sizzling queries for now.