Optimizing PostgreSQL Performance for Rails Applications

PostgreSQL Performance Optimization

A well-tuned PostgreSQL database is crucial for Rails application performance. Let's explore optimization techniques that can dramatically improve your app's speed.

Understanding EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT posts.*, users.name
FROM posts
JOIN users ON posts.user_id = users.id
WHERE posts.published = true
ORDER BY posts.created_at DESC
LIMIT 20;

Indexing Strategies

Proper indexing is the foundation of database performance:

1. B-tree Indexes (Default)

class AddIndexesToPosts < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, :user_id
    add_index :posts, :published
    add_index :posts, [:user_id, :published] # Composite index
  end
end

2. Partial Indexes

# Only index published posts
add_index :posts, :created_at,
  where: "published = true",
  name: "index_published_posts_on_created_at"

3. GIN Indexes for Full-Text Search

enable_extension 'pg_trgm'

add_index :posts, :title,
  using: :gin,
  opclass: :gin_trgm_ops

Query Optimization Tips

1. Use includes/joins wisely: Prevent N+1 queries
2. Batch operations: Use insert_all and upsert_all
3. Limit SELECT fields: Use select to fetch only needed columns
4. Use database views: For complex queries

Connection Pool Tuning

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

PostgreSQL Configuration

# postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
random_page_cost = 1.1

Monitoring Queries

Use pg_stat_statements to track slow queries:

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;