Database indexing strategies

I/O savings on the table. Production systems require deliberate alignment between index topology and actual query execution paths.

Core Solution

Implementing a production-grade indexing strategy requires a systematic pipeline: profile, design, implement, validate, and monitor. The following steps outline the technical implementation with TypeScript integration and PostgreSQL as the reference engine.

Step 1: Profile Query Patterns

Identify the top 20 most frequent queries by execution count and latency. Extract WHERE, JOIN, ORDER BY, and GROUP BY clauses. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) to capture actual execution plans. Focus on queries with Seq Scan or Bitmap Heap Scan nodes that exceed 50ms p99.

Step 2: Select Index Type by Data Shape

• B-tree: Default for equality and range queries. Covers 85% of use cases.
• Hash: Equality-only lookups. Faster than B-tree for = but unsupported in replication until PG 13+ and lacks range support.
• BRIN (Block Range INdexes): Ideal for time-series or monotonically increasing columns (e.g., created_at). Storage overhead <2%, but requires physical ordering.
• GIN/GiST: JSONB, arrays, full-text search, and geometric data. GIN excels at containment queries; GiST supports nearest-neighbor and range containment.

Step 3: Design Composite & Covering Indexes

Apply the left-prefix rule: composite indexes are only used when query filters match the leftmost columns. Order columns by selectivity (most restrictive first) unless ORDER BY dictates otherwise. Add INCLUDE columns to create covering indexes that satisfy queries without heap fetches.

TypeScript/Drizzle ORM example demonstrating correct index alignment:

import { pgTable, varchar, timestamp, integer, index } from 'drizzle-orm/pg-core';

export const transactions = pgTable('transactions', {
  id: varchar('id', { length: 26 }).primaryKey(),
  tenantId: varchar('tenant_id', { length: 36 }).notNull(),
  status: varchar('status', { length: 20 }).notNull(),
  amount: integer('amount').notNull(),
  createdAt: timestamp('created_at', { mode: 'string' }).notNull().defaultNow(),
}, (table) => ({
  // Composite index matching frequent query pattern:
  // SELECT ... WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC
  tenantStatusTimeIdx: index('idx_tenant_status_created')
    .on(table.tenantId, table.status, table.createdAt),
  
  // Covering index for reporting queries that only need amount & createdAt
  tenantAmountCoveringIdx: index('idx_tenant_amount_covering')
    .on(table.tenantId, table.createdAt)
    .include(table.amount),
}));

Step 4: Implement with Zero-Downtime Syntax

Always use CREATE INDEX CONCURRENTLY in production. This avoids table locks and allows concurrent reads/writes during index build. In TypeScript migrations, wrap in a transaction-safe block:

