-
[OPIK-4380] [BE] Add ClickHouse migration for experiment_aggregates and experiment_item_aggregates tables (#5365)
frostbyte_neo 发布于
2026-02-24 17:16:50 +00:00 - [OPIK-4380] [BE] Add ClickHouse migration for experiment_aggregates and experiment_item_aggregates tables
Creates two new ReplicatedReplacingMergeTree tables:
- experiment_aggregates: stores aggregated metrics at the experiment level
(scores, trace count, duration/cost/usage percentiles, feedback score stats) - experiment_item_aggregates: stores aggregated metrics per experiment item
(trace input/output, duration, cost, usage, feedback scores)
Both tables use ZSTD(3) compression on all columns, Delta codec on timestamps,
and include minmax skip indexes on the most common filter columns.- Revision 2: Address PR review comments on migration script
- Add index justification comments before each ADD INDEX
- Rename input_truncated/output_truncated to input_slim/output_slim
- Apply best-practice codecs: Gorilla for Float64, Delta+ZSTD(1) for
timestamps and integer counters, ZSTD(1) for Decimal(38,12) - Use ZSTD(1) compression level throughout
- Revision 3: Update experiment_item_aggregates schema
- Add dataset_id to experiment_aggregates ORDER BY key
- Change duration from Decimal64(9) to Float64
- Reduce input_slim/output_slim compression from ZSTD(3) to ZSTD(1)
- Fix syntax error on input_slim/output_slim codec definitions
- Revision 4: Remove redundant dataset_id skip index
dataset_id is now part of the ORDER BY key so ClickHouse uses the
primary key index for filtering — a separate minmax skip index is
redundant.- Revision 5: Reduce skip index GRANULARITY from 4 to 1
minmax indexes are cheap on both storage and CPU. GRANULARITY 1 tracks
min/max per granule (8,192 rows) instead of per 4 granules (32,768 rows),
enabling finer-grained pruning and better query performance.下载附件