Default B-tree indexes fail when query shape, data shape, and growth curve stop matching.
01 THE PROBLEM
Indexing debt is the failure mode where a database keeps using default B-tree indexes long after workload reality has changed.
It starts innocently. A product team ships on PostgreSQL or MySQL, adds a few primary keys, maybe a composite index or two, and things stay fast at 5 million rows. Then the company adds event data, JSON payloads, full-text search, time-series retention, tenant isolation, or AI retrieval metadata. The same indexing strategy becomes the hidden tax on every write, every vacuum cycle, every cache miss, and every slow query.
The consequence is not just “queries get slower.” The consequence is operational drag that compounds over quarters.
Read latency spikes first.
Then write amplification shows up in replication lag and autovacuum pressure.
Then storage costs climb because indexes outgrow memory and stop being cache-resident.
Then engineers start chasing symptoms: bigger instances, more replicas, sharding discussions, read-through caches, or a migration to a “faster database” that mostly masks a design mistake.
This is why indexing mistakes are expensive. They masquerade as scaling problems.
A B-tree is an excellent general-purpose index. It is the correct default for primary keys, range scans, ordered retrieval, and a large share of OLTP workloads. But “excellent default” is not the same as “correct long-term strategy.”
Once your workload has one of these properties, the default often becomes the bottleneck:
- append-heavy time-series or event tables
- high-cardinality JSONB search
- trigram or full-text pattern matching
- equality-only hot paths at extreme scale
- skewed multi-tenant access patterns
- soft deletes and sparse query predicates
- write-heavy tables with too many overlapping indexes
The practical issue is that most teams discover this too late. They don’t notice when the index has become the heaviest part of the table, when page splits are dominating writes, or when a wide composite index is carrying dead weight because the query planner only uses the first prefix.
By the time the pain is visible to customers, the fix is no longer “add an index.” The fix is usually a risky combination of reindexing, changing query patterns, deleting indexes nobody owns, partitioning, and teaching teams that not every access path deserves a B-tree.
For CTOs and Staff+ engineers, this matters because indexing strategy is one of the few database choices that affects all four of the DORA metrics indirectly: deployment friction, incident frequency, restoration complexity, and lead time for schema changes. Every large index increases migration time, lock risk, rollback complexity, and the blast radius of a bad release. The 2023 Google Cloud and DORA State of DevOps reporting continues to reinforce the same core pattern from the Accelerate research by Nicole Forsgren, Jez Humble, and Gene Kim: throughput and stability improve together when teams reduce change risk through better system design, not when they brute-force around it.
The database version of that principle is simple: if your indexes don’t match your workload, you are paying a tax on every deploy.
02 WHY IT HAPPENS
The root cause is structural: teams optimize for query correctness early, but they only optimize for access path economics after pain appears.
That bias is rational. In the first 12 to 18 months of a product, the default B-tree solves most real problems. Equality lookups, primary key joins, timestamp ranges, and sort-by-created-at views all work fine. The team gets rewarded for shipping features, not for predicting a future indexing topology.
Then the workload changes faster than the schema discipline.
Three shifts usually happen at once.
First, the data model becomes less relational than the database. Product teams start storing operational flexibility in JSONB columns, denormalized metadata, event blobs, tags, embeddings, and semi-structured properties. PostgreSQL supports this extremely well, but not with plain B-tree indexes. JSONB containment and existence checks want GIN. Full-text wants GIN or GiST depending on the use case. Append-only timestamp-correlated data may want BRIN because the table’s physical order carries enough information to make a block-range index efficient.
Second, the read pattern fragments. The original product may have had three core screens and ten predictable queries. By Series B, the same company often has admin tools, internal ops consoles, analytics slices, audit views, customer exports, search, API filtering, and machine-generated workloads. One table that used to have two access paths now has fifteen. Teams respond by adding indexes tactically, which means index sprawl. They rarely remove old indexes because deletion feels riskier than addition.
Third, write volume stops being negligible. Every extra index turns one row insert into multiple tree updates. On hot tables, that cost is not theoretical. It shows up as lock contention, page churn, cache eviction, WAL growth, replication lag, and vacuum pressure.
This is why mature teams treat indexing as workload design, not schema decoration.
PlanetScale has been unusually clear on this point in its public engineering content around MySQL and Vitess: indexes are a tradeoff paid on writes, storage, and operational complexity, not just a free performance feature. Their guidance repeatedly emphasizes pruning unused indexes and designing for actual query paths because every secondary index has maintenance cost. That’s true in PostgreSQL, MySQL, and distributed SQL alike.
GitHub has discussed similar realities in its MySQL scaling work over the years: database performance bottlenecks are often not the absence of indexes, but the accumulation of access paths and patterns that no longer fit the original data model. Once tables become business-critical and write-heavy, every schema change must be evaluated for its effect on replication, migrations, and operational risk.
The planner adds another structural wrinkle. Teams assume “I created an index, therefore the database will use it.” That is false often enough to hurt.
Planners choose based on statistics, selectivity, correlation, cost estimates, and available operators. A B-tree on a JSONB column may exist but still be useless for the operator your query uses. A composite index on `(tenant_id, status, created_at)` may fail to help if the query shape often omits the leftmost column or if cardinality makes a bitmap heap scan cheaper. A B-tree on a low-selectivity boolean column usually won’t save you because scanning half the table through the index can cost more than reading the heap directly.
The mismatch is not incompetence. It is the predictable outcome of software organizations where schema ownership is diffuse, observability on index utility is weak, and the cost of extra indexes is delayed.
The pattern that emerges at scale is this: product complexity grows continuously, but index strategy usually grows by ticket.
03 WHAT MOST GET WRONG
The most common misdiagnosis is “the table is large, so we need more indexes.”
That is wrong often enough to become dangerous.
Large tables do not automatically need more indexes. They need fewer, sharper ones aligned to the dominant query operators and the table’s write pattern.
The second common mistake is treating B-trees as universal because they are safe defaults.
This creates three failure modes.
Failure mode 1: indexing the column, not the query
Teams index `metadata`, `payload`, or `body` and expect the planner to accelerate JSON containment, array membership, or text search. It does not. Query operators matter. PostgreSQL’s `@>`, `?`, `?|`, `@@`, full-text `@@`, and trigram similarity all have operator classes designed for specialized indexes. If you use the wrong index type, you pay the write cost without earning the read benefit.Failure mode 2: over-indexing write-heavy tables
A hot orders, events, jobs, or logs table accumulates indexes for every dashboard and support workflow. Inserts that should be cheap become expensive. Teams then scale the instance vertically and congratulate themselves on “fixing” the problem, even though they only raised the threshold for the same failure.Failure mode 3: using composite indexes as a substitute for query discipline
Developers add `(a, b, c, d)` because several queries involve those columns in some order. But composite indexes are not magic bags of fields. Their utility depends heavily on left-prefix matching, sort direction, and predicate selectivity. One broad composite index often performs worse operationally than two targeted indexes and one query rewrite.A lot of teams also misunderstand partial indexes. If 95% of rows are inactive, soft-deleted, archived, or belong to old states nobody queries in the hot path, indexing all rows is waste. A partial index can slash index size and maintenance overhead. Yet many engineering teams never revisit old “active = true” patterns after the table passes tens or hundreds of millions of rows.
The expensive part is that these mistakes look like success for a while.
The query gets faster in staging.
The incident goes away for two weeks.
The dashboard is green after an instance upgrade.
Then the write path slows, autovacuum falls behind, and bloat creeps in.
Shopify’s engineering organization has written extensively about operating MySQL at scale, including the reality that every index imposes write and storage costs. Their database work reflects a discipline larger organizations develop out of necessity: indexes are managed assets with lifecycle cost, not one-time fixes. That is the lesson smaller startups often learn too late.
There is also a subtler mistake: teams jump from “B-tree is bad here” to “specialized indexes everywhere.”
That fails too.
GIN indexes can be large and slower to update than B-trees. BRIN indexes are tiny and excellent for naturally ordered data, but poor when values are randomly distributed. Hash indexes are narrow tools for equality-only cases and still lose much of the time to B-trees because B-trees support more operators and broader planner utility. Trigram indexes are powerful for `LIKE` and fuzzy search, but expensive if used indiscriminately on frequently updated text columns.
The practical error is not choosing B-tree. The practical error is never asking what cost model your workload now demands.
A good cautionary analogue comes from incidents around migrations and schema churn more broadly. GitLab has published multiple postmortems where database changes, background migrations, or schema-heavy operations created operational risk because the real runtime cost in production differed from expectations. The lesson generalizes to indexing: the danger is not the concept; the danger is applying it without production-aware cost accounting.
04 THE FRAMEWORK
What works is not “pick a better index type.” What works is a repeatable indexing review process tied to workload evidence.
Use this framework.
1. Start with the top 10 queries by total database time, not the slowest single query
The slowest query is often a distraction.
The better lens is cumulative cost: which query patterns consume the most total CPU, I/O, or lock time across a day? PostgreSQL’s `pg_stat_statements` and MySQL performance schema give you this directly. Focus on `total_exec_time`, call count, shared block hits/reads, temp usage, and rows examined versus rows returned.
A query that takes 8 ms but runs 50 million times a day matters more than a 3-second admin export that runs twice.
Stripe’s engineering organization has repeatedly emphasized workload-aware infrastructure decisions in public talks and writing: optimize the paths that dominate production usage, not the anecdote that gets the most attention. Applied to indexing, that means ranking by aggregate cost.
A practical threshold: if a query family consumes more than 5% of total database CPU or appears in the top 10 by total time for seven consecutive days, treat its index strategy as a first-class engineering task.
2. Classify the query by operator, not by entity
This is where most teams level up.
Do not think “this is the users table” or “this is the events table.” Think in operators:
- exact equality: `=`, `IN`
- range: `<`, `>`, `BETWEEN`
- ordering: `ORDER BY created_at DESC LIMIT 50`
- containment: JSONB `@>`, arrays
- existence: JSONB `?`
- pattern matching: `LIKE`, `ILIKE`, regex
- full-text search
- geospatial
- nearest-neighbor or vector retrieval
- sparse predicates: `WHERE deleted_at IS NULL`
Each operator class points toward likely candidates:
- B-tree for equality, range, sorting, and join keys
- Hash only for narrow equality-only cases; usually not worth the tradeoff
- GIN for JSONB containment, arrays, full-text, trigram
- GiST for geometric, range, nearest-neighbor, some full-text cases
- BRIN for very large, naturally ordered tables, especially append-heavy time-series
- Partial indexes when hot queries touch a minority of rows
- Expression indexes when the query uses a derived expression consistently, such as `lower(email)`
PostgreSQL’s own documentation is still the best reference here because operator support is exact, not approximate. Choosing an index without matching the operator class is where false confidence starts.
3. Measure index utility in memory terms, not just query-plan terms
The key strategic question is not “does this index help one query plan?” It is “can the working set of this index stay hot enough to justify itself?”
Once indexes outgrow RAM, B-tree defaults become much more expensive. You get more random I/O, more cache churn, worse heap fetch locality, and less predictable latency under burst traffic.
For OLTP systems, an operationally useful benchmark is this: if your hot table’s combined heap plus critical indexes no longer fit comfortably within the memory allocated to the database buffer cache and OS page cache, expect tail latency to worsen before average latency does. This is where leaders get fooled. P50 stays acceptable while P95 and P99 drive customer-visible incidents.
Cloudflare’s engineering writing on systems performance consistently highlights this broader truth: averages lie, and tail behavior is where architecture choices become product problems. The same is true for index working sets.
Track:
- index size by relation
- cache hit ratio by relation if available
- `idx_scan` versus `seq_scan`
- heap fetches required after index scans
- WAL volume after adding or changing indexes
- replication lag during peak write windows
If an index is 80 GB, touched constantly, and your buffer pool budget for hot data is 40 GB, your real problem may be data shape or table design rather than “need another replica.”
4. Remove indexes before adding more
This feels backwards. It is often the highest-ROI move.
Use PostgreSQL’s `pg_stat_user_indexes` or MySQL equivalents to identify indexes with negligible scan counts over a meaningful interval, such as 14 to 30 days. Then validate manually against known periodic jobs, monthly reports, and rare but critical workflows.
On mature systems, index pruning can produce immediate gains:
- lower write latency
- lower WAL generation
- less storage
- faster vacuum and maintenance
- simpler planner choices
- faster schema changes
PlanetScale has publicly advocated index hygiene as a recurring operational discipline, not a one-time cleanup. That advice is easy to underestimate until you see a hot table carrying six secondary indexes added by six different engineers over eighteen months.
A useful rule: if an index is not tied to a named production query pattern and does not support a constraint, it should be reviewed for deletion.
5. Use partial indexes aggressively on skewed hot paths
This is one of the most underused tools in PostgreSQL.
If your dashboard, API, or queue only touches active rows, don’t index archived rows.
Examples:
- `WHERE deleted_at IS NULL`
- `WHERE status IN ('pending', 'running')`
- `WHERE published = true`
- `WHERE tenant_id = ? AND archived = false`
A partial index can be dramatically smaller than a full-table B-tree, which means better cache residency and lower write overhead.
This is especially important in SaaS products where the “live” working set may be 5% to 20% of historical data, but engineers keep indexing 100% of rows out of habit.
Linear’s product characteristics make this pattern intuitive: issue trackers and project systems have high read demand on active items and long tails of historical data. Any system with “current work” versus “archive” semantics should suspect that a full index is overpaying for cold rows.
A practical trigger: if fewer than 30% of table rows satisfy the predicate used by the majority of hot queries, evaluate a partial index before adding a broader full-table index.
6. Use BRIN for append-heavy giant tables before reaching for partitioning
Teams often jump to partitioning too early because they feel the table is “too large for indexes.”
For append-heavy tables where physical row order correlates strongly with a timestamp or monotonically increasing key, BRIN can change the economics completely. BRIN stores summaries per block range rather than an entry per row, so it stays tiny even at very large row counts. It is not a replacement for B-tree on every query, but for broad time-bounded scans on operational event data, it can be the right first move.
This matters because partitioning adds routing complexity, planner overhead, operational work, and migration risk. A well-chosen BRIN index can buy you quarters before partitioning is warranted.
Use BRIN when:
- table is huge
- inserts are append-mostly
- queried column is correlated with physical insertion order
- range predicates dominate
- precision can be looser because the database will recheck candidate pages
Do not use BRIN when values are random and frequently updated.
7. Use GIN for JSONB and search, but budget for write cost
The typical anti-pattern in AI-first startups is storing flexible product metadata, event payloads, model settings, and retrieval tags in JSONB, then indexing none of it properly or trying to force B-tree onto one extracted field at a time.
GIN is often the right answer for JSONB containment and existence queries.
But GIN is not free.
It can be large.
It is slower to update.
It can make bulk writes or high-churn rows noticeably more expensive.
That tradeoff is worth it when the query path is core to the product. It is wasteful when used as a blanket answer to “we search a lot of JSON.”
Figma, Notion, and other collaborative products have publicly discussed the broad pattern of carefully choosing storage and indexing approaches based on access patterns rather than insisting on one canonical shape. Even when the exact index type differs by engine or service, the strategic lesson holds: flexible schemas demand intentional access-path design.
If JSONB queries are central, standardize them. Choose a small set of operators and shapes. Random ad hoc predicates across arbitrary JSON keys create index sprawl and planner unpredictability.
8. Reorder composite indexes around reality, not theory
Composite indexes should reflect the actual query prefix used most often.
If nearly every query scopes by `tenant_id`, put it first.
If the system is multi-tenant and every request is tenant-bound, leaving `tenant_id` out of the leading position is one of the fastest ways to create bloated scans and cache inefficiency.
This is a common failure in B2B SaaS: an engineer designs for single-tenant local reasoning, then the production planner has to traverse large cross-tenant index sections before applying other filters.
For a query pattern like:
```sql WHERE tenant_id = $1 AND status = 'pending' ORDER BY created_at DESC LIMIT 50 ```
the likely useful index is:
```sql (tenant_id, status, created_at DESC) ```
not `(status, created_at)` and not `(created_at, tenant_id)`.
This sounds obvious until you audit a live production schema.
9. Treat `EXPLAIN ANALYZE` as necessary but insufficient
An `EXPLAIN ANALYZE` on one parameter value is not enough.
You need to test:
- hot tenant versus cold tenant
- selective predicate versus broad predicate
- before and after cache warmup
- write load in the background
- realistic result set sizes
A plan that looks great on a highly selective test case can degrade badly on a tenant with skewed distribution. This is where operator-level thinking matters. Production cardinality is rarely uniform.
Netflix’s engineering culture has repeatedly stressed testing systems under realistic production conditions, not idealized local assumptions. Database plan evaluation is no different.
10. Put index review into the engineering operating cadence
This is where strategy becomes habit.
If index changes only happen during incidents, your organization will always be reactive.
Create a monthly or quarterly review for the top write-heavy tables and top cumulative-cost queries. This does not need a database guild or a six-week architecture process. It needs one owner, one dashboard, and one ruthless question: which indexes are still paying rent?
For startups between 20 and 200 engineers, this is often the difference between “the database team is overloaded” and “the product teams can move safely.”
A lightweight review should include:
- top 20 queries by total DB time
- top 20 largest indexes
- indexes with near-zero scans in the last 30 days
- write-heavy tables with more than 5 secondary indexes
- tables where index size exceeds heap size
- migrations blocked or slowed by index maintenance
- p95/p99 query latency by query family
If your table has more bytes in indexes than in primary data, stop and justify every one of them.
That threshold is not universally bad, but it is universally worth scrutiny.
05 STRATEGIC TAKEAWAY
Default B-trees become costly when leadership mistakes “works today” for “scales with our workload.” If you apply an operator- and workload-driven indexing strategy, you usually defer a database migration, reduce write-path friction, and shrink the blast radius of schema changes within one quarter. If you do not, the cost shows up as larger instances, slower deploys, noisier incidents, and premature architectural rewrites disguised as scaling strategy. The CTO decision is not whether to care about indexing; it is whether to pay with planned engineering time now or with emergency infrastructure spend and migration risk later.
06 IMPLEMENTATION ANGLE
Start with instrumentation, not brainstorming. In PostgreSQL, enable `pg_stat_statements`, collect relation and index sizes, and keep a weekly snapshot of scan counts, bloat indicators, and top query families. In MySQL, use performance schema, slow query analysis, and index usage reporting from your managed platform or Vitess layer if applicable. The first useful deliverable is a one-page heatmap: top cumulative-cost queries, largest indexes, write-heaviest tables, and obviously unused indexes.
Then assign ownership by domain, not by DBA heroics. The team that owns the workload should own the query shape; the platform or data-infrastructure owner should define guardrails, migration patterns, and review thresholds. That split is what keeps indexing from turning into tribal knowledge. Architecting Safety for AI Agents with Cyber Capabilities
If your engineering org is growing quickly, this is one of those areas where a small amount of systems support has outsized leverage. Teams like Amplify can help engineering organizations scale by giving product teams safer operational scaffolding, but the core discipline still has to live in your architecture reviews, query standards, and production observability. No vendor can fix an index portfolio nobody owns.
