Re: Autovacuum on partitioned table (autoanalyze)

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: yuzuko <yuzukohosoya(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, David Steele <david(at)pgmasters(dot)net>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Daniel Gustafsson <daniel(at)yesql(dot)se>, Amit Langote <amitlangote09(at)gmail(dot)com>, Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Greg Stark <stark(at)mit(dot)edu>
Subject: Re: Autovacuum on partitioned table (autoanalyze)
Date: 2021-04-08 03:22:35
Message-ID: 20210408032235.GA6842@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

(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.)

Álvaro Herrera Valdivia, Chile
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.

Attachment Content-Type Size
v16_autovacuum_on_partitioned_table.patch text/x-diff 21.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2021-04-08 03:24:58 Re: Set access strategy for parallel vacuum workers
Previous Message Masahiko Sawada 2021-04-08 03:22:09 Re: Set access strategy for parallel vacuum workers