Inside ActiveRecord's Query Cache: When It Helps, When It Hurts

A few months ago, I was debugging a memory issue in a Sidekiq worker that processed CSV exports. The job was straightforward: iterate through a few hundred thousand records, transform them, and write to a file. Memory usage should have been stable, we were using find_each with sensible batch sizes. Instead, the worker's memory climbed steadily until it was killed by the OOM reaper.

The culprit? ActiveRecord's query cache.

Inside each batch iteration, we were loading associated records. Nothing unusual. But because the query cache was enabled (as it is by default for background jobs since Rails 5.0), every single query result was being stored in memory for the entire duration of the job. The cache that's designed to speed up web requests was silently hoarding gigabytes of data.

This is the kind of bug that doesn't show up in development. It doesn't fail your tests. It waits until production, until that export job runs against real data, and then it strikes.

The query cache is one of those Rails features that "just works", until it doesn't. Understanding how it actually operates, when it helps, and when it actively hurts your application is essential knowledge for any senior Rails engineer.

What Is the Query Cache? (And What It Isn't)

ActiveRecord's query cache is an in-memory store that caches the results of SELECT queries within a single request or job. When you execute the same query twice, the second execution returns cached results instead of hitting the database.

You've probably seen this in your logs:

User Load (2.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]
User Load (0.0ms)  CACHE SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]

That CACHE prefix and 0.0ms timing indicate a cache hit. Rails returned the stored result without touching the database.

What It Is

A per-request (or per-job) in-memory hash
Keyed by the SQL query pattern and bind parameters
Automatically cleared when any write operation occurs (INSERT, UPDATE, DELETE)
Automatically cleared at the end of each request or job

What It Isn't

Not a cross-request cache — It doesn't persist between HTTP requests. Each request starts fresh.
Not Redis or Memcached — It's purely in-process memory, not shared between workers.
Not the same as Rails.cache — That's application-level caching you control explicitly.
Not memoization — Instance variables (@users ||= User.all) cache the Ruby objects. The query cache stores raw SQL results.

This distinction matters. I've seen developers assume the query cache works across requests and wonder why their "cached" queries still hit the database on every page load. Different tools, different purposes.

How It Works Under the Hood

The query cache lives in the database connection adapter. You can inspect it directly:

ActiveRecord::Base.connection.query_cache

This returns a hash structure where:

Keys are SQL query patterns (parameterised queries)
Values are themselves hashes, mapping bind parameter combinations to ActiveRecord::Result objects

Here's a simplified view of what that looks like:

{
  "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" = $1 LIMIT $2" => {
    [1, 1] => #<ActiveRecord::Result ...>,
    [2, 1] => #<ActiveRecord::Result ...>,
    [3, 1] => #<ActiveRecord::Result ...>
  }
}

Notice that the cache is smart about parameterised queries. User.find(1), User.find(2), and User.find(3) share the same SQL pattern but have different bind parameters. Each combination gets its own cached result under the same key.

What Gets Cached

Standard ActiveRecord queries (find, where, joins, etc.)
Queries that return ActiveRecord::Result objects

What Doesn't Get Cached

Raw SQL via execute - ActiveRecord::Base.connection.execute("SELECT ...") bypasses the cache entirely. It won't read from the cache, and it won't populate it. However, Rails assumes execute has side effects and will clear the cache when you call it.
Some pluck and select_all calls - Behaviour has varied across Rails versions.

The execute behaviour is worth understanding: while it clears the cache (assuming writes), it doesn't participate in caching for reads. If you're using execute for read-only queries and want to preserve the existing cache, consider using select_all instead.

The Hidden Cost of Cache Hits

Here's something most developers miss: cache hits aren't free.

When Rails retrieves a result from the query cache, it still has to:

Instantiate new ActiveRecord objects from the cached SQL result
Run type casting on all attributes
Execute after_initialize and after_find callbacks

The cache stores the raw database result, not the Ruby objects. Every cache hit still incurs the cost of object instantiation.

Gavin Morrice documented this in a detailed benchmark, showing that an endpoint relying on query cache hits took 2.5x longer than one using proper memoization. The database round-trip was eliminated, but object instantiation overhead remained.

