"I would like to convert a table with a primary key into a partitioned setup by a column which is not part of the primary key"
That isn't possible. The partition key must be contained by the primary key. That is, the primary key could be site_id, id and you can create hash partition on id or site_id but not created_on.
You could drop primary key and foreign keys and implement them via trigger functions as described in this blog series, but it seems questionable-
You could drop primary key and foreign keys and implement them via trigger functions as described in this blog series, but it seems questionable-
I do not assume the restriction would be dropped in future releases. I don't know that scanning all the partitions to figure out whether the primary key is violated would be advisable. Which is what the trigger functions described in the blog post has to do, right?
It might be noteworthy that partitioning with more than 10-100 partitions is MUCH faster in PG12 than PG11 (up to 4-8 thousand partitions) from testing shared by those working on that code.
