Database Indexing Strategies Performance Tuning

Indexing Is Still the Highest-Leverage Performance Lever in 2026

Query optimizers have improved substantially, Postgres 17 and MySQL 8.4 both ship smarter cost-based planning, and vector search extensions have matured. But indexing strategy remains the single highest-leverage lever for database performance, and it’s also one of the most commonly botched topics in both production incidents and technical interviews. A missing or wrong index turns a 5ms query into a 4-second one; an over-indexed table turns every write into a tax on five B-trees nobody reads from anymore.

This topic shows up constantly in backend and full-stack interviews in 2026, especially at companies running Postgres at scale (Stripe, Notion, Figma) where interviewers expect candidates to reason about index selection, not just recite “add a B-tree index.” If you’re preparing for these rounds, The 0-to-1 SWE Interview Playbook (https://www.amazon.com/dp/B0H256Z1MF?tag=sirjohnnymai-20) covers exactly this kind of query-optimization deep dive candidates get asked to whiteboard.

B-Tree, Hash, GIN, GiST, BRIN: When Each One Actually Wins

Postgres and MySQL both offer multiple index types, and picking the wrong one is a common production mistake:

• B-Tree (default): correct choice for equality and range queries on scalar columns (WHERE age > 30, WHERE created_at BETWEEN). Handles ~95% of use cases.
• Hash indexes: faster for pure equality lookups in Postgres 13+ (now WAL-logged and crash-safe), but cannot support range queries. Rarely worth the tradeoff over B-tree unless the table is enormous and equality-only.
• GIN (Generalized Inverted Index): essential for JSONB containment queries (@>), full-text search (tsvector), and array membership. Without GIN, JSONB queries on large tables force sequential scans regardless of table size.
• GiST: used for geometric data, range types, and nearest-neighbor search; also the backing structure for pg_trgm fuzzy text search when combined with trigram operators.
• BRIN (Block Range Index): dramatically smaller footprint (often 1000x smaller than B-tree) for naturally ordered, append-only data like time-series logs. A BRIN index on a 500GB events table can be under 10MB versus multiple GB for an equivalent B-tree.

The interview-relevant insight: index type selection is a function of query pattern and data distribution, not a fixed rule. Candidates who default to “just add a B-tree index” without considering data shape signal shallow understanding.

The Write Amplification Problem Nobody Budgets For

Every index added to a table is a write-path cost. Benchmarks on Postgres 16/17 with a 10-million row table show:

• Each additional B-tree index adds roughly 8-15% overhead to INSERT/UPDATE latency, depending on index width and fill factor.
• A table with 6+ secondary indexes can see write throughput drop by 40-60% compared to an unindexed baseline under sustained load.
• Index bloat from frequent UPDATEs (especially on indexed columns) requires periodic REINDEX CONCURRENTLY or autovacuum tuning; without it, index size can grow 3-5x beyond the data it covers within months.

This is why “just add an index” is bad advice for write-heavy tables (event ingestion, audit logs, high-frequency trading order books). The correct question is always: what is the read/write ratio, and what is the cost of the alternative (sequential scan, partial index, materialized view)?

Comparison Table: Index Strategy by Workload

Composite Index Column Order: The Detail That Fails Interviews

A composite index on (status, created_at) is not the same as (created_at, status). The rule, confirmed by Postgres’s planner behavior in 2026: place the highest-selectivity, most frequently equality-filtered column first, and range-filtered columns last. A query filtering WHERE status = 'active' AND created_at > NOW() - INTERVAL '7 days' benefits from (status, created_at) because the planner can narrow to the active rows first, then range-scan within that subset. Reversing the order forces a much larger range scan before the status filter applies.

This exact scenario, tested with EXPLAIN ANALYZE output, is a recurring interview whiteboard question in 2026 at data-intensive companies. Candidates are expected to reason about selectivity, not just memorize “leftmost prefix rule.”

Diagnosing Missing or Unused Indexes in Production

The 2026 standard workflow for index audits:

1. Run EXPLAIN (ANALYZE, BUFFERS) on slow queries identified via pg_stat_statements, looking for Seq Scan on large tables.
2. Query pg_stat_user_indexes for idx_scan = 0 to find unused indexes that are pure write overhead with no read benefit.
3. Check pg_stat_user_tables for high n_dead_tup ratios indicating autovacuum isn’t keeping pace, which bloats both table and index size.
4. For MySQL, use sys.schema_unused_indexes and the Performance Schema to find equivalent dead weight.

Teams running this audit quarterly typically find 15-25% of existing indexes are unused and safe to drop, directly improving write latency without any read regression.

FAQ

Q: How many indexes is “too many” on a single table? A: There’s no universal number, but as a practical heuristic, tables handling more than a few hundred writes per second rarely benefit from more than 4-5 secondary indexes before write latency degradation outweighs read gains. Audit with pg_stat_user_indexes rather than guessing; unused indexes should be dropped regardless of count.

Q: Why does adding an index sometimes make a query slower? A: Usually because the planner chooses the new index for a query where a sequential scan or a different existing index was actually faster, especially on small tables where sequential scans fit entirely in memory. It can also happen when index statistics are stale after a bulk load; running ANALYZE after large data changes resolves most of these regressions.

Q: What should I say in an interview when asked to design indexes for an unfamiliar schema? A: Ask about query patterns and read/write ratio before proposing any index. Interviewers specifically watch for candidates who jump straight to “index everything” versus those who first establish which queries are actually hot and what the write load tolerance looks like, then propose targeted indexes with justification for column order and type.

For a full walkthrough of how database performance questions get asked and scored in 2026 backend interviews, see The 0-to-1 SWE Interview Playbook: https://www.amazon.com/dp/B0H256Z1MF?tag=sirjohnnymai-20