Is an index on a boolean column useful?

Index led by a boolean or near-constant column

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

What happens

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.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE INDEX idx_users_active ON users (is_active);

The safe pattern

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

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN is_active boolean NOT NULL DEFAULT true;
CREATE INDEX CONCURRENTLY idx_users_active_email ON users (is_active, email);

SET lock_timeout = '5s';
CREATE TABLE users (id bigint PRIMARY KEY, is_active boolean NOT NULL DEFAULT true, email text);
CREATE INDEX idx_users_active ON users (is_active);

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_users_deleted ON users (deleted);

SET lock_timeout = '5s';
CREATE TABLE users (id bigint PRIMARY KEY, is_active boolean NOT NULL DEFAULT true, email text);
CREATE INDEX idx_users_email_active ON users (email, is_active);

SET lock_timeout = '5s';
CREATE TABLE users (id bigint PRIMARY KEY, is_active boolean NOT NULL DEFAULT true, email text);
CREATE INDEX idx_users_active ON users (id) WHERE is_active;

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_users_flagged ON users (flagged);

How to check locally

npx bolvrk check migration.sql --remote