List Based Table Partitioning on non-Primary Key Columns

Hello,

I have a question about table partitioning in PostgreSQL. To give you a
little context, currently we are using an Oracle database that has List
based partitioning on all large tables. Partition key is a CLIENT_ID
column. There is a separate partition for each client, roughly 200+
partitions in a table. We plan to redesign our application and migrate data
from Oracle to PostgreSQL for the new application.

In PostgreSQL it seems like the partition key column must be part of a
primary or Unique key. If we follow the same partitioning option as Oracle,
it will force us to create composite primary and foreign keys to include
(ID and CLIENT_ID) columns.

Data varies in each partition, there are some partitions with 10+ million
records, and some have only 300,000 or less records.

For an example: In Oracle we have tables structures like this:

CREATE TABLE TEST1 (ID NUMBER, NAME VARCHAR2(30), CLIENT_ID NUMBER)

PARTITION BY LIST (CLIENT_ID)

(

PARTITION TEST1_P1 VALUES (1)

);

ALTER TABLE TEST1 ADD (

Constraint TEST1_PK1 PRIMARY KEY (ID));

My question is, is there any downside of using a similar partition option
in PostgreSQL from performance or manageability perspective? Has anyone
dealt with a similar type of partition issues? Is there any other alternate
option we should be using?

Thanks

Amit Sharma