Querying histograms

Histograms are a powerful tool for understanding the distribution of your metric data, such as request latencies or response sizes. Instead of just tracking an average, histograms group observations into configurable buckets, giving you a much richer view of your data's performance and characteristics.

In our system, histogram data is stored in a highly optimized format. This guide will walk you through how this data is structured and how to write SQL queries to visualize it as tables, heatmaps, and even calculate custom quantiles like p95.

For details on how you can send us histograms, see Ingesting metrics API. Histograms and summaries coming from Prometheus and histograms from OpenTelemetry are supported out-of-the-box.

Querying histograms

We store histograms in a unified and efficient way. Histogram data is split into bucket_bounds (the upper limits of the buckets) and bucket_counts (the number of observations in each bucket, stored as deltas). Pre-computed quantiles are available via bucket_quantiles.

Understanding the histogram data structure

Column	Merge Function	Description
`bucket_bounds`	`anyLastMerge()`	Histogram bucket boundaries (e.g., `[0.1, 0.5, inf]`).
`bucket_counts`	`sumForEachMerge()`	Counts per bucket (element-wise sum of deltas).
`bucket_quantiles`	`histogramQuantile(p)`	Pre-computed quantile function for efficient percentile calculation.
`bucket_sum`	`sumMerge()`	Sum of all observed values.
`bucket_count`	`sumMerge()`	Total number of observations.
`bucket_min`	`minMerge()`	Minimum observed value.
`bucket_max`	`maxMerge()`	Maximum observed value.

You can calculate the precise average value of a metric using sumMerge(bucket_sum) / sumMerge(bucket_count). This is often more accurate than estimating an average from bucket midpoints.

Visualizing buckets as a table

To visualize the number of events in each bucket over a selected time range for a metric, you'll join the bucket_bounds with the bucket_counts. Remember that bucket_counts stores deltas, so sumForEachMerge is used to aggregate them.

Fill in the query variable {{metric_name}} with your metric, e.g. http_request_duration_seconds.

SQL query to show bucket counts

Copied!

SELECT
  {{time}} AS time,
  label('status') AS status, -- Example label for grouping
  bound AS upper_limit,
  count AS value
FROM
(
  SELECT
    {{time}} AS time,
    series_id,
    anyLastMerge(bucket_bounds) AS bounds,
    sumForEachMerge(bucket_counts) AS counts
  FROM {{source}}
  WHERE name = {{metric_name}}
    AND dt BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY time, series_id
)
ARRAY JOIN arrayZip(bounds, counts) AS bucket_data
SETTINGS enable_unaligned_array_join = 1
WHERE isFinite(bucket_data.1) -- Exclude +Inf bound for clearer visualization
-- If specific labels are needed for grouping outside of series_id, add them here
-- For example: AND label('status') = '200'
GROUP BY time, status, upper_limit
ORDER BY time, status, toFloat64OrNull(upper_limit)

Key points for this query:

sumForEachMerge(bucket_counts) correctly aggregates the delta counts across intervals.
ARRAY JOIN arrayZip(bounds, counts) AS bucket_data pairs each bound with its corresponding count.
isFinite(bucket_data.1) filters out the +Inf bucket, which is typically the last element in bucket_bounds and represents the total count.
label('status') is an example of including other metric labels for further breakdown.

Creating a heatmap

A heatmap provides a visual representation of how your metric's distribution changes over time. We aggregate bucket_counts to get the total count within each bucket for each time interval.

SQL query for a histogram heatmap

Copied!

SELECT
  {{time}} AS time,
  concat(toString(round(bound * 1000, 2)), 'ms') AS series, -- Format bounds nicely
  toUInt64(sum(count)) AS value
FROM
(
  SELECT
    {{time}} AS time,
    series_id,
    arrayJoin(arrayZip(anyLastMerge(bucket_bounds), sumForEachMerge(bucket_counts))) AS bucket_data,
    bucket_data.1 AS bound,
    bucket_data.2 AS count
  FROM {{source}}
  WHERE name = {{metric_name}}
    AND dt BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY time, series_id
)
WHERE isFinite(bound) -- Exclude +Inf bound
-- Example: Optional filtering by a label
-- AND label('status') = '200'
GROUP BY time, series
ORDER BY toFloat64OrNull(replaceRegexpAll(series, '[^0-9\\.]', '')) DESC

Key points for this query:

We GROUP BY time, series_id first to ensure consistent bucket boundaries before arrayJoin.
sumForEachMerge(bucket_counts) is crucial for aggregating the delta counts.
isFinite(bound) filters out the +Inf bucket.
series is constructed from the bound to provide meaningful labels for the heatmap.

Calculating custom quantiles (e.g., p95)

Bucket quantiles are stored in the bucket_quantiles aggregate column, which works with the quantilePrometheusHistogramArrayMerge function. A helper function histogramQuantile(p) is available for convenience.

Copied!

