0Pricing
SQL Interview Prep · Lesson

When Indexes Hurt: Writes and Selectivity

Write amplification and why an index on a low-selectivity column is useless.

When Indexes Hurt: Writes and Selectivity is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question Behind the Question

After three lessons on why indexes help, interviewers flip it: 'Why not just index every column?' A strong candidate explains that indexes have real costs, on writes and in cache and storage, and that some indexes the planner will never even use.

This lesson covers the two big reasons an index can hurt: write amplification and low selectivity.

Every Index Slows Writes

An index must stay in sync with the table. Every INSERT, every DELETE, and every UPDATE to an indexed column must also update the index structure. This is write amplification: one row change becomes one table write plus one write per affected index.

A table with eight indexes pays roughly nine times the write work of an unindexed one. On write-heavy or high-throughput tables, that is a serious tax.

Worked Example: The Write Tax

Imagine an events table ingesting thousands of rows per second. Each extra index makes every insert do more work, splitting index pages, updating leaves, and competing for cache.

For an append-only, write-dominated table, the right answer is often few or no indexes beyond the primary key, and to do heavy reads on a replica or warehouse instead.

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

What Selectivity Means

Selectivity is how well a column distinguishes rows, the fraction of rows a typical value matches. High selectivity means few rows per value (like an email or a UUID). Low selectivity means many rows per value (like a boolean or a status with three options).

Indexes pay off on high-selectivity columns, where a lookup eliminates almost everything. On low-selectivity columns, they often do not.

Why a Low-Selectivity Index Is Useless

Suppose is_active is true for 90% of users. An index lookup would return 90% of the table, and for that many rows the engine would do a heap fetch per row, slower than just scanning the table sequentially in one pass.

So the planner correctly ignores the index and does a sequential scan. The index then only costs write overhead and storage while giving zero read benefit.

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

The Rough Threshold

A useful rule of thumb to state aloud: when a predicate matches more than roughly 5 to 20% of a table, a sequential scan usually beats an index scan, because random heap fetches cost more than streaming pages in order.

The exact crossover depends on row size, caching, and storage speed, which is why the planner uses statistics, not a fixed number, to decide.

Partial Indexes to the Rescue

If you only ever query the rare values of a skewed column, a partial index (Postgres) indexes just those rows, tiny, selective, and cheap to maintain.

If 1% of orders are pending and those are the ones you constantly query, index only them. The index stays small and the planner will gladly use it.

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Stale Statistics Mislead the Planner

The optimizer decides index-vs-scan from column statistics. If those are stale, after a bulk load or big update, it can misjudge selectivity and pick the wrong plan.

When an interviewer says 'the index exists but isn't used,' a great answer includes refreshing statistics with ANALYZE before blaming the index itself.

ANALYZE orders;  -- refresh planner statistics

Other Ways Indexes Hurt

Round out the answer with the lesser-known costs:

  • Storage and cache: indexes occupy disk and compete for memory, evicting useful data pages.
  • Redundant/overlapping indexes: maintained but never chosen.
  • Bloat: under heavy updates, B-Trees fragment and need REINDEX.
  • Optimizer confusion: too many similar indexes make planning slower and less predictable.

Finding Unused Indexes

To defend a real-world cleanup, mention that Postgres tracks index usage. Indexes with idx_scan = 0 are candidates to drop, they cost writes and space while never serving a read.

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

How to Phrase It in the Interview

A complete, balanced summary:

'Indexes cost write amplification, every insert/update/delete maintains them, plus storage and cache pressure. They only pay off on high-selectivity predicates; on a column where most rows match, the planner rightly prefers a sequential scan, so the index is pure overhead. For skewed columns I reach for a partial index, and I keep statistics fresh with ANALYZE and drop unused indexes.'

Quick Check

Decide which index is least likely to be worth its cost.

Recap: When Indexes Hurt

Key takeaways:

  • Every index adds write amplification plus storage and cache cost.
  • Indexes help on high-selectivity columns; on low-selectivity ones the planner prefers a sequential scan.
  • Above roughly 5 to 20% of rows matched, a scan usually wins.
  • Use a partial index for skewed columns you only query at the rare values.
  • Keep statistics fresh with ANALYZE and drop unused indexes (idx_scan = 0).

That completes the indexing-strategy course: build them where they earn their keep, and prove it with the plan.

Frequently asked questions

Is the “When Indexes Hurt: Writes and Selectivity” lesson free?

Yes — the full text of “When Indexes Hurt: Writes and Selectivity” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “When Indexes Hurt: Writes and Selectivity”?

Write amplification and why an index on a low-selectivity column is useless. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “When Indexes Hurt: Writes and Selectivity” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. B-Tree Indexes and How They Help
  2. Composite Index Column Order
  3. Covering Indexes and Index-Only Scans
  4. When Indexes Hurt: Writes and Selectivity
← Back to SQL Interview Prep