Before we can understand what goes wrong with too many indexes, we need to be completely sure what an index is and why humans invented them long before computers existed.

A Short History

The idea of an index is far older than computers. Books have had back-of-the-book indexes for centuries, and libraries have used card catalogs since the 1780s. The idea is always the same: build a smaller, sorted, searchable summary of a bigger, unsorted collection.

When relational databases were invented in the 1970s (starting with IBM’s System R and the first commercial version, Oracle, in 1979), engineers borrowed this exact idea. Early databases used simple sorted-file indexes. By the 1980s, the B-Tree (and its variant, the B+Tree) became the standard index structure, because it stays balanced and fast even as data grows into the millions or billions of rows. Almost every modern relational database still uses B+Trees as its default index type today, more than 40 years later — a rare case of a 1970s data structure still being state-of-the-art in production systems in 2026.

Indexes were an unambiguous win at first: add an index, queries get faster, everyone is happy. But as applications grew and teams got bigger, a second-order problem appeared: teams kept adding more and more indexes, one for every new query pattern, without ever removing old ones. Eventually, some tables had 15, 20, even 40 indexes. That’s when engineers started noticing that writes were getting slower, storage costs were climbing, and the query planner was sometimes making worse choices, not better ones. This tutorial is about understanding exactly why that happens, and what the tools and habits are for keeping a system’s indexing healthy over the long haul rather than accidentally letting it decay.

Reads want more indexes; writes want fewer. These two goals pull in opposite directions on every table, in every serious application, forever.

Here is the core tension every database engineer eventually runs into:

• Reads want more indexes. Every new way you query a table (by email, by date, by status, by user ID and date together) can be made faster with a matching index.
• Writes want fewer indexes. Every INSERT, UPDATE, or DELETE has to update every single index on that table, not just the table itself.

These two goals pull in opposite directions. A table with zero indexes has blazing-fast writes and painfully slow reads. A table with thirty indexes has blazing-fast reads for thirty different query patterns — and painfully slow, resource-hungry writes. Somewhere in between is the right balance, and finding it is one of the most common real-world decisions a backend engineer or database administrator makes.

This isn’t a rare, academic concern. It is one of the most frequently asked database performance questions in real interviews, and one of the most common root causes discovered in production incident post-mortems at companies large and small. Understanding it well is genuinely useful, not just theoretical — the difference between a team that ships smoothly at scale and one that ends up in an unplanned emergency “why is the database on fire again” conversation every quarter is very often precisely how well they manage this trade-off over time.

The building blocks we need before we can talk about downsides: tables, rows, indexes, and the B-Tree that makes them fast.

3.1 The Table (a.k.a. the “Heap”)

A database table is just a big collection of rows, usually stored on disk in the order they were inserted (this raw storage is often called the heap). Finding one row in the heap, without help, means checking rows one at a time — called a full table scan.

3.2 The Index

An index is a second, smaller structure that stores a sorted copy of one or more columns, plus a pointer (called a row identifier or RID, or in PostgreSQL a TID) back to the exact row in the heap. Because it is sorted, the database can use fast search techniques (like binary search, or more precisely a B-Tree lookup) instead of checking every row.

-- Without an index, this scans all 1,000,000 rows
SELECT * FROM customers WHERE email = 'ana@example.com';

-- Create an index to make it fast
CREATE INDEX idx_customers_email ON customers(email);

-- Now the same query jumps almost straight to the row
SELECT * FROM customers WHERE email = 'ana@example.com';

3.3 The B-Tree (and B+Tree)

Almost every relational database index is built on a B-Tree or its close cousin, the B+Tree. This is a tree data structure where:

• Every node can hold many keys (not just 2, like a binary tree) — often hundreds.
• The tree stays balanced: every path from the root to a leaf is the same length.
• Leaf nodes are linked together in sorted order, so range queries (WHERE age BETWEEN 20 AND 30) are efficient.

3.4 Types of Indexes You Should Know

Every one of these index types, no matter how clever, shares the same fundamental cost: it is extra data that must be kept in sync with the table, forever, on every write. That single sentence is the seed of everything we cover in the rest of this tutorial.

Indexes are not a “view” on the table or a shortcut inside it — they are separate, physically-stored data structures that live next to the heap and must be written to on every change.

Key components involved:

Notice that none of these components is optional in a real production database — they are the price of admission for correct, durable, high-performance storage. Every one of them, however, does more work as the index count on a table grows, and that compounding is what makes the “too many indexes” problem show up simultaneously in several very different-looking dashboards (CPU, memory, disk I/O, replication lag) rather than in one obvious place.

What actually happens, step by step, when you insert one row into a heavily indexed table — and why it is never just “one write”.

5.1 B-Tree Rebalancing During Writes

Each index is not just “add one line to a list.” A B-Tree node can only hold a limited number of keys. When a node becomes full and a new key needs to go in, the database must split that node into two, and update the parent node to point to both. This can cascade upward, splitting parent nodes too. This is more CPU and disk work than it sounds like, and it happens independently, in every index, for every write that touches an indexed column.

5.2 Updates and Deletes Are Just As Expensive

People often think only INSERT is affected, but:

• UPDATE on an indexed column must remove the old index entry and insert a new one, in every affected index (in some engines like PostgreSQL, an update to any column can force new versions of index entries too, due to MVCC — more in section 6).
• DELETE must remove entries from every index, or at least mark them for later cleanup.

Following one write request from the application all the way to disk, and one read request through the query planner — so the full lifecycle of an index becomes concrete rather than abstract.

Lifecycle of an Index, End to End

This is the heart of the tutorial. Each downside below is explained with a beginner analogy, a concrete example, and a production angle — because the point is not to scare anyone off indexes, it is to make the invisible costs visible.

7.1 Slower Writes (Write Amplification)

7.2 More Disk Storage

7.3 Slower Backups, Restores, and Replication

7.4 Buffer Pool / Cache Pollution

7.5 Query Planner Confusion

7.6 Lock Contention and Concurrency Overhead

7.7 Slower Schema Changes and Maintenance Windows

Adding a column, changing a data type, or running large maintenance operations (like VACUUM FULL in PostgreSQL or OPTIMIZE TABLE in MySQL) often has to account for, rebuild, or re-validate every index on the table. More indexes directly means longer maintenance windows and longer application downtime or degraded performance during those windows.

7.8 CPU Cost of Statistics Collection

Query planners rely on statistics (row counts, value distributions) about both tables and indexes to make good decisions. Databases periodically recompute these statistics (e.g. PostgreSQL’s ANALYZE, auto-triggered by autovacuum). More indexes means more statistics objects to compute and refresh, adding background CPU load that competes with your actual application traffic.

To be fair, indexes are not the enemy — the enemy is excess, unmanaged indexes. Here is the balanced picture, side by side.

How this problem changes shape as data volume and traffic grow — and why it compounds nastily at scale rather than staying constant.

The relationship between index count and write latency is roughly linear at low index counts, then gets worse as more indexes compete for the same cache and the same disk I/O bandwidth. A rough mental model many engineers use:

At scale, this problem compounds with two more factors:

• Sharding / Partitioning: if a table is partitioned across multiple physical shards (a common pattern at companies like Netflix, Uber, and Amazon), every index typically must be maintained per shard. Ten indexes on a table sharded into 50 pieces means 500 index structures being kept alive across the fleet.
• Replication: every index change on a primary database node must also be applied on every read replica. More indexes means more replication log volume, which can widen replication lag — the delay between a write happening on the primary and becoming visible on replicas.

How excess indexing quietly threatens uptime, not just speed — and why maintenance debt on indexes eventually becomes an availability problem in disguise.

• Replication lag: as covered above, more index writes mean more work replicas must replay, which can push read replicas further behind the primary — risking stale reads in read-replica-backed applications.
• Failover risk: during a failover (a replica getting promoted to primary), if that replica was lagging due to heavy index write load, the promotion can lose recent writes or take longer to complete safely.
• Long-running index builds blocking availability: building a new index on a huge table without using a non-blocking mode (like PostgreSQL’s CREATE INDEX CONCURRENTLY) can lock the table for writes for the whole build, effectively causing an outage.
• Vacuum/cleanup falling behind: in PostgreSQL, if autovacuum can’t keep up with the write and bloat rate across many indexes, table and index bloat can grow unchecked, eventually causing severe performance degradation or, in extreme neglected cases, transaction ID wraparound risk.

Indexing is mostly a performance topic, but it has a couple of real security-adjacent implications worth knowing when doing threat modeling or compliance reviews.

• Data duplication risk: an index on a sensitive column (like a national ID number or email) creates another physical copy of that data on disk. Encryption-at-rest and access controls must cover index files too, not just table files — easy to overlook.
• Timing side channels (rare, theoretical): in specific, unusual setups, the presence or absence of an index can change query timing in ways that could leak information about data existence — a minor concern mostly relevant to security-sensitive systems doing careful threat modeling, not typical applications.
• Availability as a security property: excessive indexes degrading write performance can make a service more vulnerable to being overwhelmed by legitimate traffic spikes or write-heavy abuse patterns, which is a denial-of-service-adjacent concern.

You cannot manage what you don’t measure. Here’s how real teams find out which indexes are helping and which are just costing money.

12.1 Finding Unused Indexes

Most production databases expose statistics views for exactly this purpose.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;

This query lists indexes ordered by size, filtered to ones used fewer than 50 times since the last statistics reset. Large, rarely-used indexes are prime candidates for removal.

SELECT
    object_schema, object_name, index_name,
    count_star AS times_used
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_star ASC
LIMIT 20;

12.2 Key Metrics to Track

12.3 Tracing a Slow Write

Tools like EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, or slow query logs in MySQL, combined with application-level distributed tracing (e.g. OpenTelemetry spans around database calls), let engineers see exactly how long a write spends and correlate spikes with deployments that added new indexes.

How managed cloud databases change (and sometimes hide) this problem — and where the cost signal actually shows up on the bill.

• Provisioned IOPS billing: services like AWS RDS/Aurora, Azure SQL, and Google Cloud SQL often charge based on IOPS or throughput tiers. Excess index writes directly increase IOPS consumption and therefore cost.
• Storage auto-scaling: cloud databases that auto-grow storage can silently absorb index bloat for a long time, hiding the problem until the monthly bill spikes.
• Managed index advisors: many cloud providers now offer index recommendation tools (e.g. Azure SQL Database’s automatic tuning, AWS’s Performance Insights combined with recommendations) that suggest both adding and removing indexes based on real workload data — a modern best practice compared to manually guessing.
• Serverless databases: in serverless/auto-scaling database tiers (e.g. Aurora Serverless, PlanetScale), write amplification from excess indexes can trigger unnecessary scale-up events, increasing cost even more directly and visibly than in traditional fixed-capacity deployments.

How this issue interacts with the rest of a typical production data stack — and why architectural tricks often beat “just add one more index” on the primary.

• Application caching (Redis/Memcached): a common and effective way to reduce pressure to add more database indexes is to cache frequently-read results in an external cache. If most reads are served from Redis, the database doesn’t need an index for every possible read pattern — only for the patterns that actually hit the database.
• Read replicas + load balancing: read-heavy, differently-indexed workloads can be routed to dedicated read replicas (sometimes with extra indexes not present on the primary, in databases that support it), keeping the write-serving primary lean.
• CQRS pattern (Command Query Responsibility Segregation): a more advanced architecture where writes go to a lean, minimally-indexed write model, and a separate, heavily-indexed (or even a different database technology, like Elasticsearch) read model is built asynchronously for complex queries. This directly sidesteps the “too many indexes” problem by never putting all indexes on the same table that receives all the writes.

Certain shapes of indexing keep working in real production; certain other shapes keep causing incidents. Giving them names lets a team diagnose a problem in a hallway conversation rather than in a two-hour meeting.

Good Patterns

• Composite index consolidation: replacing three separate single-column indexes with one well-ordered composite index that can serve all three query patterns.
• Covering indexes for hot queries: adding extra columns to an index (an INCLUDE clause in PostgreSQL/SQL Server) so a critical, frequent query never has to touch the table at all — a deliberate, measured trade-off, not a guess.
• Partial indexes: indexing only the relevant subset of rows (e.g. WHERE status = 'active') instead of the whole table, cutting index size and write overhead dramatically when most rows don’t matter for that query.
• Regular index audits: a recurring (e.g. quarterly) review of index usage statistics, removing anything with near-zero real usage.

Anti-patterns

The gap between teams that stay healthy over time and teams that firefight the database every quarter is rarely one clever trick — it’s a small set of habits, practiced consistently.

Every large-scale operator has confronted this trade-off. A quick tour of how a few well-known companies handle it, followed by a hands-on Java demo you can run on your own machine to feel the cost yourself.

A Small Java Demonstration

A short JDBC example that measures insert throughput before and after adding several indexes, so the write-cost effect becomes something you can see with your own eyes, not just read about.

