Re: Partition management - best practices and avoid long access exclusive lock during partition creation

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