Rule

Avoid redundant database indexes.
Overlapping database indexes waste
storage and slow down writes.

Supported languages: SQL

Introduction

Redundant indexes occur when multiple indexes cover the same columns or when one index is a prefix of another. Every index consumes disk space and must be updated on INSERT, UPDATE, and DELETE operations. A table with five overlapping indexes on similar columns pays the write performance penalty five times while only one index would suffice for read optimization.

Why it matters

Performance impact: Every index slows down write operations because the database must update all indexes when data changes. Redundant indexes multiply this cost without providing query benefits. A table with three redundant indexes on user_id triples write overhead while only one index is ever used.

Storage costs: Indexes consume disk space proportional to indexed column sizes and row counts. Redundant indexes waste storage that could be used for actual data or useful indexes. Large tables with unnecessary indexes can waste gigabytes of storage.

Maintenance complexity: More indexes mean more objects to monitor, analyze, and maintain. Database administrators spend time optimizing indexes that provide no value. Query planners have more options to evaluate, potentially choosing suboptimal execution plans.

Code examples

❌ Non-compliant:

-- Redundant indexes on users table
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_created_status ON users(created_at, status);

-- The single-column indexes are redundant because
-- the composite indexes can serve the same queries

Why it's wrong: The index on email is redundant because idx_users_email_status starts with email and can handle queries filtering only by email. Similarly, idx_users_created is redundant with idx_users_created_status. Every insert or update to this table updates four indexes when two would suffice.

✅ Compliant:

-- Optimized indexes on users table
CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_users_created_status ON users(created_at, status);

-- Composite indexes can serve queries on their prefix columns
-- Queries on just email use idx_users_email_status
-- Queries on just created_at use idx_users_created_status

Why this matters: Two composite indexes serve all query patterns while eliminating redundancy. Queries filtering by email alone use the first index, and queries filtering by created_at alone use the second. Write performance improves because only two indexes need updates instead of four.

Conclusion

Audit your database indexes regularly to identify redundant ones. Remove indexes that are prefixes of other indexes or that duplicate coverage. Composite indexes can serve queries on their leading columns, eliminating the need for separate single-column indexes in most cases.