devLap

The Hidden Cost of Missing or Wrong Database Indexes in Rails

ruby dev.to

Do you think your SQL Query Is Fine?!
Lets today talk about the hidden cost of missing or wrong Database indexes in Rails.

A few years ago, I was investigating a Rails endpoint that suddenly became slow.

Nothing unusual had happened.
No major deployment.
No infrastructure changes.
No traffic spike.
The endpoint that normally responded in under 100ms was now taking several seconds.
The Rails logs looked clean.
The SQL query looked reasonable.
The application servers were healthy.

The problem .........
A missing database index.
One small database optimization turned a multi-second query into a millisecond operation.

If you've ever experienced unexplained performance degradation in a Rails application, there's a good chance missing indexes were involved.

let's go deeper in indexing.

1- What Is a Database Index?
Think of a database index as the index section at the back of a book.
Without an index, finding a specific topic means reading page after page until you locate the information.
With an index, you can jump directly to the correct page.
Databases work in a similar way.
When a query filters records using indexed columns, the database can locate matching rows quickly.
Without an index, the database may be forced to scan the entire table.
And that's where trouble begins.

2- The Problem Isn't Today
One of the reasons missing indexes are so dangerous is that they often go unnoticed during development.

Consider this scenario:

  • Development database: users table contains 500 rows
  • Production database: users table contains 50 million rows

A query that feels instant during development may become painfully slow in production.
The larger your dataset becomes, the more expensive table scans become.
This is why indexing is ultimately a scalability concern.

3- Why Missing Indexes Cause Performance Problems
When a query searches for records, the database has two options:

Best Case (Use an index).
Locate matching rows quickly
Return results
Done

Worst Case (Perform a full table scan).
Read row 1
Read row 2
Read row 3
...
Read row 50,000,000

The second option consumes:

  • More CPU
  • More memory
  • More disk I/O
  • More query execution time

As a result:

  • Requests become slower
  • Application workers remain busy longer
  • CPU usage increases
  • Database load grows

Eventually users begin noticing.

4- Common Places Where Missing Indexes Appear
Most Rails applications contain several predictable indexing candidates.

  • Foreign Keys: 
belongs_to :user
Enter fullscreen mode Exit fullscreen mode

If your queries frequently search by:

WHERE user_id = ?
Enter fullscreen mode Exit fullscreen mode

An index is usually required.

  • Status Columns: 
Order.where(status: "completed")
Enter fullscreen mode Exit fullscreen mode

Frequently filtered columns are often strong indexing candidates.

  • Search Fields 
User.find_by(email: email)
Enter fullscreen mode Exit fullscreen mode

Email addresses, usernames, slugs, and external identifiers are common index targets.

  • Sorting Operations 
Post.order(created_at: :desc)
Enter fullscreen mode Exit fullscreen mode

Sorting large datasets can become expensive without proper indexing.

5- Why Rails Developers Accidentally Create This Problem
Most missing index issues come from one of these situations.

Feature-Driven Development

  • A feature is delivered.
  • Tests pass.
  • Users are happy.
  • Nobody thinks about query execution plans.
  • The feature works today.
  • The performance problem appears six months later.

Small Development Databases
Local databases rarely contain production-sized datasets.
Queries that appear instant locally may behave very differently with millions of records.

Schema Evolution

  • Applications grow.
  • New reports are added.
  • New filters are introduced.
  • New business requirements emerge.
  • The indexing strategy that worked last year may no longer be sufficient today.

6- How to Avoid Missing Indexes
The easiest strategy is simple:
Whenever you introduce a new query pattern, ask yourself:

Will this column be frequently searched, filtered, joined, or sorted?
If the answer is yes, consider whether an index should exist.

Examples:

User.find_by(email: email)
Order.where(customer_id: customer.id)
Post.order(created_at: :desc)
Enter fullscreen mode Exit fullscreen mode

These queries often benefit from indexing.

7- Helpful Tools Every Rails Engineer Should Know

  • EXPLAIN

The first tool every engineer should learn.

User.where(email: email).explain
Enter fullscreen mode Exit fullscreen mode

Rails can show the database execution plan and reveal whether indexes are being used.

  • PgHero

One of the most valuable tools for PostgreSQL-powered Rails applications.

PgHero can identify:

  1. Missing indexes
  2. Unused indexes
  3. Slow queries
  4. Expensive database operations

Many Rails teams discover performance issues with PgHero long before users report them.

  • Bullet

Most engineers know Bullet for detecting N+1 Queries.
However, it can also provide useful performance insights during development.

  • New Relic

Provides visibility into:

  1. Slow database transactions
  2. Query performance
  3. Endpoint bottlenecks

Excellent for identifying problematic database behavior in production.

  • Datadog APM

Useful for tracing application requests and understanding where time is spent between Rails and PostgreSQL.

8- When Not to Add an Index
A common mistake is believing every column needs an index.
Indexes are powerful, but they are not free.

Every index:

  1. Consumes storage
  2. Slows INSERT operations
  3. Slows UPDATE operations
  4. Increases maintenance overhead

The goal is not to index everything.
The goal is to index the right things.
Good indexing is a balancing act.

Source: dev.to

arrow_back Back to Tutorials