Guides

Advanced SQL Patterns & Sketch Functions

Overview

This page covers sketch metric functions and advanced SQL patterns for Arthur's TimescaleDB metrics store. If you're new to the metrics store, start with Metrics Overview & Data Model and Basic Metric Query Patterns first.

Sketch Metric Functions

Sketch metrics store a KLL (Karnin-Lang-Liberty) float sketch — a compact data structure that supports approximate quantile queries over large datasets without storing every individual value. Arthur uses the Apache DataSketches implementation, exposed through PostgreSQL aggregate and scalar functions.

⚠️

Do not use DuckDB functions here. Functions like approx_quantile(), quantile_cont() on sketch columns, or any DuckDB-specific syntax will not work in the TimescaleDB metrics store.

Available kll_float_sketch_* Functions

FunctionSignatureDescription
kll_float_sketch_get_quantile(sketch BYTEA, rank DOUBLE PRECISION) → FLOATReturns the value at the given quantile rank (0.0–1.0) from a single sketch
kll_float_sketch_get_quantiles(sketch BYTEA, ranks DOUBLE PRECISION[]) → FLOAT[]Returns values at multiple quantile ranks from a single sketch
kll_float_sketch_mergeaggregate (sketch BYTEA) → BYTEAMerges multiple sketches into one (use in GROUP BY queries)
kll_float_sketch_get_n(sketch BYTEA) → BIGINTReturns the number of items represented by the sketch
kll_float_sketch_get_min_item(sketch BYTEA) → FLOATReturns the minimum value in the sketch
kll_float_sketch_get_max_item(sketch BYTEA) → FLOATReturns the maximum value in the sketch
kll_float_sketch_get_pmf(sketch BYTEA, split_points DOUBLE PRECISION[]) → DOUBLE PRECISION[]Returns the probability mass function (histogram) at the given split points

Query a Single Sketch Bucket

Get the p50, p95, and p99 latency for a specific 5-minute window:

SELECT
    timestamp,
    kll_float_sketch_get_quantile(value, 0.50) AS p50_latency_ms,
    kll_float_sketch_get_quantile(value, 0.95) AS p95_latency_ms,
    kll_float_sketch_get_quantile(value, 0.99) AS p99_latency_ms
FROM metrics_sketch_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'response_latency_ms'
  AND timestamp    = '2024-01-15 14:00:00+00';

Query Quantiles Over a Time Range

To get quantiles over a time range, you must first merge the sketches using kll_float_sketch_merge, then apply the quantile function to the merged result:

SELECT
    kll_float_sketch_get_quantile(
        kll_float_sketch_merge(value), 0.95
    ) AS p95_latency_ms
FROM metrics_sketch_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'response_latency_ms'
  AND timestamp >= NOW() - INTERVAL '1 hour';

Median by Dimension

Get the daily median latency broken down by rule type:

SELECT
    time_bucket(INTERVAL '1 day', timestamp)                              AS bucket,
    kll_float_sketch_get_quantile(kll_float_sketch_merge(value), 0.5)    AS median_latency,
    dimensions->>'rule_type'                                               AS rule_type
FROM metrics_sketch_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'rule_latency'
  AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
GROUP BY bucket, rule_type;

Hourly Quantile Time Series

Merge sketches into hourly buckets and compute quantiles at each hour:

SELECT
    time_bucket('1 hour', timestamp)                                       AS hour,
    kll_float_sketch_get_quantile(kll_float_sketch_merge(value), 0.50)    AS p50_ms,
    kll_float_sketch_get_quantile(kll_float_sketch_merge(value), 0.95)    AS p95_ms,
    kll_float_sketch_get_quantile(kll_float_sketch_merge(value), 0.99)    AS p99_ms,
    kll_float_sketch_get_n(kll_float_sketch_merge(value))                 AS sample_count
FROM metrics_sketch_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'response_latency_ms'
  AND timestamp >= NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1 ASC;

Multiple Quantiles in One Pass

Use kll_float_sketch_get_quantiles to retrieve several percentiles efficiently without scanning the sketch multiple times:

SELECT
    time_bucket('1 hour', timestamp)                                              AS hour,
    (kll_float_sketch_get_quantiles(
        kll_float_sketch_merge(value),
        ARRAY[0.25, 0.50, 0.75, 0.95, 0.99]
    ))[1]                                                                          AS p25_ms,
    (kll_float_sketch_get_quantiles(
        kll_float_sketch_merge(value),
        ARRAY[0.25, 0.50, 0.75, 0.95, 0.99]
    ))[3]                                                                          AS p75_ms,
    (kll_float_sketch_get_quantiles(
        kll_float_sketch_merge(value),
        ARRAY[0.25, 0.50, 0.75, 0.95, 0.99]
    ))[5]                                                                          AS p99_ms
FROM metrics_sketch_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'response_latency_ms'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 ASC;
💡

Performance tip: When you need multiple quantiles from the same merged sketch, use a CTE to merge once and query the result multiple times. See CTE Optimization below.

Build a Distribution Histogram (PMF)

Use kll_float_sketch_get_pmf to get the probability mass function — a histogram of how values are distributed across a range:

