Autovacuum on partitioned table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Autovacuum on partitioned table

yuzuko
Hello,

Greg reported in [1] before, autovacuum ignores partitioned tables.
That is, even if individual partitions’ statistics are updated, its parent's
statistics are not updated.  This is TODO for declarative partitioning.
As Amit mentioned in [2], a way to make parent's statistics from
partitions' statistics without scanning the partitions would be nice,
but it will need a lot of modifications.  So I tried to fix that using the
current analyze method.

The summary of the attached patch is as follows:
* If the relation is a partitioned table, check its children if they need
  vacuum or analyze.  Children need to do that are added to
  a table list for autovacuuum.  At least one child is added to the list,
  the partitioned table is also added to the list.  Then, autovacuum
  runs on all the tables in the list.
* If the partitioned table has foreign partitions, ignore them.

When the parent has children don't need vacuum/analyze or foreign
partitions, parent's stats are updated scanning the current data of all
children, so old stats and new are mixed within the partition tree.
Is that suitable?  Any thoughts?

[1] https://www.postgresql.org/message-id/CAM-w4HMQKC8hw7nB9TW3OV%2BhkB5OUcPtvr_U_EiSOjByoa-e4Q%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CA%2BHiwqEeZQ-H2OVbHZ%3Dn2RNNPF84Hygi1HC-MDwC-VnBjpA1%3DQ%40mail.gmail.com

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

v1_autovacuum_on_partitioned_table.patch (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum on partitioned table

Laurenz Albe
On Mon, 2019-12-02 at 18:02 +0900, yuzuko wrote:

> Greg reported in [1] before, autovacuum ignores partitioned tables.
> That is, even if individual partitions’ statistics are updated, its parent's
> statistics are not updated.  This is TODO for declarative partitioning.
> As Amit mentioned in [2], a way to make parent's statistics from
> partitions' statistics without scanning the partitions would be nice,
> but it will need a lot of modifications.  So I tried to fix that using the
> current analyze method.
>
> The summary of the attached patch is as follows:
> * If the relation is a partitioned table, check its children if they need
>   vacuum or analyze.  Children need to do that are added to
>   a table list for autovacuuum.  At least one child is added to the list,
>   the partitioned table is also added to the list.  Then, autovacuum
>   runs on all the tables in the list.

That means that all partitions are vacuumed if only one of them needs it,
right?  This will result in way more vacuuming than necessary.

Wouldn't it be an option to update the partitioned table's statistics
whenever one of the partitions is vacuumed?

Yours,
Laurenz Albe



Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum on partitioned table

yuzuko
Hi Laurenz,

Thanks for the comments.

On Mon, Dec 2, 2019 at 6:19 PM Laurenz Albe <[hidden email]> wrote:

>
> On Mon, 2019-12-02 at 18:02 +0900, yuzuko wrote:
> > Greg reported in [1] before, autovacuum ignores partitioned tables.
> > That is, even if individual partitions’ statistics are updated, its parent's
> > statistics are not updated.  This is TODO for declarative partitioning.
> > As Amit mentioned in [2], a way to make parent's statistics from
> > partitions' statistics without scanning the partitions would be nice,
> > but it will need a lot of modifications.  So I tried to fix that using the
> > current analyze method.
> >
> > The summary of the attached patch is as follows:
> > * If the relation is a partitioned table, check its children if they need
> >   vacuum or analyze.  Children need to do that are added to
> >   a table list for autovacuuum.  At least one child is added to the list,
> >   the partitioned table is also added to the list.  Then, autovacuum
> >   runs on all the tables in the list.
>
> That means that all partitions are vacuumed if only one of them needs it,
> right?  This will result in way more vacuuming than necessary.
>
Autovacuum runs only partitions need vacuum/analyze, so unnecessary
partitions stats are not updated.  However, to make parent's stats,
all children are scanned.  It might be a waste of time.

> Wouldn't it be an option to update the partitioned table's statistics
> whenever one of the partitions is vacuumed?
>
> Yours,
> Laurenz Albe
>

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center