Indexes and Primary Keys on Partitioned Tables in PostgreSQL

The first surprise in partitioning is the primary key. A table partitioned by date cannot have PRIMARY KEY (id). PostgreSQL insists that the key include the partition column, and from that rule flow most of the design choices around indexes, foreign keys and lookups. This article explains the rule and its reason, the behavior of indexes on partitioned tables, a staged way to build them on huge tables, how foreign keys work and the patterns for lookups by id. It belongs to the series that table partitioning in PostgreSQL that actually pays off opens.

The rule and its reason

CREATE TABLE event (
    id bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    PRIMARY KEY (id)
) PARTITION BY RANGE (occurred_at);
-- ERROR:  unique constraint on partitioned table must include all partitioning columns
-- DETAIL:  PRIMARY KEY constraint on table "event" lacks column "occurred_at" which is part of the partition key.

Every partition has its own physical indexes, and an index can only check for duplicates among the rows it contains. If the key is (id) and rows with id = 5 can land in two partitions, each partition's index sees only one and cannot detect the duplicate. If the partition key is part of the unique key, then a given key value maps to exactly one partition, so a per-partition index is enough. The constraint is therefore not arbitrary; PostgreSQL has no global index that would check across partitions.

The consequence: the key becomes (id, occurred_at). Uniqueness of the pair is enforced; uniqueness of id alone is not, though if ids come from one sequence or a UUID generator they are unique in practice. If the business rule requires id uniqueness without a time component, you cannot enforce it with a constraint on the partitioned table. Options are an application guarantee through the generator, a separate unlogged or regular lookup table (event_id(id primary key, occurred_at)) maintained transactionally, or choosing a different partition key that is part of the identifier (a tenant id in a tenant-partitioned table).

Indexes on the parent

Create regular indexes on the parent table:

CREATE INDEX event_user_idx ON event (user_id, occurred_at);

PostgreSQL creates a corresponding index on each existing partition and records the relationship; when you later create or attach a partition, it gets a matching index automatically (an attached partition with an equivalent existing index reuses it). The parent's index is a "partitioned index", a catalog shell with no storage. \d+ event shows it, and \d+ event_2026_03 shows the local index.

Unique indexes follow the same key rule. Partial and expression indexes are supported. Dropping an index on the parent drops it from all partitions; dropping a single partition's index is not allowed while it is attached to the parent's index.

Building indexes on large tables

CREATE INDEX on a parent builds all partition indexes in one statement and holds locks while doing so. With CONCURRENTLY, the statement is not supported on partitioned tables (cannot create index on partitioned table "event" concurrently). The workaround is a staged build:

-- 1. create an invalid placeholder on the parent only
CREATE INDEX event_user_idx ON ONLY event (user_id, occurred_at);

-- 2. build each partition's index concurrently
CREATE INDEX CONCURRENTLY event_2026_03_user_idx ON event_2026_03 (user_id, occurred_at);
CREATE INDEX CONCURRENTLY event_2026_04_user_idx ON event_2026_04 (user_id, occurred_at);

-- 3. attach them
ALTER INDEX event_user_idx ATTACH PARTITION event_2026_03_user_idx;
ALTER INDEX event_user_idx ATTACH PARTITION event_2026_04_user_idx;

ON ONLY creates the parent index without recursing, and it stays invalid until every partition has an attached index; once all are attached, it becomes valid automatically. Each concurrent build avoids blocking writes on its partition. You can run builds for several partitions in parallel from different sessions. The same technique adds a unique index or a primary key: build unique indexes concurrently on each partition, then ALTER TABLE ... ADD PRIMARY KEY USING INDEX per partition is not available at the parent level, so for adding a primary key to a big existing partitioned table, the sequence is CREATE UNIQUE INDEX ... ON ONLY, concurrent per-partition unique indexes, attach, then add the constraint using the parent index.

Index maintenance such as REINDEX CONCURRENTLY applies to partition indexes individually; in version 14 and later REINDEX on a partitioned table recurses to its partitions. Rebuild only partitions that need it, usually the hot ones.

Foreign keys

From a partitioned table to a normal table works: each partition enforces the reference to the target, and the target's deletes and updates check the partitioned table through its indexes. The referencing column should be indexed on the partitioned table (an index on the parent) so deletes on the parent table can find children.

From a normal table to a partitioned table is supported from version 12, and it must reference the unique key of the partitioned table, which includes the partition key. So the referencing table has to carry the partition key too: FOREIGN KEY (event_id, event_time) REFERENCES event (id, occurred_at). That is awkward and is a reason to drop such foreign keys on very large partitioned tables, enforcing the relation in application code, or to design so that referencing tables hold the key.

Between partitioned tables that are partitioned the same way works similarly.

Foreign keys have maintenance costs on partitioned tables: dropping a partition referenced by another table's foreign key requires dropping or reorganizing that constraint, which is another reason retention-heavy tables often avoid inbound foreign keys.

Lookups by id

With PRIMARY KEY (id, occurred_at), a query WHERE id = 42 cannot prune: the planner does not know which partition holds id 42 and probes the primary key index of each partition. With 24 partitions that is 24 index probes (each cheap, a few page reads) and planning for all of them. It works and costs ten to twenty times a single-table lookup. Mitigations:

• Include the key in the lookup. If the application knows the timestamp (it was returned with the id, or the id embeds it), pass both: WHERE id = 42 AND occurred_at = ... or a narrow range, and pruning applies. Time-ordered ids such as UUIDv7 or snowflake-style ids let you derive a time window from the id.

• A mapping table. event_location(id bigint primary key, occurred_at timestamptz) filled on insert allows two-step lookups.

• Keep ids time-ordered so that recent ids imply recent partitions.

• Design the access patterns so lookups by id alone are rare.

If lookups by id alone dominate, partitioning by date may be the wrong design for this table; see partitioning mistakes that make queries slower.

Index choice per partition

Because each partition has its own indexes, you can vary them, but keep the parent's definition in charge of the standard set. Hot recent partitions may deserve additional indexes that old partitions do not need (a covering index for a dashboard); cold old partitions may drop secondary indexes to save space. Use per-partition CREATE INDEX directly on the partition for extras (not attached to the parent), and remember that they are not propagated. BRIN on the partition key inside each partition is a tiny addition for range scans inside a month, as in BRIN indexes for huge append only tables.

When you need uniqueness without the partition key

If a unique rule cannot include the partition key, partitioning that table is in conflict with the rule. Examples: unique email across a table partitioned by date, a globally unique external reference. Solutions: partition by a different key that is part of the unique value (hash the email, partition by hash of email, and the unique key is then (email) since the hash column is derived from it, needing it as a generated column in the key); keep a separate unpartitioned unique table for the rule and write to it in the same transaction; or accept a weaker guarantee and detect duplicates with a periodic report. Each costs something; choose with the full rule in view. Or reconsider whether the table should be partitioned.

What to do next

List the unique constraints that your largest tables need, and check each against the candidate partition key. Test a staged index build on a copy. The manual describes the rules in partitioning limitations and the statements in CREATE INDEX. Next, read partition pruning and checking it works.