partition_data_async better implementation

[keithf4]

Discussed in #826

^{Originally posted by Faithfinder November 4, 2025}
Based on my previous research, there's a very good pattern for creating partitions with minimal table downtime.

Here:

-- Creating and attaching a partition separately causes a more permissive lock, than creating a table straight as a partition
CREATE TABLE partition_p00000000 (LIKE parent_table 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 partition_p00000000 ADD CONSTRAINT "partition_check"
   CHECK ( partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11' );
  
-- Moving data from the default partition in case some is there
INSERT INTO partition_p00000000 SELECT * FROM partition_default WHERE partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11';
DELETE FROM partition_default WHERE partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11';


-- 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
-- Creating it as "NOT VALID" allows to create it without checking the data, limiting the ExclusiveLock time
ALTER TABLE partition_default ADD CONSTRAINT "default_partition_check"
   CHECK ( partition_column < DATE '2024-12-04' ) NOT VALID;
-- Validating the constraint after the check is applied only does `ShareUpdateExclusiveLock`, allowing selects and inserts during the process
ALTER TABLE partition_default VALIDATE CONSTRAINT "default_partition_check";
  
-- Attach the new partition
ALTER TABLE parent_table ATTACH PARTITION partition_p00000000
    FOR VALUES FROM ('2024-12-04') TO ('2024-12-11');

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

For me, the key difference from what partition_data_async currently does is that the "outside" table is not temporary, and is attached as the partition itself, not dropped. The obvious tradeoff is that you can't insert data that would go to this partition until everything is done, but that's not necessarily a bad thing.

If I'm being silly, feel free to disregard, just thought I'd share my research in case it's useful :)

[Faithfinder]

Sorry, I'm not comfortable doing a PR - I'm actually not that good at SQL and have never written an SQL function/procedure in my life. This is just the result of targeted research when we migrated a bunch of multi-terabyte tables to partitioned.

I could ask Claude to do something as a starting point, but that probably won't save you much time =P

This probably is the best way to create a new partition on a live table in general? Maybe that can be useful even outside of partition_data_async

[keithf4]

No problem if you can't do a PR, I just ask because I want to make sure people know I'm open to code contributions. Appreciate you sharing what you found.

So for generally making new child tables, partman does first make the child table and then attach it. But those are completely empty, so there's really no advantage there with making the constraint first. Whether this would work well with the non-async method, I'd have to look and see.

I'd prefer not to have AI generated code, so thanks for asking first.

[Faithfinder]

But those are completely empty, so there's really no advantage there with making the constraint first. Whether this would work well with the non-async method, I'd have to look and see.

So, from what I remember, when attaching an empty partition, you still need the check on the default partition. When attaching the partition, Postgre goes on to check 2 things:

• Attached table doesn't have data in it, that should be in other partitions (doesn't matter with empty table).
• Default partition doesn't have data that should be in new partition (does matter even if attached table is empty).

Or, quoting https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE

Similarly, if the partitioned table has a DEFAULT partition, it is recommended to create a CHECK constraint which excludes the to-be-attached partition's constraint. If this is not done, the DEFAULT partition will be scanned to verify that it contains no records which should be located in the partition being attached. This operation will be performed whilst holding an ACCESS EXCLUSIVE lock on the DEFAULT partition. If the DEFAULT partition is itself a partitioned table, then each of its partitions will be recursively checked in the same way as the table being attached, as mentioned above.

[keithf4]

The point about the default is interesting. But that constraint would need to be made on the default then, right? And I'm not quite sure how much better that would be. When you add a new constraint to a table, it still has to scan that table to see if any existing data is there correct? When doing partition maintenance that check and the new table addition are still all done in the same transaction and the same lock is taken on the default, no?

Would have to really do some experimentation with that to see if it really provides a benefit. The constraint would need to be dropped afterwards as well, otherwise it's just building up a bunch of duplicate constraints on the default.

[Faithfinder]

Hmm, how that interacts with transactions is a bit beyond my knowledge. Without transactions though, you add the check as "NOT VALID" which is almost instantaneous and prevents bad data from being written, then you validate it, which only produces a very permissive lock, so doesn't block the table from working, and then attaching the partition should be much faster. And yes, you do clean up after yourself

Relevant commands from snippet above:

-- 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
-- Creating it as "NOT VALID" allows to create it without checking the data, limiting the ExclusiveLock time
ALTER TABLE partition_default ADD CONSTRAINT "default_partition_check"
   CHECK ( partition_column < DATE '2024-12-04' ) NOT VALID;
-- Validating the constraint after the check is applied only does `ShareUpdateExclusiveLock`, allowing selects and inserts during the process
ALTER TABLE partition_default VALIDATE CONSTRAINT "default_partition_check";

[keithf4]

So I'm still unclear how this is going to be any more effective if there is actually data in the default that would conflict with the new child table. Yes the not valid constraint on the default would get added instantaneously, but that didn't give you any advantages if there is actually invalid data in the default. You cannot validate it until that data has been cleared. And I'm not quite sure how making the constraint not valid would actually affect whether the new child table attachment wouldn't cause the default table to be scanned. It likely has to be a valid constraint to preclude the scan that checks the new child table's ability to be attached.

So if the default is empty, there's no need for this extra constraint. But if the default isn't empty, then there has to be something that actually validates the default has no matching data before that new child table can be added. There's no getting around that scenario that I can see.

[Faithfinder]

The scenario I'm thinking about is when there is data in default, but not data that belongs to the partition that is being created. E.g the default has 3TB of data for 2024, because partitions were screwed up back then. The data is usually not accessed by the app, so moving it to correct partitions isn't a priority for the foreseeable future. However, you would like any current data, for 2025, to be partitioned correctly. When partman creates future empty partitions during maintenance, default check constraint should help.

[keithf4]

The preferred solution to this would be to move that data into a partition that actually defines what's in it, not leave it in the default. The partition_data_async() function was designed pretty much exactly for this scenario to use small transactions to slowly move data that isn't being regularly accessed. Getting that data properly partitioned out would then make it easier to deal with for retention if it's really not needed in the database anymore more.

I'd rather not make partman's constraint management more complicated to support an anti-pattern. It is definitely good to know about that technique of adding a constraint to the default to avoid the scan when new partitions are made and I will see if that can be incorporated somehow in another manner.