Question on creating keys on partitioned tables

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.