Database indexing strategies

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:
```ts
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.