Rails Counter Cache—When Posts.count Brought Down Production

I once added user.posts.count to a dashboard. Looked innocent. The app had 50K users, average 200 posts each. Every dashboard load triggered 50K COUNT queries—one per user row. Response time? 800ms. Database CPU? Spiked to 90%. Production went down twice before I discovered Rails counter_cache performance could fix this with a single column. Here’s what I learned about counting associations without murdering your database.

The Problem with Counting Has-Many Associations

Every time you call user.posts.count, Rails hits the database. That’s fine for one user. It’s a disaster when you’re iterating over collections.

Here’s what I had in production:

SQL version (what Rails generates without counter_cache):

-- For EACH user in the result set
SELECT COUNT(*) FROM posts WHERE posts.user_id = 123
SELECT COUNT(*) FROM posts WHERE posts.user_id = 124
SELECT COUNT(*) FROM posts WHERE posts.user_id = 125
-- Repeat 50,000 times...

ActiveRecord version (the code that killed production):

# Admin dashboard showing all users
@users = User.limit(100)

# In the view
@users.each do |user|
  <%= user.posts.count %> # N+1 query—hits DB every iteration
end

Production impact: On a dashboard showing 100 users, this generated 101 queries (1 for users + 100 for counts). Load time: 340ms. When we increased the limit to show 500 users? 4.2 seconds. At 1000 users, the page timed out.

The Bullet gem caught this immediately in development, but I ignored it. “It’s just a count,” I thought. “How expensive could it be?” Very expensive. On 10M posts across 50K users, these COUNT queries were scanning millions of rows every page load.

What Rails Counter_Cache Actually Does

Counter cache adds an integer column to the parent model that stores the association count. Rails updates it automatically whenever you create or destroy child records.

Migration to add counter_cache:

class AddPostsCountToUsers < ActiveRecord::Migration[7.0]
  def change
    # Add the counter column
    add_column :users, :posts_count, :integer, default: 0, null: false

    # Backfill existing counts (critical for production data)
    reversible do |dir|
      dir.up do
        User.find_each do |user|
          User.update_counters(user.id, posts_count: user.posts.count)
        end
      end
    end

    # Add index for queries that filter by post count
    add_index :users, :posts_count
  end
end

Why this migration matters: The reversible block backfills existing data. Without it, all your current users show 0 posts until they create a new post. In production, that means your metrics are wrong until users take action. The backfill took 8 minutes on our 50K user table—run this during off-peak hours.

Update the Post model:

class Post < ApplicationRecord
  belongs_to :user, counter_cache: true
  # Rails now updates users.posts_count automatically
  # on Post.create and Post.destroy
end

The User model needs nothing:

class User < ApplicationRecord
  has_many :posts
  # Rails knows about posts_count because of naming convention
  # user.posts.count automatically uses the cached column
end

What changes in your queries:

Before counter_cache:

-- Every user.posts.count hits the database
SELECT COUNT(*) FROM posts WHERE user_id = 123

After counter_cache:

-- No query at all—reads from users.posts_count column
-- Already loaded with the User record

When Counter Cache Actually Helps (And When It Doesn’t)

I’ve shipped counter_cache to 6 production apps. Here’s when it’s worth the complexity.

Use counter_cache when:

• You’re displaying counts in lists or dashboards (user index with post counts, product listings with review counts)
• The parent record has hundreds or thousands of child records (users with 200+ posts, products with 500+ reviews)
• You’re filtering or sorting by count (User.where("posts_count > 10"), Product.order(reviews_count: :desc))
• The association rarely changes (blog posts, reviews, orders—created once, rarely deleted)

Skip counter_cache when:

• The association is small (user has 1-5 addresses, account has 2-3 subscriptions)
• You’re only showing counts on detail pages, not lists (single user show page with post count)
• The association changes constantly (real-time chat messages, live activity feeds)
• You need counts with conditions (user.posts.published.count doesn’t use counter_cache)

Production metrics that convinced me:

Without counter_cache (100 users on dashboard):

• 101 database queries
• 340ms average response time
• 2,400 queries/minute during business hours
• Database CPU: 60-70%

With counter_cache (same dashboard):

• 1 database query
• 45ms average response time (7.5x faster)
• 24 queries/minute (100x reduction)
• Database CPU: 15-20%

Those numbers are from our production Rails 7 app running on Heroku Postgres. 50K users, 10M posts, $200/month database plan. Counter_cache dropped our database load enough to delay a $400/month upgrade for 6 months.

The Gotchas That Bit Me in Production

Counter cache isn’t automatic magic. I learned these lessons by shipping bugs.

Gotcha 1: Conditional counts don’t use the cache

# This uses the cache—no database query
user.posts.count
# => 247 (reads from users.posts_count)

# This IGNORES the cache—hits database every time
user.posts.published.count
# => SELECT COUNT(*) FROM posts WHERE user_id = 123 AND status = 'published'

The fix: If you need conditional counts frequently, add multiple counter caches:

class Post < ApplicationRecord
  belongs_to :user, counter_cache: true
  belongs_to :user, counter_cache: :published_posts_count,
             if: -> { published? }
end

