RoR Indexes and DB Optimization

A database index works exactly like the index at the back of a book. Without the index, you read every page to find what you need. With the index, you jump straight to the right page. Database indexes make lookups on large tables instant instead of slow, and adding them is one of the highest-impact performance improvements you can make.

How an Index Works

Without index on users.email:
  SELECT * FROM users WHERE email = 'alice@example.com'
  → Database scans every row in the table (full table scan)
  → 1,000,000 users? Reads 1,000,000 rows.
  → Slow.

With index on users.email:
  SELECT * FROM users WHERE email = 'alice@example.com'
  → Database uses the index (like a sorted phonebook lookup)
  → Finds the row in microseconds regardless of table size.
  → Fast.

When to Add an Index

Add an index on any column you regularly use to:

• Search or filter: WHERE email = ?
• Sort: ORDER BY created_at
• Join: JOIN orders ON orders.user_id = users.id
• Look up uniquely: WHERE username = ?

Foreign key columns (user_id, product_id) always need an index. Rails does NOT add these automatically.

Adding Indexes via Migration

Add an index to an existing column:
  rails generate migration AddIndexToUsersEmail

class AddIndexToUsersEmail < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email, unique: true
  end
end

Add multiple indexes in one migration:
class AddIndexesToArticles < ActiveRecord::Migration[7.1]
  def change
    add_index :articles, :user_id
    add_index :articles, :published
    add_index :articles, :created_at
    add_index :articles, [:user_id, :published]   ← composite index
  end
end

Add Index When Creating a Table

class CreateProducts < ActiveRecord::Migration[7.1]
  def change
    create_table :products do |t|
      t.string  :name,       null: false
      t.string  :sku,        null: false
      t.decimal :price
      t.integer :category_id
      t.boolean :available,  default: true
      t.timestamps
    end

    add_index :products, :sku,         unique: true
    add_index :products, :category_id
    add_index :products, :available
    add_index :products, [:category_id, :available]
  end
end

Types of Indexes

Composite Index Order Matters

add_index :articles, [:user_id, :published]

This index helps:
  WHERE user_id = 1 AND published = true   ✓
  WHERE user_id = 1                        ✓ (leftmost prefix works)

This index does NOT help:
  WHERE published = true                   ✗ (right column alone doesn't use the index)

Rule: Put the most selective column first.
      Put the column used most often in WHERE first.

Removing an Index

remove_index :users, :email
remove_index :articles, [:user_id, :published]

Find Slow Queries

Enable slow query logging in PostgreSQL:

config/database.yml (PostgreSQL)

development:
  adapter: postgresql
  ...
  variables:
    log_min_duration_statement: 100   ← log any query taking over 100ms

Or use the query_reviewer gem or pg_query_stats to identify slow queries in your Rails log.

EXPLAIN — See the Query Plan

Rails lets you see the database's execution plan to understand if an index is being used:

In Rails console:
  pp Article.where(user_id: 1).explain

Output with index:
  QUERY PLAN
  Index Scan using index_articles_on_user_id on articles
  (cost=0.43..8.45 rows=10)    ← fast, uses index

Output without index:
  QUERY PLAN
  Seq Scan on articles
  (cost=0.00..4285.00 rows=50000)  ← slow, full table scan

Other Database Optimization Techniques

Use select to Fetch Only Required Columns

Article.select(:id, :title, :published_at)
  ← fetches 3 columns instead of all 10
  ← less data transferred, faster

Paginate Large Result Sets

gem "pagy"   ← fast, lightweight pagination

@articles = Article.published.page(params[:page]).per(20)
  ← never load 10,000 records all at once

Use find_each for Bulk Processing

BAD — loads all 500,000 users into memory:
  User.all.each { |u| u.send_digest }

GOOD — loads 1,000 at a time:
  User.find_each(batch_size: 1000) { |u| u.send_digest }

Database-Level Constraints

Migration:
  t.string :email, null: false
  add_index :users, :email, unique: true

  t.decimal :price, precision: 8, scale: 2
  t.check_constraint "price > 0"   ← PostgreSQL

These enforce data integrity at the database level,
independent of Rails validations.

Index Maintenance

Too many indexes slow down writes:
  INSERT / UPDATE / DELETE must update every index on the table
  Add only indexes your queries actually use

Check unused indexes (PostgreSQL):
  SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
  ← Any index with 0 scans since last reset is unused

Rebuild fragmented indexes:
  REINDEX TABLE articles;   ← rebuilds all indexes on the articles table

Indexes are the fastest, most reliable way to speed up a slow Rails application. Profile your queries, add indexes where they are missing, and remove them where they are unused. The impact on page response time is often immediate and dramatic.