PostgreSQL Unique Index Mechanics Explained

PostgreSQL Unique Index Mechanics Explained​

When exploring partitioned table solutions, ensuring global data uniqueness across partitions is critical. While approaches like global indexes exist, they often involve controversial storage logic changes. This analysis explores PostgreSQL’s native uniqueness enforcement without core modifications—starting with its underlying mechanisms.

Unique Enforcement During CREATE INDEX​

PostgreSQL checks uniqueness through this process:

1. Perform a heap scan on the target partition.
2. Store visible tuples in a BTSpool structure (spool1) and invalid tuples in another (spool2).
3. Sort both pools (if they exist). The sorting algorithm detects duplicates:

// src/backend/access/nbtree/nbtsort.c
if (duplicate_found && index_is_unique) 
    ereport(ERROR, ...);

‍

1. If no duplicates exist, build the index tree from spool1 and spool2.
2. Destroy BTSpool structures afterward.

DefineIndex in indexcmds.c orchestrates this process per partition.

Unique Checks for INSERTUPDATE

After routing data to the correct partition:

1. Insert the heap tuple into the target heap relation.
2. Call _bt_doinsert() to add a new index entry.
3. If enforcing uniqueness, _bt_check_unique() scans for conflicts using the new tuple’s values.
4. ​Conflict resolution:
   • If a matching uncommitted tuple exists, the current process waits for commit/rollback.
   • Post-resolution, recheck visibility:

// src/backend/access/nbtree/nbtinsert.c
if (index_is_unique && tuple_is_visible) 
    ereport(ERROR, ...);

‍

1. Proceed only if no conflict is confirmed.

Handling Uniqueness During ATTACH

When attaching a partition:

• ​No existing index: PostgreSQL auto-creates a matching unique index (using the CREATE INDEX flow).
• ​Existing compatible index: Directly attach the partition—no new index is created.

⚠️ ​Note: Global uniqueness across partitions isn’t natively enforced; solutions require custom implementations.

​Key Takeaways

‍