Horton

Re: Postgresql 14 partitioning advice

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Slava Mudry <slava44@gmail.com>
Cc: Rick Otten <rottenwindfish@gmail.com>, pgsql-performance@postgresql.org
Date: 2022-08-08T22:55:15Z
Lists: pgsql-performance 
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