Review this proposal. A team has six slow queries against a 400 GB table taking 40,000 writes a minute, and proposes adding six indexes, one per query. What would you remove, what would you change, and what would you keep?

What the proposal gets right

Indexes are the correct first instrument for a slow query, far ahead of caching or sharding, and the team read the plans before proposing. That is more discipline than most proposals of this shape carry.

What is missing: the write side

Every index is a second tree that every insert, and every update touching an indexed column, must also maintain, because the index must stay consistent with the heap inside the same transaction. Six indexes against 40,000 writes a minute is 240,000 additional index maintenance operations a minute, plus the write-ahead log they generate, plus the pages they dirty.

The second cost is quieter and usually larger: index pages compete with table pages for the buffer pool. Six new indexes on a 400 GB table add tens of gigabytes that want to be cached, evicting data pages that were making everything else fast. This is the cost that presents in production as "the whole database got slower last Tuesday" with no query to blame, and it is the failure that makes a well-intentioned index proposal look like an outage.

What I would remove

Probably four of the six. The recurring finding is that one well-chosen composite index serves several queries. If three of them filter on tenant_id and differ only in a second predicate, a single index on (tenant_id, created_at) with the right column order may serve all three. And check prefix redundancy: an index on (a) is dead weight if (a, b) exists.

What I would change

• Verify with the planner, not by reasoning. Create each candidate on a copy and confirm the plan actually uses it. An index the optimiser ignores is pure cost, and this happens more often than people expect once predicates involve functions or type coercion.
• Use partial indexes where a constant predicate exists. WHERE status = 'pending' on a table that is 99% completed gives an index a hundredth of the size with essentially all of the benefit.
• Prefer covering the query by including the projected columns, which removes the heap fetch. That is frequently a larger win than adding a separate index, and it costs one index instead of two.

What I would keep, even though it looks odd

The index serving the query nobody complains about but which runs every 30 seconds from a scheduled job or a health check. Aggregate load is frequency times cost: a 20 ms query at twice a second costs more total database time than a two-second query run hourly, and the complaint volume is inversely related.

How I would argue it

With measurement. Index size, write throughput before and after on a representative copy, and buffer-pool hit ratio. Then propose the smallest set that fixes the worst two queries and a review in two weeks.

And one question the proposal does not ask: which existing indexes can be dropped? Mature tables reliably carry indexes nothing has used in a year, the database records usage statistics that will tell you, and almost nobody looks. Dropping three is often how you afford to add two.

When six indexes is the right answer, and when not to apply this critique

On a read-mostly table — a dimension table, a reference dataset loaded nightly, a materialised view refreshed off-peak — write amplification is near zero and none of the analysis above applies. Index it for every access pattern you have. This critique is specific to 40,000 writes a minute, and applying it to a static table would be cargo-culted caution.