/clickhouse-js-node-rowbinary

Case study: RowBinary vs JSON on a financial ledger (wide ints & decimals)

TL;DR — When every column is wider than a JS number can hold (UInt128, Int64, Decimal128(18), UInt256), RowBinary wins twice over. Stock JSON.parse is not merely slow here — it is silently wrong, rounding every value to a float64. The only correct JSON path quotes the values server-side and re-parses each string into a bigint/decimal pair by hand, which is ~5x slower than the optimized RowBinary reader over a 2.1–2.6x larger wire. RowBinary reads each value exactly, straight off the wire.

This is the workload the SKILL's format-choice guidance calls out explicitly: "RowBinary clearly wins when the result is dominated by wide numerics — Int128/Int256/UInt128/UInt256, Decimal128/Decimal256."

Reproduce: npx vitest bench --run tests/ledger.bench.ts (against a live ClickHouse server). Source: tests/ledger.bench.ts, reader: src/examples/ledger.ts.

The data

A financial ledger — every column exceeds IEEE-754 double's 53-bit exact range:

txn_id   UInt128         -- 16 bytes
account  Int64           --  8 bytes  (values past 2^53)
amount   Decimal128(18)  -- 16 bytes  (~32 significant digits)
balance  Decimal128(18)  -- 16 bytes
fee      Decimal64(4)    --  8 bytes
volume   UInt256         -- 32 bytes

50,000 rows, fixed-width (96 bytes/row), fetched from a live server.

The correctness trap

ClickHouse emits these types as bare, unquoted JSON numbers. So stock JSON.parse parses them as float64 and silently corrupts every one — measured on row 0 of the live result:

No exception, no warning — just wrong numbers. For money and IDs, that is a correctness bug, not a performance footnote.

Making JSON correct costs extra work

The only way to get exact values through JSON is to quote them server-side so they arrive as strings, then re-parse each one:

... SETTINGS output_format_json_quote_64bit_integers = 1,
             output_format_json_quote_decimals = 1

txn_id:  BigInt(r.txn_id),                 // string -> bigint
amount:  parseDecimal(r.amount, 18),       // string -> [unscaled, scale]

That per-field BigInt(...) / decimal parse is work RowBinary doesn't do — it reads the exact bigint directly with two DataView reads — and it lands on top of a larger wire (strings are longer than the binary words).

Wire size (correct paths quote wide values as strings)

Decode throughput (full 50k-row decode; higher = faster)

Node 24 / V8. Your numbers will vary; run npm run bench on your own hardware.

Takeaways

• The fast JSON path is the wrong one. Bare JSON.parse is JSON's quickest option and it is still 2.95x slower than RowBinary — and it silently corrupts every wide value. There is no "fast and correct" JSON here.
• The correct JSON path is ~5x slower. Quote + per-field BigInt/decimal parsing is the price of correctness, on top of a 2.1–2.6x larger wire.
• RowBinary is correct by construction. Each value is composed from 64-bit words read at constant offsets (high word signed for the signed types), yielding an exact bigint or [unscaled, scale] pair — no rounding, no string re-parsing.
• Contrast with the IoT case study: there the numbers fit a float64 and the win was purely throughput (3.5x). Here the values don't fit, so the win is correctness first, throughput second. Match the format to the shape of the data.