Rails Adds The Ability To Ignore Counter Cache Columns While They Are Backfilling

In typical Rails applications, developers often encounter the N+1 query problem, where multiple queries are executed to retrieve counts for associated records.

class Task < ApplicationRecord
  belongs_to :project
end

class Project < ApplicationRecord
  has_many :tasks
end

For instance, if we want to display the number of tasks for each project, a naive approach would result in a separate query for each project to count its tasks.

<% @projects.each do |project| %>
    <%= project.tasks.count %>
<% end %>

This can significantly slow down the application as the number of projects increases.

Counter cache

Rails counter_cache is a powerful feature designed to optimize performance by efficiently tracking the number of associated records for a given model.

By storing the count of associated records in a dedicated integer column directly within the parent record, it eliminates the need for frequent database queries.

For example, we have a Project model and a Task model, we can store the number of tasks a project has directly in the project table in a column called tasks_count.

It is very easy to add a counter cache column to a new table (thanks to the convention over configuration approach), just add a column.

class AddTasksCountToProjects < ActiveRecord::Migration[7.1]
  def change
    add_column :projects, :tasks_count, :integer, default: 0, null: false
  end
end

class Task < ApplicationRecord
  belongs_to :project, counter_cache: true
end

It is also easy to add a counter cache column to an existing small table - just add a column, lock additionally the referenced table (to avoid adding new records) and backfill in a single transaction.

# Populate counter_cache for the existing projects

class PopulateProjectTasksCount < ActiveRecord::Migration[7.1]
  def up
    Project.find_each do |article|
      Project.reset_counters(project.id, :tasks)
    end
  end
end

Before

Introducing counter caches on existing large tables can be problematic, as the column values must be backfilled separately from the column addition to avoid locking the table for too long. Refer

class PopulateTasksCountToProjects < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL.squish
      UPDATE projects
      SET tasks_count = (
        SELECT count(1)
        FROM tasks
        WHERE tasks.project_id = projects.id
      )
    SQL
  end

  def down
    execute <<-SQL.squish
      UPDATE projects
      SET tasks_count = 0
    SQL
  end
end

We must complete the backfilling before enabling :counter_cache, otherwise methods like size, any?, and others that use the counter cache might produce inaccurate results.

After

Rails 7.2 introduces option to ignore counter cache columns while they are backfilling.

To safely backfill the counter cache column while keeping it updated with changes to child records, use the following approach:

class Task < ApplicationRecord
  belongs_to :project, counter_cache: { active: false }
end

class AddTasksCountToProjects < ActiveRecord::Migration[7.2]
  def change
    add_column :projects, :tasks_count, :integer, default: 0, null: false
  end
end

# Populate counter_cache for the existing projects tasks_count

class PopulateProjectTasksCount < ActiveRecord::Migration[7.1]
  def up
    Project.find_each do |article|
      Project.reset_counters(project.id, :tasks)
    end
  end
end

While the counter_cache is not active, methods like size, any?, and others will bypass the cache and retrieve results directly from the database.

Once the counter cache column has been backfilled, simply remove the { active: false } option from the counter_cache definition. The methods will then start using the counter cache for their results.

class Task < ApplicationRecord
  belongs_to :project, counter_cache: true
end