pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: Slava Mudry <slava44@gmail.com>
Cc: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql 14 partitioning advice
Date: Mon, 8 Aug 2022 17:55:15 -0500
Message-ID: <20220808225515.GB19644@telsasoft.com> (raw)
In-Reply-To: <CAEFxPe29wJo6SVfpAfdfZf12g1yC9=jY2fp0Z_RPT4Y6TpyEtw@mail.gmail.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>

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





view thread (9+ messages)

Message-ID: <20220808225515.GB19644@telsasoft.com>
Permalink:  ../20220808225515.GB19644@telsasoft.com/
Also on:    postgresql.org/message-id/20220808225515.GB19644@telsasoft.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pryzby@telsasoft.com, slava44@gmail.com, rottenwindfish@gmail.com
  Subject: Re: Postgresql 14 partitioning advice
  In-Reply-To: <20220808225515.GB19644@telsasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox