PostgreSQL: UNIQUE INDEX vs INDEX — A Deep Dive

TL;DR

#

A UNIQUE INDEX is just a regular B-tree index with an extra uniqueness check on insert. They share the same data structure — the only difference is what happens when you try to insert a duplicate.

The Basics

#

INDEX — speeds up lookups, allows duplicates:

CREATE INDEX idx_users_email ON users (email);
-- Multiple rows CAN have the same email

UNIQUE INDEX — speeds up lookups AND enforces uniqueness:

CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Duplicate emails are REJECTED

Under the Hood

#

A unique index uses the exact same B-tree structure as a regular index. Same pages, same leaf nodes, same lookup algorithm. The only addition is a uniqueness check during insertion.

The insertion flow

#

heap_insert()
  → ExecInsertIndexTuples()
    → index_insert() on each index
      → for unique indexes: _bt_check_unique()
        → if duplicate found & visible → ERROR
        → if duplicate found but dead/invisible → proceed
      → _bt_doinsert() → place tuple in B-tree

Key implementation details

#

• Locking: PostgreSQL takes a short-lived lock on the index page during the uniqueness check to prevent race conditions between concurrent inserts.
• MVCC-aware: The check only considers tuples visible to the current transaction. Dead tuples from aborted transactions don’t block new inserts.
• Deferred uniqueness: With DEFERRABLE INITIALLY DEFERRED, the check moves to commit time — useful for bulk operations that temporarily violate uniqueness mid-transaction.

What Happens When You Create a UNIQUE INDEX on a Non-Unique Column?

#

The CREATE UNIQUE INDEX statement fails immediately:

ERROR:  could not create unique index "idx_name"
DETAIL:  Key (column)=(duplicate_value) is duplicated.

This is a DDL-time rejection, not a query-time one. PostgreSQL scans all existing values before building the index.

If the column currently has unique values, the index is created — but future inserts/updates introducing duplicates will be rejected.

“When the unique index is created for a table that already has data, all existing values in the indexed columns are checked for uniqueness.” — PostgreSQL Docs: CREATE INDEX

UNIQUE Constraint vs UNIQUE INDEX

#

They’re nearly identical under the hood — a UNIQUE constraint always creates a unique index. The differences are semantic:

Use constraint when you’re modeling domain rules (this column must be unique). Use index when you need advanced features (partial, expression, covering).

Practical Guidelines

#

• Use UNIQUE INDEX for: emails, slugs, external IDs, API keys — anything that must never have duplicates.
• Use plain INDEX for: foreign keys, status columns, timestamps — columns you query often but naturally have duplicates.
• Prefer constraints for simple uniqueness. Use explicit CREATE UNIQUE INDEX when you need partial or expression indexes.

Drizzle ORM Example

#

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
  status: text('status').notNull(),
}, (t) => [
  uniqueIndex('idx_users_email').on(t.email),  // enforces uniqueness
  index('idx_users_status').on(t.status),       // just for speed
]);

Other thoughts

#

1. Add index only when it’s necessary. Check your query