Counters and gauges overview

Functions for analyzing monotonic counters and gauge metrics

Since 1.3.0

Analyze counter and gauge metrics commonly found in monitoring and observability systems. These functions help you calculate rates, deltas, and trends from time-series measurements.

• Counters: Analyze data whose values are designed to monotonically increase, with any decreases treated as resets (for example, request counts, bytes sent)
• Gauges: Analyze data that can both increase and decrease (for example, temperature, memory usage, queue depth)

Two-step aggregation

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
4. Perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Samples

Calculate counter delta and rate

Create daily counter aggregates and calculate the change over each day:

WITH daily_counters AS (
    SELECT
        date_trunc('day', ts) AS day,
        counter_agg(ts, requests) AS counter_summary
    FROM metrics
    WHERE service_id = 'api-server'
    GROUP BY day
)
SELECT
    day,
    delta(counter_summary) AS daily_requests,
    rate(counter_summary) AS avg_requests_per_second
FROM daily_counters
ORDER BY day;

Calculate gauge statistics

Analyze gauge metrics to understand trends and variability:

WITH hourly_gauges AS (
    SELECT
        time_bucket('1 hour'::interval, ts) AS hour,
        gauge_agg(ts, memory_usage) AS gauge_summary
    FROM system_metrics
    WHERE host = 'web-01'
    GROUP BY hour
)
SELECT
    hour,
    delta(gauge_summary) AS memory_change,
    rate(gauge_summary) AS memory_change_rate
FROM hourly_gauges
ORDER BY hour;

Roll up and extrapolate counter values

Roll up hourly counter aggregates into daily summaries and extrapolate rates:

WITH hourly AS (
    SELECT
        time_bucket('1 hour'::interval, ts) AS hour,
        counter_agg(ts, bytes_sent, tstzrange('2024-01-01', '2024-01-02')) AS cs
    FROM network_metrics
    GROUP BY hour
),
daily AS (
    SELECT
        date_trunc('day', hour) AS day,
        rollup(cs) AS daily_cs
    FROM hourly
    GROUP BY day
)
SELECT
    day,
    extrapolated_delta(daily_cs, '1 day'::interval) AS estimated_total_bytes,
    extrapolated_rate(daily_cs, '1 day'::interval) AS estimated_avg_bytes_per_sec
FROM daily;

Available functions

Counter aggregation

• counter_agg(): analyze monotonically increasing counter metrics

Gauge aggregation

• gauge_agg(): analyze gauge metrics that can increase or decrease