I try to complete at least one significant feature item per PostgreSQL release. This time the feature is making pg_restore run in parallel. This is quite important for many users, particularly some large enterprise users.
It's important that people understand what this will do and what it won't do. pg_restores runs a number of steps. In conventional mode it simply runs them all in a single connection to the database, one after the other. In parallel mode it first runs all the quick and easy steps, essentially those that don't involve any data access, such as table and function creation, in a single connection, just like conventional mode. Then it runs the remaining steps each in its own connection. The steps are the same, and there is no parallelism within a given step. For example, a single COPY to a table is not parallelised. Rather, we run it in parallel with other data intensive steps.
The maximum amount of parellelism is controlled by the user. This will involve some experimentation to get to the sweet spot for your setup. A good place to start is the number of physical processors you have available. The idea here is to improve the situation where the CPU is the limiting factor, and allow you to drive the restoration rate up to where IO is in fact the limiting factor. With very high end hardware we believe that you can drive the parallelism quite high.
Like many performance features, this one might well require several releases to tweak it for optimal performance gain. The program works by keeping a pool of slots to be used for the steps that are run in parallel. One possible area for improvement is in the algorithm that selects the item to be used for a slot as it it becomes available. Currently we keep a queue of items that have no remaining unrestored dependencies. An item gets put on the queue as soon as all the items it depends on have been restored. This is likely to be a fairly good approximation of an optimal algorithm, but there might well be a way of tweaking it. Another possible area of optimsation would be to take some notice of the tablespace that each item affects, and try to balance these, so we use as many IO channels as possible.
What is important is that we have now got the basic framework of parallel restore, so that some researchers can easily experiment with various tweaks to improve the performance.
pg_restore is going to be with us for quite a long time. Even if we manage to get pg_upgrade working pretty well, that will take quite a bit of time, and there is currently no guarantee that it will for for every release. So I expect pg_restore to be the most common method of upgrading for quite some time, making it run as fast as possible is thus still a significant requirement.
I'm proud to have been able to contribute this feature to Postgres, and look forward to other people improving it further as time goes by.
