Partition creation doesn't use the best method?

[Faithfinder]

I'm not an SQL master, so this is more of a question than a statement, I might easily be missing something.

However, I'm exploring partitioning right now, and reading the PG docs, creating partitions via CREATE TABLE ... PARTITION OF like psqlextra does seems to be inefficient.

django-postgres-extra/psqlextra/backend/schema.py

Lines 75 to 77 in 7d582d9

	sql_add_range_partition = (
	"CREATE TABLE %s PARTITION OF %s FOR VALUES FROM (%s) TO (%s)"
	)

According to the docs, this way puts an ACCESS EXCLUSIVE lock on the whole parent table, while attaching a partition only locks that partition and puts just a SHARE UPDATE EXCLUSIVE lock in the parent. From what I saw during my experiments, it also looks through the default partition to check whether something belonging in the new partition is there, making this a potentially long operation, so ACCESS EXCLUSIVE is bad.

It seems like the better way to create a partition is like this:

- Creating and attaching a partition separately causes a more permissive lock than creating a table straight as a partition
CREATE TABLE table_name_suffix (LIKE table_name INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- Check constraint on the partition table tells Postgre that this table has no data that should be in other partitions, no need to scan
ALTER TABLE table_name_suffix ADD CONSTRAINT "partition_check"
   CHECK ( part_column >= DATE '2024-11-13' AND part_column < DATE '2024-11-20' );
  
-- Moving data from the default partition in case some is there, maybe out of scope for psqlextra
-- INSERT INTO table_name_suffix SELECT * FROM table_name_default WHERE part_column >= DATE '2024-11-13' AND part_column < DATE '2024-11-20';
-- DELETE FROM table_name_default WHERE part_column >= DATE '2024-11-13' AND part_column < DATE '2024-11-20';


-- Check constraint on default partition tells Postgre that it has no data that should be in the new partition and no need to scan it
ALTER TABLE table_name_default ADD CONSTRAINT "default_partition_check"
   CHECK ( part_column < DATE '2024-11-13' );
  
-- Attach the new partition
ALTER TABLE table_name ATTACH PARTITION table_name_suffix 
    FOR VALUES FROM ('2024-11-13') TO ('2024-11-20');

-- Check constraints aren't needed after the operation is complete
ALTER TABLE table_name_suffix DROP CONSTRAINT "partition_check";
ALTER TABLE table_name_default DROP CONSTRAINT "default_partition_check";

This ran near-instantaneously on the same table that spend 3 minutes doing basic create table partition of

[Photonios]

Correct! The built-in method is not the most efficient. It wasn't designed to be. The method you describe is significantly more complicated, which is why I initially implemented the most straightforward way of doing this.

Please feel free to open a PR to implement this method. It would be more than welcome :)