WITH merged AS (
    SELECT
        kll_float_sketch_get_pmf(
            kll_float_sketch_merge(value),
            ARRAY[0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5,
                  0.55, 0.6, 0.65, 0.7, 0.75, 0.8, 0.85, 0.9, 0.95]
        ) AS sketch,
        kll_float_sketch_get_n(kll_float_sketch_merge(value)) AS total
    FROM metrics_sketch_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'toxicity_score'
      AND timestamp BETWEEN '{{dateStart}}' AND '{{dateEnd}}'
)
SELECT
    ROUND(((ordinality) / 20.0), 2)::VARCHAR AS bucket,
    val * merged.total                        AS inference_count
FROM merged,
     unnest(merged.sketch) WITH ORDINALITY AS val;

CTE Optimization

Avoid redundant sketch merges by computing the merged sketch in a CTE:

WITH merged AS (
    SELECT
        time_bucket('1 hour', timestamp) AS hour,
        kll_float_sketch_merge(value)     AS merged_sketch
    FROM metrics_sketch_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'response_latency_ms'
      AND timestamp >= NOW() - INTERVAL '24 hours'
    GROUP BY 1
)
SELECT
    hour,
    kll_float_sketch_get_quantile(merged_sketch, 0.50) AS p50_ms,
    kll_float_sketch_get_quantile(merged_sketch, 0.95) AS p95_ms,
    kll_float_sketch_get_quantile(merged_sketch, 0.99) AS p99_ms,
    kll_float_sketch_get_min_item(merged_sketch)        AS min_ms,
    kll_float_sketch_get_max_item(merged_sketch)        AS max_ms,
    kll_float_sketch_get_n(merged_sketch)               AS n
FROM merged
ORDER BY hour ASC;

Joining Numeric and Sketch Metrics

Combine a numeric metric (e.g., error rate) with a sketch metric (e.g., latency) on the same time axis:

WITH hourly_numeric AS (
    SELECT
        time_bucket('1 hour', timestamp) AS hour,
        AVG(value)                        AS avg_error_rate
    FROM metrics_numeric_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'error_rate'
      AND timestamp >= NOW() - INTERVAL '24 hours'
    GROUP BY 1
),
hourly_sketch AS (
    SELECT
        time_bucket('1 hour', timestamp)                                    AS hour,
        kll_float_sketch_get_quantile(kll_float_sketch_merge(value), 0.95) AS p95_latency_ms
    FROM metrics_sketch_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'response_latency_ms'
      AND timestamp >= NOW() - INTERVAL '24 hours'
    GROUP BY 1
)
SELECT
    n.hour,
    n.avg_error_rate,
    s.p95_latency_ms
FROM hourly_numeric n
JOIN hourly_sketch  s ON n.hour = s.hour
ORDER BY n.hour ASC;

Anomaly Detection

Flag time buckets where a metric deviates more than 2 standard deviations from its recent mean:

WITH stats AS (
    SELECT
        AVG(value)    AS mean_val,
        STDDEV(value) AS stddev_val
    FROM metrics_numeric_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'prediction_score_mean'
      AND timestamp >= NOW() - INTERVAL '7 days'
),
recent AS (
    SELECT
        timestamp,
        value
    FROM metrics_numeric_latest_version
    WHERE model_id    = '{{your-model-uuid}}'
      AND metric_name  = 'prediction_score_mean'
      AND timestamp >= NOW() - INTERVAL '1 hour'
)
SELECT
    r.timestamp,
    r.value,
    s.mean_val,
    s.stddev_val,
    ABS(r.value - s.mean_val) / NULLIF(s.stddev_val, 0) AS z_score
FROM recent r
CROSS JOIN stats s
WHERE ABS(r.value - s.mean_val) > 2 * s.stddev_val
ORDER BY r.timestamp ASC;

Dimension Pivoting

Pivot dimension values into columns for a side-by-side comparison:

SELECT
    time_bucket('1 hour', timestamp)                                                     AS hour,
    AVG(value) FILTER (WHERE dimensions->>'environment' = 'production')                   AS prod_accuracy,
    AVG(value) FILTER (WHERE dimensions->>'environment' = 'staging')                      AS staging_accuracy,
    AVG(value) FILTER (WHERE dimensions->>'environment' = 'production')
        - AVG(value) FILTER (WHERE dimensions->>'environment' = 'staging')                AS prod_minus_staging
FROM metrics_numeric_latest_version
WHERE model_id    = '{{your-model-uuid}}'
  AND metric_name  = 'accuracy'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 ASC;

Continuous Aggregates

For dashboards that query large time ranges frequently, create a TimescaleDB continuous aggregate to pre-compute hourly rollups:

-- Create a continuous aggregate (run once, as a DB admin)
CREATE MATERIALIZED VIEW metrics_accuracy_hourly
WITH (timescaledb.continuous) AS
SELECT
    model_id,
    time_bucket('1 hour', timestamp) AS hour,
    AVG(value)                        AS avg_accuracy,
    MIN(value)                        AS min_accuracy,
    MAX(value)                        AS max_accuracy,
    COUNT(*)                          AS bucket_count
FROM metrics_numeric_latest_version
WHERE metric_name = 'accuracy'
GROUP BY model_id, 2
WITH NO DATA;

-- Add a refresh policy (refresh last 3 hours every 15 minutes)
SELECT add_continuous_aggregate_policy(
    'metrics_accuracy_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '15 minutes'
);
⚠️

Note: Creating continuous aggregates requires database admin privileges. Contact your Arthur platform administrator if you need this capability.

Updated about 22 hours ago