Setting the file. One moment.
Ledger Bench · Clickhouse Js Node Rowbinary · ClickHouse/agent-skills · Skills Docs
Back to the top of the page
135 of 192 tests/ ledger.bench.ts
TypeScript · 201 lines · 8 KB
9 } from "../src/examples/ledger.js" ;
10
11 /**
12 * Benchmark + correctness proof: RowBinary vs JSON for a financial ledger whose
13 * every column is WIDER than a JS `number` can hold — `UInt128`, `Int64`,
14 * `Decimal128(18)`, `UInt256`. The SKILL says RowBinary "clearly wins" on wide
15 * numerics; here it wins twice over, because for this shape JSON isn't just
16 * slower, it's WRONG:
17 *
18 * - ClickHouse emits these as BARE JSON numbers, so stock `JSON.parse` rounds
19 * every one to a float64 — silent, lossy corruption (demonstrated below).
20 * - The only correct JSON path quotes the values server-side
21 * (`output_format_json_quote_64bit_integers` + `..._quote_decimals`) and
22 * re-parses each string into a `bigint`/decimal pair by hand — extra work on
23 * top of a larger wire.
24 *
25 * RowBinary reads each value as an exact `bigint` straight off the wire.
26 */
27 const N = 50_000 ;
28
29 const SELECT =
30 `SELECT ` +
31 // UInt128 near the top of the range, varied per row.
32 `toUInt128('340282366920938463463374607431768200000') + number AS txn_id, ` +
33 // Int64 starting at 2^53 + 1 — already past exact-double range on row 0.
34 `toInt64(9007199254740993) + number AS account, ` +
35 // Decimal128(18): ~14 integer digits + 18 fractional = 32 significant digits.
36 `CAST(concat(toString(toUInt64(98765432109876 + number)), '.123456789012345678') AS Decimal128(18)) AS amount, ` +
37 `CAST(concat(toString(toUInt64(12345678901234 + number)), '.111111111111111111') AS Decimal128(18)) AS balance, ` +
38 `CAST(concat(toString(toUInt64(1000 + number % 9000)), '.5678') AS Decimal64(4)) AS fee, ` +
39 // UInt256 near the top of the range.
40 `toUInt256('115792089237316195423570985008687907853269984665640564039457000000000') + number AS volume ` +
41 `FROM numbers(${ N })` ;
42
43 const RB_BUF = await query ( `${ SELECT } FORMAT RowBinary` );
44 // Naive JSON: bare numbers. Fast to parse, but every wide value is corrupted.
45 const JSON_BARE_BUF = await query ( `${ SELECT } FORMAT JSONEachRow` );
46 // Correct JSON: quote wide ints AND decimals so values arrive as exact strings.
47 const QUOTE =
48 "SETTINGS output_format_json_quote_64bit_integers = 1, output_format_json_quote_decimals = 1" ;
49 const JSON_STR_BUF = await query ( `${ SELECT } ${ QUOTE } FORMAT JSONEachRow` );
50 const JSON_COMPACT_STR_BUF = await query (
51 `${ SELECT } ${ QUOTE } FORMAT JSONCompactEachRow` ,
52 );
53
54 // --- decoders ---------------------------------------------------------------
55
56 function decodeRowBinary ( read : Reader < LedgerRow >) : LedgerRow [] {
57 const s = new Cursor ( RB_BUF );
58 const out : LedgerRow [] = [];
59 while (s.pos < s.buf. length ) out. push ( read (s));
60 return out;
61 }
62
63 function jsonArray ( buf : Buffer ) : unknown [] {
64 return JSON . parse (
65 `[${ buf . toString ( "utf8" ). trimEnd (). replaceAll ( " \n " , "," ) }]` ,
66 );
67 }
68
69 // Parse a fixed-point decimal string ("123.456") into the exact [unscaled, scale]
70 // pair RowBinary returns — the per-field work JSON must do to stay lossless.
71 function parseDecimal ( str : string , scale : number ) : DecimalValue {
72 const neg = str. charCodeAt ( 0 ) === 45 ; // '-'
73 const s = neg ? str. slice ( 1 ) : str;
74 const dot = s. indexOf ( "." );
75 let digits : string ;
76 let frac : number ;
77 if (dot === - 1 ) {
78 digits = s;
79 frac = 0 ;
80 } else {
81 digits = s. slice ( 0 , dot) + s. slice (dot + 1 );
82 frac = s. length - dot - 1 ;
83 }
84 let unscaled = BigInt (digits);
85 if (frac < scale) unscaled *= 10 n ** BigInt (scale - frac);
86 else if (frac > scale) unscaled /= 10 n ** BigInt (frac - scale);
87 return [neg ? - unscaled : unscaled, scale];
88 }
89
90 // Correct decode of the quoted JSON: turn the string fields back into the exact
91 // bigint / decimal-pair shape RowBinary produces.
92 function decodeJsonObjectsCorrect ( buf : Buffer ) : LedgerRow [] {
93 const rows = jsonArray (buf) as Record < string , string >[];
94 const out : LedgerRow [] = new Array (rows. length );
95 for ( let i = 0 ; i < rows. length ; i ++ ) {
96 const r = rows[i] ! ;
97 out[i] = {
98 txn_id: BigInt (r.txn_id ! ),
99 account: BigInt (r.account ! ),
100 amount: parseDecimal (r.amount ! , 18 ),
101 balance: parseDecimal (r.balance ! , 18 ),
102 fee: parseDecimal (r.fee ! , 4 ),
103 volume: BigInt (r.volume ! ),
104 };
105 }
106 return out;
107 }
108
109 function decodeJsonCompactCorrect ( buf : Buffer ) : LedgerRow [] {
110 const rows = jsonArray (buf) as string [][];
111 const out : LedgerRow [] = new Array (rows. length );
112 for ( let i = 0 ; i < rows. length ; i ++ ) {
113 const r = rows[i] ! ;
114 out[i] = {
115 txn_id: BigInt (r[ 0 ] ! ),
116 account: BigInt (r[ 1 ] ! ),
117 amount: parseDecimal (r[ 2 ] ! , 18 ),
118 balance: parseDecimal (r[ 3 ] ! , 18 ),
119 fee: parseDecimal (r[ 4 ] ! , 4 ),
120 volume: BigInt (r[ 5 ] ! ),
121 };
122 }
123 return out;
124 }
125
126 // --- correctness cross-check + the corruption demonstration (runs at load) ---
127
128 const eqDec = ( a : DecimalValue , b : DecimalValue ) =>
129 a[ 0 ] === b[ 0 ] && a[ 1 ] === b[ 1 ];
130 const eqRow = ( a : LedgerRow , b : LedgerRow ) =>
131 a.txn_id === b.txn_id &&
132 a.account === b.account &&
133 eqDec (a.amount, b.amount) &&
134 eqDec (a.balance, b.balance) &&
135 eqDec (a.fee, b.fee) &&
136 a.volume === b.volume;
137
138 {
139 const rb = decodeRowBinary (readLedgerRowFast);
140 const api = decodeRowBinary (readLedgerRow);
141 const jObj = decodeJsonObjectsCorrect ( JSON_STR_BUF );
142 const jCompact = decodeJsonCompactCorrect ( JSON_COMPACT_STR_BUF );
143 const bare = jsonArray ( JSON_BARE_BUF ) as Record < string , number >[]; // the WRONG path
144
145 if (rb. length !== N )
146 throw new Error ( `RowBinary: ${ rb . length } rows, expected ${ N }` );
147 for ( let i = 0 ; i < N ; i ++ ) {
148 if ( ! eqRow (rb[i] ! , api[i] ! ))
149 throw new Error ( `ledger: API vs fast mismatch @${ i }` );
150 if ( ! eqRow (rb[i] ! , jObj[i] ! ))
151 throw new Error ( `ledger: RowBinary vs quoted-JSON mismatch @${ i }` );
152 if ( ! eqRow (rb[i] ! , jCompact[i] ! ))
153 throw new Error ( `ledger: RowBinary vs quoted-compact mismatch @${ i }` );
154 }
155
156 // The corruption: stock JSON.parse over the BARE numbers disagrees with the
157 // exact RowBinary value on every wide column of row 0.
158 const r0 = rb[ 0 ] ! ;
159 const b0 = bare[ 0 ] ! ;
160 console. log (
161 ` \n Financial ledger — ${ N . toLocaleString () } rows. Stock JSON.parse on bare numbers (row 0): \n ` +
162 ` txn_id RowBinary ${ r0 . txn_id } \n ` +
163 ` JSON.parse ${ BigInt ( Math . trunc ( b0 . txn_id as unknown as number )). toString () } ${ BigInt ( Math . trunc ( b0 . txn_id as unknown as number )) === r0 . txn_id ? "ok" : "✗ CORRUPTED"} \n ` +
164 ` account RowBinary ${ r0 . account } \n ` +
165 ` JSON.parse ${ b0 . account } ${ BigInt ( b0 . account ! ) === r0 . account ? "ok" : "✗ CORRUPTED"} \n ` +
166 ` amount RowBinary ${ formatDecimal ( r0 . amount ) } \n ` +
167 ` JSON.parse ${ b0 . amount } ✗ CORRUPTED (only ~16 sig digits survive) \n ` ,
168 );
169
170 const mb = ( b : Buffer ) => (b. length / 1e6 ). toFixed ( 2 );
171 const x = ( b : Buffer ) => `${ ( b . length / RB_BUF . length ). toFixed ( 1 ) }x` ;
172 console. log (
173 ` Wire size (correct paths quote wide values as strings): \n ` +
174 ` RowBinary ${ mb ( RB_BUF ) } MB \n ` +
175 ` JSONCompactEachRow quoted ${ mb ( JSON_COMPACT_STR_BUF ) } MB ${ x ( JSON_COMPACT_STR_BUF ) } \n ` +
176 ` JSONEachRow quoted ${ mb ( JSON_STR_BUF ) } MB ${ x ( JSON_STR_BUF ) } \n ` ,
177 );
178 }
179
180 // --- benchmarks -------------------------------------------------------------
181
182 describe ( "Financial ledger: RowBinary vs JSON decode throughput" , () => {
183 bench ( "RowBinary — optimized (monomorphized)" , () => {
184 decodeRowBinary (readLedgerRowFast);
185 });
186 bench ( "RowBinary — API (combinators)" , () => {
187 decodeRowBinary (readLedgerRow);
188 });
189 bench (
190 "JSONCompactEachRow quoted — JSON.parse + BigInt/decimal (correct)" ,
191 () => {
192 decodeJsonCompactCorrect ( JSON_COMPACT_STR_BUF );
193 },
194 );
195 bench ( "JSONEachRow quoted — JSON.parse + BigInt/decimal (correct)" , () => {
196 decodeJsonObjectsCorrect ( JSON_STR_BUF );
197 });
198 bench ( "JSONEachRow bare — JSON.parse only (FAST BUT WRONG)" , () => {
199 jsonArray ( JSON_BARE_BUF );
200 });
201 });