Re: Autovacuum on partitioned table (autoanalyze)

Hi,

On 2021-08-11 18:33:07 -0400, Alvaro Herrera wrote:
> After thinking about the described issues for a while, my proposal is to
> completely revamp the way this feature works. See below.
>
> Now, the proposal seems awfully invasive, but it's *the* way I see to
> avoid the pgstat traffic. For pg14, maybe we can live with it, and just
> use the smaller patches that Horiguchi-san and I have posted, which
> solve the other issues; also, Euler Taveira suggested that we could add
> a reloption to turn the feature off completely for some tables (maybe
> make it off by default and have a reloption to turn it on for specific
> partition hierarchies), so that it doesn't cause unduly pain for people
> with large partitioning hierarchies.

I think we should revert the changes for 14 - to me the feature clearly isn't
mature enough to be released.

> * PgStat_StatTabEntry gets a new "Oid reportAncestorOid" member. This is
> the OID of a single partitioned ancestor, to which the changed-tuple
> counts are propagated up.
> Normally this is the topmost ancestor; but if the user wishes some
> intermediate ancestor to receive the counts they can use
> ALTER TABLE the_intermediate_ancestor SET (autovacuum_enabled=on).
>
> * Corollary 1: for the normal case of single-level partitioning, the
> parent partitioned table behaves as currently.
>
> * Corollary 2: for multi-level partitioning with no especially
> configured intermediate ancestors, only the leaf partitions and the
> top-level partitioned table will be analyzed. Intermediate ancestors
> are ignored by autovacuum.
>
> * Corollary 3: for multi-level partitioning with some intermediate
> ancestor(s) marked as autovacuum_enabled=on, that ancestor will
> receive all the counts from all of its partitions, so it will get
> analyzed itself; and it'll also forward those counts up to its
> report-ancestor.

This seems awfully confusing to me.

One fundamental issue here is that we separately build stats for partitioned
tables and partitions. Can we instead tackle this by reusing the stats for
partitions for the inheritance stats? I think it's a bit easier to do that
for partitioned tables than for old school inheritance roots, because there's
no other rows in partitioned tables.

Greetings,

Andres Freund