Thread: Partition management - best practices and avoid long access exclusive lock during partition creation
Partition management - best practices and avoid long access exclusive lock during partition creation
From
srinivasan s
Date:
Hi All,
Hope you are well.
I am looking forward to some suggestions to avoid exclusive lock during the partition creation in postgresql 15. Currently we have a simple monthly range partition setup on a table.
I Scheduled a pg_cron job, which runs every month and checks if we have a partition available for next six months and creates necessary partitions.
The command used to create the partition is given below, unfortunately this 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 difficult to find a maintenance window
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)
I was going through the documentation PostgreSQL: Documentation: 15: 5.11. Table Partitioning and there was note by creating a table, adding a check constraint & attaching to the parent table minimizes the lock during partition maintenance. something like below ? looking forward to the suggestions from partitioning experts. the change that I am making to help to avoid such huge locks or other suggestions ? Please note at this time using pg_partman is very difficult, as we have different naming conventions for partitioned tables and it is not easy to change all the partitioned table names.
EXECUTE format('CREATE TABLE IF NOT EXISTS %I (LIKE %I INCLUDING DEFAULTS INCLUDING CONSTRAINTS)', partition_name, table_name); -- Add the CHECK constraint with the dynamic name EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I CHECK (created_at >= DATE %L AND created_at < DATE %L)', partition_name, constraint_name, start_date, end_date); -- Attach the partition EXECUTE format('ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM (%L) TO (%L)',Re: Partition management - best practices and avoid long access exclusive lock during partition creation
From
Laurenz Albe
Date:
On Thu, 2025-01-30 at 14:17 +0530, srinivasan s wrote:
> I am looking forward to some suggestions to avoid exclusive lock during the partition
> creation in postgresql 15. Currently we have a simple monthly range partition setup
> on a table.
>
> I Scheduled a pg_cron job, which runs every month and checks if we have a partition
> available for next six months and creates necessary partitions.
>
> The command used to create the partition is given below, unfortunately this 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 difficult to find
> a maintenance window
>
> 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)
>
> I was going through the documentation PostgreSQL: Documentation:
> 15: 5.11. Table Partitioning and there was note by creating a table, adding a
> check constraint & attaching to the parent table minimizes the lock during partition
> maintenance. something like below ? looking forward to the suggestions from
> partitioning experts. the change that I am making to help to avoid such huge locks
> or other suggestions ?
>
> EXECUTE format('CREATE TABLE IF NOT EXISTS %I (LIKE %I INCLUDING DEFAULTS INCLUDING CONSTRAINTS)',
> partition_name, table_name);
> -- Add the CHECK constraint with the dynamic name
> EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I CHECK (created_at >= DATE %L AND created_at < DATE %L)',
> partition_name, constraint_name, start_date, end_date);
> -- Attach the partition
> EXECUTE format('ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM (%L) TO (%L)',
Yes, that should only take a SHARE UPDATE EXCLUSIVE lock, which won't conflict with
SELECT or data modifications.
But I am surprised that the original statement is a problem. Sore, it takes a higher
lock, but only for a very short time. Perhaps you have long-running transactions all
the time. If yes, that's a problem you should work on.
Yours,
Laurenz Albe