p_interval - the time or integer range interval for each partition.

[rparantesgmail]

The pg_partman extension only accepts integer and date as partitioning columns. I would like to know if at some point other types of data will be accepted, such as numeric for example.

[keithf4]

There are currently no plans for other data types at this time unless there is a high demand for one in particular or someone submits a PR to fully support it. I will say it's unlikely for any data types other than number or time based ones will be accepted unless a use-case for ongoing maintenance requirements for such can be made.

[rparantesgmail]

Hello Keith, thanks for the feedback. I work in the Inter Bank and we have several Aurora Postgresql environments, with tables partitioned by a numeric(49) field, we maintain the partitions through procedures created by me, it would be very useful if we could use pg_partman to perform the partitioning and the maintenance.

[keithf4]

Typically it's not recommended to use numeric for partitioning or primary key columns due to significant performance differences. Especially in comparison operations which, I would imagine, are fairly common when partitioning since the main reason for doing so is using a WHERE condition on the partition key. Integer values would be much more efficient in these cases if that's something you may be able to consider in the future.

I could possibly see about adding numeric support in the future, but I cannot give any sort of ETA on that. If you'd like to work on a patch I'd be happy to review it.

[rparantesgmail]

We had to use numeric due to the characteristic of the application, which uses idempotence, as follows:
SSLLLLLLLLLLLLLLLLLLLLLTTTTTTTTTTTTTTTTTTT

SSS - identification of the source system

LLLLLLLLLLLLLLLLLLLLL - any value of the LONG type, between zero and MAX_LONG (that is, between 0000000000000000000 and 9223372036854775807)

Step TTTTTTTTTTTTTTTTTTT is simply a LONG, between zero and MAX_LONG (ie between 0 and 9223372036854775807)

so we suggest adding the date format below so that we can do daily partitioning: YYYYMMDD

Now we have YYYYMMDDSSSLLLLLLLLLLLLLLLLLLLTTTTTTTTTTTTTTTTTTT

I would like to work on a patch to resolve this issue. What would be the process for me to start this work ?

[keithf4]

If your goal is to actually partition by day, then I might suggest something else here. Create another column that is a date column. Have a trigger that automatically updates that column with the relevant date/time from the first portion of that numeric column's value. Then you can partition the table on that column instead.

If the queries you were running were using the numeric column as the condition for any date/time calculations to narrow the data, they would have to be rewritten to use that new column instead to take advantage of parition-prunning/constraint-exclusion. But you would likely see a marked improvement in read query performance as well. There might be a slight impact on write performance with an additional column and the trigger processing, but in this case I think it would likely not be very noticeable. Testing is recommended of course.

Or maybe you could completely remove that YYYYMMDD prefix for the numeric field all together if the only reason you were adding it was to enable daily partitioning. Then you wouldn't need the trigger at all. Application would just need to process the row in a different manner.

If you'd like to see about adding numeric support, I'd suggest looking at the check_control_type() function and go from there. You'd have to account for all places where the partition column is cast to the necessary type as well.

[rparantesgmail]

Hi Keith,
Thanks for the feedback and for the help, we are looking into the option of using the date column to do the partitioning. If it is not feasible for the application, we will think about developing the patch. Thank you very much.