Improve Rails App Performance Using Active Record’s Counter Cache

In this guide, I will explain how we can improve the performance of our rails app by using the counter cache.

Consider an app dashboard that shows a total no of counts from various sections of the app. For example, users have many numbers of events and our dashboard shows lists of users with their corresponding total no of events.

Usually, we will loop through all users and get each user’s event count from the events associated table. This operation hits the events table more and it costs a lot. This is where the Counter cache comes in to stop hitting the events table and keeps track of events count in the user table.

Let’s see how we can implement counter cache using the user and events table as our example. Below are the respective migrations for User and Event tables.

class CreateUsers < ActiveRecord::Migration[5.2]
  def change
    create_table :users do |t|
      t.string :name
      t.integer :events_count, default: 0, null: false

      t.timestamps
    end
  end
end

class CreateEvents < ActiveRecord::Migration[5.2]
  def change
    create_table :events do |t|
      t.string :title
      t.references :user, foreign_key: true

      t.timestamps
    end
  end
end

Once migrated, we have to specify has many relationships in the user model and enable counter cache in our event model.

class User < ApplicationRecord
  has_many :events
end

class Event < ApplicationRecord
  belongs_to :user, counter_cache: true
end

By specifying counter_cache true in Event relationship monitors event insert/delete and do update event_count in the User with proper increment/decrement. Now we would try to create a user and assign an event to that user in the rails console.

Here we can notice that an update query automatically triggers while creating an event to update the event count in user table. Similar kind of update query triggers even while deleting an event. This will omit a query something like “select count(‘id’) from events where user_id = 1” and we can get event count straight from the user table.

Using counter_cache will make a huge difference when we are handling tables with millions of records.

Using counter_cache on the existing app

The above section demonstrates the implementation of counter_cache in a new system. Now we will go through implementing counter_cache in an existing system.

So, I have a table called pictures that belongs to the user in which counter cache is not enabled for pictures count. If we just add the pictures_count column to the user and enable counter cache then by default initial picture_count will be 0 and gets incremented for any new picture insertions.

But, in our case picture table already hold users’ picture data. To successfully implement counter_cache we need to update pictures_count for each user during migration.

The below picture represents the migration file for adding a new pictures_count column to the user table.

class AddPicturesCountToUsers < ActiveRecord::Migration[5.2]
  def change
    add_column :users, :pictures_count, :integer, default: 0, null: false

    execute <<-SQL.squish
      UPDATE users
        SET pictures_count = (SELECT count(1)
                                FROM pictures
                                WHERE pictures.user_id = users.id)
    SQL
  end
end

Upon migration, a New column will be created and the pictures_count value will update accordingly.

Note: Avoid updating the count in the migration file if the existing table is huge and make it a separate rake task. Since migrations can stop other DB transactions for a while which leads to app downtime.