Database index decay: why good data goes bad over time

A database index does not stay good just because it was good on day one. Over time, write-heavy tables accumulate dead entries, page splits, stale statistics, skewed value distributions, and workload changes that make yesterday’s perfect index a mediocre one. That is index decay. The fix is not blind rebuilding on a schedule. The fix is knowing whether the problem is physical bloat, optimizer misinformation, or a workload that has outgrown the original index design.

What “index decay” actually means

Most people use one label for several different problems.

• The index structure itself can become less efficient after months of inserts, updates, and deletes.
• The optimizer can make bad choices because its statistics no longer describe the table accurately enough.
• The application can evolve until the index is still technically valid, but no longer a good match for the real query mix.
• You can also end up with indexes that are rarely used but still paid for on every write.

That is why blanket advice like “just reindex the table” is often wrong. Rebuilding a bloated index helps one class of problem. It does nothing for a stale query pattern, a missing composite index, or a table whose planner statistics are lying.

In our experience managing production servers, the biggest operational mistake is treating all slow queries as if they come from a weak VPS. Plenty do not. Many come from indexes that have drifted out of alignment with reality while the host itself is still perfectly fine.

The three ways good index performance dies

1. Physical decay

On write-heavy tables, indexes change constantly. Inserts force page splits. Updates can create dead or obsolete index entries. Deletes leave space that is not always reused neatly. PostgreSQL documents this directly: bloated indexes can contain many empty or nearly empty pages, and `REINDEX` exists partly to rebuild them without those dead pages.

This is the version of index decay most people think about first because it is visible. Disk usage grows. Cache efficiency drops. Reads become less predictable. Maintenance windows get heavier. But it is only one part of the story.

2. Statistical decay

The optimizer only chooses well if it has current information. PostgreSQL’s `ANALYZE` stores statistics in `pg_statistic`, and the planner uses them to pick execution plans. MariaDB says the same thing in simpler language: `ANALYZE TABLE` updates index statistics used by the optimizer to choose the best plan.

If the data distribution shifts and statistics lag behind, the planner can start choosing table scans when it should not, or it can insist on using an index that is now a poor fit. At that point, the index is not broken. The model of the table is broken.

3. Workload decay

This is the part most teams ignore. Your query mix changes over time. Reporting gets added. Search filters get more specific. Sort orders change. A once-helpful single-column index becomes less useful than a composite one. A secondary index that mattered six months ago becomes dead weight. The data did not betray you. Your application moved on.

That is why index maintenance is never just a storage problem. It is also an application and query-shape problem.

PostgreSQL: the decay mechanisms people usually miss

PostgreSQL is honest about maintenance. Its documentation says databases require periodic vacuuming, and vacuuming exists partly to reuse disk space from updated or deleted rows, update planner statistics, and maintain the visibility map that helps index-only scans. If autovacuum is weak, delayed, or badly tuned for a hot table, index quality and planner quality both degrade together.

There are three practical PostgreSQL lessons here.

• A bloated index and stale planner stats are different problems, even if they show up in the same incident.
• `REINDEX` is useful, but on a live system it affects writes unless you use `CONCURRENTLY`.
• For B-tree indexes on heavy insert and update workloads, `fillfactor` matters because a lower fillfactor can smooth out page splits early in the index’s life.

A simple first-pass check for hot PostgreSQL tables looks like this:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

And a quick index-usage view looks like this:

SELECT schemaname,
       relname       AS table_name,
       indexrelname  AS index_name,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, schemaname, relname;

That second query is not permission to drop every low-scan index. A low-scan unique index may still protect data integrity. A monthly reporting index can still be essential. But it does tell you where to start asking hard questions.

If you do identify real bloat, PostgreSQL gives you a production-safe path:

REINDEX INDEX CONCURRENTLY idx_orders_created_at;

Use ordinary `REINDEX` only if the lock impact is acceptable. PostgreSQL’s own docs are very clear that normal reindexing blocks writes on the parent table while the rebuild runs.

For write-heavy indexes that will keep growing, build smarter next time instead of reindexing forever:

CREATE INDEX CONCURRENTLY idx_events_created_at
ON events (created_at)
WITH (fillfactor = 80);

That will not save a bad schema, but it can reduce early page-split pain on workloads that are known to churn.

MySQL and MariaDB: the decay pattern looks different, but it is still decay

On MySQL and MariaDB, the biggest operational trap is assuming that an index that exists is an index the optimizer fully understands right now. That is not always true.

