This Postgres extension provides various functions for operating on arrays, for instance taking the histogram of an array of numbers.

These functions are useful because if you have a lot values you want to aggregate, queries that fetch each value from a separate row can have poor performance. Storing all the values in a single row as a Postgres array can drastically improve query performance. For instance, computing a 1000-bucket histogram on one million float values stored in separate rows took 12 seconds in a simple benchmark, compared to 27 milliseconds with the array_to_hist function.

Using arrays in this way is a bit like a poor-man's column-store database: it lets you keep all the values for one attribute in one place. (It's also a bit like how in R and Pandas you often see parallel arrays rather than arrays of objects.) To simplify a little, imagine pulling one million floats off disk in a single 8MB chunk instead of asking the drive for one million separate reads. I wouldn't use this pattern if your arrays get updated a lot, lest you take a hit on writes (At least test first!), but if they are pretty stable then using arrays can greatly speed up reads.

With such an approach you could still use SQL or PLPGSQL, but these functions outperform such code, because the Postgres C API lets you skip a lot of the work for interfacing at those higher levels. For instance, the same benchmark gave 398ms for a SQL solution and 12 seconds for a plpgsql solution. We show further benchmark results below.

Note that despite the name, these functions are not true aggregate functions (summarizing multiple rows). Rather they do aggregate-like calculations on a single input array. If you want actual aggregates that take multiple input arrays, then you might be looking for my other extension, aggs_for_vecs. If this extension takes a column-store approach to your data, that one takes a row-store approach.

This package installs like any Postgres extension. First say:

make && sudo make install

You will need to have pg_config in your path, but normally that is already the case. You can check with which pg_config.

Then in the database of your choice say:

CREATE EXTENSION aggs_for_arrays;

The available functions are described below. In general, these functions accept arrays of any integer or floating-point type, namely SMALLINT, INTEGER, BIGINT, REAL, or DOUBLE PRECISION (aka FLOAT). The return value will either be the same type (e.g. for a minimum), a FLOAT (e.g. for a mean), or an INTEGER type (e.g. for histogram bucket counts). If a function can take any numeric type, its types are shown as T.

Returns the bucket count based on the values and bucket characteristics you request.

Returns the bucket count as a 2-D array based on the values and bucket characteristics you request. The data arrays x_values and y_values must be the same length. We compare each array's first element and plot it, then their second element, etc. If either x_values or y_values is NULL, the whole result is NULL. If either contains a NULL, then that position isn't plotted.

Returns the mean of all the values in the array.

Returns the median. Does not require a pre-sorted input. If there are an even number of values, returns the mean of the two middle values.

Just like array_to_median, but assumes values is already sorted.

Returns the mode. Does not require a pre-sorted input. If there are several values tied for most common, returns their mean.

Just like array_to_mode, but assumes values is already sorted.

Returns the percentile you request, where percentile is a number from 0 to 1 inclusive. Asking for 0 will always give the minimum, 1 for maximum, and 0.5 the median. If you ask for a percentile that lands between two data points, we return a linear interpolation between them.

Just like array_to_percentile, but assumes values is already sorted.

Just like array_to_percentile, but you can pass several percentiles and get the result for each in a single call.

Just like array_to_percentiles, but assumes values is already sorted.

Returns the greatest value in the array.

Returns the least value in the array.

Returns a tuple with the min in position 1 and the max in position 2.

Computes the skewness of the given values.

Computes the kurtosis of the given values.

Assume you have two tables:

CREATE TABLE samples (
  id INTEGER PRIMARY KEY,
  measurement_id INTEGER NOT NULL,
  value FLOAT NOT NULL
);

CREATE TABLE sample_groups {
  id INTEGER PRIMARY KEY,
  measurement_id INTEGER NOT NULL,
  values FLOAT[] NOT NULL
};

These tables store the same information, but samples stores each sample in a separate row, and sample_groups stores a whole group in just one row.

You can run bench.sh to test the performance of various approaches:

• SQL on samples.
• SQL on sample_groups.
• PLPGSQL on sample_groups.
• The aggs_for_arrays function on sample_groups.

The sorted_array_to_* methods use sorted_samples and sorted_sample_groups instead.