Slow database queries can significantly impact the user experience of any Rails application. Identifying and fixing these performance bottlenecks requires a systematic approach and the right set of tools.

In this post, we will explore various approaches to debugging query performance in Rails, from built in Rails tools to database specific utilities.

Understanding the Problem

Before diving into debugging tools, it’s important to understand what makes a query slow. Common causes include:

Missing or inefficient indexes
N+1 query problems
Full table scans
Complex joins without proper optimization
Large result sets being loaded into memory
Inefficient WHERE clauses

Rails Built in Tools

Using `explain` Method

Rails provides the explain method on Active Record relations to show the query execution plan. This is one of the most powerful tools for understanding how the database executes queries.

User.where(status: 'active').explain

This outputs the database’s query plan, showing how it intends to execute the query.

Rails 7.1 enhanced the explain method to accept options:

# PostgreSQL specific options

User.where(status: 'active').explain(:analyze, :verbose, :buffers)

# MySQL specific options

User.where(status: 'active').explain(:analyze)

The :analyze option actually executes the query and provides real execution statistics rather than estimates.

Async Queries with `load_async`

Rails 7 introduced load_async, which schedules queries on a background thread so independent queries can run in parallel rather than sequentially. This was further refined in Rails 8 with additional async variants.

# Fire independent queries in parallel

@users = User.where(status: 'active').load_async
@posts = Post.where(published: true).load_async
@stats = Stat.where(date: Date.today).load_async

# Queries execute concurrently on background threads;

# results are awaited when first accessed in the view

Rails 7.1+ also added async aggregation methods:

# These return Promise-like objects that resolve when accessed

active_count = User.where(status: 'active').async_count
total_revenue = Order.async_sum(:amount)
oldest_user = User.async_minimum(:created_at)

To use load_async effectively, configure the async executor pool in the environment:

# config/environments/production.rb

config.active_record.async_query_executor = :global_thread_pool
config.active_record.global_executor_concurrency = 4

This is especially useful in controller actions that load multiple independent collections for a view.

ActiveSupport::Notifications

Rails uses ActiveSupport::Notifications to instrument SQL queries. We can subscribe to these notifications to log slow queries:

# config/initializers/slow_query_logger.rb

ActiveSupport::Notifications.subscribe('sql.active_record') do |name, start, finish, id, payload|
  duration = (finish - start) * 1000 # Convert to milliseconds


  if duration > 100 # Log queries taking more than 100ms

    Rails.logger.warn "SLOW QUERY (#{duration.round(2)}ms): #{payload[:sql]}"
  end
end

Query Log Tags (Built-in Marginalia)

Rails 7 introduced built in query log tags that help identify where queries originate from. This is the framework’s built-in replacement for the older marginalia gem:

# config/application.rb

config.active_record.query_log_tags_enabled = true
config.active_record.query_log_tags = [
  :application,
  :controller,
  :action,
  :job
]

This adds comments to SQL queries showing their source:

SELECT * FROM users WHERE id = 1
/*application:MyApp,controller:users,action:show*/

Strict Loading Mode

Rails 6.1 introduced strict loading to catch N+1 queries during development:

# Enable globally in development

# config/environments/development.rb

config.active_record.strict_loading_by_default = true

# Or enable per query

User.strict_loading.includes(:posts).each do |user|
  user.posts # This works

  user.comments # This raises StrictLoadingViolationError

end

Strict loading supports two modes. The default :all mode raises on any lazy-loaded association. The :n_plus_one_only mode is more practical for real applications because it only raises when a lazy load would cause an N+1 query, while still allowing singular associations like belongs_to:

# Only catch actual N+1 patterns, allow belongs_to lazy loads

user.strict_loading!(mode: :n_plus_one_only)

# Or set the mode globally

config.active_record.strict_loading_by_default = true
config.active_record.strict_loading_mode = :n_plus_one_only

We can also configure the violation behavior to log warnings instead of raising errors, which makes strict loading safe to use in production for detecting N+1 queries without crashing:

# config/environments/production.rb

config.active_record.action_on_strict_loading_violation = :log

Essential Gems for Query Debugging

Bullet Gem

The bullet gem is excellent for detecting N+1 queries and unused eager loading:

# Gemfile

gem 'bullet', group: :development

# config/environments/development.rb

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

Bullet will notify us when:

N+1 queries are detected
Eager loading is used but not needed
Counter cache should be used

