Will there be a potential performance issue with the default partition table constraint

[anderssjoblom-vertex]

I notice that the default partition has the constraints build from the constraint from the actual partitioned tables with a NOT and OR clause. If the partitions grows into the 100s of intervals, seems like this might be a performance issue with this constraint as show below instead of building one that will cover the sequence not partitioned:

Partition constraint: ((cust_id IS NOT NULL) AND (cust_id < 0 OR cust_id >= 59))

This is how the default partition constraint is built:

Partition constraint: (NOT ((cust_id IS NOT NULL) AND (((cust_id >= 0) AND (cust_id < 1)) OR ((cust_id >= 1) AND (cust_id < 2)) OR ((cust_id >= 2) AND (cust_id < 3)) OR ((cust_id >= 3) AND (cust_id < 4)) OR ((cust_id >= 4) AND (cust_id < 5)) OR ((cust_id >= 5) AND (cust_id < 6)) OR ((cust_id >= 6) AND (cust_id < 7)) OR ((cust_id >= 7) AND (cust_id < 8)) OR ((cust_id >= 8) AND (cust_id < 9)) OR ((cust_id >= 9) AND (cust_id < 10)) OR ((cust_id >= 10) AND (cust_id < 11)) OR ((cust_id >= 11) AND (cust_id < 12)) OR ((cust_id >= 12) AND (cust_id < 13)) OR ((cust_id >= 13) AND (cust_id < 14)) OR ((cust_id >= 14) AND (cust_id < 15)) OR ((cust_id >= 15) AND (cust_id < 16)) OR ((cust_id >= 16) AND (cust_id < 17)) OR ((cust_id >= 17) AND (cust_id < 18)) OR ((cust_id >= 18) AND (cust_id < 19)) OR ((cust_id >= 19) AND (cust_id < 20)) OR ((cust_id >= 20) AND (cust_id < 21)) OR ((cust_id >= 21) AND (cust_id < 22)) OR ((cust_id >= 22) AND (cust_id < 23)) OR ((cust_id >= 23) AND (cust_id < 24)) OR ((cust_id >= 24) AND (cust_id < 25)) OR ((cust_id >= 25) AND (cust_id < 26)) OR ((cust_id >= 26) AND (cust_id < 27)) OR ((cust_id >= 27) AND (cust_id < 28)) OR ((cust_id >= 28) AND (cust_id < 29)) OR ((cust_id >= 29) AND (cust_id < 30)) OR ((cust_id >= 30) AND (cust_id < 31)) OR ((cust_id >= 31) AND (cust_id < 32)) OR ((cust_id >= 32) AND (cust_id < 33)) OR ((cust_id >= 33) AND (cust_id < 34)) OR ((cust_id >= 34) AND (cust_id < 35)) OR ((cust_id >= 35) AND (cust_id < 36)) OR ((cust_id >= 36) AND (cust_id < 37)) OR ((cust_id >= 37) AND (cust_id < 38)) OR ((cust_id >= 38) AND (cust_id < 39)) OR ((cust_id >= 39) AND (cust_id < 40)) OR ((cust_id >= 40) AND (cust_id < 41)) OR ((cust_id >= 41) AND (cust_id < 42)) OR ((cust_id >= 42) AND (cust_id < 43)) OR ((cust_id >= 43) AND (cust_id < 44)) OR ((cust_id >= 44) AND (cust_id < 45)) OR ((cust_id >= 45) AND (cust_id < 46)) OR ((cust_id >= 46) AND (cust_id < 47)) OR ((cust_id >= 47) AND (cust_id < 48)) OR ((cust_id >= 48) AND (cust_id < 49)) OR ((cust_id >= 49) AND (cust_id < 50)) OR ((cust_id >= 50) AND (cust_id < 51)) OR ((cust_id >= 51) AND (cust_id < 52)) OR ((cust_id >= 52) AND (cust_id < 53)) OR ((cust_id >= 53) AND (cust_id < 54)) OR ((cust_id >= 54) AND (cust_id < 55)) OR ((cust_id >= 55) AND (cust_id < 56)) OR ((cust_id >= 56) AND (cust_id < 57)) OR ((cust_id >= 57) AND (cust_id < 58)) OR ((cust_id >= 58) AND (cust_id < 59)))))

[keithf4]

Converted this to a Q&A Discussion.

I'm not aware of any potential performance issues with this since more recent versions of PG use partition pruning methods to find the proper child table. At least I don't think there would be any issues as long as you're not inserting a lot of data outside of the range covered by non-default children. But there are other bigger concerns with having a lot of data in the default, that being that the adding of new child tables can potentially take a very long time and lock the table if you have a lot of data in the default. This is because it has to check all the data in the default to see if it matches any new child table constraints being added.

So I just recommend monitoring your default tables for data and acting on it as soon as possible to clear it out. pg_partman provides the check_default() function to do that.

If you're really curious for a more in-depth answer, I'd direct you to the official mailing lists since this behavior is a core PG feature and not something specific to pg_partman.

[anderssjoblom-vertex]

Thanks for the information.
Yes, we never want any data in default since that will fail creating new partitions. It was a reaction after seeing the logic for the constraint how it could be written in a more readable and efficient way.