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

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
<https://www.postgresql.org/docs/15/ddl-partitioning.html> 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)',