Database Indexing & Query Optimization

Composite and partial index strategies in PostgreSQL

# db/migrate/20250601_add_performance_indexes.rb
class AddPerformanceIndexes < ActiveRecord::Migration[7.1]
  def change
    # Composite index: column order matters
    # Query: WHERE customer_id = ? AND status = ? ORDER BY created_at DESC
    # Most selective column first (customer_id), then filter (status), then sort (created_at)
    add_index :orders, [:customer_id, :status, :created_at],
      name: 'idx_orders_customer_status_created'

    # Partial index: indexes only active subscriptions, not the full table
    # Dramatically smaller index for queries that always filter on active = true
    add_index :subscriptions, :plan_id,
      where: "status = 'active'",
      name: 'idx_subscriptions_plan_active'

    # Covering index (PostgreSQL 11+): includes non-key columns to avoid table fetch
    # Query: SELECT id, email FROM users WHERE company_id = ? - no heap fetch needed
    add_index :users, :company_id,
      include: [:id, :email],
      name: 'idx_users_company_covering'

    # GIN index for full-text search and JSONB containment queries
    add_index :products, :metadata, using: :gin,
      name: 'idx_products_metadata_gin'

    # Expression index: index on lower(email) for case-insensitive lookups
    add_index :users, 'lower(email)',
      unique: true, name: 'idx_users_email_lower_unique'
  end
end

Composite index column order follows the rule: equality conditions first (customer_id = ?), then range/filter conditions (status IN ?), then sort columns (created_at DESC). The index only accelerates queries that use a prefix of its columns.

Using EXPLAIN ANALYZE to validate index use

# In Rails console or a migration debug session
# Run EXPLAIN ANALYZE to see actual execution plan

result = ActiveRecord::Base.connection.execute(<<~SQL)
  EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
  SELECT id, total_cents, status
  FROM orders
  WHERE customer_id = 42
    AND status = 'confirmed'
  ORDER BY created_at DESC
  LIMIT 20
SQL

plan = JSON.parse(result.first['QUERY PLAN']).first
# Look for:
# "Node Type": "Index Scan" (good) vs "Seq Scan" (potentially bad)
# "Actual Rows": vs "Plan Rows" (large discrepancy = stale statistics)
# "Shared Hit Blocks": data served from buffer cache (fast)
# "Shared Read Blocks": data read from disk (slow)
# Run: ANALYZE orders; to update statistics after large data changes

puts "Plan type: #{plan.dig('Plan', 'Node Type')}"
puts "Actual rows: #{plan.dig('Plan', 'Actual Rows')}"
puts "Total cost: #{plan.dig('Plan', 'Total Cost')}"

BUFFERS shows whether data was served from PostgreSQL's shared buffer cache (Shared Hit Blocks) or disk (Shared Read Blocks). A large ratio of reads to hits indicates the working set does not fit in shared_buffers and the system is I/O-bound.