PostgreSQL Query Optimization in Rails

Introduction to Optimization

PostgreSQL query optimization is crucial for maintaining Rails application performance as they grow. A poorly optimized query can become a significant bottleneck.

Identifying Slow Queries

The first step is identifying which queries are causing problems:

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

# In the logs you'll see:
# User Load (2.3ms)  SELECT "users".* FROM "users"
# ↳ app/controllers/users_controller.rb:10:in `index`

The N+1 Problem

One of the most common problems in Rails:

# ❌ Problematic - N+1 queries
@users = User.all
@users.each { |user| puts user.posts.count }

# ✅ Optimized - 2 queries
@users = User.includes(:posts)
@users.each { |user| puts user.posts.count }

Using EXPLAIN

PostgreSQL provides EXPLAIN to analyze queries:

# In Rails console
User.joins(:posts).explain

# Or directly in PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users
JOIN posts ON posts.user_id = users.id;

A well-placed index can reduce query time from seconds to milliseconds.