Autovacuum on partitioned table
yuzuko <yuzukohosoya@gmail.com>
From: yuzuko <yuzukohosoya@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Greg Stark <stark@mit.edu>
Date: 2019-12-02T09:02:42Z
Lists: pgsql-hackers
Attachments
- v1_autovacuum_on_partitioned_table.patch (application/octet-stream) patch v1
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
Commits
-
Keep stats up to date for partitioned tables
- e1efc5b465c8 14.0 landed
- 375aed36ad83 15.0 landed
-
Revert analyze support for partitioned tables
- b3d24cc0f0aa 14.0 landed
- 6f8127b73901 15.0 landed
-
Document ANALYZE storage parameters for partitioned tables
- 41badeaba8be 14.0 landed
-
autovacuum: handle analyze for partitioned tables
- 0827e8af70f4 14.0 landed