Re: partitions vs indexes

"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-
https://www.depesz.com/2018/10/31/foreign-key-to-partitioned-table-part-2/

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.

>