How do I fix N+1 queries in Rails?

Use eager loading with includes, preload, or eager_load to batch-load associations. Add the Bullet gem in development to detect N+1 queries automatically. For count columns, implement counter caches so Rails reads a pre-computed integer column instead of running a COUNT query for each record.

What PostgreSQL indexes should every Rails app have?

At minimum, index all foreign keys, columns used in WHERE clauses, JOIN conditions, and ORDER BY columns. Use compound indexes matching your query patterns, and follow the left-prefix rule. Add partial indexes for frequently queried subsets and GIN indexes for JSONB columns.

When should I use materialized views in Rails?

Use materialized views for expensive aggregation queries that power dashboards or reports and do not need real-time accuracy. Refresh them on a schedule - hourly or daily depending on requirements. They trade data freshness for query speed, turning multi-second reports into instant reads.

How do I monitor slow database queries in production Rails apps?

Enable PostgreSQL slow query logging with log_min_duration_statement, install pg_stat_statements for query statistics, and use an APM tool like Skylight or New Relic. Subscribe to ActiveSupport sql.active_record notifications to alert on queries exceeding your threshold in application code.

PostgreSQL Optimization in Rails: Cut Query Times by 95%

PostgreSQL query optimization techniques for Rails applications including indexing, N+1 fixes, and materialized views

A SaaS dashboard loading in 4.2 seconds will lose users. The same dashboard at 180ms feels instant. The difference is almost always the database - and the fixes are more straightforward than you’d expect.

This guide covers the techniques that make the biggest difference in production Rails applications.

Technique	Typical Improvement	Effort	Best For
Fix N+1 queries	10-50x fewer queries	Low	Every Rails app
Add compound indexes	5-100x faster lookups	Low	WHERE/JOIN/ORDER queries
Counter caches	Eliminates COUNT queries	Low	Displaying counts in lists
SELECT specific columns	2-5x less memory	Low	Wide tables with JSONB/TEXT
Materialized views	Instant complex reports	Medium	Dashboards and analytics
PgBouncer	5-10x connection capacity	Medium	High-concurrency apps
Partial indexes	50-80% smaller index size	Low	Filtered queries on subsets

Understanding Query Cost

Measure query cost with EXPLAIN ANALYZE before optimizing anything. PostgreSQL shows you the execution plan, actual row counts, and wall-clock time per node - the difference between a sequential scan and an index scan on a 10-million-row table is typically 1000x. Every decision below depends on first reading one of these plans correctly.

Enable Query Logging in Development

# config/environments/development.rb
config.active_record.logger = Logger.new(STDOUT)
config.log_level = :debug

# Or use the bullet gem
gem 'bullet', group: :development

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.console = true
end

Use EXPLAIN ANALYZE

# In Rails console
User.joins(:transactions).where(created_at: 1.year.ago..).explain

