Re: Autovacuum on partitioned table

On Fri, 27 Dec 2019 at 12:37, yuzuko <yuzukohosoya@gmail.com> wrote:
>
> Hi,
>
> As Laurenz commented in this thread, I tried adding option
> to update parent's statistics during Autovacuum. To do that,
> I propose supporting 'autovacuum_enabled' option already
> exists on partitioned tables.
>
> In the attached patch, you can use 'autovacuum_enabled' option
> on partitioned table as usual, that is, a default value of this option
> is true. So if you don't need autovacuum on a partitioned table,
> you have to specify the option:
> CREATE TABLE p(i int) partition by range(i) with (autovacuum_enabled=0);
>
> I'm not sure but I wonder if a suitable value as a default of
> 'autovacuum_enabled' for partitioned tables might be false.
> Because autovacuum on *partitioned tables* requires scanning
> all children to make partitioned tables' statistics.
> But if the default value varies according to the relation,
> is it confusing?  Any thoughts?

I don't look at the patch deeply yet but your patch seems to attempt
to vacuum on partitioned table. IIUC partitioned tables don't need to
be vacuumed and its all child tables are vacuumed instead if we pass
the partitioned table to vacuum() function. But autovacuum on child
tables is normally triggered since their statistics are updated.

I think it's a good idea to have that option but I think that doing
autovacuum on the parent table every time when autovacuum is triggered
on one of its child tables is very high cost especially when there are
a lot of child tables. Instead I thought it's more straight forward if
we compare the summation of the statistics of child tables (e.g.
n_live_tuples, n_dead_tuples etc) to vacuum thresholds when we
consider the needs of autovacuum on the parent table. What do you
think?

Regards,

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