Optimizing Rails API Performance: Database Indexing and Query Optimization

Advanced techniques for improving Rails API performance through strategic database indexing, query optimization, and caching strategies based on real production experience.

Understanding Database Indexing

Database indexes are like a book's index - they help the database find data quickly without scanning every row. Proper indexing can dramatically improve query performance.

When to Add Indexes

1. Foreign Keys (Always)

Always index foreign keys:

class CreatePosts < ActiveRecord::Migration[7.0]
  def change
    create_table :posts do |t|
      t.references :user, null: false, foreign_key: true, index: true
      t.string :title
      t.text :content
      t.timestamps
    end
  end
end

2. Frequently Queried Columns

Index columns used in WHERE clauses:

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

3. Columns Used in ORDER BY

class AddIndexToPostsPublishedAt < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, :published_at
  end
end

Composite Indexes

For queries that filter on multiple columns:

# Query: Post.where(user_id: 1, published: true).order(created_at: :desc)
class AddCompositeIndexToPosts < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, [:user_id, :published, :created_at]
  end
end

Index order matters! Put the most selective columns first.

Query Optimization Techniques

1. Use `select` to Limit Columns

Only fetch the data you need:

# Bad - loads all columns
users = User.all

# Good - only loads necessary columns
users = User.select(:id, :name, :email)

2. Use `pluck` for Single Values

# Bad - loads full AR objects
user_ids = User.where(active: true).map(&:id)

# Good - direct SQL query
user_ids = User.where(active: true).pluck(:id)

3. Batch Processing

For large datasets, use find_each:

# Bad - loads all records into memory
User.all.each do |user|
  user.update(processed: true)
end

# Good - processes in batches of 1000
User.find_each do |user|
  user.update(processed: true)
end

4. Use `exists?` Instead of `any?`

# Bad - loads records
if User.where(email: email).any?
  # ...
end

# Good - just checks existence
if User.where(email: email).exists?
  # ...
end

Caching Strategies

1. Fragment Caching

# In controller
def show
  @user = User.find(params[:id])
  fresh_when(@user)
end

# In view
<% cache @user do %>
  <%= render @user %>
<% end %>

2. Low-Level Caching

def expensive_calculation
  Rails.cache.fetch("user_#{id}/calculation", expires_in: 1.hour) do
    # Expensive operation here
    perform_complex_calculation
  end
end

3. Russian Doll Caching

# app/views/users/show.html.erb
<% cache @user do %>
  <h1><%= @user.name %></h1>
  <% cache @user.posts do %>
    <%= render @user.posts %>
  <% end %>
<% end %>

Database Connection Pooling

Configure your connection pool based on your traffic:

# config/database.yml
production:
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

Using EXPLAIN to Analyze Queries

# In development
User.where(email: 'test@example.com').explain

# Output shows if indexes are being used
# QUERY PLAN
# Index Scan using index_users_on_email on users

Real-World Optimization Example

Here's a real optimization from a production API:

Before

def index
  @posts = Post.all
  @posts.each do |post|
    post.author_name = post.user.name
    post.comment_count = post.comments.count
  end
  render json: @posts
end

Performance: ~3000ms for 100 posts (1 + 100 + 100 queries)

After

def index
  @posts = Post
    .includes(:user)
    .select('posts.*, COUNT(comments.id) as comment_count')
    .joins('LEFT JOIN comments ON comments.post_id = posts.id')
    .group('posts.id')
    
  render json: @posts.as_json(
    include: { user: { only: [:id, :name] } },
    methods: [:comment_count]
  )
end

Performance: ~50ms for 100 posts (2 queries with proper indexes)

Monitoring and Profiling

1. Use Rack Mini Profiler

# Gemfile
gem 'rack-mini-profiler'

2. Enable Query Logs

# config/environments/development.rb
config.active_record.verbose_query_logs = true

3. Use New Relic or Scout APM

Monitor production performance with APM tools to identify slow queries.

Database-Specific Optimizations

PostgreSQL

# Use JSONB for flexible data
class AddMetadataToPosts < ActiveRecord::Migration[7.0]
  def change
    add_column :posts, :metadata, :jsonb, default: {}
    add_index :posts, :metadata, using: :gin
  end
end

# Query JSONB
Post.where("metadata @> ?", { category: 'tech' }.to_json)

Full-Text Search

# Add pg_search
gem 'pg_search'

class Post < ApplicationRecord
  include PgSearch::Model
  pg_search_scope :search_by_content,
    against: [:title, :content],
    using: { tsearch: { prefix: true } }
end

# Usage
Post.search_by_content("rails performance")

Best Practices Checklist

✅ Add indexes on foreign keys
✅ Use includes to prevent N+1 queries
✅ Use select and pluck to limit data
✅ Implement caching strategies
✅ Use find_each for batch processing
✅ Monitor query performance with EXPLAIN
✅ Configure connection pooling appropriately
✅ Use database-specific features (JSONB, full-text search)
✅ Profile queries in production
✅ Test with production-like data volumes

Conclusion

API performance optimization is crucial for user experience and scalability. By:

Adding strategic indexes on frequently queried columns
Optimizing queries with eager loading and proper SQL
Implementing caching at multiple levels
Monitoring performance with appropriate tools

You can dramatically improve your Rails API performance and provide a better experience for your users.

Remember: always measure before and after optimization to ensure your changes have the desired effect.