Date: Mon, 8 Aug 2022 17:55:15 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Slava Mudry <slava44@gmail.com>
Cc: Rick Otten <rottenwindfish@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Postgresql 14 partitioning advice
Message-ID: <20220808225515.GB19644@telsasoft.com>
References: 
 <CAMAYy4LE2ORnk7NEFSwA+A800rzWCS5qdKe7ZxPoFmwgT=843Q@mail.gmail.com>
 <CAMAYy4LKAJL6oCOV6OXXfJbCXUwnwcnhUHv+npF0n7HUOf3OuQ@mail.gmail.com>
 <CAMAYy4+BrxW3azSn1mNv07tKxEeabdQ0pT+GieoPmyp42i9jbw@mail.gmail.com>
 <CAMAYy4+yYyNgaOcd3du0kxQDh66JqRvTZHN7xz5OBevWSuR2cg@mail.gmail.com>
 <CAEFxPe29wJo6SVfpAfdfZf12g1yC9=jY2fp0Z_RPT4Y6TpyEtw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEFxPe29wJo6SVfpAfdfZf12g1yC9=jY2fp0Z_RPT4Y6TpyEtw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220808225515.GB19644%40telsasoft.com>
Precedence: bulk

On Mon, Aug 08, 2022 at 03:45:11PM -0700, Slava Mudry wrote:
> Postgres 14 improved partitioning quite a bit. I used it in Postgres 9 and
> there was a lot of locking on partition hierarchy when you add/drop
> partition tables.

Note that postgres 9 didn't have native/declarative partitioning, and most
improvements in native partitioning don't apply to legacy/inheritance
partitioning.

https://www.postgresql.org/docs/devel/ddl-partitioning.html

"Native" partitioning added in v10 tends to require stronger locks for add/drop
than legacy partitioning, since partitions have associated bounds, which cannot
overlap.  The locking is improved in v12 with CREATE+ATTACH and v14 with
DETACH CONCURRENTLY+DROP.

-- 
Justin