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

# Understand hyperfunctions

> Understand hyperfunctions and how they enable complex real-time analysis on time-series data

export const HYPERFUNC_CAP = 'Hyperfunctions';

export const HYPERFUNC = 'hyperfunctions';

export const PG = 'Postgres';

export const TOOLKIT_LONG = 'TimescaleDB Toolkit';

export const CLOUD_LONG = 'Tiger Cloud';

export const TIMESCALE_DB = 'TimescaleDB';

{TIMESCALE_DB} {HYPERFUNC} are a specialized set of functions that power real-time analytics on
time series and events. IoT devices, IT systems, marketing analytics, user behavior, financial
metrics, cryptocurrency - these are only a few examples of domains where {HYPERFUNC} can make a
huge difference. {HYPERFUNC_CAP} provide you with meaningful, actionable insights in real time.

## Hyperfunctions

Real-time analytics demands more than basic SQL functions—efficient computation becomes essential as datasets grow in size and complexity. That's where {TIMESCALE_DB} {HYPERFUNC} come in: high-performance, SQL-native functions purpose-built for time-series analysis. They are designed to process, aggregate, and analyze large volumes of data with maximum efficiency while maintaining consistently high performance. With {HYPERFUNC}, you can run sophisticated analytical queries and extract meaningful insights in real time.

{HYPERFUNC_CAP} introduce partial aggregation, letting {TIMESCALE_DB} store intermediate states instead of raw data or final results. These partials can be merged later for rollups (consolidation), eliminating costly reprocessing and slashing compute overhead, especially when paired with continuous aggregates.

Take tracking p95 latency across thousands of app instances as an example:

* With standard SQL, every rollup requires rescanning and resorting massive datasets.
* With {TIMESCALE_DB}, the `percentile_agg` hyperfunction stores a compact state per minute, which you simply merge to get hourly or daily percentiles—no full reprocess needed.

![CLOUD\_LONG hyperfunctions][cloud-hyperfunctions-img]

The result? Scalable, real-time percentile analytics that deliver fast, accurate insights across high-ingest, high-resolution data, while keeping resource use lean.

{CLOUD_LONG} includes all {HYPERFUNC} by default, while self-hosted {TIMESCALE_DB} includes a
subset of them. For additional {HYPERFUNC}, install the [{TOOLKIT_LONG}][install-toolkit] {PG}
extension.

For more information, read the [{HYPERFUNC} blog post][hyperfunctions-blog].

## Common hyperfunction use cases

Learn how to use {HYPERFUNC} for specific analysis tasks:

* [Analyse data distribution][analyse-data-distribution]: use percentile approximation to understand data distribution in large datasets
* [Count distinct values efficiently][count-distinct]: use approximate count distinct to find the number of unique values, or cardinality, in a large dataset
* [Monitor application performance][counter-aggregation]: collect counter data with counter aggregation functions that handle resets and interruptions
* [Gapfilling and interpolation][gapfilling]: handle missing data when you query time-series data
* [Analyze intermittent time-series data][heartbeat]: analyze intermittent or irregular time-series data with heartbeat aggregation
* [Calculate common statistical measures][stats]: use two-step aggregation for continuous aggregates and window functions
* [Handle unevenly sampled time series data][time-weighted]: use time-weighted averages and integrals with irregularly sampled time-series data

For a complete list of all {HYPERFUNC}, see the [{HYPERFUNC} reference][api-hyperfunctions].

## Function pipelines

<Icon icon="flask" /> Early access

Function pipelines are an experimental feature, designed to radically improve how you write
queries to analyze data in {PG} and SQL. They work by applying principles from functional
programming and popular tools like Python Pandas, and PromQL.

<Warning>
  The [`timevector()`][timevector] function materializes all its data points in memory. This means that if you use
  it on a very large dataset, it runs out of memory. Do not use the `timevector()` function on a
  large dataset, or in production.
</Warning>

SQL is the best language for data analysis, but it is not perfect, and at times it can be
difficult to construct the query you want. For example, this query gets data from the last day
from the measurements table, sorts the data by the time column, calculates the delta between the
values, takes the absolute value of the delta, and then takes the sum of the result of the
previous steps:

```sql theme={"dark"}
SELECT device_id,
  sum(abs_delta) as volatility
FROM (
  SELECT device_id,
    abs(val - lag(val) OVER last_day) as abs_delta
  FROM measurements
  WHERE ts >= now()-'1 day'::interval
) calc_delta
GROUP BY device_id;
```

You can express the same query with a function pipeline like this:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
    -> toolkit_experimental.abs()
    -> toolkit_experimental.sum() as volatility
FROM measurements
WHERE ts >= now()-'1 day'::interval
GROUP BY device_id;
```

Function pipelines are completely SQL compliant, meaning that any tool that speaks SQL is able to
support data analysis using function pipelines.

### Anatomy of a function pipeline

Function pipelines are built as a series of elements that work together to create your query. The
most important part of a pipeline is a custom data type called a `timevector`. The other elements
then work on the `timevector` to build your query, using a custom operator to define the order in
which the elements are run.

### Timevectors

A [`timevector`][timevector-api] is a collection of time,value pairs with a defined start and end time, that could
look something like this:

![An example timevector](https://assets.timescale.com/docs/images/timevector.webp)

Your entire database might have time,value pairs that go well into the past and continue into the
future, but the `timevector` has a defined start and end time within that dataset, which could
look something like this:

![An example of a timevector within a larger dataset](https://assets.timescale.com/docs/images/timeseries_vector.webp)

To construct a `timevector` from your data, use a custom aggregate and pass in the columns to
become the time,value pairs. It uses a `WHERE` clause to define the limits of the subset, and a
`GROUP BY` clause to provide identifying information about the time-series. For example, to
construct a `timevector` from a dataset that contains temperatures:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
FROM measurements
WHERE ts >= now() - '1 day'::interval
GROUP BY device_id;
```

