Understanding PostgreSQL Aggregation and Hyperfunctions’ Design

Why we use the two-step aggregate design pattern

There are four basic reasons we expose the two-step aggregate design pattern to users rather than leave it as an internal structure (and the last two helped us build our continuous aggregates):

1. Allow multi-parameter aggregates to re-use state, making them more efficient.
2. Cleanly distinguish between parameters that affect aggregates vs. accessors, making performance implications easier to understand and predict.
3. Enable easy-to-understand rollups, with logically consistent results, in continuous aggregates and window functions (one of our most common requests on continuous aggregates).
4. Allow easier retrospective analysis of downsampled data in continuous aggregates as requirements change, but the data is already gone.

That’s a little theoretical, so let’s dive in and explain each one.

Efficiency of Two-Step Aggregates

Re-using state

PostgreSQL is very good at optimizing statements (as we saw earlier in this post through pictures 🙌), but you have to give it things in a way it can understand.

For instance, when we talked about deduplication, we saw that PostgreSQL could “figure out” when a statement occurs more than once in a query (i.e., avg(bar)) and only run the statement a single time to avoid redundant work:

SELECT avg(bar), avg(bar) / 2 AS half_avg FROM foo;

This works because the avg(bar) occurs multiple times without variation.

However, if I write the equation in a slightly different way and move the division inside the parentheses so that the expression avg(bar) doesn’t repeat so neatly, PostgreSQL can’t figure out how to optimize it:

SELECT avg(bar), avg(bar / 2) AS half_avg FROM foo;

It doesn’t know that the division is commutative or that those two queries are equivalent.

This is a complicated problem for database developers to solve, and thus, as a PostgreSQL user, you need to make sure to write your query in a way that the database can understand.

Performance problems caused by equivalent statements that the database doesn’t understand are equal (or that are equal in the specific case you wrote but not in the general case) can be some of the trickiest SQL optimizations to figure out as a user.

Therefore, when we design our APIs, we try to make it hard for users to write low-performance code unintentionally: in other words, the default option should be the high-performance option.

For the next bit, it’ll be useful to have a simple table defined as:

CREATE TABLE foo(
	ts timestamptz, 
	val DOUBLE PRECISION);

Let’s look at an example of how we use two-step aggregation in the percentile approximation hyperfunction to allow PostgreSQL to optimize performance.

SELECT 
    approx_percentile(0.1, percentile_agg(val)) as p10, 
    approx_percentile(0.5, percentile_agg(val)) as p50, 
    approx_percentile(0.9, percentile_agg(val)) as p90 
FROM foo;

...is treated as the same as:

SELECT 
    approx_percentile(0.1, pct_agg) as p10, 
    approx_percentile(0.5, pct_agg) as p50, 
    approx_percentile(0.9, pct_agg) as p90 
FROM 
(SELECT percentile_agg(val) as pct_agg FROM foo) pct;

This calling convention allows us to use identical aggregates so that, under the hood, PostgreSQL can deduplicate calls to the identical aggregates (and is faster as a result).

Now, let’s compare this to the one-step aggregate approach.

PostgreSQL can’t deduplicate aggregate calls here because the extra parameter in the approx_percentile aggregate changes with each call:

So, even though all of those functions could use the same approximation built up over all the rows, PostgreSQL has no way of knowing that. The two-step aggregation approach enables us to structure our calls so that PostgreSQL can optimize our code, and it enables developers to understand when things will be more expensive and when they won't. Multiple different aggregates with different inputs will be expensive, whereas multiple accessors to the same aggregate will be much less expensive.

Cleanly distinguishing between aggregate/accessor parameters

We also chose the two-step aggregate approach because some of our aggregates can take multiple parameters or options themselves, and their accessors can also take options:

