Re: Autovacuum on partitioned table (autoanalyze) - Mailing list pgsql-hackers

From Álvaro Herrera
Subject Re: Autovacuum on partitioned table (autoanalyze)
Date
Msg-id 202108161403.moxi73uz5zzf@alvherre.pgsql
Whole thread Raw
In response to Re: Autovacuum on partitioned table (autoanalyze)  (Álvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: Autovacuum on partitioned table (autoanalyze)
List pgsql-hackers
On 2021-Aug-13, Álvaro Herrera wrote:

> Some doc changes are pending, and some more commentary in parts of the
> code, but I think this is much more sensible.  I do lament the lack of
> a syscache for pg_inherits.

Thinking about this again, this one here is the killer problem, I think;
this behaves pretty horribly if you have more than one partition level,
because it'll have to do one indexscan *per level per partition*.  (For
example, five partitions two levels down mean ten index scans).  There's
no cache for this, and no way to disable it.  So for situations with a
lot of partitions, it could be troublesome.  Granted, it only needs to
be done for partitions with DML changes since the previous autovacuum
worker run in the affected database, but still it could be significant.

Now we could perhaps have a hash table in partition_analyze_report_ancestors()
to avoid the need for repeated indexscans for partitions of the same
hierarchy (an open-coded cache to take the place of the missing
pg_inherits syscache); and perhaps even use a single seqscan of
pg_inherits to capture the whole story first and then filter down to the
partitions that we were asked to process ... (so are we building a
mini-optimizer to determine which strategy to use in each case?).

That all sounds too much to be doing in the beta.

So I'm leaning towards the idea that we need to revert the patch and
start over for pg15.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)



pgsql-hackers by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: CI/windows docker vs "am a service" autodetection on windows
Next
From: Alvaro Herrera
Date:
Subject: Re: Some RELKIND macro refactoring