ruby rails activerecord sql optimization n-1

ActiveRecord query optimization and N+1 prevention

Sarah Mitchell Feb 2026

3 tabs

# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

# GOOD: Eager loading with includes
@users = User.includes(:posts).all
@users.each do |user|
  puts user.posts.count  # No additional queries
end

# Multiple associations
@users = User.includes(:posts, :comments, profile: :avatar)

# Conditional eager loading
@users = User.includes(:posts).where(posts: { published: true })

# Using joins for filtering (doesn't load association)
@users = User.joins(:posts).where(posts: { status: 'published' }).distinct

# Preload vs Eager Load
User.preload(:posts)     # Always 2 queries
User.eager_load(:posts)  # Always LEFT OUTER JOIN
User.includes(:posts)    # Smart choice based on conditions

# Select specific columns
User.select(:id, :name, :email)  # Only fetch needed columns

# Pluck for single/multiple columns (returns array, not AR objects)
User.pluck(:email)              # => ["alice@example.com", ...]
User.pluck(:id, :name)          # => [[1, "Alice"], [2, "Bob"]]

# exists? vs present?/any?
User.where(status: 'active').exists?  # Fast COUNT query
User.where(status: 'active').any?     # Loads records into memory

class Post < ApplicationRecord
  belongs_to :user
  has_many :comments

  # Scopes for reusable queries
  scope :published, -> { where(status: 'published') }
  scope :recent, -> { where('created_at > ?', 1.week.ago) }
  scope :popular, -> { where('views_count > ?', 1000) }
  scope :by_author, ->(user_id) { where(user_id: user_id) }

  # Combining scopes
  scope :trending, -> { published.recent.popular }

  # Counter cache
  belongs_to :user, counter_cache: true  # Adds posts_count to users

  # Custom SQL with sanitization
  def self.search(term)
    where('title ILIKE :term OR content ILIKE :term', term: "%#{sanitize_sql_like(term)}%")
  end

  # Subqueries
  def self.with_recent_comments
    where(id: Comment.select(:post_id).where('created_at > ?', 1.day.ago))
  end

  # Aggregations
  def self.statistics
    group(:status).count
    # => { "published" => 150, "draft" => 42 }
  end

  def self.avg_views_by_category
    joins(:category).group('categories.name').average(:views_count)
  end
end

# Batch processing for large datasets
User.find_each(batch_size: 1000) do |user|
  user.update_statistics
end

# find_in_batches returns arrays
User.find_in_batches(batch_size: 500) do |users|
  UserExportService.export(users)
end

# Merge scopes
class User < ApplicationRecord
  scope :active, -> { where(status: 'active') }
end

Post.joins(:user).merge(User.active)

# Upsert (Rails 6+)
User.upsert(
  { email: 'alice@example.com', name: 'Alice' },
  unique_by: :email
)

# Upsert multiple
User.upsert_all([
  { email: 'alice@example.com', name: 'Alice' },
  { email: 'bob@example.com', name: 'Bob' }
], unique_by: :email)

# Migration with indices
class CreatePosts < ActiveRecord::Migration[6.1]
  def change
    create_table :posts do |t|
      t.references :user, null: false, foreign_key: true, index: true
      t.string :title, null: false
      t.text :content
      t.string :status, null: false, default: 'draft'
      t.integer :views_count, default: 0
      t.timestamps
    end

    # Composite index for common queries
    add_index :posts, [:user_id, :status]
    add_index :posts, [:status, :created_at]

    # Partial index (PostgreSQL)
    add_index :posts, :created_at, where: "status = 'published'"

    # Full-text search index (PostgreSQL)
    execute "CREATE INDEX posts_content_search_idx ON posts USING gin(to_tsvector('english', content))"
  end
end

# Query analysis
Post.where(user_id: 1, status: 'published').explain
# Shows query plan and index usage

# Benchmark queries
require 'benchmark'

Benchmark.bm do |x|
  x.report("without index:") { Post.where(title: 'Test').load }
  x.report("with index:") { Post.where(title: 'Test').load }
end

3 files · ruby Explain with highlit

ActiveRecord provides powerful query interface, but naive usage causes N+1 queries. includes eager loads associations in 2-3 queries. joins performs SQL JOINs for filtering. preload always uses separate queries; eager_load forces LEFT OUTER JOIN. I use select to limit columns, reducing memory. find_each and find_in_batches process large datasets efficiently. Scopes chain for composable queries. merge combines scopes. Counter caches avoid COUNT queries. Database indices speed lookups—I add indices on foreign keys and frequently queried columns. explain reveals query plans. Bullet gem detects N+1 in development. Proper query optimization dramatically improves performance, especially at scale.