run_maintenance taking too long approx 2-4 minutes when running on a large parent table

[doshimunjal]

Hello,
pg_partman run_maintenance job is taking too long 2-4 minutes when running on a large parent table. We are using PG 11+, pg_partman version 4.0.0 and time based native partitioning on daily basis.

There is premake of 4 future partition tables and run_maintenance runs on daily basis to make sure 4 future partitions are there.

Since its pg11, analyze shouldn't be running within run_maintenance then why its taking too long to create just child partition table.

[keithf4]

Do you have any long running transactions running on these partition sets? Adding/dropping child tables does require a brief lock in order to complete the operation. You can check the pg_stat_activity view and see if the maintenance operation is being held up by locks by looking at the wait_event_type and wait_event columns and referring to the docs for their meanings https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

And actually, I just realized the background worker is set to have analyze enabled by default. I'll have to see about fixing that to only enable it by default for versions prior to 11. So if you're using the background worker for maintenance, make sure you disable the analyze in your postgresql.conf

pg_partman_bgw.analyze = 'off'

[keithf4]

So I'm marking this as a confirmed bug just so I know I have to fix the BGW variable. Not sure if it is what is causing your issue, but I would appreciate if you could let me know either way.

[doshimunjal]

@keithf4 Sorry for delay in response. I think we found what is causing the issue. Maybe you can confirm that. As mentioned above, we maintain 4 past and 4 future partitions. Daily at midnight, run_maintenance is triggered and we are expecting it to create only one future partition as 3 future partitions will already be present.

In our dataset, 95% of data is lying in default partition(~ 2 billion rows), so each midnight when run_maintenance is triggered, it creates a future partition and scan the default table to ensure data integrity is maintained between default and future partition. That's why it is taking approx 5 minutes to complete.

Can you confirm our analysis is true. And is there a way to avoid scanning default when a new partition is created. Let me know.

Thanks.

[keithf4]

Data is really not meant to stay in the default partition. Especially for native partitioning. The default is just meant to be there to catch data that doesn't have a matching child so it is not lost and then moved out when the time is right. There's really no way around this that I'm aware of other than getting that data moved out into proper child tables. The latest version of pg_partman has better tooling available to help you move the data out of the default.

[keithf4]

The default for analyzing partition sets when new child tables are created is now set to false for the background working in PG11+. This change will be included in the next release.

If you don't have any other questions regarding the use of the default partition, this issue will be closed once the new release comes out.

[keithf4]

As of v4.2.0, PG11+ now no longer analyzes by default when using the BGW for maintenance.