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