Message-ID: <a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at>
Subject: Re: Partition management - best practices and avoid long access
 exclusive lock during partition creation
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: srinivasan s <srinioracledba7@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Thu, 30 Jan 2025 15:01:53 +0100
In-Reply-To: 
 <CAOEeMcU4mSZb-BySSHoZ2WiHy1PMpe3TvuhqQuB-c1wjt+50Og@mail.gmail.com>
References: 
	<CAOEeMcU4mSZb-BySSHoZ2WiHy1PMpe3TvuhqQuB-c1wjt+50Og@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-01-30 at 14:17 +0530, srinivasan s wrote:
> I am looking forward to=C2=A0some suggestions to avoid exclusive lock dur=
ing the partition
> creation in=C2=A0postgresql 15. Currently we have a simple monthly range =
partition setup
> on a table.
>=20
> I Scheduled a pg_cron job, which runs every month=C2=A0and checks if we h=
ave a partition
> available for next six months and creates necessary partitions.
>=20
> The command used to create the partition is given below, unfortunately th=
is is
> causing a huge exclusive lock for a long time and blocking other sessions=
 causing a
> resource crunch on the system. this is very busy system 24/7 very difficu=
lt to find
> a maintenance window
>=20
> CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L=
)', partition_name, table_name, DATE(start_date), DATE(end_date)
>=20
> I was going through the documentation=C2=A0PostgreSQL: Documentation:
> 15: 5.11.=C2=A0Table Partitioning=C2=A0and there was note by creating a t=
able, adding a
> check constraint=C2=A0& attaching to the parent table minimizes the lock =
during partition
> maintenance. something like below ?=C2=A0 looking forward to the suggesti=
ons from
> partitioning=C2=A0experts. the change that I am making to help to avoid s=
uch huge locks
> or other suggestions ?
>=20
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0EXECUTE format('CREATE TA=
BLE IF NOT EXISTS %I (LIKE %I INCLUDING DEFAULTS INCLUDING CONSTRAINTS)',
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0p=
artition_name, table_name);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-- Add the CHECK constrai=
nt with the dynamic name
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0EXECUTE format('ALTER TAB=
LE %I ADD CONSTRAINT %I CHECK (created_at >=3D DATE %L AND created_at < DAT=
E %L)',
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0p=
artition_name, constraint_name, start_date, end_date);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-- Attach the partition
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0EXECUTE format('ALTER TAB=
LE %I ATTACH PARTITION %I FOR VALUES FROM (%L) TO (%L)',

Yes, that should only take a SHARE UPDATE EXCLUSIVE lock, which won't confl=
ict with
SELECT or data modifications.

But I am surprised that the original statement is a problem.  Sore, it take=
s a higher
lock, but only for a very short time.  Perhaps you have long-running transa=
ctions all
the time.  If yes, that's a problem you should work on.

Yours,
Laurenz Albe