SELECT
    approx_percentile(0.5, uddsketch(1000, 0.001, val)) as median,--1000 buckets, 0.001 target err
    approx_percentile(0.9, uddsketch(1000, 0.001, val)) as p90, 
    approx_percentile(0.5, uddsketch(100, 0.01, val)) as less_accurate_median -- modify the terms for the aggregate get a new approximation
FROM foo;

That’s an example of uddsketch, an advanced aggregation method for percentile approximation that can take its own parameters.

Imagine if the parameters were jumbled together in one aggregate:

-- NB: THIS IS AN EXAMPLE OF AN API WE DECIDED NOT TO USE, IT DOES NOT WORK
SELECT
    approx_percentile(0.5, 1000, 0.001, val) as median
FROM foo;

It’d be pretty difficult to understand which argument is related to which part of the functionality.

Conversely, the two-step approach separates the arguments to the accessor vs. aggregate very cleanly, where the aggregate function is defined in parenthesis within the inputs of our final function:

SELECT
    approx_percentile(0.5, uddsketch(1000, 0.001, val)) as median
FROM foo;

By making it clear which is which, users can know that if they change the inputs to the aggregate, they will get more (costly) aggregate nodes, =while inputs to the accessor are cheaper to change.

So, those are the first two reasons we expose the API—and what it allows developers to do as a result. The last two reasons involve continuous aggregates and how they relate to hyperfunctions, so first, a quick refresher on what they are.

Continuous Aggregates and Two-Step Design in TimescaleDB

TimescaleDB includes a feature called continuous aggregates, which are designed to make queries on very large datasets run faster. TimescaleDB's continuous aggregates continuously and incrementally store the results of an aggregation query in the background, so when you run the query, only the data that has changed needs to be computed, not the entire dataset.

In our discussion of the combine function above, we covered how you could take the expensive work of computing the transition function over every row and split the rows over multiple parallel aggregates to speed up the calculation.

TimescaleDB continuous aggregates do something similar, except they spread the computation work over time rather than between parallel processes running simultaneously. The continuous aggregate computes the transition function over a subset of rows inserted some time in the past, stores the result, and then, at query time, we only need to compute over the raw data for a small section of recent time that we haven’t yet calculated.

When we designed TimescaleDB hyperfunctions, we wanted them to work well within continuous aggregates and even open new possibilities for users.

Let’s say I create a continuous aggregate from the simple table above to compute the sum, average, and percentile (the latter using a hyperfunction) in 15-minute increments:

CREATE MATERIALIZED VIEW foo_15_min_agg
WITH (timescaledb.continuous)
AS SELECT id,
    time_bucket('15 min'::interval, ts) as bucket,
    sum(val),
    avg(val),
    percentile_agg(val)
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts);

And then what if I come back and I want to re-aggregate it to hours or days rather than 15-minute buckets—or need to aggregate my data across all IDs? Which aggregates can I do that for, and which can’t I?

Logically consistent rollups

One of the problems we wanted to solve with two-step aggregation was how to convey to the user when it is “okay” to re-aggregate and when it’s not. (By “okay,” I mean you would get the same result from the re-aggregated data as you would running the aggregate on the raw data directly.)

For instance:

SELECT sum(val) FROM tab;
-- is equivalent to:
SELECT sum(sum) 
FROM 
    (SELECT id, sum(val) 
    FROM tab
    GROUP BY id) s;

But:

SELECT avg(val) FROM tab;
-- is NOT equivalent to:
SELECT avg(avg) 
FROM 
    (SELECT id, avg(val) 
    FROM tab
    GROUP BY id) s;

Why is re-aggregation okay for sum but not for avg?

Technically, it’s logically consistent to re-aggregate when:

• The aggregate returns the internal aggregate state. The internal aggregate state for sum is (sum), whereas for average, it is (sum, count).
• The aggregate’s combine and transition functions are equivalent. For sum(), the states and the operations are the same. For count(), the states are the same, but the transition and combine functions perform different operations on them. sum()’s transition function adds the incoming value to the state, and its combine function adds two states together or a sum of sums. Conversely, count()s transition function increments the state for each incoming value, but its combine function adds two states together, or a sum of counts.

But, you have to have in-depth (and sometimes rather arcane) knowledge about each aggregate’s internals to know which ones meet the above criteria—and, therefore, which ones you can re-aggregate.

With the two-step aggregate approach, we can convey when it is logically consistent to re-aggregate by exposing our equivalent of the combine function when the aggregate allows it.

We call that function rollup(). Rollup() takes multiple inputs from the aggregate and combines them into a single value.

All of our aggregates that can be combined have rollup functions that will combine the output of the aggregate from two different groups of rows. (Technically, rollup() is an aggregate function because it acts on multiple rows. For clarity, I’ll call them rollup functions to distinguish them from the base aggregate). Then you can call the accessor on the combined output!

So using that continuous aggregate we created to get a 1-day re-aggregation of our percentile_agg becomes as simple as:

SELECT id, 
    time_bucket('1 day'::interval, bucket) as bucket, 
    approx_percentile(0.5, rollup(percentile_agg)) as median
FROM foo_15_min_agg
GROUP BY id, time_bucket('1 day'::interval, bucket);

(We actually suggest that you create your continuous aggregates without calling the accessor function for this very reason. Then, you can just create views over top or put the accessor call in your query).

This brings us to our final reason.

Retrospective analysis using continuous aggregates

When we create a continuous aggregate, we’re defining a view of our data that we could then be stuck with for a very long time.

For example, we might have a data retention policy that deletes the underlying data after X time period. If we want to go back and re-calculate anything, it can be challenging, if not impossible, since we’ve “dropped” the data.

But we understand that in the real world, you don’t always know what you’re going to need to analyze ahead of time.

Thus, we designed hyperfunctions to use the two-step aggregate approach so they would better integrate with continuous aggregates. As a result, users store the aggregate state in the continuous aggregate view and modify accessor functions without requiring them to recalculate old states that might be difficult (or impossible) to reconstruct (because the data is archived, deleted, etc.).

The two-step aggregation design also allows for much greater flexibility with continuous aggregates. For instance, let’s take a continuous aggregate where we do the aggregate part of the two-step aggregation like this:

CREATE MATERIALIZED VIEW foo_15_min_agg
WITH (timescaledb.continuous)
AS SELECT id,
    time_bucket('15 min'::interval, ts) as bucket,
    percentile_agg(val)
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts);

When we first create the aggregate, we might only want to get the median:

SELECT
    approx_percentile(0.5, percentile_agg) as median
FROM foo_15_min_agg;

But then, later, we decided we wanted to know the 95th percentile as well.

Luckily, we don’t have to modify the continuous aggregate; we just modify the parameters to the accessor function in our original query to return the data we want from the aggregate state:

SELECT
    approx_percentile(0.5, percentile_agg) as median,
    approx_percentile(0.95, percentile_agg) as p95
FROM foo_15_min_agg;

And then, if a year later, we want the 99th percentile as well, we can do that too:

SELECT
    approx_percentile(0.5, percentile_agg) as median,
    approx_percentile(0.95, percentile_agg) as p95,
    approx_percentile(0.99, percentile_agg) as p99
FROM foo_15_min_agg;

That’s just scratching the surface. Ultimately, our goal is to provide a high level of developer productivity that enhances other PostgreSQL and TimescaleDB features, like aggregate deduplication and continuous aggregates.

Example: Time-Weighted Average

To illustrate how the two-step aggregate design pattern impacts how we think about and code hyperfunctions, let’s look at the time-weighted average family of functions. (Our What Time-Weighted Averages Are and Why You Should Care post provides a lot of context for this next bit, so if you haven’t read it, we recommend doing so. You can also skip this next bit for now.)

The equation for the time-weighted average is as follows:

\begin{equation} time\_weighted\_average = \frac{area\_under\_curve}{ \Delta T} \end{equation}