This is why instance-level memoization (@users ||= User.where(active: true).to_a) can outperform the query cache for repeated access within the same code path. You're caching the actual Ruby objects, not just the SQL results.

When the Cache Is Cleared

The cache is automatically cleared when:

Any write operation occurs: INSERT, UPDATE, DELETE, or any query that modifies data
The request or job completes: The cache is wiped at the end of each controller action or background job
You explicitly clear it: Via ActiveRecord::Base.connection.clear_query_cache

Write operations clear the cache across all connections by default. This is intentional, if you have read replicas, a write to the primary should invalidate cached reads from replicas that might now be stale.

The Lifecycle: Web Requests vs Background Jobs

Web Requests

For web requests, the query cache is managed by ActiveRecord::QueryCache middleware. The lifecycle is:

Request begins → Cache is enabled
Queries are cached as they execute
Write operations clear the cache
Request ends → Cache is disabled and cleared

This is generally safe. Web requests are short-lived, and the cache provides genuine benefit for pages that query the same data multiple times (think: rendering a user's name in the header, sidebar, and footer).

Background Jobs

Here's where it gets interesting. Since Rails 5.0, the query cache is enabled by default for background jobs, including Sidekiq workers.

The same lifecycle applies:

Job begins → Cache is enabled
Queries are cached as they execute
Write operations clear the cache
Job ends → Cache is disabled and cleared

For short jobs, this works fine. For long-running jobs that process large datasets, this is a trap.

Consider a job that processes 100,000 users:

class ProcessUsersJob < ApplicationJob
  def perform
    User.find_each do |user|
      process_user(user)
    end
  end

  def process_user(user)
    # Load associations
    user.account
    user.preferences
    user.recent_orders.limit(5)
    # ... processing logic
  end
end

Every unique query inside that loop gets cached. If each user has different associated records, you're potentially caching hundreds of thousands of query results.

Here's the key insight: the cache only clears when the job finishes. A job processing 200,000 records might run for 30 minutes, and the cache accumulates every unique query result during that entire duration. By iteration 100,000, you could have 300,000+ cached query results sitting in memory, all waiting for a job completion that's still 15 minutes away.

The cache grows unbounded (in Rails < 7.1) until the job completes or memory runs out, whichever comes first.

Evolution: Rails 7.1's LRU and Rails 8.x Fixes

The Unbounded Cache Problem (Pre-7.1)

Before Rails 7.1, the query cache had no size limit. It would happily store every query result until:

A write operation cleared it
The request/job ended
Your process ran out of memory

This was a known issue. The Rails team discussed various solutions over the years, but concerns about cache eviction affecting performance for "normal" use cases delayed action.

Rails 7.1: The 100-Entry LRU Limit

Rails 7.1 introduced a significant change: the query cache now uses an LRU (Least Recently Used) eviction strategy with a default limit of 100 entries.

When the cache exceeds 100 entries, the least recently accessed entries are evicted to make room for new ones.

This doesn't eliminate memory issues entirely, 100 large result sets can still consume significant memory, but it prevents unbounded growth. A job processing millions of records will now have a roughly constant cache memory footprint instead of linear growth.

Rails 8.0: The `query_cache: false` Fix

Rails 8.0.0 fixed a bug where setting query_cache: false in database.yml didn't fully disable the cache. If you'd tried this configuration in earlier versions and found it didn't work as expected, upgrade to Rails 8.0+.

# config/database.yml
production:
  adapter: postgresql
  query_cache: false  # Now actually works in Rails 8.0+

When the Query Cache Helps

The query cache genuinely improves performance in several scenarios:

1. Repeated Queries in Views

Consider a view that renders a collection of posts, each showing the author's name:

<% @posts.each do |post| %>
  <article>
    <h2><%= post.title %></h2>
    <p>By <%= post.author.name %></p>  <!-- N+1 query here -->
  </article>
<% end %>

If multiple posts share the same author, the query cache prevents redundant database hits. The first post.author for author ID 5 hits the database; subsequent posts by author 5 return cached results.

This isn't a substitute for proper eager loading (@posts = Post.includes(:author)), but it provides a safety net when associations slip through.

2. Shared Data Across Partials

When multiple partials or helpers query the same data:

# _header.html.erb
<%= current_user.name %>

# _sidebar.html.erb  
<%= current_user.account.plan_name %>

# _footer.html.erb
<%= current_user.email %>

If these partials independently call methods that trigger queries on the same records, the cache prevents duplicate database work.

3. Read-Heavy Controller Actions

Actions that perform multiple read operations on the same data benefit from the cache:

def show
  @user = User.find(params[:id])
  @recent_activity = @user.activities.recent
  @stats = calculate_stats(@user)  # Might query @user again internally
  @recommendations = recommend_for(@user)  # Might also query @user
end

When the Query Cache Hurts

1. Batch Processing and Large Exports

This is the classic footgun. Any job that iterates through large datasets can accumulate massive cache entries:

class ExportJob < ApplicationJob
  def perform
    CSV.open("export.csv", "w") do |csv|
      Order.includes(:line_items, :customer).find_each do |order|
        csv << [order.id, order.customer.name, order.total]
      end
    end
  end
end

Even with find_each batching the main query, associations loaded inside the block are cached.

2. The `find_each` Nuance

Since Rails 6.0.2.1, find_each, find_in_batches, and in_batches skip the query cache for the batch query itself. This prevents caching the large result sets from each batch.

However, queries inside the block still use the cache:

User.find_each do |user|
  user.account  # This gets cached
  user.orders   # This gets cached
end

So while the batch iteration is safe, your per-record queries can still accumulate.

3. Memory Bloat in Background Jobs

A job that runs for minutes or hours, processing diverse data, can accumulate significant cache entries. Even with Rails 7.1's LRU limit, 100 entries containing thousands of rows each can consume substantial memory.

4. Stale Reads in Concurrent Scenarios

If external processes modify the database while your code is running, the cache won't reflect those changes:

user = User.find(1)
# External process updates user's email in the database
user = User.find(1)  # Returns cached result with old email

In web requests, this is rarely a problem, they're short lived. In long running jobs, it can cause subtle bugs where your code operates on stale data.

5. Randomised Queries

If you're using ORDER BY RANDOM() or similar, the cache will undermine your randomisation:

User.order("RANDOM()").limit(5)  # First call: returns [A, C, E, B, D]
User.order("RANDOM()").limit(5)  # Second call: returns [A, C, E, B, D] (cached!)

Rails does not automatically exclude these queries from the cache. The Rails documentation explicitly notes this as a reason you might want to clear the cache manually. Wrap random queries in uncached to ensure fresh results each time.

Debugging and Inspecting the Cache

Reading the Cache Directly

You can inspect the current cache state in a console or with debugging code:

# Check if caching is enabled
ActiveRecord::Base.connection.query_cache_enabled
# => true

# View the cache contents
ActiveRecord::Base.connection.query_cache
# => { "SELECT ..." => { [...] => #<ActiveRecord::Result ...> } }

# Count cached entries
ActiveRecord::Base.connection.query_cache.values.sum { |v| v.size }
# => 47

Understanding the Logs

When you see this in your logs:

User Load (0.0ms)  CACHE SELECT "users".* FROM "users" WHERE ...

The CACHE prefix indicates a cache hit. The 0.0ms timing reflects that no database round trip occurred (though object instantiation time isn't shown here).

Custom Instrumentation

You can subscribe to ActiveRecord's notifications to track cache behaviour:

ActiveSupport::Notifications.subscribe("sql.active_record") do |name, start, finish, id, payload|
  if payload[:cached]
    Rails.logger.debug "CACHE HIT: #{payload[:sql]}"
  end
end

For production observability, consider tracking:

Cache hit rate per request/job
Total cache size at request end
Memory delta during long-running jobs

Controlling the Cache

Disabling for a Block

The most common control mechanism is uncached:

ActiveRecord::Base.uncached do
  # All queries in this block bypass the cache
  User.find_each do |user|
    process(user)
  end
end

The `dirties` Option

When you use uncached, write operations inside the block still clear caches on all connections by default. If you want to prevent this (rare, but useful for read-only operations on replicas):

ActiveRecord::Base.uncached(dirties: false) do
  # Queries here won't dirty other connections' caches
end

Clearing the Cache Manually

You can clear the cache at any point:

ActiveRecord::Base.connection.clear_query_cache

This is useful for batch processing where you want caching within each batch but not across batches:

User.find_in_batches do |batch|
  batch.each { |user| process(user) }
  ActiveRecord::Base.connection.clear_query_cache
end

Disabling Globally in database.yml

For environments where you never want the query cache:

# config/database.yml
production:
  adapter: postgresql
  query_cache: false

Note: This had bugs before Rails 8.0. Verify it works as expected in your version.

Sidekiq Middleware Pattern

To disable the query cache for all Sidekiq jobs:

# app/middleware/sidekiq_disable_query_cache.rb
class SidekiqDisableQueryCache
  def call(worker, job, queue)
    ActiveRecord::Base.uncached do
      yield
    end
  end
end

# config/initializers/sidekiq.rb
Sidekiq.configure_server do |config|
  config.server_middleware do |chain|
    chain.add SidekiqDisableQueryCache
  end
end

Alternatively, disable it selectively for heavy jobs:

class HeavyExportJob < ApplicationJob
  def perform
    ActiveRecord::Base.uncached do
      # ... export logic
    end
  end
end

Measuring Cache Effectiveness

Before disabling the cache everywhere, measure whether it's actually helping or hurting.

Basic Instrumentation

# config/initializers/query_cache_instrumentation.rb
if Rails.env.production?
  ActiveSupport::Notifications.subscribe("sql.active_record") do |*, payload|
    if payload[:cached]
      StatsD.increment("activerecord.query_cache.hit")
    else
      StatsD.increment("activerecord.query_cache.miss")
    end
  end
end

Questions to Answer

What's your hit rate? A low hit rate (<10%) suggests the cache isn't providing much value.
How large does the cache grow? Monitor memory usage correlation with cache size.
Are hits actually faster? Compare response times with cache enabled vs disabled for representative requests.

When Cache Overhead Exceeds Benefit

The cache has overhead:

Hash lookups and insertions
Memory allocation
Object instantiation from cached results

For jobs that rarely hit the same query twice, this overhead provides no benefit. Disabling the cache can actually improve performance.

Practical Recommendations

For Web Requests

Generally, leave it enabled. The query cache works well for typical web request patterns. The per-request lifecycle prevents unbounded growth, and the LRU limit in Rails 7.1+ provides additional protection.

Watch out for:

Requests that process large datasets (paginate aggressively)
Long-polling or streaming responses
Requests with many unique queries (consider if your data model has issues)

For Background Jobs

Default to disabled for batch-processing jobs. If a job iterates through large datasets, wrap it in uncached or use the Sidekiq middleware pattern.

Keep enabled for short jobs. Jobs that perform a few queries and complete quickly benefit from the cache just like web requests.

For Batch Processing

Use this pattern:

def perform
  Model.find_in_batches(batch_size: 1000) do |batch|
    ActiveRecord::Base.uncached do
      batch.each { |record| process(record) }
    end
    # Or: process with cache, then clear
    # batch.each { |record| process(record) }
    # ActiveRecord::Base.connection.clear_query_cache
  end
end

Checklist for New Projects

Understand that the query cache is enabled by default
Add query cache instrumentation to your observability stack
Review background jobs for batch processing patterns
Consider a Sidekiq middleware to disable cache for all jobs (opt-in to caching for specific jobs if needed)
Upgrade to Rails 7.1+ for LRU protection, 8.0+ for query_cache: false fix

Wrapping Up

ActiveRecord's query cache is a well-intentioned optimisation that works beautifully for its designed use case: short-lived web requests with repeated queries. But it operates silently, and its behaviour in background jobs can cause significant problems.

The key insights:

It's per-request/per-job, not global — Don't confuse it with Redis or Rails.cache
Cache hits still instantiate objects — It's faster than a database hit, but not free
Background jobs are the danger zone — Long-running jobs with diverse queries accumulate memory
Rails 7.1's LRU limit helps, but isn't a complete solution — 100 large result sets can still consume significant memory
Measure before you optimise — Instrument your cache hit rate before disabling it everywhere

Understanding these internals transforms the query cache from a mysterious source of bugs into a tool you can wield deliberately.

Navigation