Scaling PostgreSQL: Using more than just one core for aggregation

Scaling PostgreSQL: Modern servers provide people with more and more CPU cores. 16 cores or more are not uncommon these days. Unfortunately, PostgreSQL cannot scale a single query to more than one core. A lot of effort is going into this issue already.

CYBERTEC experiments

To address the issue, we've done some experiments to see what can be done to scale things up. At this point, we've written a small module which takes an existing execution plan and does some post-processing of the plan using “custom plan nodes”.

The idea is experimental: what happens if the approach described in this post is actually used?

The main challenge we face at a client is:

So the goal in our case is really to scale out the processing done in the SELECT clause.

Some test data for scaling

For a start, 100 million rows have been created to do a little testing. 20 million rows are in each partition:

Giving multicore PostgreSQL a try - scaling test

Our prototype module has a simple runtime variable, which allows us to define the number of processes we want to see. Setting parallel.agg_workers to 1 basically means turning the plan-processing code off.

The data in the table is scanned normally. We need around 29 seconds to process the query:

Let us try the same with more cores: 7 cores will be used for worker processes.

The 8^th core will be taken by the coordinating process:

The important part here is that more processes are actually a net loss. The reason is simple: The table is scanned by a single process at the moment so there is a serious bottleneck on the scan side. The aggregation part is simply not complex enough to provide us with a speedup. Remember: All we do is adding up some values (which is ways too simple). This is by far not enough to justify the overhead of moving data around via shared memory queues.

Handling long SELECT clauses when scaling

However, the situation starts to change significantly if we try a query involving a lot of processing in the SELECT-clause. The more stuff we do in the SELECT-clause, the more benefit we can observe.

Here is an example:

In this case, we do a lot of aggregation and therefore the overhead of moving tuples around is not as big compared to the rest of the query.

The performance difference is already quite huge:

What we see is a nice 3.5 times speedup. Of course, this is still far from linear. Linear scalability can hardly be achieved with this approach. However, if there are 50-100 aggregation functions in the SELECT-clause (which is far from uncommon), there is clearly a major performance improvement. That tends to get pretty close to “linear” in many cases.

At the moment, actually providing the data to our worker processes is still clearly an issue:

The process dispatching the data is running at 100%, while the worker processes are not able to make full use of the CPU. It seems that some overhead can still be shaved off to provide users with better overall performance. A prototype might be possible fairly soon.

In case you need any assistance, please feel free to contact us.
 

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.