Обсуждение: 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
> On Oct 16, 2023, at 2:55 PM, Amit Sharma <amitpgsql@gmail.com> wrote: > > My question is, is there any downside of using a similar partition option in PostgreSQL from performance or manageabilityperspective? Has anyone dealt with a similar type of partition issues? Is there any other alternate option weshould be using? It's not unreasonable. But an alternative is to have a set, fewer, number of partitions and hash partition. You could windup with better balanced partitions, and the cost of on average larger partitions.