Database indexing strategies

tegy:** Define indexes declaratively in your migration system to ensure reproducibility and version control.

// src/migrations/20240520_optimize_users.ts
import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  // 1. Composite index for frequent auth lookup
  // Strategy: Equality on email (high selectivity), covering for password_hash
  await knex.raw(`
    CREATE UNIQUE INDEX idx_users_email_covering 
    ON users (email) INCLUDE (password_hash, updated_at);
  `);

  // 2. Partial index for active sessions
  // Strategy: Avoid indexing inactive rows to reduce bloat and write penalty
  await knex.raw(`
    CREATE INDEX idx_sessions_active 
    ON sessions (user_id, expires_at) 
    WHERE status = 'active';
  `);

  // 3. BRIN index for time-series logs
  // Strategy: Low storage overhead for append-only data
  await knex.raw(`
    CREATE INDEX idx_logs_timestamp_brin 
    ON logs USING brin (created_at);
  `);

  // 4. GIN index for JSONB payload search
  // Strategy: Fast containment queries on document store field
  await knex.raw(`
    CREATE INDEX idx_events_payload_gin 
    ON events USING gin (payload jsonb_path_ops);
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw('DROP INDEX IF EXISTS idx_users_email_covering;');
  await knex.raw('DROP INDEX IF EXISTS idx_sessions_active;');
  await knex.raw('DROP INDEX IF EXISTS idx_logs_timestamp_brin;');
  await knex.raw('DROP INDEX IF EXISTS idx_events_payload_gin;');
}

Query Pattern Validation: Use TypeScript to wrap query execution with EXPLAIN ANALYZE in development to catch missing indexes.

// src/db/query-analyzer.ts
import { pool } from './connection';

export async function analyzeQuery(sql: string, params: any[]) {
  const explainQuery = `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`;
  const result = await pool.query(explainQuery, params);
  const plan = result.rows[0]['QUERY PLAN'][0];
  
  // Detect sequential scans on large tables
  const hasSeqScan = JSON.stringify(plan).includes('Seq Scan');
  if (hasSeqScan) {
    console.warn(`[PERF WARNING] Sequential scan detected:\n${JSON.stringify(plan, null, 2)}`);
  }
  
  return plan;
}

Pitfall Guide

1. Indexing Low-Cardinality Columns Without Partials

• Mistake: Creating an index on a boolean is_deleted column. The planner will likely ignore it because the cost of an index scan plus heap fetch exceeds a sequential scan for 50% of rows.
• Fix: Use a partial index: CREATE INDEX ... WHERE is_deleted = false. This indexes only the relevant subset, making the index small and highly selective.

2. Violating the Left-Prefix Rule

• Mistake: Creating index (B, A) but querying WHERE A = ? AND B = ?. The database cannot use the index for filtering efficiently.
• Fix: Analyze query predicates and order index columns to match the most common access path. If multiple access paths exist, create separate indexes or a composite that covers the dominant pattern.

3. Write Amplification from Excessive Indexes

• Mistake: Adding an index for every filter condition. Each index adds write overhead (WAL generation, page splits, lock contention).
• Fix: Audit indexes regularly. Remove unused indexes. Consolidate overlapping indexes. Monitor write latency after adding new indexes.

4. Function Calls on Indexed Columns

• Mistake: Querying WHERE LOWER(email) = 'user@example.com' with an index on email. The function prevents index usage.
• Fix: Use functional indexes: CREATE INDEX idx_email_lower ON users (LOWER(email)). Alternatively, normalize data at write time to avoid functions in queries.

5. Ignoring Index Bloat

• Mistake: High update/delete activity causes index bloat, where dead tuples occupy space. This degrades cache efficiency and increases I/O.
• Fix: Schedule regular REINDEX or VACUUM operations. Monitor bloat ratios using system catalogs. Consider pg_repack for zero-downtime maintenance.

6. Indexing Small Tables

• Mistake: Indexing tables with fewer than 100 rows. The overhead of index traversal outweighs the benefit; sequential scan is faster.
• Fix: Exclude small lookup tables from indexing strategies. Let the planner use sequential scans.

7. Missing Statistics

• Mistake: The planner makes poor decisions because table statistics are stale. This often happens after bulk loads or significant data changes.
• Fix: Ensure autovacuum is configured correctly. Run ANALYZE manually after large data modifications.

Production Bundle

Action Checklist

• Audit Usage: Query pg_stat_user_indexes to identify indexes with zero scans and remove them.
• Profile Top Queries: Extract the top 10 queries by total execution time and analyze their plans.
• Implement Composites: Replace single-column indexes with composite indexes based on the left-prefix rule.
• Add Partials: Convert low-selectivity indexes to partial indexes filtering on active states.
• Enable Covering: Add INCLUDE columns to indexes for high-frequency queries to enable index-only scans.
• Monitor Bloat: Set up alerts for index bloat ratio exceeding 30%.
• Review ORM Config: Ensure ORM models define indexes that match the strategic design, not just default annotations.
• Test Write Impact: Measure write throughput before and after index changes in a staging environment.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-Volume Writes	Minimal indexes; BRIN for time-series; Hash for equality lookups	Reduces write amplification and WAL volume	Low storage, High write throughput
JSONB Document Queries	GIN index with jsonb_path_ops operator class	Optimizes containment and existence checks	Moderate storage, Fast document reads
Geospatial Filtering	GiST index on geometry/point types	Supports R-Tree structure for spatial ops	Moderate storage, Fast spatial queries
Ad-hoc Analytics	Columnar store or materialized views; avoid B-Tree	B-Trees inefficient for full scans/aggregations	Higher compute, Lower query latency
Authentication Lookups	Unique B-Tree with INCLUDE for session data	Ensures uniqueness and covers query columns	Low latency, High security

Configuration Template

PostgreSQL Index Management Policy:

-- 1. Identify Unused Indexes
SELECT 
    schemaname, 
    tablename, 
    indexname, 
    idx_scan, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 2. Detect Index Bloat
SELECT 
    schemaname, tablename, indexname, 
    real_size, 
    extra_size, 
    CASE WHEN extra_size > 0 THEN 'True' ELSE 'False' END AS bloat
FROM (
    SELECT 
        schemaname, tablename, indexname, 
        pg_relation_size(indexrelid) AS real_size,
        (pg_relation_size(indexrelid) - (n_tup_ins * 2)) AS extra_size
    FROM pg_stat_user_indexes
    JOIN pg_class ON pg_class.oid = pg_stat_user_indexes.indexrelid
    WHERE n_tup_ins > 0
) AS bloat_data
WHERE extra_size > 1048576; -- Alert if bloat > 1MB

-- 3. Strategic Index Creation Template
-- Usage: Replace placeholders with actual schema details
CREATE INDEX CONCURRENTLY idx_{table}_{columns} 
ON {table} ({column_list}) 
INCLUDE ({include_columns})
WHERE {partial_condition};

Quick Start Guide

1. Identify Bottlenecks: Run SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; to find slow queries.
2. Analyze Plan: Prefix the query with EXPLAIN (ANALYZE, BUFFERS) and check for Seq Scan or high actual rows vs rows discrepancies.
3. Create Index: Apply the composite/partial strategy. Use CREATE INDEX CONCURRENTLY to avoid locking production writes.
4. Verify Improvement: Re-run EXPLAIN to confirm Index Scan usage and reduced cost.
5. Monitor: Watch pg_stat_user_indexes for idx_scan increments and monitor write latency for degradation.

Conclusion

Effective database indexing is an engineering discipline, not a configuration task. It demands a deep understanding of query patterns, data distribution, and storage internals. By adopting a workload-aware strategy—prioritizing composite structures, partial filters, and covering indexes—teams can achieve optimal performance while minimizing resource consumption. Regular auditing and validation are essential to maintain this efficiency as the application evolves.