# Or raw SQL
ActiveRecord::Base.connection.execute("
  EXPLAIN ANALYZE
  SELECT users.*, COUNT(transactions.id)
  FROM users
  LEFT JOIN transactions ON transactions.user_id = users.id
  GROUP BY users.id
").to_a

Look for:

Sequential scans on large tables (bad)
High execution time
Rows scanned vs. rows returned (massive difference = inefficient)

Monitor in Production

# Use rack-mini-profiler
gem 'rack-mini-profiler'

# Or APM tools
gem 'skylight'  # My favorite for Rails apps
gem 'newrelic_rpm'

Problem #1: The N+1 Query Epidemic

An N+1 happens when Rails runs one query to fetch a parent collection and then one extra query per row to load an association. For 100 users with associated transactions that is 201 queries instead of 2. Fix it with includes, preload, or eager_load, and install the Bullet gem to catch new ones in development. This is the single most common performance killer in Rails apps.

The Problem

# app/controllers/users_controller.rb
def index
  @users = User.all
end

<!-- app/views/users/index.html.erb -->
<% @users.each do |user| %>
  <tr>
    <td><%= user.name %></td>
    <td><%= user.transactions.count %></td>  <!-- N+1! -->
    <td><%= user.latest_transaction&.amount %></td>  <!-- N+1! -->
  </tr>
<% end %>

Database queries: 1 (fetch users) + N (count transactions for each) + N (fetch latest transaction) = 1 + 2N queries

For 100 users: 201 queries. For 1000 users: 2001 queries.

The Solution: Eager Loading

# app/controllers/users_controller.rb
def index
  @users = User
    .includes(:transactions)  # Load transactions
    .left_joins(:transactions)
    .select('users.*, COUNT(transactions.id) as transactions_count')
    .group('users.id')
end

Database queries: 2 (users + transactions). Always 2, regardless of user count.

Advanced Pattern: Preload with Scope

class User < ApplicationRecord
  has_many :transactions
  has_one :latest_transaction,
          -> { order(created_at: :desc) },
          class_name: 'Transaction'
end

# Controller
def index
  @users = User.includes(:transactions, :latest_transaction)
end

<!-- View -->
<% @users.each do |user| %>
  <tr>
    <td><%= user.name %></td>
    <td><%= user.transactions.size %></td>  <!-- No query! -->
    <td><%= user.latest_transaction&.amount %></td>  <!-- No query! -->
  </tr>
<% end %>

Even Better: Counter Caches

For counts, use counter caches:

# Migration
class AddTransactionsCountToUsers < ActiveRecord::Migration[8.0]
  def change
    add_column :users, :transactions_count, :integer, default: 0, null: false

    # Backfill existing data
    User.find_each do |user|
      User.reset_counters(user.id, :transactions)
    end
  end
end

# Model
class Transaction < ApplicationRecord
  belongs_to :user, counter_cache: true
end

Now user.transactions.count is instant - it reads a pre-computed column.

Problem #2: Missing Indexes

Missing indexes force PostgreSQL to scan entire tables row by row. Adding the right index can turn a 3-second query into a 5ms one.

Identify Missing Indexes

# This query is slow
Transaction.where(user_id: 123, status: 'completed').order(created_at: :desc)

# Check execution plan
Transaction.where(user_id: 123, status: 'completed').order(created_at: :desc).explain

# Look for "Seq Scan" (bad) vs "Index Scan" (good)

Add the Right Index

class AddIndexToTransactions < ActiveRecord::Migration[8.0]
  def change
    # Compound index for WHERE + ORDER BY
    add_index :transactions,
              [:user_id, :status, :created_at],
              name: 'index_transactions_on_user_status_date'
  end
end

Rule of thumb: Index columns used in:

WHERE clauses
JOIN conditions
ORDER BY clauses
GROUP BY clauses

Index Column Order Matters

# If you query by user_id AND status
add_index :transactions, [:user_id, :status]

# But if you also query by user_id alone
add_index :transactions, [:user_id, :status]  # Works for both!

# But NOT if you query by status alone
# This index won't be used for: where(status: 'completed')
# You'd need a separate index on [:status]

Left-prefix rule: An index on [a, b, c] can be used for queries filtering:

a
a, b
a, b, c

But NOT for: b, c, or b, c alone.

Partial Indexes

For columns with many NULLs or specific values you query frequently:

# Only index completed transactions
add_index :transactions,
          [:user_id, :created_at],
          where: "status = 'completed'",
          name: 'index_completed_transactions'

# Much smaller index, faster queries on completed transactions

Unique Indexes

Enforce uniqueness at the database level:

add_index :users, :email, unique: true
add_index :transactions, [:user_id, :external_id], unique: true

Problem #3: Inefficient Queries

Rewrite queries that do more work than the answer needs: use exists? instead of count > 0, pluck instead of loading full records, database aggregation instead of Ruby iteration, and find_each instead of each on large scopes. These rewrites cost one line of code and routinely turn multi-second requests into sub-100ms ones.

Use EXISTS Instead of COUNT

# Slow: counts all matching records
if user.transactions.where(status: 'pending').count > 0
  # ...
end

# Fast: stops at first match
if user.transactions.where(status: 'pending').exists?
  # ...
end

Use SELECT to Limit Columns

# Loads all columns (including JSONB, TEXT, etc.)
@users = User.all

# Loads only what you need
@users = User.select(:id, :name, :email)

For tables with JSONB or TEXT columns, this can cut memory usage by 2-5x and reduce network transfer proportionally.

Use LIMIT

# Scans entire table
User.where('created_at > ?', 1.year.ago).to_a

# Stops after finding 100
User.where('created_at > ?', 1.year.ago).limit(100).to_a

Always use LIMIT for:

Autocomplete dropdowns
“Recent items” lists
Preview queries

Batch Processing

# BAD: Loads all records into memory
User.all.each do |user|
  user.process_data
end

# GOOD: Processes in batches of 1000
User.find_each(batch_size: 1000) do |user|
  user.process_data
end

# For custom queries
User.where(active: true).in_batches(of: 500) do |batch|
  batch.update_all(last_checked: Time.current)
end

Problem #4: Slow Aggregations

Push aggregations down to PostgreSQL instead of computing them in Ruby. user.transactions.sum(:amount) runs a single SUM in the database and returns a scalar; loading records into memory and calling map(&:amount).sum in Ruby reads every row, allocates objects, and runs the arithmetic in the web process. For reports that can tolerate slight staleness, materialized views turn multi-second aggregations into instant reads.

Use Database Aggregations

# Slow: Loads all records into Ruby memory
transactions = user.transactions.to_a
total = transactions.sum(&:amount)
average = transactions.map(&:amount).sum / transactions.size

# Fast: Database does the math
total = user.transactions.sum(:amount)
average = user.transactions.average(:amount)
count = user.transactions.count

PostgreSQL is optimized for aggregations. Use it.

Materialized Views for Complex Reports

For reports that are expensive to calculate:

# db/migrate/20250101_create_user_statistics_view.rb
class CreateUserStatisticsView < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW user_statistics AS
      SELECT
        users.id AS user_id,
        users.name,
        COUNT(DISTINCT transactions.id) AS transaction_count,
        SUM(transactions.amount) AS total_amount,
        AVG(transactions.amount) AS average_amount,
        MAX(transactions.created_at) AS last_transaction_at
      FROM users
      LEFT JOIN transactions ON transactions.user_id = users.id
      WHERE transactions.created_at > NOW() - INTERVAL '1 year'
      GROUP BY users.id, users.name;

      CREATE UNIQUE INDEX ON user_statistics (user_id);
    SQL
  end

  def down
    execute "DROP MATERIALIZED VIEW IF EXISTS user_statistics"
  end
end

# app/models/user_statistic.rb
class UserStatistic < ApplicationRecord
  self.primary_key = 'user_id'

  # Refresh materialized view
  def self.refresh
    connection.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics')
  end
end

# Schedule refresh (e.g., every hour)
# config/schedule.rb (with whenever gem)
every 1.hour do
  runner "UserStatistic.refresh"
end

Now complex reports are instant - just SELECT * FROM user_statistics.

Problem #5: JSON Columns Performance

JSON columns are powerful but can be slow if not indexed properly.

# Schema
create_table :transactions do |t|
  t.jsonb :metadata, default: {}, null: false
end

# Add GIN index for JSONB
add_index :transactions, :metadata, using: :gin

# Now these queries are fast
Transaction.where("metadata @> ?", { payment_method: 'credit_card' }.to_json)
Transaction.where("metadata -> 'payment_method' = ?", 'credit_card')
Transaction.where("metadata ->> 'amount' = ?", '100.00')

Specific Key Indexes

# If you frequently query a specific JSON key
add_index :transactions, "(metadata -> 'payment_method')", name: 'index_transactions_on_payment_method'

Problem #6: Connection Pool Exhaustion

Connection pool exhaustion happens when your Rails processes request more database connections than the pool allows, causing requests to time out waiting. Fix it by sizing the pool to match your thread count, and front PostgreSQL with PgBouncer in transaction pooling mode when you have many application servers sharing a database - this can 5-10x your effective connection capacity without raising PostgreSQL’s own limit.

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

Symptoms:

could not obtain a database connection within 5 seconds
Requests timing out under load

Solutions:

1. Increase Pool Size

production:
  pool: 20  # Match Puma workers * threads

2. Use PgBouncer

PgBouncer pools connections at the database level:

# pgbouncer.ini
[databases]
myapp = host=localhost dbname=myapp_production

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

3. Close Long-Running Connections

# config/initializers/active_record.rb
ActiveRecord::Base.connection_pool.with_connection do |conn|
  conn.execute("SET statement_timeout = 30000")  # 30 seconds
end

Production Case Study: SaaS Dashboard

Here’s a complete optimization example for a SaaS analytics dashboard.

Before Optimization

# app/controllers/dashboards_controller.rb
def show
  @user = current_user
  @activities = @user.activities.order(created_at: :desc).limit(10)
  @total_usage = @user.usage_records.sum(&:value)
  @monthly_cost = @user.invoices.sum(&:total)
  @saved_items = @user.bookmarks.map(&:title)
end

Performance:

Page load: 4.2 seconds
Database queries: 127
Database time: 3.8 seconds

After Optimization

# app/models/user.rb
class User < ApplicationRecord
  has_many :activities
  has_many :usage_records
  has_many :bookmarks

  # Cached calculations
  def usage_summary
    Rails.cache.fetch("user_#{id}_usage_summary", expires_in: 5.minutes) do
      {
        total_usage: usage_records.sum(&:value),
        monthly_cost: invoices.sum(&:total),
        items_count: bookmarks.count
      }
    end
  end
end

# app/controllers/dashboards_controller.rb
def show
  @user = current_user

  # Eager load with limits
  @activities = @user.activities
                     .includes(:related_resource)
                     .order(created_at: :desc)
                     .limit(10)

  # Cached aggregation
  @usage_summary = @user.usage_summary

  # Single query for bookmarks
  @saved_items = @user.bookmarks.pluck(:title)
end

# Add indexes
add_index :activities, [:user_id, :created_at]
add_index :usage_records, :user_id
add_index :bookmarks, :user_id

Performance After:

Page load: 180ms (23x faster)
Database queries: 4
Database time: 45ms

Monitoring & Ongoing Optimization

1. Identify Slow Queries

# PostgreSQL slow query log
# postgresql.conf
log_min_duration_statement = 100  # Log queries > 100ms

# Or use pg_stat_statements
CREATE EXTENSION pg_stat_statements;

# Then query
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

2. Set Up Alerts

# config/initializers/notifications.rb
ActiveSupport::Notifications.subscribe('sql.active_record') do |name, start, finish, id, payload|
  duration = (finish - start) * 1000

  if duration > 1000  # Queries over 1 second
    Rails.logger.warn "Slow Query (#{duration.round}ms): #{payload[:sql]}"

    # Optional: Send to error tracking
    Rollbar.warning("Slow database query", {
      duration: duration,
      sql: payload[:sql]
    })
  end
end

3. Regular VACUUM

PostgreSQL needs maintenance:

-- Manual
VACUUM ANALYZE;

-- Automatic (recommended)
-- postgresql.conf
autovacuum = on

Optimization Checklist

Before deploying to production, check:

Advanced Techniques

1. Composite Primary Keys (Rails 7.1+)

# For many-to-many tables
create_table :user_roles, primary_key: [:user_id, :role_id] do |t|
  t.integer :user_id, null: false
  t.integer :role_id, null: false
end

2. CTE (Common Table Expressions)

User.with(
  active_users: User.where(active: true),
  recent_transactions: Transaction.where('created_at > ?', 30.days.ago)
).joins('INNER JOIN active_users ON...')

3. Window Functions

# Rank users by transaction volume
User.select('
  users.*,
  RANK() OVER (ORDER BY transaction_count DESC) as rank
').joins(:transactions)

Limitations and When to Be Careful

These techniques work in most cases, but optimization is not free.

Index Overhead

Every index speeds up reads but slows down writes. A table with 10 indexes will have noticeably slower INSERT and UPDATE operations. For write-heavy tables (audit logs, event streams, analytics), be selective about which indexes you add.

Table Type	Recommended Indexes	Risk
Read-heavy (users, products)	5-10 targeted indexes	Low write impact
Write-heavy (logs, events)	2-3 essential indexes	High write impact per index
Mixed (orders, transactions)	3-6 balanced indexes	Monitor write latency

Materialized Views Are Not Free

Materialized views consume disk space and require refresh operations. A REFRESH MATERIALIZED VIEW CONCURRENTLY takes an exclusive lock on the unique index during the swap phase. For large views refreshing frequently, this can cause brief lock contention.

Caching Can Hide Problems

The case study above uses Rails.cache.fetch for dashboard stats. Caching masks slow queries instead of fixing them. If your cache goes cold (deploy, Redis restart, key rotation), all that hidden latency comes back at once. Fix the underlying query first, then cache for extra speed.

When These Techniques Are Not Enough

If you’ve applied everything here and still hit performance walls, the problem might be architectural:

Data volume: Beyond 100M+ rows, consider partitioning or TimescaleDB for time-series workloads
Connection limits: PostgreSQL handles ~500 connections well. Beyond that, PgBouncer helps but you may need read replicas
Query complexity: Some analytical queries will never be fast on an OLTP database. Consider a separate analytics store or materialized views with longer refresh intervals
Write throughput: If you’re doing 10,000+ writes/second, look into batch inserts, COPY, or dedicated write-ahead patterns

The Bottom Line

Database optimization is about measuring first and fixing the biggest bottlenecks:

Eliminate N+1 queries - the single biggest win in most Rails apps
Add compound indexes matching your actual query patterns
Use database aggregations instead of loading records into Ruby
Monitor continuously - production data reveals problems that development never will

These techniques have consistently reduced page load times from seconds to milliseconds, cut database costs by 60-70%, and enabled handling 10x more traffic on the same hardware.

Start with EXPLAIN ANALYZE on your slowest queries. The numbers will tell you exactly where to focus.

Need help with database performance? I help teams with PostgreSQL optimization, query tuning, and scaling decisions. If your Rails app is hitting database bottlenecks, reach out at nikita.sinenko@gmail.com.