aggs_for_arrays

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.

Installing

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;

Functions

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.

INTEGER[] array_to_hist(values T[], bucket_start T, bucket_width T, bucket_count INTEGER)

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

INTEGER[] array_to_hist_2d(x_values T[], y_values T[], x_bucket_start T, y_bucket_start T, x_bucket_width T, y_bucket_width T, x_bucket_count INTEGER, y_bucket_count INTEGER)

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.

FLOAT array_to_mean(values T[])

Returns the mean of all the values in the array.

FLOAT array_to_median(values T[])

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.

FLOAT sorted_array_to_median(values T[])

Just like array_to_median, but assumes values is already sorted.

FLOAT array_to_mode(values T[])

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

FLOAT sorted_array_to_mode(values T[])

Just like array_to_mode, but assumes values is already sorted.

FLOAT array_to_percentile(values T[], percentile FLOAT)

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.

FLOAT sorted_array_to_percentile(values T[], percentile FLOAT)

Just like array_to_percentile, but assumes values is already sorted.

FLOAT[] array_to_percentiles(values T[], percentiles FLOAT[])

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

FLOAT[] sorted_array_to_percentiles(values T[], percentiles FLOAT[])

Just like array_to_percentiles, but assumes values is already sorted.

T array_to_max(values T[])

Returns the greatest value in the array.

T array_to_min(values T[])

Returns the least value in the array.

T[] array_to_min_max(values T[])

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

FLOAT array_to_skewness(values T[])

Computes the skewness of the given values.

FLOAT array_to_kurtosis(values T[])

Computes the kurtosis of the given values.

Benchmarks

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.

| function | SQL row-based | SQL array-based | PLPGSQL array-based | aggs_for_arrays | |:——————————|–––––––:|––––––––:|––––––––––:|——————:| | array_to_hist | 12218.1 ms | 398.235 ms | 12310.800 ms | 26.936 ms | | array_to_mean | 10630.0 ms | 121.677 ms | 390.983 ms | 25.226 ms | | array_to_median | 33587.0 ms | 1163.070 ms | 1258.160 ms | 47.996 ms | | sorted_array_to_median | 23239.5 ms | 30.107 ms | 41.225 ms | 14.835 ms | | array_to_mode | 13724.1 ms | 1505.310 ms | 1552.610 ms | 201.943 ms | | sorted_array_to_mode | 13195.2 ms | 1474.130 ms | 1577.770 ms | 45.171 ms | | array_to_percentile | 24218.2 ms | 2591.240 ms | 1698.570 ms | 179.916 ms | | sorted_array_to_percentile | 24305.5 ms | 2102.520 ms | 1204.140 ms | 21.947 ms | | array_to_percentiles | 32367.0 ms | 10735.300 ms | 3608.800 ms | 188.752 ms | | sorted_array_to_percentiles | 32294.3 ms | 10153.300 ms | 3120.830 ms | 22.227 ms | | array_to_max | 10613.2 ms | 115.094 ms | 398.791 ms | 17.321 ms | | array_to_min | 10600.5 ms | 113.859 ms | 400.926 ms | 17.204 ms | | array_to_min_max | 10727.9 ms | 169.226 ms | 824.539 ms | 23.922 ms | | array_to_skewness | 22267.2 ms | 802.463 ms | 1077.630 ms | 120.925 ms | | array_to_kurtosis | 22253.1 ms | 806.296 ms | 1075.960 ms | 112.210 ms |

Development

These tests follow the PGXS and pg_regress framework used for Postgres extensions, including Postgres’s own contrib package. To run the tests, first install the extension somewhere then say make installcheck. You can use standard libpq envvars to control the database connection, e.g. PGPORT=5436 make installcheck.