Re: parallel vacuum options/syntax

On Fri, 3 Jan 2020 at 08:51, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Thu, Jan 2, 2020 at 5:39 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.
> >
> > 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?
>
> IMHO, it's better to keep the parallelism enables by default. Because
> if the user is giving an explicit vacuum then better to keep it fast
> by default. However, I agree that we can provide an option for the
> user to disable it and provide the parallel degree with the vacuum
> command something like option (b).

+1

Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com