Jira (PDB-5554) Remove partitions in a non-world-blocking way

Issue Type:	Improvement
Assignee:	Unassigned
Created:	2022/10/20 2:12 PM
Priority:	Normal
Reporter:	Austin Blatt

Postgres 14 added support for removing partitions in a non-blocking manner. Currently, removing a partition requires an access exclusive lock on both the partition itself and the parent table. Using DETACH PARTITION partition_name CONCURRENTLY reduces the lock required on the parent table to SHARE UPDATE EXCLUSIVE.

This message was sent by Atlassian Jira (v8.20.11#820011-sha1:0629dd8)

Nick Lewis (Jira)

unread,

Oct 26, 2022, 1:21:02 PM10/26/22

to puppe...@googlegroups.com

Nick Lewis updated an issue

PuppetDB /

Change By:	Nick Lewis
Team:	Skeletor

Joshua Partlow (Jira)

unread,

Nov 1, 2022, 2:24:01 PM11/1/22

to puppe...@googlegroups.com

Joshua Partlow assigned an issue to Joshua Partlow

PuppetDB /

Change By:	Joshua Partlow
Assignee:	Joshua Partlow

Joshua Partlow (Jira)

unread,

Nov 1, 2022, 2:25:03 PM11/1/22

to puppe...@googlegroups.com

Joshua Partlow updated an issue

PuppetDB /

Change By:	Joshua Partlow
Sprint:	Skeletor 11/09/2022

Joshua Partlow (Jira)

unread,

Nov 1, 2022, 3:13:03 PM11/1/22

to puppe...@googlegroups.com

Joshua Partlow commented on

Is this intended only for main? If I'm understanding it, 'detach partition * concurrently' is only supported in Postgres 14 and 7.x still allows Postgres 11.

Austin Blatt (Jira)

unread,

Nov 2, 2022, 11:39:03 AM11/2/22

to puppe...@googlegroups.com

Austin Blatt commented on

When I wrote this ticket I was thinking it would only be on main, but this week I learned 7.x will be the PuppetDB version in PE 2023, so I think we would need to do it conditionally on the Postgres version.

Rob Browning any thoughts on this?

Cas Donoghue (Jira)

unread,

Nov 9, 2022, 12:23:01 PM11/9/22

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Story Points:	1

Cas Donoghue (Jira)

unread,

Nov 9, 2022, 1:15:01 PM11/9/22

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Sprint:	Skeletor 11/09/2022 , Skeletor 11/23/2022

Cas Donoghue (Jira)

unread,

Nov 23, 2022, 1:48:03 PM11/23/22

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Sprint:	Skeletor 11/09/2022, Skeletor 11/23/2022 , Skeletor 12/07/2022

Joshua Partlow (Jira)

unread,

Nov 29, 2022, 3:41:02 PM11/29/22

to puppe...@googlegroups.com

Joshua Partlow commented on

Testing https://github.com/puppetlabs/puppetdb/pull/3748 with Postgresql 14 turned up that use of alter table detach partition assumes the tables use postgresql declarative partitioning, and our tables achieve partitioning with inheritance and triggers. So this isn't something we can implement without changing the method of partitioning.

Sean McDonald (Jira)

unread,

Dec 7, 2022, 1:06:02 PM12/7/22

to puppe...@googlegroups.com

Sean McDonald updated an issue

PuppetDB /

Change By:	Sean McDonald
Sprint:	Skeletor 11/09/2022, Skeletor 11/23/2022, Skeletor 12/07/2022 , Skeletor 12/21/2022

Joshua Partlow (Jira)

unread,

Dec 16, 2022, 7:01:02 PM12/16/22

to puppe...@googlegroups.com

Joshua Partlow commented on

PR is up for some initial review.

Cas Donoghue (Jira)

unread,

Jan 4, 2023, 1:13:01 PM1/4/23

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Sprint:	Skeletor 11/09/2022, Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022 , Skeletor 01/18/2023

Cas Donoghue (Jira)

unread,

Jan 18, 2023, 1:25:02 PM1/18/23

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Sprint:	Skeletor 11/09/2022, Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022, Skeletor 01/18/2023 , Skeletor 02/01/2023

Cas Donoghue (Jira)

unread,

Feb 1, 2023, 1:17:03 PM2/1/23

to puppe...@googlegroups.com

Cas Donoghue updated an issue

PuppetDB /

Change By:	Cas Donoghue
Sprint:	Skeletor 11/09/2022, Skeletor 11/23/2022, Skeletor 12/07/2022, Skeletor 12/21/2022, Skeletor 01/18/2023, Skeletor 02/01/2023 , Skeletor 02/15/2023

Joshua Partlow (Jira)

unread,

Feb 3, 2023, 7:09:03 PM2/3/23

to puppe...@googlegroups.com

Joshua Partlow commented on

Charlie Sharpsteen this pdb change will be going into 7.x and 8.x/main and makes an adjustment to gc, but only if you are running PG14+. This allows us to detach the tables with reduced locking before dropping them. The PG11 workflow should be unchanged.

However this required adjusting the reports and resource_events tables and their partitions to use postgresql declarative partitioning instead of the older inheritance partitioning available in PG10. That's PDB-5567. That also locked in pdb postgresql requirements to PG11 as a minimum.

PR is open here if you want to review it.

Joshua Partlow (Jira)

unread,

Feb 3, 2023, 7:11:01 PM2/3/23

to puppe...@googlegroups.com

Joshua Partlow updated an issue

PuppetDB /

Change By:	Joshua Partlow
Release Notes:	Enhancement
Release Notes Summary:	PuppetDB installations with PostgreSQL 14+ will detach reports and resource_events partitions concurrently before dropping them.

Joshua Partlow (Jira)

unread,

Feb 3, 2023, 7:12:01 PM2/3/23

to puppe...@googlegroups.com

Joshua Partlow updated an issue

PuppetDB /

Change By:	Joshua Partlow
Labels:	docs

Joshua Partlow (Jira)

unread,

Feb 10, 2023, 12:25:03 PM2/10/23

to puppe...@googlegroups.com

Joshua Partlow commented on