Thread: Question on creating keys on partitioned tables
In PostgreSQL partitioning, the decision of where to place the primary key can depend on the specific requirements of the application and the partitioning strategy being used.
If you are using the "table inheritance" approach to partitioning, where child tables inherit from a parent table, then the primary key should be placed on the parent table. This is because the child tables do not have their own primary key constraints, and their primary key columns are inherited from the parent table.
On the other hand, if you are using the "declarative partitioning" approach, where each partition is a separate table defined within a partitioned table, then the primary key can be placed on either the parent table or the child tables. However, placing the primary key on the child tables can improve query performance, as it allows for more efficient indexing and partition pruning.
In summary, if you are using table inheritance for partitioning, place the primary key on the parent table. If you are using declarative partitioning, you can choose to place the primary key on either the parent table or the child tables, with potential performance benefits for placing it on the child tables.
On Thu, 2023-03-30 at 17:05 -0700, Siddharth Jain wrote: > I have this question. Say I create a partitioned table on column X. > > Option 1: > > I add a primary key on (X,Y). Y is another column. Even though Y is a globally unique PK (global meaning it is unique acrosspartitions, not just in one partition), Postgres does not allow me to > create a PK on Y in a partitioned table. > > Option 2: > > I add PK on Y on each of the partitions > > Are these not equivalent? If not, which is better and why? No, they are not equivalent. Option 2 comes closer to guaranteeing uniqueness for column X, so use that. > PS: This is what my best friend had to say: > > [...] If you are using the "table inheritance" approach [...] Don't even consider that. Declarative partitioning is so much better. Yours, Laurenz Albe
On Thu, 2023-03-30 at 17:05 -0700, Siddharth Jain wrote:
> I have this question. Say I create a partitioned table on column X.
>
> Option 1:
>
> I add a primary key on (X,Y). Y is another column. Even though Y is a globally unique PK (global meaning it is unique across partitions, not just in one partition), Postgres does not allow me to
> create a PK on Y in a partitioned table.
>
> Option 2:
>
> I add PK on Y on each of the partitions
>
> Are these not equivalent? If not, which is better and why?
No, they are not equivalent.
Option 2 comes closer to guaranteeing uniqueness for column X, so use that.
> PS: This is what my best friend had to say:
>
> [...] If you are using the "table inheritance" approach [...]
Don't even consider that. Declarative partitioning is so much better.
Yours,
Laurenz Albe
Siddharth Jain <siddhsql@gmail.com> writes:
> I think the two are equivalent. If not, could you please explain why?
Well, they're formally equivalent if you require there to be only one
X value per partition (ie, PARTITION BY LIST with only one listed value
per partition); if there's more, they're not the same thing.
Neither one guarantees that Y is globally unique. We have no mechanism
for enforcing uniqueness across partitions except for partition key
columns.
regards, tom lane
Siddharth Jain <siddhsql@gmail.com> writes:
> I think the two are equivalent. If not, could you please explain why?
Well, they're formally equivalent if you require there to be only one
X value per partition (ie, PARTITION BY LIST with only one listed value
per partition); if there's more, they're not the same thing.
Neither one guarantees that Y is globally unique. We have no mechanism
for enforcing uniqueness across partitions except for partition key
columns.
regards, tom lane
On Fri, 2023-03-31 at 10:35 -0700, Siddharth Jain wrote: > Is following correct? > > when a PK is created on (X,Y) on the parent table what happens internally is that the > command is run individually on each of the child tables. nothing more. nothing less. If you are talking about inheritance, no. Creating a constraint on the parent table has no effect on a child table. If you are talking about partitioning: If you create a primary key on the partitioned table, that will be implemented by a partitioned index. That partitioned index consists of a unique index for each partition. So I guess that is more or less what you mean. There are some differences between a primary key on the partitioned table and a unique index on each partition: - the primary key is a constraint, so it can be the target of a foreign key - the primary key will force a unique index on each new partition - the primary key keeps you from dropping the unique index on the partitions Yours, Laurenz Albe