import java.sql.*;

public class IndexWriteCostDemo {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/demo";
        try (Connection conn = DriverManager.getConnection(url, "postgres", "password")) {

            setupTable(conn, false); // no extra indexes
            long noIndexTime = timeInserts(conn, 20_000);
            System.out.println("Insert time with 0 extra indexes: " + noIndexTime + " ms");

            setupTable(conn, true);  // 5 extra indexes added
            long withIndexTime = timeInserts(conn, 20_000);
            System.out.println("Insert time with 5 extra indexes: " + withIndexTime + " ms");

            double slowdown = (double) withIndexTime / noIndexTime;
            System.out.printf("Writes became %.2fx slower with 5 extra indexes%n", slowdown);
        }
    }

    // Creates a fresh table, optionally adding 5 extra indexes
    private static void setupTable(Connection conn, boolean withIndexes) throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute("DROP TABLE IF EXISTS demo_orders");
            st.execute("""
                CREATE TABLE demo_orders (
                    id BIGSERIAL PRIMARY KEY,
                    user_id INT,
                    status TEXT,
                    amount NUMERIC,
                    created_at TIMESTAMP,
                    notes TEXT
                )
            """);
            if (withIndexes) {
                st.execute("CREATE INDEX idx_user_id ON demo_orders(user_id)");
                st.execute("CREATE INDEX idx_status ON demo_orders(status)");
                st.execute("CREATE INDEX idx_created_at ON demo_orders(created_at)");
                st.execute("CREATE INDEX idx_user_status ON demo_orders(user_id, status)");
                st.execute("CREATE INDEX idx_status_created ON demo_orders(status, created_at)");
            }
        }
    }

    // Inserts N rows and returns elapsed time in milliseconds
    private static long timeInserts(Connection conn, int rowCount) throws SQLException {
        String sql = "INSERT INTO demo_orders (user_id, status, amount, created_at, notes) " +
                     "VALUES (?, ?, ?, now(), ?)";
        long start = System.currentTimeMillis();

        conn.setAutoCommit(false);
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < rowCount; i++) {
                ps.setInt(1, i % 1000);
                ps.setString(2, (i % 2 == 0) ? "paid" : "pending");
                ps.setBigDecimal(3, java.math.BigDecimal.valueOf(19.99));
                ps.setString(4, "sample order note");
                ps.addBatch();
                if (i % 500 == 0) ps.executeBatch(); // batch for realistic throughput measurement
            }
            ps.executeBatch();
        }
        conn.commit();
        conn.setAutoCommit(true);

        return System.currentTimeMillis() - start;
    }
}

What this shows: the code inserts the exact same 20,000 rows twice — once into a table with no secondary indexes, and once into an identical table with 5 typical indexes added. On most machines, you’ll observe the second run taking noticeably longer (commonly 1.3× to 3×, depending on hardware and row size), purely because of index maintenance overhead, with everything else held constant. This is a simple, honest way to feel the cost described throughout this tutorial rather than just taking it on faith.

A quick round of the questions that come up in almost every real conversation about indexing.

No universal number exists. It depends on write volume, row size, hardware, and how much read speed you actually need. The right approach is measuring real usage and write-latency impact, not following a fixed rule like “never more than 5.”

Usually not directly — indexes speed up reads. But indirectly, yes: too many indexes can pollute the cache and confuse the query planner, which can end up making even read queries slower in some cases, as explained in Section 7.

Yes, technically a primary key is implemented as a unique index internally, and it has the same maintenance cost as any other index. It’s simply unavoidable and essential, so it’s not usually counted as “excess,” but it does still cost write time like any index.

Raw storage cost has dropped a lot, but the real damage from excess indexes is mostly the write-latency and cache-pollution cost, not the dollar cost of disk space. Storage bloat is a visible symptom; write slowdown is the deeper issue.

No. Every database that supports secondary indexes has some version of this trade-off. MongoDB indexes also cost write throughput and RAM; DynamoDB even makes the cost explicit by charging separately for each Global Secondary Index’s provisioned capacity.

Check real usage statistics (Section 12) over a representative time window (ideally including any monthly/seasonal peak traffic), confirm the index isn’t backing a constraint you still need (like a unique or foreign key), and remove it in a lower-risk environment first if possible before production.

The full picture, distilled — a short, portable set of ideas you can carry into any indexing conversation or design review.