`

Understanding Database Indices with Rails

image

Author: Himanshu Saxena

Think of an index like a book's index:
- Without an index: You'd need to scan every page to find content (full table scan)
- With an index: You can quickly jump to the right page (indexed lookup)

When to Add Indices

Add indices when columns are frequently used in:
1. WHERE clauses
2. ORDER BY statements
3. JOIN conditions
4. Unique constraints
5. Foreign keys

Example scenarios:
# Needs index: Frequently searching users by email
User.where(email: 'example@email.com')

# Needs index: Sorting posts by date
Post.order(created_at: :desc)

# Needs index: Join condition
User.joins(:posts).where(posts: { status: 'published' })

Creating Indices in Rails

1. Via Migration:

# Single column index
class AddIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email
  end
end

# Compound index (multiple columns)
class AddIndexToPostsTitleAndAuthor < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, [:title, :author_id]
  end
end

# Unique index
class AddUniqueIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email, unique: true
  end
end

2. In model definition:
class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :email
      t.timestamps
      t.index :email, unique: true
    end
  end
end

Index Types

1. B-tree Index (Default):
add_index :users, :email

2. Partial Index:
add_index :posts, :published_at, where: "published = true"

3. Expression Index:
add_index :users, 'LOWER(email)'

Performance Considerations

1. Check index usage:

# In Rails console
User.where(email: 'example@email.com').explain

2. Index size vs performance tradeoff:
# Selective index (good)
add_index :users, :email  # Unique values

# Less useful index (consider carefully)
add_index :users, :active  # Boolean with low selectivity

3. Compound indices order matters:
# This index:
add_index :posts, [:category_id, :published_at]

# Works for these queries:
Post.where(category_id: 1, published_at: Date.today)
Post.where(category_id: 1)

# But not efficiently for:
Post.where(published_at: Date.today)

Best Practices

1. Index foreign keys:
class CreateComments < ActiveRecord::Migration[7.0]
  def change
    create_table :comments do |t|
      t.references :post, foreign_key: true, index: true
      t.text :content
    end
  end
end

2. Remove unused indices:
class RemoveUnusedIndexFromPosts < ActiveRecord::Migration[7.0]
  def change
    remove_index :posts, :rarely_searched_column
  end
end

3. Monitor index usage:
-- PostgreSQL
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes;

Common Pitfalls to Avoid

1. Over-indexing:
# Bad: Indexing everything
add_index :users, :created_at  # Unless frequently used in queries
add_index :users, :updated_at  # Unless frequently used in queries

# Good: Index what you actually use
add_index :users, :email  # Frequently searched

2. Forgetting compound index order:
# Less efficient for common queries
add_index :posts, [:published_at, :category_id]

# More efficient if you often query by category first
add_index :posts, [:category_id, :published_at]

3. Missing foreign key indices:
# Always index foreign keys
add_index :posts, :user_id
add_index :comments, :post_id

Remember to:
- Analyze query patterns before adding indices
- Monitor index usage and performance
- Consider the write/update overhead of indices
- Test performance improvements with real data volumes

Published :