Resampling — Downsampling and Upsampling Time Series

Resampling changes the time resolution of a series. Downsampling compresses (1-minute → 1-hour). Upsampling expands (daily → hourly). In SQL, downsampling uses DATE_TRUNC + aggregate; upsampling uses generate_series

• interpolation.

-- Downsample server metrics: 1-minute → 5-minute → 1-hour buckets, one query
SELECT
    host,
    -- 5-minute bucket
    DATE_TRUNC('hour', recorded_at)
        + (EXTRACT(MINUTE FROM recorded_at)::INT / 5 * 5 || ' minutes')::INTERVAL
                                                         AS bucket_5m,
    COUNT(*)                                             AS readings,
    ROUND(AVG(cpu_pct)::numeric, 2)                      AS avg_cpu,
    ROUND(MIN(cpu_pct)::numeric, 2)                      AS min_cpu,
    ROUND(MAX(cpu_pct)::numeric, 2)                      AS max_cpu,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP
        (ORDER BY cpu_pct)::numeric, 2)                  AS p95_cpu,
    -- Alert: was cpu > 90 at any point in this 5-min window?
    bool_or(cpu_pct > 90)                                AS had_spike,
    SUM(error_count)                                     AS total_errors
FROM ts_server_metrics
WHERE recorded_at >= NOW() - INTERVAL '24 hours'
GROUP BY host,
    DATE_TRUNC('hour', recorded_at)
        + (EXTRACT(MINUTE FROM recorded_at)::INT / 5 * 5 || ' minutes')::INTERVAL
ORDER BY host, bucket_5m
LIMIT 40;