rack-mini-profiler

The rack-mini-profiler gem provides detailed profiling information in the browser:

# Gemfile

gem 'rack-mini-profiler'
gem 'memory_profiler' # Optional for memory profiling

gem 'stackprof' # Optional for flamegraphs

It displays a badge in the corner of the pages showing request time, SQL queries executed, and their individual durations.

prosopite

The prosopite gem is another N+1 detection tool that works well in production environments:

# Gemfile

gem 'prosopite'

# config/environments/production.rb

config.after_initialize do
  Prosopite.rails_logger = true
  Prosopite.prosopite_logger = Logger.new('log/prosopite.log')
end

activerecord-explain-analyze

For more detailed EXPLAIN ANALYZE output, the activerecord-explain-analyze gem provides formatted output:

# Gemfile

gem 'activerecord-explain-analyze'

# Usage

User.where(status: 'active').explain_analyze

Note that since Rails 7.1 natively supports explain(:analyze), this gem is mainly useful for applications running on older Rails versions.

PostgreSQL Specific Tools

PostgreSQL offers powerful tools for query analysis.

pg_stat_statements

This extension tracks execution statistics for all SQL statements:

-- Enable the extension

CREATE EXTENSION pg_stat_statements;

-- Find slowest queries

SELECT
  query,
  calls,
  total_exec_time / 1000 as total_seconds,
  mean_exec_time / 1000 as avg_seconds,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

EXPLAIN ANALYZE with Options

PostgreSQL’s EXPLAIN supports several useful options:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = '[email protected]';

Key things to look for:

Seq Scan - Full table scan, might need an index
Index Scan - Using an index efficiently
Nested Loop - Can be slow with large datasets
Hash Join - Generally efficient for larger datasets
Buffers - Shows I/O operations

auto_explain

PostgreSQL’s auto_explain extension automatically logs slow queries with their execution plans:

-- Enable auto_explain

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = true;

pgBadger

pgBadger is a fast PostgreSQL log analyzer written in Perl that generates detailed HTML reports about query performance. It’s actively maintained with v13.0 released in late 2024:

pgbadger /var/log/postgresql/postgresql.log -o report.html

The generated report includes query statistics, connection graphs, lock analysis, and performance trends.

pganalyze

For a more modern SaaS approach, pganalyze provides continuous PostgreSQL monitoring with:

Automated query performance insights
Index advisor recommendations
EXPLAIN plan visualization
Anomaly detection and alerts

It integrates well with Rails applications and provides deeper insights than log based analysis.

PgHero

The pghero gem provides a dashboard for PostgreSQL performance:

# Gemfile

gem 'pghero'

# config/routes.rb

mount PgHero::Engine, at: 'pghero'

PgHero shows:

Slow queries
Missing indexes
Unused indexes
Index bloat
Connection stats

MySQL Specific Tools

EXPLAIN and EXPLAIN ANALYZE

MySQL 8.0+ supports EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM users WHERE status = 'active';

Look for:

type: ALL - Full table scan
type: index - Full index scan
type: ref - Index lookup
type: eq_ref - Unique index lookup
Extra: Using filesort - Sorting without index
Extra: Using temporary - Temporary table created

Performance Schema

MySQL’s Performance Schema provides detailed query statistics:

-- Enable performance schema (usually enabled by default)

-- Find slow queries

SELECT
  DIGEST_TEXT,
  COUNT_STAR as calls,
  SUM_TIMER_WAIT / 1000000000000 as total_seconds,
  AVG_TIMER_WAIT / 1000000000000 as avg_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

MySQL Slow Query Log

Enable slow query logging in MySQL:

# my.cnf
slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 0.1

log_queries_not_using_indexes = 1

pt-query-digest

Part of Percona Toolkit, pt-query-digest analyzes MySQL slow query logs:

pt-query-digest /var/log/mysql/slow.log > report.txt

SQLite Specific Tools

Rails 8 made SQLite a first-class production database. The built-in adapter now ships with sensible defaults that directly affect query performance:

# Rails 8 SQLite adapter defaults (applied automatically)

# PRAGMA journal_mode = WAL

# PRAGMA synchronous = NORMAL

# PRAGMA mmap_size = 128MB

# PRAGMA busy_timeout = 5000

WAL (Write-Ahead Logging) mode allows concurrent reads during writes, and mmap_size enables memory-mapped I/O for faster reads. These defaults mean that most Rails 8 SQLite applications will perform well out of the box without manual PRAGMA tuning.

