Index led by a boolean or near-constant column

Note — this works and blocks nothing, but a performance regression is likely.

What it catches

A B-tree whose leading column has two values splits the table in half at best. The planner will usually ignore it in favor of a sequential scan, and when it does use it the index returns half the table. Either way every write pays to maintain it. The useful form is a partial index — WHERE is_active — that only contains the rows you actually look up.

Fires on

CREATE INDEX idx_users_active ON users (is_active);

Do this instead

-- Only the active rows live in the index:
CREATE INDEX CONCURRENTLY idx_users_active ON users (id) WHERE is_active;