Ruby on Rails SQL Performance: Indexing for Efficiency

Before you start

Keep in mind that these tricks are for optimization, and to optimize, you need an app with real users.

• Optimization(0) = 0

In the real world, you need to start with the data:

• Identify endpoints, actions, and views that are slow
• Ensure that data operations are done in SQL and that Ruby is only iterating the lists
• List the slow queries
• Then find the pattern

In this article, I will tell you what to look for so that you can recognize a pattern among the list of queries that are slowing you down and start with a simple plan.

Give yourself a few days to really see the changes in the data and lastly:

• Backup your data before any operations
• Remember to run your migration with: algorithm: :concurrently

Example:

class AddIndexToAsksActive < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :asks, :active, algorithm: :concurrently
  end
end

Solution: Add Indexes to

Indexes are crucial for quick data retrieval in relational databases.

For those who are not familiar:

• SQL indexes work by creating a sorted data structure that improves the speed of data retrieval operations in databases by allowing rapid access to specific rows based on indexed columns.

• SQL indexes allow rapid access to specific rows by organizing the data in a sorted structure, such as a B-tree, which enables the database to quickly locate the desired rows based on the indexed columns without having to scan the entire table.

PostgreSQL is a rabbit hole, but I invite you to get addicted to it, why?

Ask any 10x engineer about what was the pinnacle of success in their career, 9 out of 10, it’s dealing with data at loads.

Learn more here

Here’s where to effectively implement them.

Primary Keys

Most relational databases automatically index primary keys:

# app/models/user.rb
class User < ApplicationRecord
  has_many :posts
end

The users.id is automatically indexed, facilitating fast look-ups for actions like /users/1.

Foreign Keys

Index your foreign keys to speed up queries involving associated records:

# app/models/comment.rb
class Comment < ApplicationRecord
  belongs_to :user
end

Add an index to comments.user_id to optimize queries for /users/1/comments.

Columns Used in Polymorphic Conditional Joins

Polymorphic associations need careful indexing to optimize join operations:

# app/models/tag.rb
class Tag < ApplicationRecord
  has_many :taggings
end

# app/models/tagging.rb
class Tagging < ApplicationRecord
  belongs_to :tag
  belongs_to :taggable, polymorphic: true
end

# app/models/comment.rb
class Comment < ApplicationRecord
  has_many :taggings, as: :taggable
end

Add a composite index on taggings.taggable_type and taggings.taggable_id to support efficient querying.

Columns Used in Uniqueness Validations

Unique columns should be indexed to speed up validation checks:

# app/models/user.rb
class User < ApplicationRecord
  validates :email, uniqueness: true
end

Index users.email to ensure quick uniqueness checks when saving records.

Columns Used for STI

Indexing type columns in STI setups facilitates quicker queries by type:

# app/models/user.rb
class User < ApplicationRecord
  # This model is assumed to be using STI
end

If AdminUser and User share the users table, index users.type.

Columns Used by to_param

Pretty URLs demand indexing on commonly searched fields:

# app/models/location.rb
class Location < ApplicationRecord
  def to_param
    city
  end
end

Index locations.city to enhance look-up efficiency in URL resolutions.

Other Columns Used in WHERE Clauses

Index appropriately based on your query patterns to optimize performance, especially for columns frequently used in WHERE clauses.

DateTime and Boolean Columns

Consider indexing boolean columns selectively and datetime columns for sorting or filtering operations.

Continuous Diagnosis of Slow Queries in SQL

The thing about performance is that everyone should be alert about it. But somehow, the developer often forgets about it, and cases are present only in production.

Here’s a rundown of tools and techniques to seek out and eliminate performance bottlenecks:

1. Identify

I love this gem:

• Rack Mini Profiler

The gem really helps developers to keep themsemfe on track regading performance and mostlhy checking they are not hitting N+1 perfromance issues.

2. Ensure best practices

Normally, if you use Rails generators often, you are unlikely to miss important indexes.

By following basic best practices and using generators, you are mostly covered for the MVP of your feature or version 1.

Indexing is an optimization issue. Therefore, it’s an issue that should arise once there is traffic. Prior to that, your primary responsibility is to ensure code quality.

3. Examine logs

Most of the data will come from production logs and metrics.

If you are receiving comments about speed in important parts of the app, I would suggest using an all-in-one observability tool.

But again, your judgment is really important.

Imagine an important LMS app with 300,000 users:

• GET /subscription (9700ms) endpoint that is used to accept payments if the card has been declined.
• POST /post (780ms) endpoint that is used in your paid forum.

You might be tempted to sort your logs and focus on the slowest one.

But in this scenario, you are prioritizing an endpoint that is only used once a month.

The POST /post (780ms) should actually be the focus since the community is where everyone will create their opinion about the product.

If you look at other messaging apps in the market, you don’t wait to talk… 780ms is not the market standard and people will feel it and complain about it.

But again, there are two types of software:

• The ones that people complain about
• The ones that people don’t use

What’s the Downside?

Carefully evaluate the need for indexes, as adding them introduces maintenance overhead and can slow down write operations.

• Identify endpoints, actions, and views that are slow
• Ensure that data operations are done in SQL and that Ruby is only iterating the lists
• List the slow queries
• Then find the pattern

Related posts