-- Definition of the helper function (already available in the database)
CREATE OR REPLACE FUNCTION histogramQuantile AS (bound) ->
  quantilePrometheusHistogramArrayMerge(bound)(bucket_quantiles);

Important: When to use histogramQuantile vs. Conditional Form:

histogramQuantile(p) (helper): Use when your WHERE clause filters for exactly one metric name. If you query multiple metrics or no specific metric, the histogram buckets from different metrics will be mixed, producing incorrect results.
quantilePrometheusHistogramArrayMergeIf(p)(bucket_quantiles, condition): Use when querying multiple metric names or when no specific metric is filtered. The condition allows you to apply the quantile calculation selectively.

Example: Single metric quantile calculation (recommended)

SQL query to calculate quantiles - single metric)

Copied!

SELECT
  {{time}} AS time,
  histogramQuantile(0.50) AS p50,
  histogramQuantile(0.90) AS p90,
  histogramQuantile(0.95) AS p95,
  histogramQuantile(0.99) AS p99
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
  AND name = 'http_request_duration_seconds' -- Filter by a single metric name
GROUP BY time
ORDER BY time

Example: Multiple metrics quantile calculation (conditional form)

SQL query to calculate quantiles - multiple metrics

Copied!

SELECT
  {{time}} AS time,
  quantilePrometheusHistogramArrayMergeIf(0.95)(bucket_quantiles, name = 'metric_a') AS metric_a_p95,
  quantilePrometheusHistogramArrayMergeIf(0.95)(bucket_quantiles, name = 'metric_b') AS metric_b_p95
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
  AND name IN ('metric_a', 'metric_b')
GROUP BY time
ORDER BY time

Histogram Statistics

For basic statistics without manual percentile calculation, you can use the dedicated bucket_sum, bucket_count, bucket_min, and bucket_max aggregate columns.

SQL query for histogram statistics

Copied!

SELECT
  {{time}} AS time,
  sumMerge(bucket_sum) / sumMerge(bucket_count) AS avg_value,  -- Average
  minMerge(bucket_min) AS min_value,
  maxMerge(bucket_max) AS max_value,
  sumMerge(bucket_count) AS total_count
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
  AND name = 'http_request_duration_seconds'
GROUP BY time
ORDER BY time

Querying legacy sources created before 2026

Understanding the histogram data structure

When you send histogram data, it's stored in several columns. The most important one is the buckets column. Let's look at its structure and related fields:

Column	Data Type	Description
`buckets`	`Array(Tuple(String, Float64))`	An array of `(upper_bound, cumulative_count)` tuples. This is the core histogram data.
`buckets_sum`	`Nullable(Float64)`	The sum of all observed values in the histogram.
`buckets_count`	`Nullable(UInt64)`	The total count of all observed values.
`buckets_rate`	`Array(Tuple(String, Nullable(Float64)))`	A pre-calculated rate version of the `buckets` column.

Depending on how you're sending us the data, the buckets column may data in a cumulative format. For example, a value like [('0.1', 10), ('0.5', 25), ('+inf', 30)] could mean:

10 events had a value less than or equal to 0.1.
25 events had a value less than or equal to 0.5.
30 events were observed in total.

10 events had a value less than or equal to 0.1.
25 events had a value less than or equal to 0.5 and higher than 0.1.
30 events had a value higher than 0.5.

To work with this data, you'll often need to convert these cumulative counts into individual counts for each bucket. Let's see how.

You can calculate the precise average value of a metric using anyLastMerge(buckets_sum) / anyLastMerge(buckets_count). This is often more accurate than estimating an average from bucket midpoints.

Visualizing buckets as a table

One of the simplest ways to analyze a histogram is to display the number of events in each bucket in a table.

The following query shows the counts for each bucket over the selected time range for a metric. Fill in the query variable {{metric_name}} with your metric, e.g. http_request_duration_seconds:

SQL query to show bucket counts

Copied!

WITH buckets AS (
  SELECT
    -- use default time intervals
    {{time}} AS time,
    -- all metric tags for grouping/filtering later
    tags,
    -- buckets is an anyLast-aggregated metric holding array of (upper_limit, value)
    -- arrayJoin() explodes it so each row is ONE bucket tuple
    arrayJoin(anyLastMerge(buckets)) AS bucket
  FROM {{source}}
  -- apply basic filtering
  WHERE name = {{metric_name}}
    AND time BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY time, tags
),
per_bucket AS (
  SELECT
    time,
    tags,
    -- Bucket upper bound (string like '0.1', '2.5', or '+infinity')
    bucket.1 AS upper_limit,
    -- Bucket value (usually cumulative count for that bound)         
    toInt64(bucket.2) AS value
  FROM buckets
)
-- Final select of the values
SELECT time, tags, upper_limit, value
FROM per_bucket
-- sort chronologically, then by tags, then numerically by upper_limit
-- toFloat64OrNull() makes sure '0.5' sorts before '2.5', and puts '+infinity' last
ORDER BY time, tags, toFloat64OrNull(upper_limit)

