The Underappreciated Discipline of Index Maintenance and Why It Silently Degrades Performance Over Time

You’ve just deployed a new database with carefully designed indexes. Queries fly. The app feels snappy. A month passes. Then six. Suddenly, that SELECT that used to take 20 milliseconds now takes 2 seconds. What gives?

Most developers treat indexes like fire-and-forget solutions. You create them, they work, and you move on. But indexes are living structures. They fragment, bloat, and decay. The performance degradation is so gradual you might blame the network, the ORM, or “just more data.” In reality, your indexes are silently rotting.

The Fragmentation Problem

When you insert, update, or delete rows, database indexes don’t reshuffle themselves perfectly. They leave gaps, misordered pages, and logical fragmentation. Here’s what that means in practice:

• B-tree indexes become unbalanced. Leaf pages split unevenly, creating wasted space. A query scanning an index might now read 50% more pages than necessary.
• Clustered indexes suffer from page splits when new rows are inserted into the middle of the key order. Each split creates fragmentation that only gets worse with time.
• Non-clustered indexes accumulate “ghost records” after deletes, which the database still has to skip during reads.

The result? Your index looks correct in metadata—it exists, it’s associated with the right columns—but physically, it’s a bloated, scattered mess.

Real-world Impact You Can Measure

Let’s strip away theory. In a production PostgreSQL database with 10 million rows and a modest write workload (200 inserts/second), index fragmentation can increase query cost by 3x to 10x over six months. Same data volume, same hardware, same queries. The cost shifts from index seek to index scan.

SQL Server’s sys.dm_db_index_physical_stats regularly shows fragmentation above 70% on tables that haven’t been rebuilt in three months. Developers often don’t check because “the index is there, so it’s fine.”

Why “Just Rebuild the Index” Isn’t Always Safe

The obvious fix—rebuilding indexes—comes with its own risks. Online vs. offline rebuilds lock tables. Full rebuilds consume massive tempdb and log space. If you run a rebuild during peak hours on a busy transactional system, you might cause more downtime than the fragmentation itself.

Also, rebuilding every index nightly is wasteful. Some indexes barely fragment. Others fragment heavily after just 1,000 updates.

What You Should Actually Monitor

Stop guessing. Track these three metrics:

1. Index fragmentation percentage – Aim for below 30% for OLTP workloads. Above 70%? Schedule a rebuild.
2. Page count vs. row count – If your index uses 100 pages for 5,000 rows when 20 pages should suffice, you have bloat.
3. Scan vs. seek ratio – If queries that used index seeks now fall back to scans, fragmentation is likely the culprit.

Practical Maintenance Strategies That Work

You don’t need to rebuild everything daily. A smarter approach:

• Reorganize (defragment without full rebuild) for indexes with 5–30% fragmentation. It’s lightweight and can run online.
• Rebuild for indexes above 30%. Use ONLINE = ON if your database engine supports it (SQL Server, PostgreSQL 12+ with pg_repack).
• Set a threshold-based job that checks fragmentation weekly and only acts on indexes that cross your threshold.
• Partition large tables – Partitioning limits fragmentation to a single partition, making maintenance predictable and faster.

The Hidden Cost of Ignoring It

Index maintenance isn’t just a DBA job. It’s a developer responsibility. When indexes degrade:

• Your application’s p99 latency spikes for read-heavy queries.
• Disk I/O increases because the database reads more pages than needed.
• CPU usage climbs as the query optimizer tries to work around fragmentation.
• Backup times grow because larger, bloated indexes mean more bytes to backup.

The Bottom Line

The most well-designed schema and carefully-chosen indexes will fail you if you ignore maintenance. Indexes are not static artifacts—they’re dynamic structures that need periodic care. A 15-minute weekly maintenance job that rebuilds or reorganizes fragmented indexes can save you hours of debugging, prevent costly production incidents, and keep your query performance predictable.

Treat your indexes like an engine, not a brick wall. They’ll thank you with consistent speed.