Optimizing database queries is crucial for maintaining a fast and efficient Ruby on Rails application. Poorly written queries can lead to performance bottlenecks, memory leaks, and even downtime. In this multi-part series, we’ll explore various techniques and tools to optimize Rails queries, from basic practices to advanced strategies.
Why Query Optimization Matters

• Performance Improvement: Faster response times and reduced server load.

• Cost Efficiency: Efficient queries reduce database resource consumption, lowering infrastructure costs.

• Scalability: Properly optimized queries allow your application to scale seamlessly as data grows.

1. Understanding Active Record Queries

Active Record provides an intuitive interface for writing database queries in Rails. However, writing queries without understanding their impact on the database can lead to performance issues.
Example:

# This query loads all users into memory, which can be expensive
users = User.all

Best Practice: Use .pluck to select only needed columns:

# Only loads the names, reducing memory usage
names = User.pluck(:name)

2. Eager Loading to Avoid N+1 Queries

N+1 queries occur when querying associated records inefficiently.
Example of N+1 Query:

users = User.all
users.each do |user|
  puts user.posts.count
end

This results in one query for all users, followed by one query per user to fetch posts.

Solution: Use .includes for Eager Loading

users = User.includes(:posts)
users.each do |user|
  puts user.posts.count
end

This loads all users and their posts in just two queries.

3. Selecting Only Required Data

Avoid loading unnecessary columns with .select.
Example:

# Loads all columns
users = User.all

Optimized Version:

# Loads only the name column
users = User.select(:name)

4. Using ‘find_each’ and 'find_in_batches'

When dealing with large datasets, using .all or .each can lead to high memory consumption.
Example:

# Loads all users into memory
User.all.each do |user|
  puts user.name
end

Solution: Use find_each or find_in_batches

# Loads users in batches, reducing memory usage
User.find_each do |user|
  puts user.name
end

5. Avoiding Unnecessary Queries

Rails caches queries within the same request, but repeating queries outside the cached scope can hurt performance.
Example:

users = User.all
users.each do |user|
  puts User.find(user.id).name # Unnecessary query
end

Optimized Version:

users = User.all
users.each do |user|
  puts user.name # Uses cached objects
end

6. Using exists? Instead of present?

To check if a record exists, using .present? loads the object into memory, whereas .exists? only checks for existence.
Example:

# Loads the user object into memory
User.where(email: 'test@example.com').present?

Optimized Version:

# Checks existence without loading the object
User.where(email: 'test@example.com').exists?

7. Adding Indexes to Speed Up Queries

Adding indexes to frequently queried columns significantly speeds up database lookups.
Example:

# Add index using migration
add_index :users, :email

When to Use Indexes

• On columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.

• On foreign keys to speed up association lookups.

Advanced Techniques and Tools

1. Caching Queries with Rails.cache

Rails provides built-in caching to store the results of expensive queries, reducing database load and speeding up response times.

Example — Basic Query Caching:

posts = Rails.cache.fetch('active_posts') do
  Post.where(active: true).to_a
end

• The first time this query runs, it fetches data from the database and stores it in the cache.

• Subsequent requests fetch data from the cache, avoiding unnecessary database calls.

Cache Expiration

Ensure cache invalidation to avoid serving stale data:

Rails.cache.delete('active_posts')

Best Practices

• Use caching for expensive and infrequently changing queries.

• Ensure cache keys are unique and include relevant query parameters.

2. Memoization to Avoid Repeated Queries

Memoization stores query results in memory within a single request cycle.

def active_posts
  @active_posts ||= Post.where(active: true)
end

• The query runs only once, even if the method is called multiple times.

• Useful for controllers and views to avoid redundant database hits.

3. Bullet Gem — Detecting N+1 Queries and Unused Eager Loading

The Bullet gem helps detect:

• N+1 Queries: When associated records are fetched in a loop.

• Unused Eager Loading: When associations are loaded but not used.

Installation:

Add Bullet to your Gemfile:

group :development do
  gem 'bullet'
end

Then, install and generate the configuration:

bundle install
bundle exec rails g bullet:install

Configuration:

In config/environments/development.rb:

config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.bullet_logger = true
  Bullet.rails_logger = true
end

• Bullet.alert = true shows JavaScript alerts in the browser.

• Bullet.bullet_logger = true logs issues in log/bullet.log.

Example Output:

• Detects an N+1 query and suggests adding .includes(:association).

• Warns about unused eager loading, helping you remove unnecessary .includes.

4. Prosopite — An Alternative to Bullet

Prosopite is a modern alternative to Bullet for detecting N+1 queries. It integrates seamlessly with Active Record and offers:

• Lower Overhead: Minimal performance impact.

• Better Compatibility: Works well with modern Rails versions.

Installation:

Add to your Gemfile:

group :development do
  gem 'prosopite'
end

Configuration:

Prosopite.prosopite = true
Prosopite.rails_logger = true

Choosing Between Bullet and Prosopite:

• Bullet is more mature and offers comprehensive alerting.

• Prosopite is lighter and better suited for performance-sensitive applications.

5. Identifying Memory Leaks in Queries

Memory leaks can occur when large query results are kept in memory longer than necessary.

Common Causes:

Inefficient Querying: Loading unnecessary columns or records.

Object Retention: Retaining Active Record objects across requests.

Detecting Memory Leaks:

• Derailed Benchmarks: Profile memory usage over time.

• GC::Profiler: Analyze garbage collection to identify objects retained in memory.

Example — Using Derailed Benchmarks:

bundle exec derailed bundle:mem

Profiles memory usage, helping you pinpoint memory-hungry queries.

Fixing Memory Leaks:

• Use .pluck or .select to load only necessary columns.

• Use .find_each for batch processing to free memory between batches.

6. Using EXPLAIN to Analyze Query Performance

Active Record provides the .explain method to analyze query performance and understand database execution plans.

Example — Analyzing a Query:

puts User.where(active: true).explain

Interpreting EXPLAIN Output:

• SELECT Type: Shows if the query is SIMPLE, PRIMARY, or a SUBQUERY.

• Key Used: Indicates the index used for the query.

• Rows Examined: Shows the number of rows scanned.

Optimization Tips:

• Add Indexes: If no index is used, consider adding one to the column.

• Refactor Queries: Rewrite complex queries to reduce the number of scanned rows.

What’s Next?

In Part 2, we will cover:

• Advanced indexing techniques, including composite indexes and partial indexes.

• Query optimization using database views and materialized views.

• Real-world examples of query refactoring and performance gains.