### Custom operator

Function pipelines use a single custom operator of `->`. This operator is used to apply and
compose multiple functions. The `->` operator takes the inputs on the left of the operator, and
applies the operation on the right of the operator. To put it more plainly, you can think of it as
"do the next thing."

A typical function pipeline could look something like this:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
    -> toolkit_experimental.abs()
    -> toolkit_experimental.sum() as volatility
FROM measurements
WHERE ts >= now() - '1 day'::interval
GROUP BY device_id;
```

While it might look at first glance as though `timevector(ts, val)` operation is an argument to
`sort()`, in a pipeline these are all regular function calls. Each of the calls can only operate
on the things in their own parentheses, and don't know about anything to the left of them in the
statement.

Each of the functions in a pipeline returns a custom type that describes the function and its
arguments, these are all pipeline elements. The `->` operator performs one of two different types
of actions depending on the types on its right and left sides:

* Applies a pipeline element to the left hand argument: performing the function described by the
  pipeline element on the incoming data type directly.
* Compose pipeline elements into a combined element that can be applied at some point in the
  future. This is an optimization that allows you to nest elements to reduce the number of
  passes that are required.

The operator determines the action to perform based on its left and right arguments.

### Pipeline elements

There are two main types of pipeline elements:

* Transforms change the contents of the `timevector`, returning the updated vector.
* Finalizers finish the pipeline and output the resulting data.

Transform elements take in a `timevector` and produce a `timevector`. They are the simplest
element to compose, because they produce the same type. For example:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
    -> toolkit_experimental.map($$ ($value^3 + $value^2 + $value * 2) $$)
    -> toolkit_experimental.lttb(100)
FROM measurements
```

Finalizer elements end the `timevector` portion of a pipeline. They can produce an output in a
specified format, or they can produce an aggregate of the `timevector`.

For example, a finalizer element that produces an output:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
    -> toolkit_experimental.unnest()
FROM measurements
```

Or a finalizer element that produces an aggregate:

```sql theme={"dark"}
SELECT device_id,
  toolkit_experimental.timevector(ts, val)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
    -> toolkit_experimental.time_weight()
FROM measurements
```

The third type of pipeline elements are aggregate accessors and mutators. These work on a
`timevector` in a pipeline, but they also work in regular aggregate queries. An example of using
these in a pipeline:

```sql theme={"dark"}
SELECT percentile_agg(val) -> toolkit_experimental.approx_percentile(0.5)
FROM measurements
```

For a complete list of all pipeline elements, see the [function pipeline
elements reference][pipeline-elements].

For more information about how function pipelines work, read our [blog post][blog-function-pipelines].

[analyse-data-distribution]: /manage-data/capabilities/hyperfunctions/analyse-data-distribution

[api-hyperfunctions]: /api-reference/timescaledb-toolkit

[blog-function-pipelines]: https://www.tigerdata.com/blog/function-pipelines-building-functional-programming-into-postgresql-using-custom-operators

[cloud-hyperfunctions-img]: https://assets.timescale.com/docs/images/tiger-cloud-console/percentile_agg_hyperfunction.svg

[count-distinct]: /manage-data/capabilities/hyperfunctions/approximate-count-distinct

[counter-aggregation]: /manage-data/capabilities/hyperfunctions/counter-aggregation

[gapfilling]: /manage-data/capabilities/hyperfunctions/gapfilling-and-interpolation

[gh-discussions]: https://github.com/timescale/timescaledb-toolkit/discussions

[gh-docs]: https://github.com/timescale/timescaledb-toolkit/tree/main/docs

[gh-newissue]: https://github.com/timescale/timescaledb-toolkit/issues/new?assignees=&labels=feature-request&template=feature-request.md&title=

[gh-proposed]: https://github.com/timescale/timescaledb-toolkit/labels/proposed-feature

[gh-requests]: https://github.com/timescale/timescaledb-toolkit/labels/feature-request

[heartbeat]: /manage-data/capabilities/hyperfunctions/heartbeat-aggregation

[hyperfunction]: /manage-data/reference/hyperfunctions

[hyperfunctions-blog]: https://www.tigerdata.com/blog/time-series-analytics-for-postgresql-introducing-the-timescale-analytics-project

[install-toolkit]: /deploy-and-operate/self-hosted/install-extensions/install-toolkit

[lttb]: /api-reference/timescaledb-toolkit/downsampling/lttb

[pipeline-elements]: /manage-data/reference/hyperfunction-pipeline-elements

[plotly]: https://plotly.com/chart-studio-help/json-chart-schema/

[stats]: /manage-data/capabilities/hyperfunctions/statistical-aggregation

[time-weighted]: /manage-data/capabilities/hyperfunctions/time-weighted-averages

[timevector]: /api-reference/timescaledb-toolkit/timevector/timevector

[timevector-api]: /api-reference/timescaledb-toolkit/timevector/index

[unnest]: /api-reference/timescaledb-toolkit/timevector/unnest