Add both posts_count and published_posts_count columns to users. Rails updates the right one based on the post’s published status. I use this pattern for Users with published/draft posts, Products with active/inactive reviews, and Accounts with paid/unpaid invoices.

Gotcha 2: Bulk operations break the cache

# This updates the counter—works correctly
user.posts.create(title: "New post")
# users.posts_count increments from 247 to 248

# This does NOT update the counter—breaks the cache
Post.where(user_id: user.id).delete_all
# users.posts_count still shows 248, but user has 0 posts

Why it breaks: delete_all, update_all, and raw SQL bypass ActiveRecord callbacks. The counter_cache relies on callbacks to stay in sync.

The fix: Use destroy_all instead of delete_all, or manually reset counters:

# Option 1: Use destroy_all (runs callbacks, slow on large sets)
user.posts.destroy_all

# Option 2: Manual reset after bulk operations
Post.where(user_id: user.id).delete_all
User.reset_counters(user.id, :posts)

I shipped this bug to production twice. First time: admin bulk-deleted spam posts, counters showed wrong numbers for 3 days. Second time: background job used update_all to archive old posts. Learned to always reset counters after bulk operations.

Gotcha 3: Polymorphic associations need special handling

class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true,
             counter_cache: true # Doesn't work as expected
end

For polymorphic associations, specify the column name explicitly:

class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true,
             counter_cache: :comments_count
end

Then add comments_count to every model that’s commentable (Post, Photo, Video). Rails handles the polymorphic routing, but you need the column on every parent table.

Monitoring Counter Cache Drift in Production

Counter caches drift. Bugs happen. Background jobs fail. You need a way to detect and fix drift before users notice.

Check for drift with a rake task:

# lib/tasks/counter_cache.rake
namespace :counters do
  desc "Check for counter cache drift"
  task check: :environment do
    User.find_each do |user|
      actual = user.posts.count
      cached = user.posts_count

      if actual != cached
        puts "User #{user.id}: cached=#{cached}, actual=#{actual}, diff=#{actual - cached}"
      end
    end
  end

  desc "Reset all counter caches"
  task reset: :environment do
    User.find_each do |user|
      User.reset_counters(user.id, :posts)
    end
    puts "Reset complete"
  end
end

Production monitoring: I run this check weekly via a background job. If drift exceeds 5% of users, I get a Slack alert. The reset task runs monthly during maintenance windows.

On our app, drift affects 0.1-0.3% of users between resets—usually from failed background jobs or database timeouts during bulk operations. The weekly check catches it before metrics look weird to customers.

Performance note: The check task is expensive—it runs COUNT queries for every user. On 50K users, it takes 15 minutes. Run it off-peak or in batches. The reset task is even slower (25 minutes on our database) because it updates every row.

Counter Cache vs. Alternatives (What I Use When)

Counter cache isn’t always the right solution. Here’s how I decide.

Counter cache (best for dashboards and sorting):

• ✅ Pros: Instant reads (no query), supports filtering and sorting by count, handles millions of child records
• ❌ Cons: Extra column per association, can drift out of sync, doesn’t work for conditional counts, adds complexity to bulk operations

includes(:posts).count (best for N+1 fixes with conditions):

• ✅ Pros: Works with scopes and conditions, no schema changes, always accurate
• ❌ Cons: Still queries database (just avoids N+1), slower than counter_cache, can’t sort by count efficiently

Direct queries when needed (best for reports and one-off counts):

• ✅ Pros: No setup required, supports complex conditions, always accurate
• ❌ Cons: Expensive for iteration, can cause N+1 problems, slow on large tables

Real scenario: Admin dashboard showing users

I tried all three approaches:

# Approach 1: Counter cache (what we shipped)
User.order(posts_count: :desc).limit(100)
# 1 query, 45ms, clean code

# Approach 2: includes with count (tried first, too slow)
User.includes(:posts).limit(100)
# 2 queries, 180ms, can't sort by count in SQL

# Approach 3: Subquery (complex, but accurate)
User.select("users.*, (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id) as posts_count")
    .order("posts_count DESC")
    .limit(100)
# 1 query, 220ms, complex SQL, but no schema changes

For our use case, counter_cache won: 3x faster than includes, simpler SQL than subqueries, and supports sorting by count. The trade-off? We maintain the counter column and reset it monthly. Worth it.

Final Thoughts

Rails counter_cache performance fixes N+1 counting problems by storing counts in the parent table. It’s perfect for dashboards, list views, and sorting by association counts. Use it when you’re displaying counts for hundreds of users, products, or any parent record with large child associations.

Skip it for small associations (1-5 records), conditional counts (published posts only), or rapidly changing data (chat messages). The cache adds schema complexity and can drift, but for common use cases like “users with post counts” or “products sorted by review count,” it’s a 7-10x performance improvement.

Start by profiling with rack-mini-profiler or the Bullet gem. If you see N+1 count queries in loops, counter_cache is probably the fix. Add the column, backfill the data, and watch your database CPU drop.

What’s the worst N+1 counting problem you’ve shipped? Drop a comment—I’ve probably made the same mistake. To understand rails fundamentals, see Rails 8.2 Callbacks and Background Jobs in Production.