Creating a heatmap

A heatmap is an excellent way to see how the distribution of your metric changes over time. To create one, we need to calculate the number of events in each bucket for each time interval.

This query prepares the data in a (time, bucket_label, value) format, which is perfect for a heatmap visualization.

Since histograms usually contain counters as values, let's use buckets_rate to chart the change per second of those counters.

To make sure the heatmap is clean, we will add up all rates from differently tagged time series, add an optional metric tag filter, and remove all 0 values in the final SELECT.

SQL query for a histogram heatmap

Copied!

WITH buckets AS (
  SELECT
    -- use max time resolution and all metric tags for grouping/filtering later
    dt, tags,
    -- buckets_rate is an anyLast-aggregated metric holding array of (upper_limit, rate)
    -- arrayJoin() explodes it so each row is ONE bucket tuple
    arrayJoin(anyLastMerge(buckets_rate)) AS bucket
  FROM {{source}}
  -- apply basic filtering
  WHERE name = {{metric_name}}
    AND dt BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY dt, tags
),
per_bucket AS (
  SELECT
    -- group by default interval now, and calculate average rates based on all data
    {{time}} AS time,
    tags,
    -- Bucket upper bound (string like '0.1', '2.5', or '+infinity')
    bucket.1 AS upper_limit,
    -- Bucket rate (assuming counters as values)
    avg(toFloat64(bucket.2)) AS rate
  FROM buckets
  GROUP BY time, tags, upper_limit
)
-- Final select of the values
SELECT time, upper_limit AS series, sum(rate) AS value
FROM per_bucket
[[ WHERE metricTag('path') == {{path}} ]]
GROUP BY time, upper_limit
HAVING value != 0
-- sort numerically by upper_limit
ORDER BY toFloat64OrNull(upper_limit) DESC

Calculating custom quantiles (e.g., p95)

Histograms are perfect for estimating quantiles (or percentiles) like p50 (median) or p95. This tells you the value that a certain percentage of your observations fall under. For example, a p95 latency of 250ms means that 95% of your requests were faster than 250ms.

Because our histograms store counters, we first need to calculate the individual count for each bucket in time. Then, we can use ClickHouse's quantileInterpolatedWeighted function to calculate any quantile we need. This function is ideal for pre-aggregated data like histograms.

The following query calculates p99, p95, p90, p50, and other quantiles for a given metric:

SQL query to calculate quantiles

Copied!

WITH buckets AS (
  SELECT
    -- use max time resolution and all metric tags for grouping/filtering later
    dt, tags,
    -- buckets is an anyLast-aggregated metric holding array of (upper_limit, value)
    -- arrayJoin() explodes it so each row is ONE bucket tuple
    arrayJoin(anyLastMerge(buckets)) AS bucket
  FROM {{source}}
  -- apply basic filtering
  WHERE name = {{metric_name}}
    AND dt BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY dt, tags
),
per_bucket AS (
  SELECT
    -- group by default interval now, and get the latest counts per bucket
    {{time}} AS time,
    tags,
    -- Bucket upper bound (string like '0.1', '2.5', or '+infinity')
    bucket.1 AS upper_limit,
    -- Latest count as unsigned int
    max(toUInt64(bucket.2)) AS value
  FROM buckets
  GROUP BY time, tags, upper_limit
),
per_time AS (
  SELECT
    time,
    toFloat64OrNull(upper_limit) AS upper_limit, 
    sum(value) AS value
  FROM per_bucket
  -- drop +Inf buckets, since they cannot be used
  WHERE upper_limit IS NOT NULL
    -- here you can add filters for metricTag()
  GROUP BY time, upper_limit
),
with_deltas AS (
  SELECT
    time,
    upper_limit,
    -- calculating integer change in count from the previous step in time
    greatest(
      value - lagInFrame(value, 1, 0) OVER (PARTITION BY time ORDER BY upper_limit),
      toUInt64(0)
    ) AS bucket_count
  FROM per_time
)
-- Final select of the values using quantileInterpolatedWeighted()
SELECT
  time,
  quantileInterpolatedWeighted(0.99)(upper_limit, bucket_count) AS p99,
  quantileInterpolatedWeighted(0.95)(upper_limit, bucket_count) AS p95,
  quantileInterpolatedWeighted(0.9)(upper_limit, bucket_count) AS p90,
  quantileInterpolatedWeighted(0.5)(upper_limit, bucket_count) AS p50,
  quantileInterpolatedWeighted(0.1)(upper_limit, bucket_count) AS p10,
  quantileInterpolatedWeighted(0.05)(upper_limit, bucket_count) AS p05,
  quantileInterpolatedWeighted(0.01)(upper_limit, bucket_count) AS p01
FROM with_deltas 
GROUP BY time

Need help?

Please let us know at hello@betterstack.com. We're happy to help! 🙏

Variables in SQL queries

Visualize coordinates and IPs

Explore documentation

Querying histograms