Обсуждение: 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