Redesign Your Models for Performance in Rails

Inefficient SQL with extensive joins

When dealing with a substantial number of rows and multiple table joins, SQL queries can significantly slow down your Rails application.

This inefficiency is often pronounced when attributes of several related models are required in a single query.

Here’s a classic example using where clauses leading to overly complex SQL queries:

# app/models/article.rb
class Article < ActiveRecord::Base
  belongs_to :state       # foreign_key: state_id
  belongs_to :category    # foreign_key: category_id
  belongs_to :user        # columns: id, username (index), user_id (fk, index)
end

# app/controllers/articles_controller.rb
def show
  Article.includes([:state, :category]).
          where("states.name"       => "published",
                "categories.name"   => "hiking",
                "articles.user_id" => 123)
end

This approach requires multiple joins and can greatly diminish performance in a high-volume app.

Scope refactoring

Instead of cramming all the logic into a verbose query, refactor using scopes for better maintainability:

# app/models/article.rb
class Article < ActiveRecord::Base
  belongs_to :state
  belongs_to :category
  belongs_to :user

  scope :for_state, -> (name) { joins(:state).where(states: { name: name }) }
  scope :for_category, -> (name) { joins(:category).where(categories: { name: name }) }
end

# Usage:
Article.for_state("published").for_category("hiking")

Scopes make the code modular and maintainable. However, this still does not improve performance — it is almost the same query. You can check in your console with .to_sql if you want.

The solution: denormalize

While normalization is good for data integrity and avoiding redundancy, it sometimes hampers performance.

Denormalization simplifies the schema at the cost of redundancy but enhances read performance.

Here’s how you might reconsider your model if high query performance is critical:

# app/models/article.rb
class Article < ActiveRecord::Base
  # Enums simulate what a related table would have done with less overhead
  STATES = %w(draft review published archived)
  CATEGORIES = %w(tips faqs misc hiking)

  validates :state, inclusion: { in: STATES }
  validates :category, inclusion: { in: CATEGORIES }
end

# Easier query without joins:
Article.where(state: "published", category: "hiking")

If your application is not a multi-tenant system (like a SaaS) and involves simpler, less dynamic categorization, consider using enums directly in your table instead of separate tables.

For instance, if your application only handles one blog for a single user, you can avoid the complexity of additional category tables by directly using enums.

Another denormalization example

Think critically about when to denormalize — this decision depends on the specific needs and scale of your application.

By aligning your database design with your application’s requirements, you can optimize performance and maintain clean, effective code.

As a rule of thumb, do not normalize something that does not have its own controller in the app.

Take a tagging system, for example. It could be done with a has_and_belongs_to_many setup or just with a jsonb array as a column.

In the latter case, a valid reason to opt for HABTM is if your user needs to create new tags with names and descriptions and search within them.

Otherwise, just denormalize that complexity.

Conclusion

Yes, performance issues are mostly solved by better defining the problem to eliminate the existing complexity in the code.

The added complexity that could have been removed is often there because the team did not invest enough time in defining the problem and questioning the original requirements.

Debates are not a liability because we are not coding.

Coding things that we do not understand is the liability — because ultimately we will be in a situation where we create more meetings and more PRs to re-read code that is causing issues.