Caching counters with ActiveRecord's counter caches

Instead of counting the associated records in the database every time the page loads, ActiveRecord's counter caching feature allows storing the counter and updating it every time an associated object is created or removed. In this episode of AppSignal Academy, we'll learn all about caching counters in ActiveRecord.

Let's take the classic example of a blog with articles and responses. Each article can have responses, and we'd like to display the number of responses next to each article's title on the blog's index page to show its popularity.

# app/controllers/articles_controller.rb
class ArticlesController < ApplicationController
  def index
    @articles = Article.all
  end

  # ...
end

We don't have to preload the responses, as we don't show their data on the index page. We're showing a counter, so we're only interested in the number of responses for each article. The controller finds all articles and places them in the @articles variable for the view to use.

<!-- app/views/articles/index.html.erb -->
<h1>Articles</h1>

<% @articles.each do |article| %>
<article>
  <h1><%= article.title %></h1>
  <p><%= article.description %></p>
  <%= article.responses.size %> responses
</article>
<% end %>

The view loops over each article and renders its title, description and the number of responses it received. Because we call article.responses.size in the view, ActiveRecord knows it needs to count the association instead of loading the whole record for each response.

Tip: Although #count sounds like the more intuitive choice for counting the number of responses, this example uses #size, as #count will always do a COUNT query, while #size will skip the query if the responses are already loaded.

Started GET "/articles" for 127.0.0.1 at 2018-06-14 16:25:36 +0200
Processing by ArticlesController#index as HTML
  Rendering articles/index.html.erb within layouts/application
  Article Load (0.2ms)  SELECT "articles".* FROM "articles"
  ↳ app/views/articles/index.html.erb:3
  (0.2ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 2]]
  ↳ app/views/articles/index.html.erb:7
  (0.3ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 3]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 4]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 5]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 6]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 7]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 8]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 9]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 10]]
  ↳ app/views/articles/index.html.erb:7
  (0.1ms)  SELECT COUNT(*) FROM "responses" WHERE "responses"."article_id" = ?  [["article_id", 11]]
  ↳ app/views/articles/index.html.erb:7
  Rendered articles/index.html.erb within layouts/application (23.1ms)
Completed 200 OK in 52ms (Views: 45.7ms | ActiveRecord: 1.6ms)

Requesting the blog's index results in N+1 queries, as ActiveRecord lazy-loads the response count for each article in a separate query.

Using COUNT() from the query

To avoid running an extra query per article, we can join the articles and responses tables together to count the associated responses in a single query.

# app/controllers/articles_controller.rb
class ArticlesController < ApplicationController
  def index
    @articles = Article.
      joins(:responses).
      select("articles.*", 'COUNT("responses.id") AS responses_count').
      group('articles.id')
  end

  # ...
end

In this example, we join the responses in the articles query and select COUNT("responses.id") to count the number of responses. We'll group by the product IDs to count the responses per article. In the view, we'll need to use responses_count instead of calling size on the responses association.

This solution prevents extra queries by making the first query slower and more complex. While this is a good first step in optimizing the performance of this page, we can go a step further and cache the counter so we don't need to count each response on every page view.

Counter cache

As the articles on the blog are (hopefully) read more often than they're updated, a counter cache is a good optimization to make querying this page faster and simpler.

Instead of counting the number of responses every time the articles are displayed, a counter cache keeps a separate response counter which is stored in each article's database row. The counter updates whenever a response is added or removed.

This allows the article index to render with one database query, without needing to join the responses in the query. To set it up, flip the switch in the belongs_to relation by setting the counter_cache option.

# app/models/response.rb
class Response
  belongs_to :article, counter_cache: true
end

This requires a field to the Article model named responses_count. The counter_cache option makes sure the number in that field automatically updates whenever a response is added or removed.

Tip: The field name can be overridden by using a symbol instead of true as the value for the counter_cache option.

We create a new column in our database to store the count.

$ rails generate migration AddResponsesCountToArticles responses_count:integer
      invoke  active_record
      create    db/migrate/20180618093257_add_responses_count_to_articles.rb
$ rake db:migrate
== 20180618093257 AddResponsesCountToArticles: migrating ======================
-- add_column(:articles, :responses_count, :integer)
  -> 0.0016s
== 20180618093257 AddResponsesCountToArticles: migrated (0.0017s) =============

Because the number of responses is now cached in the articles table, we don't need to join the responses in the articles query. We'll use Article.all to fetch all articles in the controller.

# app/controllers/articles_controller.rb
class ArticlesController < ApplicationController
  def index
    @articles = Article.all
  end

  # ...
end

We don't need to change the view, as Rails understands to use the counter cache for the #size method.

<!-- app/views/articles/index.html.erb -->
<h1>Articles</h1>

<% @articles.each do |article| %>
<article>
  <h1><%= article.title %></h1>
  <p><%= article.description %></p>
  <%= article.responses.size %> responses
</article>
<% end %>

Requesting our index again, we can see one query being executed. Because each article knows its number of responses, it doesn't need to query the responses table at all.

Started GET "/articles" for 127.0.0.1 at 2018-06-14 17:15:23 +0200
Processing by ArticlesController#index as HTML
  Rendering articles/index.html.erb within layouts/application
  Article Load (0.2ms)  SELECT "articles".* FROM "articles"
  ↳ app/views/articles/index.html.erb:3
  Rendered articles/index.html.erb within layouts/application (3.5ms)
Completed 200 OK in 42ms (Views: 36.5ms | ActiveRecord: 0.2ms)

Counter caches for scoped associations

ActiveRecord's counter cache callbacks only fire when creating or destroying records, so adding a counter cache on a scoped association won't work. For advanced cases, like only counting the number of published responses, check out the counter_culture gem.

Populating the counter cache

For articles that predate the counter cache, the counter will be out of sync, as it's 0 by default. We can “reset” a counter for an object by using the .reset_counters method on it and passing the object's ID and the relation the counter should be updated for.

Article.reset_counters(article.id, :responses)

To make sure this runs on production when we deploy, we’ll put it in a migration that runs directly after adding the column in the last migration.

$ rails generate migration PopulateArticleResponsesCount --force
      invoke  active_record
      create    db/migrate/20180618093443_populate_article_responses_count.rb

In the migration, we'll call Article.reset_counters for each article, passing the articles' IDs and :responses as the association's name.

# db/migrate/20180618093443_populate_article_responses_count.rb
class PopulateArticleResponsesCount < ActiveRecord::Migration[5.2]
  def up
    Article.find_each do |article|
      Article.reset_counters(article.id, :responses)
    end
  end
end

This migration updates the counts for all articles in the database including the ones that existed before the counter cache.

Callbacks

Because counter caches use callbacks to update the counters, methods that directly execute SQL commands (like when using #delete instead of #destroy) won't update the counters.

In situations where that does happen for some reason, it might make sense to add a Rake task or a background job that keeps the counts in sync periodically.

namespace :counters do
  task update: :environment do
    Article.find_each do |article|
      Article.reset_counters(article.id, :responses)
    end
  end
end

Cached counters

Preventing N+1 queries by counting associated objects in the query can help, but caching counters is an even faster way to show counters for most applications. ActiveRecord's built-in cached counters can be a lot of help, and options like counter_culture can be used for more elaborate requirements.

Have any questions about ActiveRecord's counter caches? Please don't hesitate to let us know at @AppSignal. Of course, we'd love to know how you liked this article, or if you have another subject you'd like to know more about.