고유 키 및 기본 키 인덱스

To enforce uniqueness, PostgreSQL must check existing rows fast — so it auto-builds a unique index behind every unique constraint and primary key.

Declaring a primary key creates a unique B-tree index on those columns and marks them NOT NULL. The code shows it in action.

That primary key silently builds an index called users_pkey. You never wrote CREATE INDEX, yet lookups by id are already fast.

A UNIQUE constraint also creates a supporting index and rejects duplicate values — the ALTER TABLE below adds one.

You can build a unique index directly, without a named constraint — it still enforces uniqueness, as the code shows.

Index multiple columns together for multi-column uniqueness — like one membership per user per group. The code makes the pair unique.

PostgreSQL treats NULLs as distinct by default, so a unique column allows many NULLs. Add NULLS NOT DISTINCT to forbid more than one.

A partial unique index enforces uniqueness only on rows matching a WHERE clause — for example, one active session per user.

A unique index gives you two things at once: data integrity and fast lookups. The planner uses it like any other B-tree index.

The constraint is the logical rule; the index enforces it. You can't drop the index without the constraint, and foreign keys can reference these columns.

Unique indexes add write overhead since every insert checks for duplicates. Index only the columns that genuinely need to be unique.

Primary keys, NULLs, partial indexes — let's see what stuck about unique indexes.

Recap: every primary key and unique constraint is backed by a unique B-tree index for integrity plus speed. You can also go standalone, multi-column, or partial — but each adds write cost.