Re: parallel vacuum options/syntax

On Thu, Jan 2, 2020 at 9:09 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> Hi,
>
> I am starting a new thread for some of the decisions for a parallel vacuum in the hope to get feedback from more people. There are mainly two points for which we need some feedback.
>
> 1. Tomas Vondra has pointed out on the main thread [1] that by default the parallel vacuum should be enabled similar to what we do for Create Index. As proposed, the patch enables it only when the user specifies it (ex. Vacuum (Parallel 2) <tbl_name>;). One of the arguments in favor of enabling it by default as mentioned by Tomas is "It's pretty much the same thing we did with vacuum throttling - it's disabled for explicit vacuum by default, but you can enable it. If you're worried about VACUUM causing issues, you should set cost delay.". Some of the arguments against enabling it are that it will lead to use of more resources (like CPU, I/O) which users might or might like.
>

I'm a bit wary of making parallel vacuum enabled by default. Single
process vacuum does sequential reads/writes on most of indexes but
parallel vacuum does random access random reads/writes. I've tested
parallel vacuum on HDD and confirmed the performance is good but I'm
concerned that it might be cause of more disk I/O than user expected.

> Now, if we want to enable it by default, we need a way to disable it as well and along with that, we need a way for users to specify a parallel degree. I have mentioned a few reasons why we need a parallel degree for this operation in the email [2] on the main thread.
>
> If parallel vacuum is *not* enabled by default, then I think the current way to enable is fine which is as follows:
> Vacuum (Parallel 2) <tbl_name>;
>
> Here, if the user doesn't specify parallel_degree, then we internally decide based on number of indexes that support a parallel vacuum with a maximum of max_parallel_maintenance_workers.
>
> If the parallel vacuum is enabled by default, then I could think of the following ways:
> (a) Vacuum (disable_parallel) <tbl_name>; Vacuum (Parallel <parallel_degree>) <tbl_name>;
> (b) Vacuum (Parallel <parallel_degree>) <tbl_name>; If user specifies parallel_degree as 0, then disable parallelism.
> (c) ... Any better ideas?
>

If parallel vacuum is enabled by default, I would prefer (b) but I
don't think it's a good idea to accept 0 as parallel degree. If we
want to disable parallel vacuum we should
max_parallel_maintenance_workers to 0 instead.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services