A product manager opens a ticket: the accounts list is slow for large tenants. An engineer finds the query, adds a composite index that matches the filter and sort order, deploys, and the page drops from 1.8 seconds to 40 milliseconds. Two days later the CSV export for the same accounts table times out, invoice creation p95 climbs, and a nightly reconciliation job that used to finish before breakfast is still running at noon. Nobody "broke" the database. Someone taught the planner a new favorite path, and every other path that shared that table paid for it.
This is the ordinary failure mode of index work in SaaS. Indexes are not free speed. They are a bet that one access pattern matters more than the others that touch the same rows. When the bet is local (one screen, one ticket, one demo tomorrow) it often wins locally and loses globally.
What the planner actually changed
After you add an index, the database does not promise to use it only for the query you had in mind. It re-costs every plan that can reach those columns. If the new index looks cheaper for a different query (even one that used to be fine with a sequential scan or a different index) the planner will switch. That switch is often invisible in your "fixed" page and loud everywhere else.
A common shape looks like this. The accounts list filters on tenant_id, status, and updated_at DESC, with a modest LIMIT. A composite index on (tenant_id, status, updated_at DESC) is a near-perfect match. The export job for the same tenant selects a much wider row set, sorts by created_at, and joins to billing lines. Before the new index, the planner might have used an older (tenant_id, created_at) index or a sequential scan that streamed well under parallel workers. After the deploy, it may prefer the new index for the tenant prefix, then filter and re-sort a large intermediate set that no longer fits the working memory you assumed. The list page still looks excellent. The export now does more random I/O than it did when it was "slow but honest."
Writes pay in a quieter way. Every insert, update, and delete on indexed columns maintains every index that includes those columns. One helpful composite index is usually fine. Five overlapping composites, each added for a different screen over six months, turn a single row update into a small fan-out of index maintenance. Invoice creation is a good canary here because it touches several tables in one request. The accounts list never sees that cost. Checkout does.
Partial indexes deserve special caution. An index on (tenant_id, updated_at) WHERE status = 'active' can make the active accounts page look miraculous. Any query that includes inactive rows, or that omits the status predicate the partial index requires, cannot use it. Worse, a query that almost matches (status in a list that includes active, or status compared through an expression) may still get a plan that looks related and behaves poorly. Partial indexes are precise tools. Precise tools punish imprecise callers.
Why SaaS pages fight each other
SaaS products share tables across product surfaces that believe they own the data model. The same accounts table backs a searchable list, a detail drawer, an admin audit view, a billing export, a webhook fan-out, and a support tooling page that a CS lead wrote once and never measured. Each surface wants a different leading column order, a different covering set, and a different idea of "hot."
Column order is where most of the local wins hide. An index on (tenant_id, status, name) helps equality filters on status followed by name search. An index on (tenant_id, name) helps typeahead. An index on (tenant_id, updated_at DESC) helps "recently changed." Teams often ship all three because each ticket was correct in isolation. The table then has three indexes that compete for the same prefix and disagree about what comes next. The planner picks one. Your mental model assumes another. Debugging starts with "why isn't it using my index" when the real question is "why did we create three answers to the same tenant prefix."
Covering indexes amplify the same conflict. If you INCLUDE enough columns to satisfy the list page without heap fetches, that page becomes cheap and persuasive in review. The wider the covering set, the larger the index, the more write amplification you accept, and the less likely a different query is to find a compact path. You optimized the page that files tickets. You taxed the paths that do not.
Multi-tenant cardinality makes this worse. A global index that looks selective in EXPLAIN on a small tenant can be a blunt instrument on a large one. Conversely, an index tuned on your biggest customer can be overkill (and write-heavy) for everyone else. If your load tests only use one tenant size, you are validating one product page for one customer shape, then shipping the index to the whole fleet.
ORM-generated queries add a second layer of surprise. Two screens that look similar in the UI can emit different predicate shapes: status = $1 versus status = ANY($1), nullable filters that become IS NULL branches, soft-delete clauses appended in middleware, and sort keys that change when a user clicks a column header. An index that matched last week's SQL may be a near miss for this week's. The planner does not care that both screens are "the accounts page."
How to add an index without owning the outage
Treat an index change like a schema change that affects read and write plans across the product, because that is what it is. Before you create it, collect the queries that already touch the table in production: the slow one, the write-heavy ones, and the large scans that only run for some tenants or on a schedule. If you cannot name those siblings, you are not ready to optimize one of them.
When you test, do not stop at the ticket query. Run the candidate plan for the list page, the export, an update path that modifies indexed columns, and at least one query that filters on a different secondary column. Compare estimated and actual rows. Watch whether the new index displaces an old one that still has a job. If EXPLAIN ANALYZE looks better for the list and worse for the export, you do not have a free win. You have a tradeoff that needs an owner.
Prefer fewer, wider-purpose indexes over a museum of near-duplicates. If two composites share a long prefix and diverge only in the last column used by a rare screen, ask whether the rare screen can tolerate a less perfect plan. If a partial index is tempting, write down the exact predicate the application must keep forever. Partial indexes rot when product rules drift and the WHERE clause in SQL no longer matches the WHERE clause in the index definition.
Measure writes, not only reads. Index wins are usually reported as read latency. The cost often shows up as insert/update time, replication lag, vacuum pressure, and autovacuum falling behind on a busy table. A list page that got faster while invoice creation got slower is not a performance success. It is a priority decision that was never named as one.
Finally, keep a short record of why each index exists: which page, which query shape, which ticket. Six months later someone will propose a fourth composite for the same table. The record is how you decide whether to extend an existing index, replace it, or refuse the request until the access patterns are reconciled. Databases do not negotiate product priorities. They execute the indexes you leave behind.
Indexes that fix one page are easy to celebrate. Indexes that quietly reshape every other plan on the table are the ones that show up in the next incident review. If you want the first without the second, stop treating index creation as a local patch and start treating it as a change to the shared access path for every feature that touches those rows.