Autovacuum on partitioned table

From: yuzuko <yuzukohosoya(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Greg Stark <stark(at)mit(dot)edu>
Subject: Autovacuum on partitioned table
Date: 2019-12-02 09:02:42
Message-ID: CAKkQ508_PwVgwJyBY=0Lmkz90j8CmWNPUxgHvCUwGhMrouz6UA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1_autovacuum_on_partitioned_table.patch application/octet-stream 8.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Laurenz Albe 2019-12-02 09:19:01 Re: Autovacuum on partitioned table
Previous Message Alexey Bashtanov 2019-12-02 08:56:07 Re: log bind parameter values on error