Benito Serna Tips and tools for Ruby on Rails developers

Solve n+1 queries and slow counts with counter caches

May 19, 2022

Sometimes calculating the count of a collection on every page render, even in SQL, is not the best option.

Imagine that you need to render a post, with its count of likes and comments, but the post has thousands of likes and comments. That would be slow.

One solution is to save the count of those comments and likes in the record, and when you need to render the post, instead of counting the elements again, just display the count that you already saved.

Keeping that count in sync is not easy, but rails makes it easy for us with a feature called “counter cache”.

Here I will show you how you can use a counter caches, how to introduce it in an existent association and some tips to work with them.

Add a counter cache to a new association with the rails defaults

To add a counter cache you will need to:

  1. Add the counter_cache: true option in the belongs_to declaration.
  2. Add a column to keep the count in the associated model (the one with the has_many), with the name #{table_name}_count.

For example to keep the count of likes in a post, you can add a likes_count coulumn in the posts table.

class Post < ApplicationRecord
  has_many :likes
end

class Like < ApplicationRecord
  # 1. Add the counter_cache: true option in the belongs_to declaration
  belongs_to :post, counter_cache: true
end

class CreatePosts < ActiveRecord::Migration[7.0]
  def change
    create_table :posts do |t|
      #...
      # 2. Add a column to keep the count in the associated model (has_many)
      t.integer :likes_count
    end

    create_table :likes do |t|
      t.references :post
    end
  end
end

Custom counter cache columns names

If you want to add a different name to the counter cache column you have to specify the name of the column in both association declarations, the belongs_to and the has_many.

class Post < ApplicationRecord
  # Specify the custom column name in the has_many association
  has_many :likes, counter_cache: :likes_total
end

class Like < ApplicationRecord
  # Specify the custom column name in the belongs_to association
  belongs_to :post, counter_cache: :likes_total
end

class CreatePosts < ActiveRecord::Migration[7.0]
  def change
    create_table :posts do |t|
      #...
      # 2. Add a column to keep the count in the associated model (has_many)
      t.integer :likes_total
    end

    create_table :likes do |t|
      t.references :post
    end
  end
end

It is important to remember to also define the counter_cache option in the has_many association. Without it, the size method won’t be able to read your counter cache column.

Reset the counters for an existent association with just a few records

If you want to add a counter cache to an existing association that already has records created, you will need to populate the counter column.

If you don’t have too many records to update you can use the reset_counters class method.

You can iterate through each record and reset its counter.

class Post < ApplicationRecord
  has_many :likes
end

class Like < ApplicationRecord
  # 1. Add the counter_cache: true option in the belongs_to declaration
  belongs_to :post, counter_cache: true
end

class AddLikesCountToPosts < ActiveRecord::Migration[7.0]
  def up
    # 2. Add a column to keep the count in the associated model (has_many)
    add_column :posts, :likes_count, :integer

    # 3. Update the counters for each post
    Post.find_each do |post|
      Post.reset_counters(post.id, :likes, touch: true) # The touch is optional
    end
  end

  def down
    remove_column :posts, :likes_count, :integer
  end
end

The reset_counters method will make a database call for each post, and if you have many records it will take a lot of time. Remember to just use this method if you don’t have too many records.

Reset the counters with SQL, for associations with many records

As I said before, If you will add the counter cache to an existing association that already has records, you will need to populate the counter column.

But If you have too many records to update, the previous method can be really slow, because it will make a database call for each record.

Other way of accomplish the task is by executing sql directly, like it is described by Ryan McGeary in the post Proper Counter Cache Migrations in Rails.

class Post < ApplicationRecord
  has_many :likes
end

class Like < ApplicationRecord
  # 1. Add the counter_cache: true option in the belongs_to declaration
  belongs_to :post, counter_cache: true
end

class AddLikesCountToPosts < ActiveRecord::Migration[7.0]
  def up
    # 2. Add a column to keep the count in the associated model (has_many)
    add_column :posts, :likes_count, :integer

    # 3. Update the counters for each post
    update_counters
  end

  def down
    remove_column :posts, :likes_count, :integer
  end

  def update_counters 
    execute <<-SQL.squish
      UPDATE posts
      SET likes_count = (
        SELECT count(1)
        FROM likes
        WHERE likes.post_id = posts.id
      )
    SQL
  end
end

How to use the counter cache in your views?

You can use it in two ways:

  1. Using the method with the column name
  2. Using the size method
class Post < ApplicationRecord
  has_many :likes
end

class Like < ApplicationRecord
  belongs_to :post, counter_cache: true
end

posts = Post.limit(10)
posts.each do |post|
  # Using the method with the column name
  post.likes_count

  # "size" will also read you counter cache column
  post.likes.size
end

If you are using a custom column name, you can also use both, the column name and the size method.

class Post < ApplicationRecord
  has_many :likes, counter_cache: :total_likes
end

class Like < ApplicationRecord
  belongs_to :post, counter_cache: :total_likes
end

posts = Post.limit(10)
posts.each do |post|
  # Using the method with the column name
  post.total_likes

  # "size" will also read you counter cache column
  post.likes.size
end

But, when you are using a custom counter cache name, is important to remember to also define the counter_cache option in the has_many association. Without it size won’t be able to read your counter cache column.

class Post < ApplicationRecord
  # If you don't define the custom counter cache column...
  has_many :likes
end

class Like < ApplicationRecord
  belongs_to :post, counter_cache: :total_likes
end

posts = Post.limit(10)
posts.each do |post|
  # "size" won't know the custom column name and will execute a COUNT query
  post.likes.size
end

References

Related articles

No more… “Why active record is ignoring my includes?”

Get for free the first part of the ebook Fix n+1 queries on Rails that will help you:

  • Explain what is an n+1 queries problem
  • Identify when ActiveRecord will execute a query
  • Solve the latest comment example
  • Detect n+1 queries by watching the logs
  • Learn the tools to detect n+1 queries
Get the first part of the ebook for free