Indexes Kinds WHERE · partial · conditional uniqueness

Partial Index

An index that covers only the rows matching a condition you specify. Smaller, faster, less write overhead — and the only way to enforce conditional uniqueness like one-active-subscription-per-user.

What a partial index is

A partial index indexes only the rows that match a condition you specify. Rows that don't match the condition are simply absent from the index.

That sounds restrictive but it's powerful. Two big wins:

Smaller index, less disk. If 95% of your rows have completed = 1 and you only ever search the 5% with completed = 0, why index every row?
Faster writes. Rows that don't match the condition don't touch the index at all — INSERTs and UPDATEs are free for them.

Syntax

CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;

Demo dataset — students with completion flag

SQL — Create & insert

CREATE TABLE students4 (
    student_id  SERIAL PRIMARY KEY,
    first_name  TEXT,
    last_name   TEXT,
    email       VARCHAR(50),
    completed   INT       -- 1 = course finished, 0 = still enrolled
);

INSERT INTO students4 (first_name, last_name, email, completed) VALUES
('John',    'Doe',      'john.doe@example.com',         1),
('Jane',    'Smith',    'jane.smith@example.com',       0),
('Alice',   'Johnson',  'alice.johnson@example.com',    1),
('Michael', 'Williams', 'michael.williams@example.com', 1),
('Emma',    'Brown',    'emma.brown@example.com',       0),
('William', 'Lee',      'william.lee@example.com',      0);

Example 1 — partial index for "in-progress" students

Most reports run against the small set of students who haven't finished the course yet. Index just those.

SQL

CREATE INDEX idx_students4_in_progress
    ON students4 (student_id)
    WHERE completed = 0;

-- This query uses the index
SELECT * FROM students4 WHERE completed = 0 AND student_id = 1032;

-- This one doesn't — completed = 1 isn't covered by the partial index
SELECT * FROM students4 WHERE completed = 1 AND student_id = 1030;

The optimizer only uses the partial index when the query's WHERE clause logically implies the index's condition.

Example 2 — partial unique index

A common pattern: enforce "one active subscription per user" without restricting cancelled ones.

SQL

CREATE UNIQUE INDEX idx_one_active_sub
    ON subscriptions (user_id)
    WHERE status = 'active';

A user can have hundreds of cancelled subscriptions, but no more than one active row at a time. There's no constraint syntax that does this — only a partial index.

Example 3 — index just the recent rows

SQL

-- Only events from the last 30 days are queried hot
CREATE INDEX idx_events_recent
    ON events (user_id)
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

⚠ Time-based partials drift. The condition is evaluated when the index is created, so an index WHERE created_at > CURRENT_DATE - INTERVAL '30 days' created today will permanently keep rows that were within 30 days at creation time. Refresh strategies vary — some teams rebuild the index periodically; others use a fixed timestamp constant.

Full index vs partial index

When partial indexes pay off

Skewed data. 95% of rows share one value; you only query the other 5%.
Soft deletion. Index WHERE deleted_at IS NULL to ignore archived rows.
Status workflows. Index WHERE status IN ('pending', 'processing') for the active queue.
Conditional uniqueness. One active row per user (the constraint form can't do this).

Recap

A partial index covers only the rows matching its WHERE.
Smaller, faster, less write overhead.
Used only when the query's WHERE implies the index's WHERE.
Powerful pattern for partial uniqueness — one active row per user, etc.
Watch out for time-based conditions — they're evaluated at create time, not query time.