Is my new index redundant with an existing one?

Redundant index: its columns are already a prefix of another index

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

What happens

A B-tree index on (a) is fully covered by an existing index on (a, b): the planner can use the wider index for every query the narrow one serves. The extra index buys nothing and costs a write on every INSERT, UPDATE and DELETE, plus disk, plus vacuum time, forever.

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_orders_user ON orders (user_id);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

The safe pattern

Keep the wider index and drop the narrower one (CONCURRENTLY, in its own migration). If the narrow index is the one you need for a different ordering or as a UNIQUE constraint, say so with a different column order or UNIQUE, the rule stays silent on both.

-- Keep the wide index (user_id, created_at), it serves both lookups.
-- In its own later migration, outside a transaction:
DROP INDEX CONCURRENTLY idx_orders_user;

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.

Fires (3)

covered by live index

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_again ON orders (user_id);

narrow after wide

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

wide after narrow

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);

Stays silent (5)

desc ordering

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE INDEX CONCURRENTLY idx_orders_user_desc ON orders (user_id DESC);

different leading column

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

partial twin

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE INDEX CONCURRENTLY idx_orders_user_open ON orders (user_id) WHERE closed_at IS NULL;

replaces and drops

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
DROP INDEX CONCURRENTLY idx_orders_user;

unique narrow

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at);
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_user_unique ON orders (user_id);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV050 BP002 →