Seven Indexes, Larger Than the Table: Why Index Sets Rot and Nothing Forces a Review

In many production environments, a table's index set eventually outgrows the data itself, leading to severe postgres index bloat and degraded performance. This "index rot" occurs because engineering processes typically focus on adding indexes to solve specific performance issues but rarely include a mechanism for auditing and removing them once they are no longer useful.

The number that should not be possible

In a recently documented production case by Oleksandr Kolesnykov, a SaaS database events table reached a state that seems like a horror statistic: roughly 11 GB of heap (the actual data) was supported by roughly 17 GB of indexes. The table, handling approximately 300,000 inserts a day across 35 million rows, had accumulated seven distinct indexes.

The most striking part of this case is that every one of those seven indexes was likely justified at the moment it was added. One developer needed a dashboard to load faster; another needed to optimize a specific background worker. This is not a story of incompetence, but of accumulation.

Note: This data represents a single production system documented by its operator, not a controlled benchmark. Results on other systems will vary based on hardware and traffic patterns.

Why index sets only grow: The Ratchet

This phenomenon is known as the "ratchet effect." Indexes are added one at a time, usually during an incident or a feature launch. Each addition is a local optimum—it solves a visible problem and passes code review because the benefit is clear.

However, nothing in the standard engineering lifecycle—not sprint planning, not on-call handovers, not architecture reviews—ever asks, "Is the total set of indexes still necessary?" While teams religiously review dependencies, alert thresholds, and on-call rotations, the index set is conspicuously absent from the list of things we periodically prune. This results in a monotonic growth of database overhead that no one is assigned to manage.

What an unnecessary index actually costs

We often treat indexes as "free" search optimizations, but they carry a heavy tax that compounds silently.

• Write Amplification: Every INSERT, UPDATE, and DELETE that touches an indexed column must also update the B-tree. On a table with 300,000 daily writes, an unnecessary 443 MB index is a continuous tax for a question that might never be asked.
• Buffer Cache Competition: This is the cost most teams forget. An index that is never read still occupies space in memory. If that index stays resident in the buffer cache, it is actively displacing hot data that your queries actually need, forcing more disk I/O.
• Maintenance Overhead: Every index increases the work for autovacuum, bloats your backups, and extends the time required for a point-in-time recovery (PITR).

Key Takeaway: The cost of an index isn't just disk space; it is a permanent tax on write latency and memory efficiency that persists long after the original query it served has been deleted from your codebase.

Finding the candidates without trusting the counters

The standard advice for finding unused indexes postgres is to run a query against pg_stat_user_indexes and look for a low idx_scan count. However, raw counters can be misleading.

A low idx_scan might represent a critical query that only runs once a month (like a billing report) or an index that enforces a unique constraint or foreign key lookup. Furthermore, statistics are cumulative since the last reset; if you recently rebooted or manually cleared stats, every index will look "unused."

A true audit requires matching the index set against the current "query shapes" of your application:

1. Dashboard reads: What filters are users actually applying?
2. Worker operations: What keys do background jobs use to fetch batches?
3. Lifecycle checks: Are there unique constraints or FKs being enforced?

The question worth more than the audit: Which key do your queries lead with?

The most high-leverage finding in the Kolesnykov case wasn't just an unused index—it was a prefix mismatch. The system had evolved. While early versions of the product focused on user_id, the architecture had shifted so that almost every query led with site_id.

Because user_id was the leading column in most composite indexes, but site_id was the leading predicate in the queries, the indexes were far less efficient than they appeared. On multi-tenant systems, the "tenant key" often drifts as the product's unit of work changes. If your indexes don't follow that shift, you end up with "dead weight" columns at the front of your B-trees, forcing the database to do more work than necessary.

Shipping it against a live table

Removing or restructuring indexes on a high-traffic table requires extreme care to avoid downtime.

• Concurrent Operations: Use DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY. This prevents the database from locking the table against writes while the index is being modified.
• Constraint Limitations: If you are dropping a column or a primary key, you will need a brief exclusive lock. We recommend running these inside a transaction with a very short lock_timeout and a retry loop to avoid queuing up other queries.
• Migration Timeouts: A concurrent index build on 35 million rows can easily outlast a standard 30-second CI/CD migration timeout. Ensure your migration runner is configured to allow long-running operations before you start.

For a deeper look at managing these transitions, see our guide on Zero-Downtime Database Migrations.

The partitioning decision you are allowed to decline

When a table reaches tens of millions of rows, many engineers assume postgres table partitioning is the inevitable next step. However, partitioning is often the most expensive answer to the problem.

According to PostgreSQL Documentation, a unique or primary key constraint on a partitioned table must include all partition key columns. In the source case, partitioning an events table by created_at would have required changing the primary key from (id) to (id, created_at). This change would then have to cascade to every foreign key in the system, turning a simple storage optimization into a massive schema redesign.

Before reaching for partitioning, ask:

1. Can we improve retention? Is the old data even needed online?
2. Can we fix the index set? In the referenced case, dropping redundant indexes and fixing composite prefixes reduced read IOPS from 1,715/s to 241/s and CPU from 35% to 11%.
3. Are we prepared for the constraint tax? If you think you might partition later, include the likely partition key in your uniqueness constraints today. Retrofitting it is rarely free.

Making index review a thing that happens

The reason index sets rot is that database maintenance is currently a "human memory" problem. To do it right, a person has to hold the index statistics, the current query shapes, and the product roadmap in their head simultaneously.

The goal of a modern SRE practice is to turn this from a manual chore into a triggered event. This is where Operate changes the dynamic. Instead of waiting for a human to remember to run a pg_stat query, Operate monitors your production environment, identifies slow queries and redundant indexes, and links them to the actual evidence in your traffic patterns.

When a "wrong tenant key" or a redundant index is found, Operate doesn't just alert you—it drafts the fix. It provides the patch file for the migration and the supporting statistics, turning a complex architectural review into a standard PR process.

Caveats and Results

In the source case, after replacing seven indexes (15.5 GB) with three optimized ones (4.7 GB), the operator reported a read latency drop from 4.79 ms to 1.13 ms and a significant reduction in database load. While the write-side improvements were less dramatic (a peak write latency improvement of roughly 13%), the overall stability of the system increased. These results demonstrate that managing postgres index bloat is often more effective than jumping straight to complex partitioning schemes.

Sources & further reading

• According to Oleksandr Kolesnykov, maintenance debt is often disguised as a scaling limit.
• According to PostgreSQL Documentation, uniqueness constraints must include all partition key columns.
• According to Yugabyte, high partition and index counts can lead to LockManager contention during query planning.