> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Monitor application performance

> Collect counter data with counter aggregation functions that handle resets and interruptions

export const PG = 'Postgres';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

When you are monitoring application performance, there are two main types of metrics that you can collect: gauges, and
counters. Gauges fluctuate up and down, like temperature or speed, while counters always increase, like the total
number of miles travelled in a vehicle.

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 {TIMESCALE_DB} has developed aggregate and accessor functions
that handle calculations for counters in a more practical way.

<Info>
  Counter aggregates can be used in continuous aggregates, even though they are not parallelizable in {PG}. For more
  information, see the section on [parallelism and ordering][parallelism-ordering].
</Info>

For more information about counter aggregation API calls, see the [hyperfunction API documentation][counter-agg-api].

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

## 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 a table**

   ```sql theme={"dark"}
   CREATE TABLE example (
       measure_id      BIGINT,
       ts              TIMESTAMPTZ ,
       val             DOUBLE PRECISION,
       PRIMARY KEY (measure_id, ts)
   );
   ```

2. **Create a counter aggregate and the delta accessor function**

   This gives you the change in the counter's value over the time period using [`counter_agg()`][counter_agg] and
   [`delta()`][delta], accounting for any resets. This allows you to search for fifteen minute periods where the
   counter increased by a larger or smaller amount:

   ```sql theme={"dark"}
   SELECT measure_id,
       delta(
           counter_agg(ts, val)
       )
   FROM example
   GROUP BY measure_id;
   ```

3. **Use the [`time_bucket`][time_bucket] function to produce a series of deltas**

   Produce deltas over fifteen minute increments:

   ```sql theme={"dark"}
   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`][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`][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 a hypertable**

   ```sql theme={"dark"}
   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 ...][hypertable-create-table], the default partitioning
   column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
   [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
   interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_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} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

   You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
   `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
   [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

   You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

   [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

   [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

   [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

   [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

   [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

   [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

2. **Create a counter aggregate and the extrapolated delta function**

   ```sql theme={"dark"}
   with t as (
       SELECT measure_id,
           time_bucket('15 min'::interval, ts) as bucket,
           counter_agg(ts, val, toolkit_experimental.time_bucket_range('15 min'::interval, ts))
       FROM example
       GROUP BY measure_id, time_bucket('15 min'::interval, ts))
   SELECT time_bucket,
       extrapolated_delta(counter_agg, method => 'prometheus')
   FROM t ;
   ```

   <Info>
     In this procedure, `Prometheus` is used to do the extrapolation. {TIMESCALE_DB}'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.
   </Info>

## 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**

   ```sql theme={"dark"}
   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 from the continuous aggregate into a larger bucket size**

   Use [`rollup()`][rollup] to combine counter aggregates:

   ```sql theme={"dark"}
   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 {PG}.
This is because {PG} 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][gh-parallelism-ordering].

[counter_agg]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg/counter_agg

[counter-agg-api]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg

[delta]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg/delta

[extrapolated_delta]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg/extrapolated_delta

[gh-parallelism-ordering]: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md#counter-agg-ordering

[parallelism-ordering]: #parallelism-and-ordering

[rollup]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg/rollup

[time_bucket]: /api-reference/timescaledb/hyperfunctions/time-series-utilities/time_bucket

[time_bucket_range]: /api-reference/timescaledb-toolkit/counters-and-gauges/counter_agg/time_bucket_range