Optimising DB Queries the right way in Rails

Our APIs were getting slow, servers were overloading under peak traffic and we had occasional downtime. Here is what we did to fix it.

Why we needed this?

• Some APIs were taking way too long to respond
• Server was doing unnecessary work on every request
• Under peak load, the extra DB pressure was causing downtime

1. Add Indexes on the Right Columns

If you are querying a table frequently by a column, don’t let the DB scan every row, add an index.

Good candidates:

• Columns used in WHERE, JOIN or ORDER BY clauses
• High-read, low-write columns (indexes slow down writes slightly)

In Rails, add them via a migration:

# Single column
add_index :table_name, :column_name, name: 'index_column_name'

# Composite (multi-column)
add_index :table_name, [:column_one, :column_two], name: 'index_column_one_two'

But with LIKE queries, a leading % kills the index:

# ✅ Index works
User.where("name LIKE ?", "#{query}%")

# ❌ Index ignored
User.where("name LIKE ?", "%#{query}%")

If you need full substring search, look into full-text search instead.

2. Cache frequently read, rarely updated data

Not everything needs a fresh DB hit every request. If data is read heavily but barely changes, we can cache it.

The flow:

1. Request comes in → check Redis first
2. Cache hit → return immediately
3. Cache miss → fetch from DB, store in Redis with a TTL (e.g. a few hours/days), return response
4. Subsequent requests hit Redis until TTL expires, then repeat

Things to keep in mind:

• Make cache keys specific enough to avoid collisions
• Don’t set TTL too long → stale data can lead to buggy behaviour
• If the data changes, invalidate the cache immediately, don’t wait for expiry

3. Fix N+1 Queries

This one is tricky. Your code looks fine but the DB ends up running dozens of queries instead of a few.

It happens when you load a list of records and then access associations on each one. Rails lazily fires a new query per record.

Fix it with eager loading:

# Causes N+1
records = Post.where(user_id: id)
records.each { |r| puts r.comments }

# Fixed — loads everything upfront in one go
Post.where(user_id: id).includes(:comments)

# For nested associations
Post.where(user_id: id).includes(comments: [:likes, :author])

Rails options:

• .includes → smart default, use this most of the time
• .preload → separate queries, better for large datasets
• .eager_load → single JOIN query, needed when filtering on associations

4. Find slow Queries in your Logs

Before fixing anything, know what is actually slow. This one-liner code, scans production logs for queries taking over 1 second:

perl -ne 'print if /Load \(([\d.]+)ms\)/ && $1 > 1000' production.log

Run it, see what shows up frequently, that is your todo list.

5. Monitoring with New Relic (P95 & P99)

Optimising blindly is pointless. We used New Relic APM to see what was slow and verify that fixes actually worked.

The two metrics we cared about → P95 and P99:

• P95 → 95% of requests completed within 200ms. Shows your typical slow case.
• P99 → 99% of requests completed within 500ms. Shows your worst-case outliers.

Our workflow:

• Transactions view → sorted by response time to find the worst offenders
• DB breakdown → New Relic shows how much of each request is spent in DB vs app code. If DB is eating 80%+ of the time, that is where to focus.
• Slow SQL tab → shows the actual slow queries with explain plans. Tells you exactly what needs an index.
• Custom dashboard → set up P95/P99 tracking for high-traffic endpoints to catch regressions early
• Before/after comparison → after each fix, watched the charts to confirm improvement

One endpoint had a P99 of ~3.2s. After fixing an N+1 and adding a couple indexes, it dropped to under 400ms.