MariaDB documents `ANALYZE TABLE` as the operation that analyzes and stores key distribution for a table, and says those statistics are used by the optimizer to decide join order and index selection. MariaDB’s optimizer documentation also says engine-independent statistics and histograms exist precisely because older storage-engine-provided statistics were often poor. MySQL says its `INFORMATION_SCHEMA.STATISTICS` values can be cached, with a default expiration of 86400 seconds, and that `ANALYZE TABLE` can refresh them earlier.

That means you can get a slow query for a very stupid reason: the index is there, but the optimizer is reasoning with old or coarse information.

A safe first response on MySQL or MariaDB after major churn is often:

ANALYZE TABLE orders;

That is much cheaper than rebuilding everything blindly, and it addresses the statistics problem directly.

When the problem is physical layout after substantial inserts, updates, or deletes, the tool changes. MySQL documents `OPTIMIZE TABLE` as reorganizing the physical storage of table and index data, with explicit mention of reclaiming space and improving I/O efficiency. MariaDB describes `OPTIMIZE TABLE` as either defragmenting tables or updating the InnoDB fulltext index.

OPTIMIZE TABLE orders;

That is not something you run casually in the middle of a peak traffic window. Physical reorganization has real cost. It is a maintenance action, not a magic performance button.

If you need to identify obviously unused indexes in MySQL, the `sys` schema helps:

SELECT *
FROM sys.schema_unused_indexes;

Even that needs judgment. An index that has not been used during the current uptime window may still matter in month-end reporting or rare incident response queries. Do not let one convenient view talk you into self-sabotage.

MariaDB can surface index usage too, but its `INDEX_STATISTICS` support depends on the user statistics feature, which is not enabled by default. That is a detail people miss and then wonder why their visibility is poor.

Why “just add more indexes” makes decay worse

Indexes speed up reads because the database maintains extra structure. That maintenance cost is paid on writes. If you keep adding indexes every time one query hurts, you raise write cost, grow storage pressure, enlarge the working set in cache, and increase the amount of structure that can go stale or bloated later.

Good index design is not about having more indexes. It is about having fewer, better ones that match the actual workload. That is why index decay and index sprawl usually travel together. The team that never revisits old indexes is often the same team that added them in panic and forgot them immediately after the ticket closed.

The maintenance policy that actually works

If you want a sane policy for a production VPS database, keep it simple.

• Refresh statistics after major data churn.
• Watch for dead tuples, autovacuum lag, or analyze lag on PostgreSQL.
• Use `OPTIMIZE TABLE` or rebuild operations only when the physical problem is real.
• Review low-usage indexes periodically, but never drop them without checking constraints, reporting patterns, and rollback needs.
• Revisit composite index design when the application query shape changes.

This is where managed operations saves time. If you need someone to separate statistics drift from real index bloat and from plain bad query design, ServerSpan’s Linux administration service is the correct handoff point. If you are still at the stage where the database and app need room to breathe, a properly sized KVM virtual server gives you the control to monitor, tune, and rebuild safely.

What to check before you blame the VPS

• Did the table change size or value distribution dramatically?
• Have planner statistics been refreshed recently?
• Is the index bloated, or is the planner just making the wrong choice?
• Has the query changed shape since the index was created?
• Are you maintaining too many indexes for the write rate?

That order matters. Too many people jump from “query got slower” straight to “the VPS is weak.” Sometimes the VPS is weak. Sometimes the database is lying to itself. Sometimes the index is paying for a workload that no longer exists.

This also ties directly into your broader ServerSpan content. The same discipline behind The Reality of “My Server Is Slow” Tickets applies here: symptoms are easy to report, root causes are not. And if you are comparing engines or sizing a new database host, MariaDB vs MySQL 8.0: Performance Benchmarks & Configuration Guide for VPS and Linux Swap vs RAM: The Definitive Guide to Memory Management on VPS are the right adjacent reads.

The practical answer

Good data goes bad over time because indexes are living structures, not static trophies. They decay physically under write churn, they decay statistically when planner information gets stale, and they decay functionally when the workload evolves away from the way they were designed. The right fix depends on which kind of decay you are looking at. Refresh statistics first when the optimizer is wrong. Rebuild when the structure is bloated. Redesign or drop when the workload has moved on.

If your current database maintenance policy is “we made the indexes once and the optimizer can sort it out forever,” you do not have a policy. You have deferred database debt.