Gauge aggregation overview

Analyze data coming from gauges. Unlike counters, gauges can decrease as well as increase.

If your value can only increase, use counter_agg instead to appropriately account for resets.

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

Analyze gauge metrics

Create hourly gauge aggregates and calculate changes:

SELECT
    time_bucket('1 hour'::interval, ts) AS hour,
    gauge_agg(ts, temperature) AS gauge_summary
FROM sensors
WHERE location = 'warehouse'
GROUP BY hour;

Calculate the delta and rate of change for gauge values:

WITH hourly AS (
    SELECT
        time_bucket('1 hour'::interval, ts) AS hour,
        gauge_agg(ts, temperature) AS gauge_summary
    FROM sensors
    WHERE location = 'warehouse'
    GROUP BY hour
)
SELECT
    hour,
    delta(gauge_summary) AS temp_change,
    rate(gauge_summary) AS temp_change_rate
FROM hourly
ORDER BY hour;

Available functions

Aggregate

• gauge_agg(): aggregate gauge data into an intermediate form for further analysis

Accessors

• corr(): calculate the correlation coefficient from a gauge aggregate
• delta(): calculate the change in a gauge’s value
• extrapolated_delta(): estimate the total change in a gauge over a time period
• extrapolated_rate(): estimate the average rate of change over a time period
• gauge_zero_time(): calculate the time when a gauge value was zero
• idelta_left(): calculate the instantaneous change at the left boundary
• idelta_right(): calculate the instantaneous change at the right boundary
• intercept(): calculate the y-intercept from a gauge aggregate
• interpolated_delta(): calculate the change over a specific time range with interpolation
• interpolated_rate(): calculate the rate of change over a specific time range with interpolation
• irate_left(): calculate the instantaneous rate at the left boundary
• irate_right(): calculate the instantaneous rate at the right boundary
• num_changes(): get the number of times the gauge changed value
• num_elements(): get the number of points in a gauge aggregate
• rate(): calculate the average rate of change
• slope(): calculate the slope from a gauge aggregate
• time_delta(): calculate the elapsed time in a gauge aggregate

Rollup

• rollup(): combine multiple gauge aggregates

Mutator

• with_bounds(): add time bounds to a gauge aggregate for extrapolation