Re: Autovacuum on partitioned table (autoanalyze)

On 4/8/21 5:22 AM, Alvaro Herrera wrote:
> OK, I bit the bullet and re-did the logic in the way I had proposed
> earlier in the thread: do the propagation on the collector's side, by
> sending only the list of ancestors: the collector can read the tuple
> change count by itself, to add it to each ancestor. This seems less
> wasteful. Attached is v16 which does it that way and seems to work
> nicely under my testing.
>
> However, I just noticed there is a huge problem, which is that the new
> code in relation_needs_vacanalyze() is doing find_all_inheritors(), and
> we don't necessarily have a snapshot that lets us do that. While adding
> a snapshot acquisition at that spot is a very easy fix, I hesitate to
> fix it that way, because the whole idea there seems quite wasteful: we
> have to look up, open and lock every single partition, on every single
> autovacuum iteration through the database. That seems bad. I'm
> inclined to think that a better idea may be to store reltuples for the
> partitioned table in pg_class.reltuples, instead of having to add up the
> reltuples of each partition. I haven't checked if this is likely to
> break anything.
>

How would that value get updated, for the parent?

> (Also, a minor buglet: if we do ANALYZE (col1), then ANALYZE (col2) a
> partition, then we repeatedly propagate the counts to the parent table,
> so we would cause the parent to be analyzed more times than it should.
> Sounds like we should not send the ancestor list when a column list is
> given to manual analyze. I haven't verified this, however.)
>

Are you sure? I haven't tried, but shouldn't this be prevented by only
sending the delta between the current and last reported value?

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company