Unique and Partial Indexes

~12 min · indexes, unique, partial

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

UNIQUE indexes are constraints in disguise

A UNIQUE constraint is implemented as a unique index. You can create unique indexes directly with CREATE UNIQUE INDEX — useful for adding uniqueness to existing columns or scoping uniqueness with a partial WHERE.

Partial indexes — index only what you query

A partial index includes a WHERE clause: only rows matching the predicate are indexed. Smaller index, faster to maintain, often dramatically faster for queries that match the same predicate. Classic uses: index only active rows, only pending orders, only non-deleted records.

Combining partial + unique

"Email must be unique among non-deleted users" is a partial unique index — a one-line solution to a problem that's annoying to enforce in the application.

Code

Unique indexes·sql

-- Equivalent to UNIQUE constraint
CREATE UNIQUE INDEX users_email_uniq ON users (email);

-- Multi-column unique
CREATE UNIQUE INDEX user_product_review ON reviews (user_id, product_id);

-- Partial unique: emails unique among non-deleted users
CREATE UNIQUE INDEX users_active_email_uniq
ON users (email)
WHERE deleted_at IS NULL;

Partial index for a hot query·sql

-- Only the small subset of pending orders is indexed
CREATE INDEX orders_pending_idx
ON orders (placed_at DESC)
WHERE status = 'pending';

-- Queries that match the predicate use the partial index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE  status = 'pending'
ORDER  BY placed_at DESC
LIMIT  20;
-- Tiny index, very fast scan, no need to filter status.

External links

PostgreSQL Partial Indexes

Exercise

Find a column where 'most rows are X but you query the few that are Y' (status, deleted_at, archived flag). Add a partial index for the rare case; compare EXPLAIN before and after.