Metrics Tables and Time-Series Patterns

What makes metrics data different from a generic events table

A metrics workload has a specific shape: a numeric value, sampled at (usually) regular intervals, identified by a metric name and a set of dimensions — which host, which region, which environment. cpu.load on host-042 in us-east at 14:32:10 is 0.73. Compare that to a generic event — a page view, a click, an API request — which tends to carry a variable, ad-hoc bag of fields specific to that event type. Metrics are far more uniform: the same four-part shape (name, tags, timestamp, value) repeats for every single row, no matter which metric it is. That uniformity is what makes a single, general-purpose table structure realistic for metrics in a way it usually isn't for arbitrary events.

The narrow (long) table pattern

The idiomatic shape stores one row per (metric, tag set, timestamp) combination, with a single value column — often called the "narrow" or "long" schema, borrowing the terms from the same wide-vs-long distinction in general tabular data modeling:

CREATE TABLE metrics
(
    metric_name String,
    host        LowCardinality(String),
    region      LowCardinality(String),
    tags        Map(LowCardinality(String), String),
    ts          DateTime64(3),
    value       Float64
)
ENGINE = MergeTree
ORDER BY (metric_name, host, ts);

Every metric — cpu.load, mem.used, http.requests.count, a metric nobody has invented yet — fits into the same four columns. Adding a new metric is just a new value of metric_name showing up in an INSERT; it never requires a schema change. host and region are pulled out as dedicated columns because they're the dimensions most queries filter on; everything else that varies by deployment — a Kubernetes pod name, a customer ID, a build version — goes into tags, a Map column that can hold an open-ended, per-row set of key/value pairs without any ALTER TABLE. Both host and region are also wrapped in LowCardinality — a modifier telling ClickHouse to store the column as a dictionary of distinct values plus a compact per-row index into it, which pays off exactly when a column repeats a small set of values across millions of rows, as a hostname or region name does.

Choosing ORDER BY for a metrics table

This is the direct payoff from the previous module's prefix rule, applied to a real decision: which column goes first in ORDER BY depends entirely on what your queries filter on first. The overwhelmingly dominant metrics query pattern is "show me this metric, for this host (or this small set of hosts), over some time range" — a dashboard panel, an alert evaluation, a single service's latency graph. That means metric_name should almost always lead: every one of those queries pins it down with an equality condition, so it's the column that benefits every single time. host comes next because it's the dimension queries narrow by second most often, and ts comes last — the column every query has a range on, never an equality, which is exactly where a trailing key column belongs.

region, notice, isn't in the key at all here — a query that filters by region without metric_name and host won't get any dependable narrowing from this table's primary index, for exactly the reason the previous lesson covered: it's not a leading, contiguous prefix match. That's a real limitation of this schema, not an oversight, and the design exercise later in this lesson asks you to reason about what to do when a query pattern like that actually matters.

Codecs built for time-series shapes

Two columns in a metrics table have unusually exploitable structure, and ClickHouse ships codecs designed specifically for it. Timestamps within an ORDER BY-sorted part are monotonically increasing, and consecutive samples are usually close together — so the difference between consecutive values is small and repetitive, which is exactly what delta-style codecs compress well:

CREATE TABLE metrics
(
    metric_name String,
    host        LowCardinality(String),
    region      LowCardinality(String),
    tags        Map(LowCardinality(String), String),
    ts          DateTime64(3) CODEC(DoubleDelta, ZSTD),
    value       Float64 CODEC(Gorilla, ZSTD)
)
ENGINE = MergeTree
ORDER BY (metric_name, host, ts)
TTL toDateTime(ts) + INTERVAL 14 DAY;

DoubleDelta encodes the change in the change between consecutive values — built for exactly the near-constant-interval timestamps a metrics pipeline produces. Gorilla is a floating-point codec taken from Facebook's Gorilla time-series storage paper, built for metric values that tend to stay close to their previous reading rather than jumping around. Both are then handed to a general-purpose codec (ZSTD here) for a second pass — the specialized codec exploits the sequence's structure first, and general compression mops up whatever's left.

Real-world case study: an observability platform's schema on ClickHouse

SigNoz, an open-source observability platform (metrics, traces, and logs) built directly on ClickHouse, uses this narrow-schema pattern for its metrics store: a samples_v4 table keyed by a time series identifier and timestamp, with a separate time_series_v4 table mapping each time series identifier to its metric name and label set — plus pre-aggregated samples_v4_agg_5m and samples_v4_agg_30m tables for exactly the kind of downsampled rollup this lesson's design exercise builds. The split — one table for "what is this time series" metadata, one table for the actual timestamped samples — is the same narrow-table instinct this lesson builds, just with the tag lookup normalized into its own table rather than inlined as a Map on every row; it's a variation on the same idea, not a different one, and the trade-off between inlining tags and normalizing them into a separate table is exactly the kind of decision Module 4's denormalization-tradeoffs lesson develops further.

Idiom vs. anti-pattern

CREATE TABLE metrics
(
    metric_name String,
    host        LowCardinality(String),
    ts          DateTime64(3) CODEC(DoubleDelta, ZSTD),
    value       Float64 CODEC(Gorilla, ZSTD)
)
ENGINE = MergeTree
ORDER BY (metric_name, host, ts);
-- Adding a new metric: just start inserting rows with a new metric_name.

CREATE TABLE host_metrics_wide
(
    ts          DateTime,
    host        String,
    cpu_load    Float64,
    mem_used    Float64,
    disk_used   Float64,
    net_in      Float64,
    net_out     Float64
    -- every new metric means an ALTER TABLE ADD COLUMN on a table
    -- that's already carrying months of history
)
ENGINE = MergeTree
ORDER BY (host, ts);

The wide table isn't a strawman — it's the schema almost everyone reaches for first, because it looks exactly like a spreadsheet of "host, time, and its readings," and it works fine at small scale. It falls apart for reasons that only show up as the system grows: every team that wants to track a new metric needs a schema migration on a table other teams also depend on; a row for a host that's only reporting cpu_load this cycle still has to represent (or null out) every other metric's column; and a query for "every metric this host reported" has to know every column name in advance instead of just filtering on metric_name. The narrow schema trades a slightly less obvious row shape for a table that never needs to change shape again.

Design exercise

A monitoring team runs roughly 2,000 hosts, each emitting about 150 distinct metrics every 10 seconds, tagged with host, region, and environment (prod or staging). Three query patterns matter:

1. Dashboards — "show cpu.load for host-0423 over the last 6 hours." Single metric, single host, short range. This is the large majority of query volume.
2. Alerting — "scan the last 2 minutes of every metric, every host, for threshold breaches." Broad in metric and host, narrow in time.
3. Capacity planning — "show p95 of mem.used across all hosts in region = us-east, over the last 30 days, downsampled to 5-minute buckets." Broad in host, narrow in metric, filtered by a dimension that isn't in the table's key, over a long range.