Question on creating keys on partitioned tables

[Siddharth Jain <siddhsql@xxxxxxxxx>]

Hi All,

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? 

Thanks

S.

PS: This is what my best friend had to say:

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.