0

PostgreSQL has a limitation on unique constraints on partitioned tables:

Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

This is sad, because, let's say you have a table of EMPloyees and they are partitioned by field office to which they are assigned. You want the EMP_ID to be the primary key regardless of the assigned field office.

My question is:

  • Is this just PosgtgreSQL? In other words, do Oracle or SQL Server or DB2 or whatever other major RDBMS allow this?
  • Any idea if PostgreSQL might add this?

In my view, it should not be too hard to do. An index is anyway just another relation (in PostgrSQL) so it should be maintainable regardless of partitioning key.

1 Answer 1

2

I know that Oracle has "global indexes" and hence can do this; I cannot say anything about the others.

Adding this feature would be not easy at all, because it requires a global index: a single index that indexes data from all partitions. This has actually been proposed on the developer's mailing list, and you can look at the thread to get an idea of the problems involved.

My view is that such a global index is a mixed blessing at best. Once you have such a thing on your table to support your primary key, you can no longer efficiently attach and detach partitions, and for most use cases of partitioning that is the key feature. Usually it is better to live with reduced integrity guarantees.

Note that there is no such restriction for primary keys on the individual partitions. Such constraints already go a long way. All that is missing is that uniqueness cannot be enforced across partitions. If you generate your "primary key" values with an identity column, you can already be pretty certain that there are no duplicates.

1
  • 2
    The technical difficulties aside, I agree with "mixed blessing". But I do think it would be good to have the choice. We have several partitioned tables using global indexes for foreign keys in Oracle. Those partitions are never detached or attached. The partitioning is only done for performance reasons to turn scans on a huge table to scans on smaller tables because the queries always contain the partitioning key. Partition wise joins also help here.
    – user1822
    Sep 23, 2022 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.