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)',