How to properly introduce a new counter_cache to an existing Rails project.

add_comments_count_to_posts.rb

class AddCommentsCountToPosts < ActiveRecord::Migration
  def change
    change_table :posts do |t|
      t.integer :comments_count, default: 0
    end

    reversible do |dir|
      dir.up { fast }
    end
  end

  # DO THIS!
  def fast
    execute <<-SQL.squish
        UPDATE posts
           SET comments_count = (SELECT count(1)
                                   FROM comments
                                  WHERE comments.post_id = posts.id);
    SQL
  end
 
  # NOT THIS! 
  def slow
    Post.reset_column_information
    say_with_time "Seeding posts.comments_count -- Better grab a coffee." do
      print "   -> "
      Post.select(:id).find_in_batches do |posts|
        print "."
        posts.each do |s|
          Post.reset_counters s.id, :comments
        end
      end
      puts
      Post.count
    end
  end
end

OUTPUT.md

Fast/efficient approach:

-- execute("UPDATE posts SET comments_count = (SELECT count(1) FROM comments WHERE comments.post_id = posts.id)")
   -> 1.3197s

Slow/naïve approach:

-- Seeding posts.comments_count -- Better grab a coffee.
   -> ...........................
   -> 144.7302s
   -> 26900 rows

This is great! Will have to read up on my raw SQL to fully understand, but certainly faster.

nice!