Infinite_time_partition feature for ID based partitions

[neetam15]

I am implementing a partitioning on a table based on an int id.
This table currently doesn't have any records, I have also created an empty default table and an empty child partition, since the run_maintenance proc mandates it (ideally, I wouldn't have created a child partition manually).
Now, I wish to start premaking extra empty child partitions in advance. (& create extra partitions in each run_maintenance iteration)

For time-based partitioning, this feature is available through the infinite_time_partitions flag, however no such feature exists for ID based partitioning. Is there a way to achieve this functionality? Or would I need to manually call create_partition_id() ?

[keithf4]

By your description here, you've gone and created the default and child table manually. The create_parent() function will do this for you and with a default premake of 4, will make 4 child tables ahead of the starting child table. You can increase the premake value with the p_premake parameter to create_parent() to have it initially create more if needed. Note that will permanently set that partition set's premake to that value so that it is always that many child tables ahead based on current data. You can always update the config table to increase/decrease premake as needed as well.

With time, there's a value from the system clock that's always increasing, independent of the data, so that's not a problem. For integer values, there's no such external value to base anything on in a general sense. The only way to do that sort of infinite operation for integer partitioning would be to completely disregard the premake value and just make a child table every single time maintenance is called no matter what. That doesn't seem ideal to me, especially when you have no new data being inserted related to that integer. If that integer is an epoch, there is an epoch mode for pg_partman to treat it as such and then infinite_time_partitions would apply. Even with time-based partitioning, the infinite option doesn't keep making a new child table every maintenance call. It only does it as needed as the value of time itself goes up.

I would just make sure you have premake set high enough to account for your expected data window.

[keithf4]

Also, manually calling create_partition_id() wouldn't work either. It only creates a new child table based on the premake value and the current data.