As we noted in the table above:

• time_weight() is TimescaleDB hyperfunctions’ aggregate and corresponds to the transition function in PostgreSQL’s internal API.
• average() is the accessor, which corresponds to the PostgreSQL final function.
• rollup() for re-aggregation corresponds to the PostgreSQL combine function.

The time_weight() function returns an aggregate type that has to be usable by the other functions in the family.

In this case, we decided on a TimeWeightSummary type that is defined like so (in pseudocode):

TimeWeightSummary = (w_sum, first_pt, last_pt)

w_sum is the weighted sum (another name for the area under the curve), and first_pt and last_pt are the first and last (time, value) pairs in the rows that feed into the time_weight() aggregate.

Here’s a graphic depiction of those elements, which builds on our how to derive a time-weighted average theoretical description:

So, the time_weight() aggregate does all of the calculations as it receives each of the points in our graph and builds a weighted sum for the time period (ΔT) between the first and last points it “sees.” It then outputs the TimeWeightSummary.

The average() accessor function performs simple calculations to return the time-weighted average from the TimeWeightSummary (in pseudocode where pt.time() returns the time from the point):

func average(TimeWeightSummary tws) 
	-> float {
		delta_t = tws.last_pt.time - tws.first_pt.time;
		time_weighted_average = tws.w_sum / delta_t;
		return time_weighted_average;
	}

But, as we built the time_weight hyperfunction, ensuring the rollup() function worked as expected was a little more difficult – and introduced constraints that impacted the design of our TimeWeightSummary data type.

To understand the rollup function, let’s use our graphical example and imagine the time_weight() function returns two TimeWeightSummaries from different regions of time like so:

The rollup() function needs to take in and return the same TimeWeightSummary data type so that our average() accessor can understand it. (This mirrors how PostgreSQL’s combined function takes in two states from the transition function and then returns a single state for the final function to process.)

We also want the rollup() output to be the same as if we had computed the time_weight() over all the underlying data. The output should be a TimeWeightSummary representing the full region.

The TimeWeightSummary we output should also account for the area in the gap between these two weighted sum states:

The gap area is easy to get because we have the last₁ and first₂ points—and it’s the same as the w_sum we’d get by running the time_weight() aggregate on them.

Thus, the overall rollup() function needs to do something like this (where w_sum() extracts the weighted sum from the TimeWeightSummary):

func rollup(TimeWeightSummary tws1, TimeWeightSummary tws2) 
	-> TimeWeightSummary {
		w_sum_gap = time_weight(tws1.last_pt, tws2.first_pt).w_sum;
		w_sum_total = w_sum_gap + tws1.w_sum + tws2.w_sum;
		return TimeWeightSummary(w_sum_total, tws1.first_pt, tws2.last_pt);
	}

Graphically, that means we’d end up with a single TimeWeightSummary representing the whole area:

So that’s how the two-step aggregate design approach ends up affecting the real-world implementation of our time-weighted average hyperfunctions. The above explanations are a bit condensed, but they should give you a more concrete look at how time_weight() aggregate, average() accessor, and rollup() functions work.

Summing It Up

Now that you’ve gotten a tour of the PostgreSQL aggregate API, how it inspired us to make the TimescaleDB hyperfunctions two-step aggregate API, and a few examples of how this works in practice, we hope you'll try it out yourself and tell us what you think :).

If you're currently dealing with gigantic databases, remember that you can always tier your older, infrequently accessed data to keep things running smoothly without breaking the bank—we built the perfect solution for this with our Tiered Storage architecture backend. Check it out!

If you'd like to keep learning about Postgres and its community, visit our State of PostgreSQL 2023 report, which is full of insights on how people around the world use PostgreSQL.

Going back to hyperfunctions, to get started right away, spin up a fully managed Timescale service and try it for free. Hyperfunctions are pre-loaded on each new database service on Timescale, so after you create a new service, you’re all set to use them!