Override Partition Key Limits to Set Any Column as Primary Key - PolarDB

Standard PostgreSQL requires the primary key of a partitioned table to include all partition keys and does not allow foreign keys to reference a partitioned table's primary key. PolarDB for PostgreSQL removes both restrictions: you can define a primary key on any column, regardless of whether it is part of the partition key, and reference it with a foreign key.

This is particularly useful when migrating from Oracle, which natively supports global indexes. You can keep your existing schema design without restructuring partition keys to satisfy PostgreSQL's primary key requirements.

Prerequisites

This feature is supported on PolarDB for PostgreSQL clusters running PostgreSQL 14 with minor engine version 2.0.14.10.17.0 or later.

To check your minor engine version, run:

SHOW polardb_version;

Or view it in the console. If your version does not meet the requirement, upgrade the minor engine version.

How it works

PolarDB for PostgreSQL uses global indexes to enforce uniqueness across all partitions when a primary key or UNIQUE constraint does not include all partition keys. A global index spans the entire partitioned table rather than a single partition.

Index type	When it is used	Scope
Local index	Primary key or UNIQUE constraint includes all partition keys (default PostgreSQL behavior)	Single partition
Global index	Primary key or UNIQUE constraint does not include all partition keys	All partitions

The polar_pk_in_non_partition_column_mode parameter controls whether creating a primary key on a non-partition column is allowed, and which index type is used:

Value	Behavior
`none` / `local_pk`	Creating a primary key on a non-partition column is not allowed. An error is returned if attempted.
`global_index`	A global index is used as the constraint when creating a primary key on a non-partition column.

Limitations

Limitation	Impact	Recommendation
Primary key includes all partition keys	A local index is used by default, regardless of the `polar_pk_in_non_partition_column_mode` setting.	Use `global_index` mode and explicitly create a global unique index if you need a global primary key on a column that is also the partition key.
UNIQUE constraint includes all partition keys	A local index is used as the unique constraint by default.	Same as above: create a global unique index explicitly if cross-partition uniqueness is required.
Adding a primary key or UNIQUE constraint to an existing partitioned table	A local index may not satisfy uniqueness across all existing partitions.	Always use a global index when adding a primary key or UNIQUE constraint to an existing partitioned table.
Foreign key references a row that is updated across partitions	A cross-partition update is equivalent to a delete followed by an insert. The foreign key check runs on the delete and will fail, causing the update to be rejected.	Avoid cross-partition updates on rows referenced by a foreign key. Restructure the operation as an explicit delete and insert if needed.

Examples

Create a primary key on a non-partition column

The following example shows the error you get when polar_pk_in_non_partition_column_mode is set to none and you try to create a primary key on column b, which is not the partition key:

SET polar_pk_in_non_partition_column_mode = none;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "pt1" lacks column "a" which is part of the partition key.

Set polar_pk_in_non_partition_column_mode to global_index to allow the non-partition primary key. PolarDB for PostgreSQL creates a global index on column b:

SET polar_pk_in_non_partition_column_mode = global_index;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt1_p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (1);
\d pt1
               Partitioned table "public.pt1"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           |          |
 b      | integer           |           | not null |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt1_pkey" PRIMARY KEY, btree (b) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)

Primary key that includes all partition keys

When the primary key includes all partition keys, a local index is used by default, regardless of the polar_pk_in_non_partition_column_mode setting. This is the default PostgreSQL behavior:

CREATE TABLE pt2 (a int primary key, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt2_p1 PARTITION OF pt2 FOR VALUES FROM (0) TO (1);
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a)
Number of partitions: 1 (Use \d+ to list them.)

Convert a local primary key to a global primary key

To convert an existing local primary key to a global one, drop the existing constraint, create a global unique index, then attach it as the primary key:

ALTER TABLE pt2 DROP CONSTRAINT pt2_pkey;
CREATE UNIQUE INDEX pt2_pkey ON pt2 (a) GLOBAL;
ALTER TABLE pt2 ADD PRIMARY KEY USING INDEX pt2_pkey;
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)