okfn/openspending — Issue #386: Aggregation does not scale
Author: asuffield
Created: 2012-01-30 13:33:11+00:00
State: open

When $size_of_dataset * $number_of_drilldown_dimensions_selected is larger than some constant, postgresql will take so long to compute aggregates that the query will timeout. Known parameters: the 140k rows of the UK CRA can support 3 dimensions, while the 1.8m rows of the UK 25k spending can support only 1 dimension.

This is due to the need to load all the interesting columns of the dataset into memory at once for postgres to sort them for aggregation. Currently work_mem is set at 128Mb per concurrent connection. When the sort data exceeds this size then postgres falls back to merge sort on disk, which is slow. Adding more physical memory is possible, but since it must be divided by the number of concurrent queries it will rapidly get expensive.

We probably need some new ideas for aggregations. Doing them in real time in the style of the explorer is never going to work as well as we'd like on government-sized datasets.

No general solution to this class of problems is known to exist, and it is suspected that none can exist. Special-case solutions are more realistic. We need use cases for aggregations in order to figure out what users need and what they can live without, and optimise for what they need.