pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedList Based Table Partitioning on non-Primary Key Columns
2+ messages / 2 participants
[nested] [flat]
* List Based Table Partitioning on non-Primary Key Columns
@ 2023-10-16 20:55 Amit Sharma <amitpgsql@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Amit Sharma @ 2023-10-16 20:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: List Based Table Partitioning on non-Primary Key Columns
@ 2023-10-16 21:04 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Amit Sharma <amitpgsql@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Ribe @ 2023-10-16 21:04 UTC (permalink / raw)
To: Amit Sharma <amitpgsql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 manageability perspective? Has anyone dealt with a similar type of partition issues? Is there any other alternate option we should be using?
It's not unreasonable. But an alternative is to have a set, fewer, number of partitions and hash partition. You could wind up with better balanced partitions, and the cost of on average larger partitions.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-10-16 21:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-16 20:55 List Based Table Partitioning on non-Primary Key Columns Amit Sharma <amitpgsql@gmail.com>
2023-10-16 21:04 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox