Anti-pattern: Over-indexing

"If the query is slow, I add an index." It's the default junior answer — and often correct. The problem is that it generalizes to "I add indexes just in case," and each extra index that isn't used is permanent cost with no benefit. INSERTs and UPDATEs slow down, disk space grows, autovacuum takes longer, maintenance gets complicated.

On tables with many "just in case" indexes, the difference between 200ms INSERTs and 2,000ms ones is the number of unused indexes. An audit with pg_stat_user_indexes can reveal that 30% of your indexes are never used — space and time wasted on every write.

In this capsule you're going to see the real cost of each index (a benchmark), learn to audit indexes with system catalogs, and develop the judgment to decide which ones to keep and which to remove. The simple rule: an index is justifiable if a query uses it frequently and the improvement is measurable. There's no other justification.

The real cost of each index

You're going to measure the direct impact. Create two identical tables, one without indexes and another with many.

DROP TABLE IF EXISTS no_indexes;
CREATE TABLE no_indexes (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    note TEXT
);

DROP TABLE IF EXISTS many_indexes;
CREATE TABLE many_indexes (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    note TEXT
);

-- 8 indexes on the second table
CREATE INDEX idx_mi_customer ON many_indexes(customer_id);
CREATE INDEX idx_mi_status ON many_indexes(status);
CREATE INDEX idx_mi_amount ON many_indexes(amount);
CREATE INDEX idx_mi_created ON many_indexes(created_at);
CREATE INDEX idx_mi_updated ON many_indexes(updated_at);
CREATE INDEX idx_mi_customer_status ON many_indexes(customer_id, status);
CREATE INDEX idx_mi_customer_created ON many_indexes(customer_id, created_at);
CREATE INDEX idx_mi_status_amount ON many_indexes(status, amount);

Measure INSERTs:

\timing on

-- INSERT into the table without indexes
INSERT INTO no_indexes (customer_id, status, amount, note)
SELECT
    (random() * 10000)::INT,
    (ARRAY['pending', 'shipped'])[floor(random() * 2)::INT + 1],
    (random() * 1000)::NUMERIC(10, 2),
    'note ' || generate_series
FROM generate_series(1, 50000);

-- INSERT into the table with 8 indexes
INSERT INTO many_indexes (customer_id, status, amount, note)
SELECT
    (random() * 10000)::INT,
    (ARRAY['pending', 'shipped'])[floor(random() * 2)::INT + 1],
    (random() * 1000)::NUMERIC(10, 2),
    'note ' || generate_series
FROM generate_series(1, 50000);

\timing off

Typical results (they'll vary depending on hardware):

INSERT 50000 without indexes:    ~280ms
INSERT 50000 with 8 indexes:  ~1850ms

A difference of ~6-7x. With 50k INSERTs in under a second of bulk load, you don't notice. With 1M INSERTs in production over a day, the difference accumulates.

Measure UPDATE:

\timing on

-- Massive UPDATE without indexes
UPDATE no_indexes SET note = 'updated_' || id;

-- Massive UPDATE with indexes
UPDATE many_indexes SET note = 'updated_' || id;

\timing off

Here the difference depends on the type of update. If you update a non-indexed column (note), the impact is minimal (HOT update). If you update an indexed column (status), the impact is massive.

\timing on
UPDATE no_indexes SET status = 'cancelled' WHERE id < 10000;
UPDATE many_indexes SET status = 'cancelled' WHERE id < 10000;
\timing off

Typical results:

UPDATE of status without indexes:   ~80ms
UPDATE of status with indexes:   ~450ms (5-6x slower)

Each index that touches the updated column (idx_mi_status, idx_mi_customer_status, idx_mi_status_amount) requires updating. More indexes touching the column = more work.

Why each index costs

When you insert or update a row:

1. PostgreSQL writes the new tuple to the heap (the main table).
2. For each index that covers any of the affected columns, it adds/updates an entry in the index's B-tree.
3. It does an fsync (on commit) for durability.

Each extra index adds:

• CPU: finding the right position in the B-tree.
• I/O: writing the new entry to disk (at least in the WAL).
• Memory: index pages cached in shared_buffers.
• Lock contention: under high concurrency, the index tree can be a point of contention.

And that's only the cost of maintaining the index. There are secondary costs:

• Autovacuum: bloat in indexes also accumulates. More indexes = more work for autovacuum.
• Disk space: each index has its file. They can add up to 30-50% of the table size.
• Backup time: backups copy everything, including indexes.
• Replication lag: each change is replicated with all the indexes.
• Mental overhead: code reviews have to consider whether the queries use the right indexes. More indexes = more confusion.

Audit: which indexes do you have and which are used?

PostgreSQL tracks the usage of each index in pg_stat_user_indexes.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,        -- how many times it was used
    idx_tup_read,    -- index entries read
    idx_tup_fetch,   -- rows fetched via this index
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

Typical output:

schemaname | table_name      | index_name                  | idx_scan | idx_tup_read | size
public     | many_indexes    | idx_mi_amount               |        0 |            0 | 4 MB
public     | many_indexes    | idx_mi_status_amount        |        0 |            0 | 5 MB
public     | many_indexes    | idx_mi_updated              |        0 |            0 | 4 MB
public     | many_indexes    | idx_mi_customer             |    1234 |       12345 | 3 MB
public     | many_indexes    | idx_mi_customer_status      |   45678 |      234567 | 5 MB

idx_scan = 0 is the clear signal: the index has never been used since the stats were reset. Three of them can be in this state:

1. Never used because it doesn't apply to real queries — a candidate for removal.
2. Stats were reset recently — check the stats_reset time.
3. It's a PK the ORM never queries directly — but PKs are always justifiable.

To rule out case 2:

SELECT stats_reset
FROM pg_stat_database
WHERE datname = current_database();

If stats_reset was 10 minutes ago, don't expect idx_scan to be representative. Wait for at least a week of stats before making decisions.

Detecting duplicate or redundant indexes

Sometimes it's not that the index isn't used — it's that it's redundant with another.

Case 1: index covered by a composite

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

The second index covers the first (PostgreSQL can use it for queries that only filter by customer_id). The first index is redundant — but it takes up space and cost on writes.

Detection:

SELECT
    indexrelid::regclass AS index,
    indrelid::regclass AS table,
    array_to_string(indkey::int[], ',') AS columns
FROM pg_index
WHERE indrelid::regclass::TEXT = 'orders'
ORDER BY indrelid, indkey;

The output shows the indexed columns. If you see customer_id and customer_id, status, the first is redundant (unless it's a PK, which has other uses).

Case 2: indexes with the same prefix

CREATE INDEX idx_orders_ab ON orders(col_a, col_b);
CREATE INDEX idx_orders_abc ON orders(col_a, col_b, col_c);

The second covers the first. Keep only the second if the first isn't used for something specific.

Case 3: exact duplicate indexes

CREATE INDEX idx_orders_a ON orders(col_a);
CREATE INDEX idx_users_col_a ON orders(col_a);  -- different name, identical content

It happens due to mistakes in migrations. Detection:

SELECT
    indrelid::regclass AS table,
    indkey,
    COUNT(*) AS count,
    array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

Any result with count > 1 is duplication.

How to decide what to remove

Don't remove indexes blindly. The correct process:

Step 1: identify candidates

A list of indexes with idx_scan = 0 after representative stats (at least 1 week of uptime).

Step 2: verify they aren't indispensable

Some indexes have idx_scan = 0 but are indispensable:

• PKs: they never appear in idx_scan if queries use the ORM with a direct PK lookup, but they're critical for integrity.
• Unique constraints: they enforce uniqueness, not just lookup.
• FK constraints: PostgreSQL creates them automatically when you declare REFERENCES. If you remove them, the queries the ORM does to validate an FK lock become Seq Scans.
• Recent indexes: new stats, not representative.

To check whether an index is a PK/Unique:

SELECT
    indexrelid::regclass AS index,
    indisunique AS is_unique,
    indisprimary AS is_primary
FROM pg_index
WHERE indexrelname = 'idx_orders_xyz';

If is_primary = true or is_unique = true, DON'T remove it without deep analysis.

Step 3: drop with CONCURRENTLY in production

-- In production, always CONCURRENTLY
DROP INDEX CONCURRENTLY idx_orders_unused;

CONCURRENTLY doesn't lock the table. The operation takes longer (it partially rewrites the table) but it doesn't cause downtime.

Step 4: validate afterward

Wait 24-48 hours and check that there was no regression:

• Did any query start doing a Seq Scan?
• Did any API get slower in p95?

If there's a regression, you recreate the index (CREATE INDEX CONCURRENTLY). The "test in production with a fast rollback" is safe because both commands are online.

Step 5: monitor continuously

Do an audit every 3-6 months, not just once. Apps change, queries change, what was useful 6 months ago may not be today.

When you SHOULD create a new index

The rule: create an index when you can show the query that's going to use it and the measurable improvement.

Before creating:

1. Identify the slow query with pg_stat_statements or EXPLAIN.
2. See the current plan: is it a Seq Scan? Why?
3. Predict which index would help: composite? partial? covering?
4. Create with CONCURRENTLY in production.
5. Validate the plan afterward: confirm the planner chooses it.
6. Measure the improvement: the query before/after.

If you can't answer "which exact query is going to use it," don't create it. It's the difference between reactive creation (justified) and proactive ("just in case," an anti-pattern).

Traps and common mistakes

1. Creating an index for every column "just in case".

A schema with 10 columns doesn't need 10 indexes. It needs 1-3 indexes that correspond to real queries. The rest are cost with no benefit.

2. Forgetting CONCURRENTLY in production.

CREATE INDEX takes a ShareLock that blocks writes. On a large table it can take minutes — during those minutes, writes stall. CREATE INDEX CONCURRENTLY is online.

-- ❌ In production
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ In production
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

3. Drop and recreate within the same migration.

# ❌ Migration anti-pattern
def upgrade():
    op.drop_index('idx_orders_old')
    op.create_index('idx_orders_new', ...)

Between the drop and the create, the queries that depended on the old index are going to do a Seq Scan. On a large table this is an incident. Better: create the new one, wait, drop the old one.

4. Trusting idx_scan = 0 right after a major deploy.

The stats were reset or the deploy changed queries. Wait at least 1 week before judging whether an index "isn't used."

5. Creating indexes "for the future".

"It's going to be needed when we reach 10M rows." Probably not. And if it does become needed, you create it at that moment. Creating it now pays the write cost from day 1 with no benefit.

6. Not considering the disk space cost.

On cloud instances with storage charged by GB, unnecessary indexes are a direct monetary cost. A 100GB table with 50GB of duplicate indexes is 50GB paid for no reason.

7. Thinking more indexes = faster in general.

Selective reads, yes. But the read/write balance matters. If your table receives 1M writes/day and 100 reads that could use the index, the index probably costs more than it saves.

8. Ignoring INVALID indexes.

If CREATE INDEX CONCURRENTLY fails for any reason, it leaves an index marked as INVALID. These indexes take up space but aren't used. Detection:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

If you find any, drop and recreate: DROP INDEX CONCURRENTLY ...; CREATE INDEX CONCURRENTLY ....

Exercise: index audit

Setup: a table with several indexes, some used and others not.

DROP TABLE IF EXISTS audit_demo;
CREATE TABLE audit_demo (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    note TEXT
);

CREATE INDEX idx_demo_customer ON audit_demo(customer_id);
CREATE INDEX idx_demo_status ON audit_demo(status);
CREATE INDEX idx_demo_amount ON audit_demo(amount);     -- never used in this exercise
CREATE INDEX idx_demo_note ON audit_demo(note);          -- never used
CREATE INDEX idx_demo_customer_status ON audit_demo(customer_id, status);

-- Insert 100k rows
INSERT INTO audit_demo (customer_id, status, amount, note)
SELECT
    (random() * 1000)::INT + 1,
    (ARRAY['pending', 'shipped'])[floor(random() * 2)::INT + 1],
    (random() * 1000)::NUMERIC(10, 2),
    'note ' || generate_series
FROM generate_series(1, 100000);

ANALYZE audit_demo;

Step 1: run queries that DO use some indexes.

-- Reset stats to ensure clean measurements
SELECT pg_stat_reset();

-- Queries that use the indexes
EXPLAIN ANALYZE SELECT * FROM audit_demo WHERE customer_id = 500;
EXPLAIN ANALYZE SELECT * FROM audit_demo WHERE customer_id = 200 AND status = 'pending';
EXPLAIN ANALYZE SELECT * FROM audit_demo WHERE status = 'shipped' LIMIT 100;
EXPLAIN ANALYZE SELECT * FROM audit_demo WHERE customer_id = 800;

-- ... run several times to accumulate stats

Step 2: audit usage.

SELECT
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'audit_demo'
ORDER BY idx_scan ASC;

Which indexes have idx_scan = 0?

Step 3: identify redundancies.

SELECT
    indrelid::regclass AS table,
    indkey
FROM pg_index
WHERE indrelid = 'audit_demo'::regclass
ORDER BY indkey;

Is there any index covered by another composite?

Step 4: decide what to remove.

A list of candidates for removal. Justify each one.

Step 5: drop CONCURRENTLY and measure the impact on INSERTs.

\timing on

-- INSERT before the drop
INSERT INTO audit_demo (customer_id, status, amount, note)
SELECT (random()*1000)::INT, 'pending', 50, 'x' FROM generate_series(1, 10000);

-- Drop unused indexes
DROP INDEX CONCURRENTLY idx_demo_amount;
DROP INDEX CONCURRENTLY idx_demo_note;

-- INSERT afterward
INSERT INTO audit_demo (customer_id, status, amount, note)
SELECT (random()*1000)::INT, 'pending', 50, 'x' FROM generate_series(1, 10000);

\timing off

By how much did the INSERT improve?

index_name                | idx_scan | size
idx_demo_amount           |        0 | 2 MB
idx_demo_note             |        0 | 4 MB
idx_demo_status           |        1 |  900 kB
idx_demo_customer         |        2 |  900 kB
idx_demo_customer_status  |        1 | 1.5 MB
audit_demo_pkey           |        0 |  900 kB

INSERT before (with 6 indexes):  ~120ms
INSERT after (with 4 indexes): ~80ms

Summary and next step

What you learned:

• Each index costs on INSERT, UPDATE, disk space, autovacuum, replication, mental overhead.
• Audit with pg_stat_user_indexes: indexes with idx_scan = 0 after representative stats (>1 week) are candidates for removal.
• Exceptions: PKs, unique constraints, FK constraints — check before removing.
• Redundant indexes: composites cover simple indexes with a common prefix. Detectable with pg_index.
• DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY in production: online, without downtime.
• Create reactively, not proactively. The slow query justifies the index — "just in case" doesn't.
• Periodic audit (every 3-6 months): indexes that contributed before may not contribute today.

Before moving on, you should be able to:

• Audit indexes with pg_stat_user_indexes and pg_index.
• Identify candidates for removal and justify each decision.
• Differentiate "useful but marginal" indexes from "clearly unnecessary."
• Apply CONCURRENTLY correctly in production.

In the next capsule you go to an anti-pattern of mindset, not of code: premature optimization. More Knuth applied to the database — what to tune, what not to, how to prioritize. We also cover SELECT * — a visible anti-pattern that blocks index-only scans and transfers unnecessary data. Two topics that share an idea: effective optimization is deliberate, based on measurement, not instinctive.

Resources

1. PostgreSQL Docs — pg_stat_user_indexes — the official reference for the view.
2. Markus Winand — "Don't add anomalies" — patterns to avoid when creating indexes.
3. PostgreSQL Wiki — idx_scan and stats — an operational maintenance guide.
4. pg_repack — index repacking — an alternative for indexes with bloat.
5. Crunchy Data — Index Maintenance — an operational blog with cases.
6. Citus Data — Unused indexes detection — analysis of the cost.
7. GitLab — Database team index review process — a real process from teams at scale.

Capsule 04 of 08 — Module 8 — Database Performance & Query Tuning Guide