Counter aggregation

Application telemetry usually distinguishes gauges (values that move up and down) from counters (monotonic totals like requests served). Counter aggregation focuses on the second case: detect resets, stitch segments, and keep a trustworthy running total even when exporters restart.

When you process counter data, it is usually assumed that if the value of the counter goes down, the counter has been reset. For example, if you wanted to count the total number of miles travelled in a vehicle, you would expect the values to continuously increase: 1, 2, 3, 4, and so on. If the counter reset to 0, you would expect that this was a new trip, or an entirely new vehicle. This can become a problem if you want to continue counting from where you left off, rather than resetting to 0. A reset could occur if you have had a short server outage, or any number of other reasons. To get around this, you can analyze counter data by looking at the change over time, which accounts for resets.

Accounting for resets can be difficult to do in SQL, so TimescaleDB has developed aggregate and accessor functions that handle calculations for counters in a more practical way.

Note

Counter aggregates can be used in continuous aggregates, even though they are not parallelizable in PostgreSQL. For more information, see the section on parallelism and ordering.

For more information about counter aggregation API calls, see the hyperfunction API documentation.

Run a counter aggregate query using a delta function

In this procedure, we are using an example table called example that contains counter data.

1. Create the table

   CREATE TABLE example (
       measure_id      BIGINT,
       ts              TIMESTAMPTZ ,
       val             DOUBLE PRECISION,
       PRIMARY KEY (measure_id, ts)
   );

2. Create the counter aggregate with a delta accessor

   This gives you the change in the counter’s value over the time period, accounting for any resets. This allows you to search for fifteen minute periods where the counter increased by a larger or smaller amount:

   SELECT measure_id,
       delta(
           counter_agg(ts, val)
       )
   FROM example
   GROUP BY measure_id;

3. Use time_bucket for series of deltas

   You can also use the time_bucket function to produce a series of deltas over fifteen minute increments:

   SELECT measure_id,
       time_bucket('15 min'::interval, ts) as bucket,
       delta(
           counter_agg(ts, val)
       )
   FROM example
   GROUP BY measure_id, time_bucket('15 min'::interval, ts);

Run a counter aggregate query using an extrapolated delta function

If your series is less regular, the deltas are affected by the number of samples in each fifteen minute period. You can improve this by using the extrapolated_delta function. To do this, you need to provide bounds that define where to extrapolate to. In this example, we use the time_bucket_range function, which works in the same way as time_bucket but produces an open ended range of all the times in the bucket. This example also uses a CTE to do the counter aggregation, which makes it a little easier to understand what’s going on in each part.

1. Create the hypertable

   CREATE TABLE example (
       measure_id      BIGINT,
       ts              TIMESTAMPTZ ,
       val             DOUBLE PRECISION,
       PRIMARY KEY (measure_id, ts)
   ) WITH (
     tsdb.hypertable,
     tsdb.chunk_interval='15 days'
   );

   When you create a hypertable using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk_interval, defined through after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

   You can also manually convert chunks in a hypertable to the columnstore.

2. Create the counter aggregate with extrapolated delta

   SELECT
       measure_id,
       time_bucket('15 min'::interval, ts) as bucket,
       extrapolated_delta(
           counter_agg(ts, val, toolkit_experimental.time_bucket_range('15 min'::interval, ts)),
           method => 'prometheus'
       ) as delta
   FROM example
   GROUP BY
       measure_id,
       time_bucket('15 min'::interval, ts);

   Note

   In this procedure, Prometheus is used to do the extrapolation. TimescaleDB’s current extrapolation function is built to mimic the Prometheus project’s increase function, which measures the change of a counter extrapolated to the edges of the queried region.

Run a counter aggregate query with a continuous aggregate

Your counter aggregate might be more useful if you make a continuous aggregate out of it.

1. Create the continuous aggregate

   CREATE MATERIALIZED VIEW example_15
   WITH (timescaledb.continuous)
   AS SELECT measure_id,
       time_bucket('15 min'::interval, ts) as bucket,
       counter_agg(ts, val, time_bucket_range('15 min'::interval, ts))
   FROM example
   GROUP BY measure_id, time_bucket('15 min'::interval, ts);

2. Re-aggregate into a larger bucket size

   SELECT
       measure_id,
       time_bucket('1 day'::interval, bucket),
       delta(
           rollup(counter_agg)
       )
   FROM example_15
   GROUP BY measure_id, time_bucket('1 day'::interval, bucket);

Parallelism and ordering

The counter reset calculations require a strict ordering of inputs, which means they are not parallelizable in PostgreSQL. This is because PostgreSQL handles parallelism by issuing rows randomly to workers. However, if your parallelism can guarantee sets of rows that are disjointed in time, the algorithm can be parallelized, as long as it is within a time range, and all rows go to the same worker. This is the case for both continuous aggregates and for distributed hypertables, as long as the partitioning keys are in the group by, even though the aggregate itself doesn’t really make sense otherwise.

For more information about parallelism and ordering, see the developer documentation.