Problem
Currently, when evaluating metric ratio charts (e.g. dividing the sum of one metric by the sum of another), the ClickHouse backend executes two separate queries concurrently. The results are then loaded entirely into Node.js memory, zipped by their respective series, and the mathematical ratio calculation is executed sequentially in JavaScript.
For heavy, high-cardinality charts such as time series distributions across thousands of service.names this leads to:
Significant serialization overhead to and from ClickHouse.
An unnecessarily bloated memory footprint in the Node backend due to retaining massive parallel arrays.
Higher latency and CPU cycle waste for mathematical zipping that the database is better optimized for.
Proposed Solution
Refactor @hyperdx/common-utils and ClickHouse metric compilation to execute the ratio math strictly inside the database.
Utilize ClickHouse CTEs (Common Table Expressions) to define both metrics (q0 and q1).
Combine them with a fast ANY LEFT JOIN using __hdx_time_bucket and whatever groupBy keys are requested.
Perform the division directly in the SQL select projection.
This removes the memory overhead entirely and drops end-to-end latency for metric ratios significantly.
Backward compatibility should be maintained for the final result shape (__hdx_time_bucket and correct alias strings like avg(metric.alpha)/avg(metric.beta) returned in JSON payload).
Problem
Currently, when evaluating metric ratio charts (e.g. dividing the sum of one metric by the sum of another), the ClickHouse backend executes two separate queries concurrently. The results are then loaded entirely into Node.js memory, zipped by their respective series, and the mathematical ratio calculation is executed sequentially in JavaScript.
For heavy, high-cardinality charts such as time series distributions across thousands of service.names this leads to:
Significant serialization overhead to and from ClickHouse.
An unnecessarily bloated memory footprint in the Node backend due to retaining massive parallel arrays.
Higher latency and CPU cycle waste for mathematical zipping that the database is better optimized for.
Proposed Solution
Refactor @hyperdx/common-utils and ClickHouse metric compilation to execute the ratio math strictly inside the database.
Utilize ClickHouse CTEs (Common Table Expressions) to define both metrics (q0 and q1).
Combine them with a fast ANY LEFT JOIN using __hdx_time_bucket and whatever groupBy keys are requested.
Perform the division directly in the SQL select projection.
This removes the memory overhead entirely and drops end-to-end latency for metric ratios significantly.
Backward compatibility should be maintained for the final result shape (__hdx_time_bucket and correct alias strings like avg(metric.alpha)/avg(metric.beta) returned in JSON payload).