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