export async function up(knex: Knex) {
  await knex.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tenant_status_created 
    ON transactions (tenant_id, status, created_at DESC);
  `);
}

Step 5: Validate & Monitor

Run EXPLAIN ANALYZE against the target query. Confirm the plan shows Index Scan or Index Only Scan. Monitor pg_stat_user_indexes for usage frequency. If idx_scan = 0 after 7 days, the index is dead. Track heap_blks_read vs idx_blks_read to measure I/O reduction.

Architecture Rationale: Composite indexes outperform multiple single-column indexes because the query planner can only use one index per table per query node unless bitmap heap scans are triggered, which adds CPU overhead. Covering indexes eliminate random I/O by storing required columns in the index leaf pages. Partial indexes (e.g., WHERE status = 'pending') reduce storage and write cost for sparse predicates. The strategy prioritizes execution path alignment over schema symmetry.

Pitfall Guide

1. Over-Indexing Leading to Write Amplification Every INSERT, UPDATE, or DELETE must update all associated indexes. Adding 10+ indexes per table increases transaction commit time by 200–400%. Production rule: cap at 5–7 indexes per table unless read throughput absolutely demands it.

2. Ignoring Cardinality & Selectivity Indexes on low-cardinality columns (e.g., boolean, enum with 2–3 values) are rarely chosen by the planner. The optimizer prefers sequential scans when >5% of rows match. Use partial indexes or combine with high-selectivity columns in composites.

3. Misordering Composite Index Columns The left-prefix rule is non-negotiable. An index on (A, B, C) cannot accelerate queries filtering only on B or C. Additionally, mixing ORDER BY direction with filter columns without matching index sort order forces a sort node. Align column order with the most restrictive filter first, then sort columns.

4. Accumulating Dead Indexes Schema evolution leaves orphaned indexes that consume storage and slow writes. Run SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; weekly. Drop indexes with zero scans after confirming no background jobs or analytics pipelines depend on them.

5. Index Bloat Degradation High-churn tables suffer from dead tuple accumulation in index pages, increasing I/O and cache misses. PostgreSQL requires VACUUM or REINDEX CONCURRENTLY to reclaim space. Monitor pg_stat_user_tables.n_dead_tup and set autovacuum_vacuum_scale_factor aggressively for hot tables.

6. Assuming ORM Auto-Indexes Cover All Patterns ORMs generate indexes for foreign keys and @unique constraints but rarely optimize for query patterns. Developers must manually define composite and covering indexes that match actual WHERE/JOIN/ORDER BY chains.

7. Skipping CONCURRENTLY in Production Migrations Standard CREATE INDEX acquires an ACCESS EXCLUSIVE lock, blocking all writes. In high-traffic systems, this causes connection pool exhaustion and cascading timeouts. Always use CONCURRENTLY or schedule builds during maintenance windows with replication lag monitoring.

Best Practices from Production:

• Benchmark index changes against production-like data volumes. Small datasets mislead planners.
• Use EXPLAIN (ANALYZE, BUFFERS) to measure actual page fetches, not just estimated cost.
• Align index sort order with ORDER BY to eliminate sort nodes.
• Monitor pg_stat_bgwriter and pg_statio_user_indexes for cache hit ratios.
• Document index rationale in migrations: why it exists, which query it serves, and expected selectivity.

Production Bundle

Action Checklist

• Profile top 20 queries by latency and execution count using EXPLAIN (ANALYZE, BUFFERS)
• Identify missing composite indexes for multi-column WHERE/JOIN/ORDER BY chains
• Replace low-selectivity single-column indexes with partial or composite alternatives
• Implement covering indexes with INCLUDE for high-frequency reporting queries
• Audit pg_stat_user_indexes and drop indexes with idx_scan = 0 over 7 days
• Configure autovacuum_vacuum_scale_factor ≤ 0.01 for tables with >1M rows and high churn
• Validate all new indexes with CREATE INDEX CONCURRENTLY in staging before production rollout

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-read, low-write OLTP table	Covering composite index with INCLUDE	Eliminates heap fetches, reduces I/O to sequential index reads	+25% storage, +30% write cost, -85% read latency
Sparse boolean/status column	Partial index WHERE status = 'active'	Reduces index size to matching rows only, avoids planner rejection	-60% storage, minimal write overhead
Time-series event log	BRIN index on created_at	Leverages physical sort order, near-zero storage footprint	-90% storage vs B-tree, slightly higher CPU for range scans
Multi-tenant SaaS application	Composite (tenant_id, created_at DESC)	Enforces data isolation, optimizes pagination and recent-first queries	+15% storage, highly predictable p99 latency
JSONB payload search	GIN index with jsonb_path_ops	Accelerates @> containment queries, supports nested key lookups	+40% storage, requires careful path selection to avoid bloat

Configuration Template

-- Production Index Migration Template (PostgreSQL)
-- Target: transactions table, 50M+ rows, mixed read/write workload

-- 1. Composite filter + sort index
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_tenant_status_time
ON transactions (tenant_id, status, created_at DESC);

-- 2. Covering index for dashboard aggregation
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_tenant_amount_covering
ON transactions (tenant_id, created_at DESC)
INCLUDE (amount, currency);

-- 3. Partial index for pending reconciliations
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_pending_reconcile
ON transactions (created_at DESC)
WHERE status = 'pending' AND amount > 0;

-- 4. BRIN for audit log time-series
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_log_created_brin
ON audit_logs USING brin (created_at) WITH (pages_per_range = 128);

-- Verification query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, amount FROM transactions
WHERE tenant_id = 't_123' AND status = 'completed'
ORDER BY created_at DESC LIMIT 50;

Quick Start Guide

1. Extract execution plans: Run EXPLAIN (ANALYZE, BUFFERS) on your slowest query. Note Seq Scan nodes and heap_blks_read counts.
2. Draft composite index: Match the leftmost WHERE columns, append ORDER BY columns, and add INCLUDE for selected fields.
3. Apply in staging: Execute CREATE INDEX CONCURRENTLY and re-run EXPLAIN ANALYZE. Confirm Index Only Scan or Index Scan replaces heap fetches.
4. Monitor usage: Query pg_stat_user_indexes after 24 hours. If idx_scan > 0 and latency drops, promote to production.
5. Schedule maintenance: Set autovacuum_vacuum_scale_factor = 0.01 and run REINDEX CONCURRENTLY monthly on high-churn indexes.