EXPLAIN QUERY PLAN

SQLite uses EXPLAIN QUERY PLAN for query analysis:

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

Look for:

SCAN - Full table scan
SEARCH - Using an index
USING INDEX - Covering index

sqlite3_analyzer

SQLite provides a tool to analyze database structure:

sqlite3_analyzer database.sqlite3

Solid Cache and Query Performance

Rails 8 defaults to Solid Cache for caching, replacing Redis or Memcached with a database-backed cache store. This means cache reads and writes are now SQL queries hitting the database.

When debugging query performance, be aware that Solid Cache queries will appear in our query logs and monitoring tools. If we see frequent queries against the solid_cache_entries table, that’s expected behavior rather than a problem to fix.

For high-traffic applications, consider using a separate database for Solid Cache to keep cache queries from competing with the application queries:

# config/database.yml

production:

  primary:

    <<: *default

    database: my_app_production

  cache:

    <<: *default

    database: my_app_production_cache

    migrations_paths: db/cache_migrate

Practical Debugging Workflow

Here’s a systematic approach to debugging query performance:

Step 1: Identify Slow Queries

Use query logs or monitoring tools to identify slow queries:

# Add to config/initializers/query_logger.rb

if Rails.env.development?
  ActiveSupport::Notifications.subscribe('sql.active_record') do |*args|
    event = ActiveSupport::Notifications::Event.new(*args)
    if event.duration > 50
      Rails.logger.debug "SLOW: #{event.duration.round(2)}ms - #{event.payload[:sql]}"
    end
  end
end

Step 2: Analyze the Query Plan

# In Rails console

slow_query = User.joins(:posts).where(posts: { status: 'published' })
puts slow_query.explain(:analyze)

Step 3: Check for Missing Indexes

# Using PgHero for PostgreSQL

PgHero.missing_indexes

# Or manually check

ActiveRecord::Base.connection.indexes(:users)

Step 4: Optimize the Query

Common optimizations include:

# Add appropriate indexes

add_index :posts, :status
add_index :posts, [:user_id, :status]

# Use select to limit columns

User.select(:id, :name).where(status: 'active')

# Use pluck for simple value extraction

User.where(status: 'active').pluck(:id)

# Use exists? instead of present? for existence checks

User.where(email: '[email protected]').exists?

# Use find_each for batch processing

User.where(status: 'active').find_each(batch_size: 1000) do |user|
  # Process user

end

Step 5: Verify the Improvement

# Compare before and after

require 'benchmark'

Benchmark.bm(15) do |x|
  x.report("before index:") { User.where(status: 'active').to_a }
  x.report("after index:")  { User.where(status: 'active').to_a }
end

# For more statistically meaningful results,

# use the benchmark-ips gem

require 'benchmark/ips'

Benchmark.ips do |x|
  x.report("before") { User.where(status: 'active').to_a }
  x.report("after")  { User.where(status: 'active').to_a }
  x.compare!
end

Monitoring in Production

Scout APM and New Relic

Application Performance Monitoring tools like Scout APM and New Relic provide detailed query analysis in production:

Automatic slow query detection
Query traces with source code context
Historical performance trends
Alerting on performance degradation

Custom Metrics with StatsD

# config/initializers/query_metrics.rb

ActiveSupport::Notifications.subscribe('sql.active_record') do |*args|
  event = ActiveSupport::Notifications::Event.new(*args)
  StatsD.timing('database.query', event.duration)

  if event.duration > 100
    StatsD.increment('database.slow_queries')
  end
end

Summary

Debugging query performance in Rails requires a combination of tools and a systematic approach.

Start with Rails built in tools like explain, load_async, and strict loading, add gems like Bullet and rack-mini-profiler for development, and leverage database specific tools for deeper analysis.

For PostgreSQL, pg_stat_statements and PgHero are invaluable. For MySQL, the Performance Schema and pt-query-digest provide similar insights. For SQLite, Rails 8’s production-ready defaults handle most tuning automatically.

Keep in mind that Rails 8’s Solid Cache adds database-backed cache queries to the workload, so factor those in when analyzing slow query logs.

Remember that query optimization is an iterative process. Monitor our application continuously, identify bottlenecks early, and address them before they impact users.

Different Approaches to Debugging Query Performance in Rails