Materialized view support for Metric sources (gauges)

What are you trying to do?

Make dashboard metric charts over long ranges fast. Materialized views (#1507) work well for Log and Trace sources: on our deployment (@hyperdx/app 2.42.0), a per-minute log rollup answered 24 h count-by-service charts and the search histogram in 4–16 ms instead of 1.9 s, with exact counts.

Metric sources can't use them. materializedViews is defined only for the log and trace kinds (packages/api/src/models/source.ts), so every metric chart scans the raw otel_metrics_* tables. Metric charts are now the slowest part of our dashboards, at about 4.7 B metric rows per day.

What would you like to see?

Materialized view support for Metric sources, starting with gauges. These are the simple case, because no per-series state is involved:

A Metric source can register views for its gauge table: target table, timestamp column, granularity, dimension columns, and aggregated columns (avg / min / max / last_value as -State / SimpleAggregateFunction columns, like log MVs).
A gauge chart is routed to a view when the metric name, aggregation, granularity, and group-by / filter attributes fit. Otherwise it falls back, as today.
Attribute dimensions: let a view expose Attributes['k'] / ResourceAttributes['k'] as named columns, the way filters are already mapped to __otel_materialized_* columns for logs.

Counters (Sum) and histograms are cumulative and need per-series deltas, so they are filed separately (#3483).

Alternatives you've considered

Raw-SQL tiles pointing at our own rollup tables. They work, but every panel has to be rewritten by hand and loses the builder (filters, group-by, alerts).
Materialized columns for hot attribute keys. They help filters, but each series is still keyed on cityHash64(ScopeAttributes, ResourceAttributes, Attributes), which reads the full Maps.

Anything else?

Happy to test a branch on our deployment: ClickStack with a self-hosted ClickHouse cluster.

@pulpdrew

Can I take on this issue? @pulpdrew @dhable

@Shiva953

Hey @Shiva953, thanks for reaching out! You're welcome to pick this up, but perhaps it would be best to discuss your implementation plan here first so that you and reviewers are on the same page before a PR is opened.

Specifically from a technical standpoint, I'd like to agree upon the following first:

What is the general shape of an MV rolling up gauge metrics? Is the proposal just to pre-aggregating each series by time, or are also pre-aggregating across series?
How will gauge queries be adjusted when MVs are present? How are the semantics of each aggregation preserved (particularly when
How will isDelta be handled?
What is the test / validation plan?

And procedurally, would we be able to break the change up across a few smaller PRs (perhaps stacked on each other) to aid reviewers?

Starting with gauges. These are the simple case, because no per-series state is involved

Just a comment here. As an example, I think a sum of a memory gauge across pods means "last value per pod, then sum". An MV that pre-sums Value across pods (series) per bucket could counts every scrape in the bucket, giving the wrong answer. So we'd likely need to maintain series information in the MV, even if the query itself doesn't need to compute deltas. I suspect deltas may also require per-series state.

@bcanvural

Thanks for laying these out. Here's the plan:

1. MV shape

Per series, per time bucket, never across series. The gauge path already works in two steps: Bucketed reduces each series (AttributesHash) to one LastValue per bucket, then the outer select applies the aggFn across series. The MV keeps that series grain and leaves the cross-series step at query time, so your example still takes the last value per pod first, then sums.

The target is keyed on the same cityHash64(ScopeAttributes, ResourceAttributes, Attributes) the query uses:

CREATE TABLE otel_metrics_gauge_1m
(
    MetricName LowCardinality(String),
    BucketStart DateTime,
    AttributesHash UInt64,
    ServiceName LowCardinality(String),
    ResourceAttributes Map(LowCardinality(String), String),
    ScopeAttributes Map(LowCardinality(String), String),
    Attributes Map(LowCardinality(String), String),
    LastValue AggregateFunction(argMax, Float64, DateTime64(9)),
    FirstValue AggregateFunction(argMin, Float64, DateTime64(9)),
    MinTime SimpleAggregateFunction(min, DateTime64(9)),
    MaxTime SimpleAggregateFunction(max, DateTime64(9))
)
ENGINE = AggregatingMergeTree
ORDER BY (MetricName, ServiceName, AttributesHash, BucketStart);

CREATE MATERIALIZED VIEW otel_metrics_gauge_1m_mv TO otel_metrics_gauge_1m AS
SELECT
    MetricName,
    toStartOfMinute(TimeUnix) AS BucketStart,
    cityHash64(ScopeAttributes, ResourceAttributes, Attributes) AS AttributesHash,
    any(ServiceName) AS ServiceName,
    any(ResourceAttributes) AS ResourceAttributes,
    any(ScopeAttributes) AS ScopeAttributes,
    any(Attributes) AS Attributes,
    argMaxState(Value, TimeUnix) AS LastValue,
    argMinState(Value, TimeUnix) AS FirstValue,
    min(TimeUnix) AS MinTime,
    max(TimeUnix) AS MaxTime
FROM otel_metrics_gauge
GROUP BY MetricName, BucketStart, AttributesHash;

The maps are stored as-is next to the hash. They're constant within a series, so any() and merges are safe, and Attributes['k'] filters and group-bys work without expression rewriting.
BucketStart gets its own name so it can't be confused with TimeUnix in the states or in user filters.
The savings come from fewer rows (points per series per bucket collapse to one) and a precomputed hash, which is where most of the reporter's cost was.
Trimming maps to named dimension columns is deferred. A trimmed map silently returns '' for a dropped key, so it needs a coverage check on referenced keys first.

Config goes in a new metricMaterializedViews array, separate from the log/trace materializedViews, so nothing that reads the log MV shape picks it up by accident. Fields: metricType (gauge for now, leaves room for #3483), databaseName, tableName, timestampColumn, minGranularity, minDate, attributesHashColumn, lastValueColumn, plus optional firstValueColumn, minTimeColumn and maxTimeColumn for delta.

2. Query changes and semantics

Only Source and Bucketed change. Source reads the MV with the same WHERE (rendered against the MV), and Bucketed groups by AttributesHash and __hdx_time_bucket2 with argMaxMerge(LastValue) AS LastValue. It yields the same rows and columns as the raw Bucketed, so the outer select is untouched and sum, avg, min, max, count and quantile behave the same. The two-CTE split stays for the same reason as the raw path: with any(Attributes) AS Attributes, ClickHouse resolves Attributes in WHERE to the alias and fails with an aggregate in WHERE.

Coarser chart granularity: argMax states merge across MV buckets, so each chart bucket still gets the latest point per series.
Routing conditions: the effective granularity must be a multiple of minGranularity (resolved like timeBucketExpr resolves auto, including the source's minGranularitySeconds), and minDate must cover the range. The range is aligned and made end-exclusive with getAlignedDateRange, as the log MVs do.
Validity: candidates go through testChartConfigValidity and the cheapest valid MV wins. Anything referencing a column the MV lacks (Value, ScopeName, computed columns) fails the EXPLAIN and falls back to raw. Bucketed projects only the GAUGE_BUCKETED_COLUMNS that exist on the MV, so a view without ScopeName still serves charts that don't use it.
v1 scope: single gauge selects without formulas. Multi-series and formula charts stay on raw.

One caveat: raw uses last_value(Value), which depends on read order, while the MV uses argMax by TimeUnix. They match whenever raw is deterministic, and the MV returns the true latest point when it isn't. I won't touch the raw path.

3. isDelta

renderDeltaExpression is per series per bucket: (argMax(Value, t) - argMin(Value, t)) * interval / date_diff(min t, max t). All four parts are mergeable states, so with FirstValue, MinTime and MaxTime the MV path computes the same expression (extrapolation included) from argMaxMerge, argMinMerge, min and max. Delta charts route only to MVs with all three columns, otherwise they fall back. So yes, deltas need per-series state, and the series grain covers it.

4. Tests

Unit: rendered MV SQL in renderChartConfig.test.ts; routing vs fallback in materializedViews.test.ts (non-multiple granularity, minDate, delta without extra columns, multi-series, formulas).
Integration (gauge section of api/src/clickhouse/__tests__/renderChartConfig.int.test.ts): create the MV over the fixture table, insert gaugePointsA/gaugePointsB, and assert raw and MV results are equal for every existing gauge case (max/avg/sum, where, group-by, delta, delta + group-by, dotted materialized column). New cases: coarser chart granularity than the MV, multiple points per series per bucket, series starting mid-range, and a filter on a missing column to check fallback.
Existing gauge tests stay as-is since raw doesn't change.
@bcanvural offered to test a branch on their cluster for real rows-read and latency numbers at 4.7B rows/day.

5. PR stack

Schema: MV config schema plus metricMaterializedViews on MetricSourceSchema and the Mongoose metric discriminator. No behavior change.
Query: MV branch in the gauge path of translateMetricChartConfig and a metric optimizer, with the tests above.
Wiring: useMVOptimizationExplanation and checkAlerts for metric sources, plus the existing optimization indicator.
UI: source form for configuring metric MVs, with screenshots.

Trimmed dimension columns and counters/histograms (#3483) come after.

Does this shape work? If so I'll start with the schema PR.

@SpencerTorres

Thanks, that largely makes sense. A few more thoughts:

Have you tried the proposed queries and schema on a large data set to confirm that queries are as fast as expected and what the write / storage overhead is? With the requirement that all of the attributes are written to the MV and no cross-series pre-aggregation, there's reason to believe that MVs could be significantly less effective and more expensive for metrics than logs/traces.
Potentially related to (1), for transparency, we (mostly @SpencerTorres) have explored some alternative metrics schemas internally that utilize a separate table/MV to store attributes -> series hash mappings, which significantly improved performance by (among other things) eliminating the need to read attributes maps from every row. I suspect that path might have a greater impact on performance than this time-only rollups approach, and would generalize to other metrics data types, but the approaches are likely complementary. We haven't yet prioritized that work, but it may be coming in the future, if that changes anything here for you.
Could we specify in the plan that the open api specs are updated after the UI so that the spec doesn't expose metrics MVs until the feature is complete?

@pulpdrew

Thanks @pulpdrew, good correction: a gauge rollup has to keep each series separate too. "Sum of pod memory" is the last value per pod, then the sum. My "no per-series state" was too loose.

@Shiva953 the plan looks right to us. Three requests that would make the biggest difference on our side, plus numbers from our deployment (~4.7 B metric rows/day):

Gauge table: ~265k series, ~6.7 points per series per minute, so an MV with one row per series per minute collapses rows ~6.7×.
ResourceAttributes is ~82% of the gauge table's compressed bytes.
Gauge charts were ~2/3 of our HyperDX metric queries last week (p95 9.3 s); sum charts were slower (p95 12.4 s).

1. Carry materialized columns through the MV path. On the raw path, Source/Bucketed already project the table's materialized columns (__otel_materialized_k8s.* etc.), and filters on ResourceAttributes['k8s.namespace.name'] get rewritten to them. Could the MV path keep doing that for the materialized columns the MV table defines, rather than only the fixed bucketed columns? Without it, a namespace filter on the MV still reads the full ResourceAttributes map, which is most of the bytes.

2. Several MVs per source, at different granularities. With "cheapest valid MV wins", a 1-minute and a 1-hour MV can coexist. For day- and week-range panels, an MV with one row per series per hour collapses ~400×. A test case where both exist and the coarser one is picked would lock that in.

3. A schema shape that also fits cumulative sums (#3483). The same shape, one row per series per bucket with LastValue, looks enough for monotonic sums. The rate per bucket becomes greatest(LastValue - lagInFrame(LastValue), 0) over the MV rows instead of the raw points. The one difference from raw is a counter reset inside a bucket, which seems acceptable for charts. If the config isn't gauge-only (metricType as you planned), #3483 could reuse it. Sum charts are our slowest panels, so this matters most to us.

Happy to test each PR in the stack on our deployment and report rows read and latency, raw vs MV.

@pulpdrew

Thanks @pulpdrew, fair points.

Not yet. With every series and its attributes kept, the gain is capped by points per series per bucket (~6.7x fewer rows for @bcanvural at 1 minute, a few hundred times at 1 hour), and the extra storage should be about the raw size divided by that factor. I'd measure before opening any PR, on a synthetic data set shaped like theirs (~265k series, ~6.7 points per series per minute, similar map sizes), comparing raw vs a 1m and a 1h MV on a few typical charts (sum by namespace over 24h and 7d, avg with a filter, delta). I'd report rows/bytes read and latency per query, compressed size per table from system.parts, and insert throughput with and without the MV. @bcanvural can then confirm on their cluster. If it isn't worth it, I'm happy to drop it.
Thanks for sharing. It does sound complementary. Is that the work behind the seriesTable option on metric sources? If it keys on the same cityHash64(ScopeAttributes, ResourceAttributes, Attributes), the MV could later drop the maps and keep only the hash, which removes most of the bytes, so I'd keep AttributesHash as the series key to leave that open. If you'd rather this wait for that schema, that's fine too.
Yes, the OpenAPI spec (plus the external API and MCP source schemas) goes in the last PR, after the UI.

@bcanvural, on your points:

Materialized columns. Yes. The WHERE is rendered against the MV table and its MATERIALIZED/ALIAS columns go through getComputedMetricColumns like the raw path, so if the MV defines __otel_materialized_k8s.namespace.name, a ResourceAttributes['k8s.namespace.name'] filter should be rewritten to it. I'll confirm in the benchmark that it doesn't read the map.
Several MVs. Already covered by the array plus lowest row estimate. I'll add a test with 1m and 1h MVs where a week range picks the hourly one and a short range the minute one.
Sums. metricType stays in the config so Materialized views for cumulative metrics (counter increase/rate, histograms) #3483 can reuse it. Delta temporality points (AggregationTemporality = 1) would also need a sum column, since they add up within a bucket. Sums would be their own PRs after gauges.

Updated order:

Benchmark, numbers posted here (no PR)
Schema
Gauge query, with materialized columns and the multi MV test
Chart and alert wiring
Source form UI
OpenAPI spec, external API and MCP schemas

Is that the work behind the seriesTable option on metric sources?

That's right. It's not yet certain whether we'll go in that direction, or with the timeseries engine, so if this approach yields good results then if could be worth implementing.

@pulpdrew

@pulpdrew Thanks, that's helpful. I'll do the benchmark first and post the numbers here before opening any PR, so the decision can be based on data.

Is there a data generator or dataset you already use for metrics benchmarks? Otherwise I'll script one shaped like @bcanvural's numbers, or reuse the synthetic telemetry in hdx-eval if that fits.
Would it help if I also measured a hash-only variant, where the MV keeps just AttributesHash and attributes come from a series table? That would give a rough idea of how the two approaches compare and combine.

Once the numbers are in, we can pick whichever variant holds up best and I'll adjust the plan around it.

@SpencerTorres

I'll do the benchmark first and post the numbers here before opening any PR, so the decision can be based on data.

Perfect, thank you! Please consider impact on both read and ingest.

Is there a data generator or dataset you already use for metrics benchmarks?

We have been using ClickCannon for load testing, it looks like there is metrics generation functionality there: https://github.com/ClickHouse/ClickCannon#metric-generator-otlp-metrics

Would it help if I also measured a hash-only variant, where the MV keeps just AttributesHash and attributes come from a series table? That would give a rough idea of how the two approaches compare and combine.

If the proposed rollup approach yields significant improvements, then that's probably enough to know for now. @SpencerTorres has done significant benchmarking on the series table approach and could maybe comment here on the approx. level of improvement from that approach.

@pulpdrew

@pulpdrew here are the benchmark numbers:

tldr; reads get much faster and the results are exact: 5.5–23x on 24h gauge charts with a 1m rollup, and 58x with a 1h rollup at the bucket size a 7d chart uses. Ingest is the problem. At the collector's default batch size, each rollup roughly doubles ingest CPU. Most of that comes from copying the attribute maps into the rollup, so a hash-only rollup paired with the series table looks like the better long-term shape.

Setup

One machine: ClickHouse 26.8, the ClickStack collector image (standalone, HyperDX seed schema) and ClickCannon metric_gen over OTLP. 12 cores shared by all three, 8 GB Docker VM.
Gauges shaped like @bcanvural's data at 1/10 scale: 26.5k series, 9s interval (6.7 points per series per minute), 12 resource attributes, 2–4 point attributes. 25h loaded, 264M rows.
Rollups: AggregatingMergeTree, one row per series per bucket, keyed by cityHash64(ScopeAttributes, ResourceAttributes, Attributes). Each row stores argMax/argMin states, min/max time and the three attribute maps (DDL at the end).
Queries: the gauge SQL renderChartConfig generates today (Source → Bucketed → outer select), and the same shape reading -Merge states from the rollup. One metric with 6k series, median of 5 warm runs.

Reads

chart	raw	1m rollup	1h rollup
1h, sum by namespace, 1 min buckets	795 ms	431 ms (1.8x)	n/a
24h, sum by namespace, 30 min buckets	4.8 s	886 ms (5.5x)	n/a
24h, avg where namespace = prod, 30 min buckets	3.1 s	138 ms (23x)	n/a
24h, max of delta, 30 min buckets	4.2 s	235 ms (18x)	n/a
24h, sum by namespace, 6 h buckets (7d chart's bucket size)	4.4 s	506 ms (8.7x)	76 ms (58x)

For the 24h charts, rows read fell from 69M to 14.6M and bytes read from 21.8 GB to 0.6–3.9 GB. Every chart scans its whole range on each path, so a real 7d range should cost about 7x more on all three and the ratios should hold.

Ingest (31.8M rows per run at a fixed 100k points/s; default batch processor, about 12k rows per insert; mean of 2 runs)

	server CPU, whole run	vs raw	insert CPU incl. MV	merge CPU	insert p50 / p95	points refused
raw only	254 s		15 s	183 s	118 / 166 ms	0
raw + 1m	547 s	+115%	188 s	263 s	184 / 390 ms	0
raw + 1m + 1h	790 s	+211%	356 s	333 s	245 / 565 ms	0.1%

Server CPU comes from system.metric_log over each run's window, including merges after the load. The per-query numbers come from query_log, query_views_log and part_log.

Storage: the 1m rollup is 16% of raw's compressed size, the 1h rollup 0.5%.

Why ingest costs so much

With about 12k rows per insert and 26.5k series, an insert rarely holds more than one point per series. The MV therefore collapses nothing at insert time: it writes as many rows as raw, each carrying all three maps, and merges do the collapsing later. Replaying 15 min of the same data through a 1m MV:

1m MV	rows per insert	MV CPU	rows written by MV
with attribute maps	10k	12.4 s	2.65M
with attribute maps	50k	7.8 s	1.59M
with attribute maps	200k	4.0 s	0.71M
hash + states only	10k	1.9 s	2.65M

About 85% of the MV's insert CPU goes to the attribute maps, and 200k-row inserts make it about 3x cheaper.

Other findings

Raw gauge charts use last_value(Value), which depends on read order. Against argMax(Value, TimeUnix), raw results were off by up to 0.7% at 30 min buckets and 3.3% at 6 h buckets. The rollup matched argMax in every chart. This could be a small separate fix on the raw path.
On the rollup, grouping by ResourceAttributes['k8s.namespace.name'] still reads the whole map: 886 ms, against 235 ms for the delta chart, which doesn't read it. Filtering on the materialized namespace column went to PREWHERE on the rollup but not on raw, which is where the 23x comes from.
ClickHouse 26.x refuses an AggregatingMergeTree with map columns outside the sort key unless allow_dimensions_outside_sorting_key = 1 is set. The maps depend only on AttributesHash, so that's safe here.
With both rollups, the collector's memory limiter refused 0.1% of points at 100k points/s and 0.5% at 130k. Without rollups it refused none at 100k.

Caveats: single node with the generator, collector and ClickHouse sharing CPU; 1/10 scale; warm cache. Only 2 runs per ingest variant, and raw-only varied between 218 s and 291 s. ResourceAttributes is 62% of compressed bytes here against 82% in @bcanvural's table, so the map costs are probably higher on their data.

Options as I see them

Ship as proposed, opt-in per source: large read wins for about 2x ingest CPU per rollup at default batch sizes. Fits read-heavy deployments with CPU headroom.
Hash-only rollup plus the series table: about 85% less MV insert CPU in the replay, but grouping or filtering by attributes needs the series table. That's where this would combine with @SpencerTorres's work.
In either case, document a larger collector batch for sources with rollups.

Which direction do you prefer? I can rerun anything, and @bcanvural could check the numbers at full scale. The benchmark scripts are ready to share if useful.

Rollup DDL (1m; the 1h version uses toStartOfHour and toStartOfDay in the key)

CREATE TABLE otel_metrics_gauge_1m
(
    TimeUnix DateTime CODEC(Delta, ZSTD(3)),
    ServiceName LowCardinality(String) CODEC(ZSTD(3)),
    MetricName LowCardinality(String) CODEC(ZSTD(3)),
    AttributesHash UInt64 CODEC(ZSTD(3)),
    ResourceAttributes Map(LowCardinality(String), String) CODEC(ZSTD(3)),
    ScopeAttributes Map(LowCardinality(String), String) CODEC(ZSTD(3)),
    Attributes Map(LowCardinality(String), String) CODEC(ZSTD(3)),
    LastValue AggregateFunction(argMax, Float64, DateTime) CODEC(ZSTD(3)),
    FirstValue AggregateFunction(argMin, Float64, DateTime) CODEC(ZSTD(3)),
    MinTime SimpleAggregateFunction(min, DateTime) CODEC(Delta, ZSTD(3)),
    MaxTime SimpleAggregateFunction(max, DateTime) CODEC(Delta, ZSTD(3)),
    `__otel_materialized_k8s.namespace.name` LowCardinality(String) MATERIALIZED ResourceAttributes['k8s.namespace.name']
)
ENGINE = AggregatingMergeTree
PARTITION BY toDate(TimeUnix)
ORDER BY (ServiceName, MetricName, toStartOfHour(TimeUnix), AttributesHash, TimeUnix)
SETTINGS allow_dimensions_outside_sorting_key = 1;

CREATE MATERIALIZED VIEW otel_metrics_gauge_1m_mv TO otel_metrics_gauge_1m AS
SELECT
    toStartOfMinute(g.TimeUnix) AS TimeUnix,
    g.ServiceName AS ServiceName,
    g.MetricName AS MetricName,
    cityHash64(g.ScopeAttributes, g.ResourceAttributes, g.Attributes) AS AttributesHash,
    any(g.ResourceAttributes) AS ResourceAttributes,
    any(g.ScopeAttributes) AS ScopeAttributes,
    any(g.Attributes) AS Attributes,
    argMaxState(g.Value, g.TimeUnix) AS LastValue,
    argMinState(g.Value, g.TimeUnix) AS FirstValue,
    min(g.TimeUnix) AS MinTime,
    max(g.TimeUnix) AS MaxTime
FROM otel_metrics_gauge AS g
GROUP BY ServiceName, MetricName, AttributesHash, TimeUnix;

Navigation Menu

Repository navigation

Materialized view support for Metric sources (gauges) #3482

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Materialized view support for Metric sources (gauges) #3482

Description

Activity

Shiva953 commented on Oct 29, 2026

pulpdrew commented on Oct 29, 2026

Shiva953 commented on Oct 29, 2026

pulpdrew commented on Oct 29, 2026

bcanvural commented on Oct 29, 2026

Shiva953 commented on Oct 30, 2026

pulpdrew commented on Oct 30, 2026

Shiva953 commented on Oct 30, 2026

pulpdrew commented on Nov 2, 2026

Shiva953 commented on Nov 7, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions