ActiveRecord QueryCache

October 16, 2022

ActiveRecord provides a QueryCache that will cache the results of SQL queries to make repeated queries faster.

For the Rails server, it's on by default.
For the Rails console, it's off by default.

To see if the QueryCache is on you can run:

ActiveRecord::Base.connection.query_cache_enabled
 => false

To test the QueryCache in the Rails console, you can turn it on with:

ActiveRecord::Base.connection.enable_query_cache!

If you make the same SELECT query twice, the second query will be logged with a CACHE message. Rather than going to the database, Rails will return the cached values.

Product.find(1)
  Product Load (0.3ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]

Product.find(1)
  CACHE Product Load (0.0ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]

The cached results are stored in a hash:

ActiveRecord::Base.connection.query_cache
 =>
{"SELECT \"products\".* FROM \"products\" WHERE \"products\".\"id\" = $1 LIMIT $2"=>
  {[#<ActiveRecord::Relation::QueryAttribute:0x00007fa2ec509088
     @name="id",
     @original_attribute=nil,
     @type=#<ActiveModel::Type::Integer:0x00007fa2f705e578 @limit=8, @precision=nil, @range=-9223372036854775808...9223372036854775808, @scale=nil>,
     @value=1,
     @value_before_type_cast=1,
     @value_for_database=1>,
    #<ActiveModel::Attribute::WithCastValue:0x00007fa2ec50b568
     @name="LIMIT",
     @original_attribute=nil,
     @type=#<ActiveModel::Type::Value:0x00007fa2f7064950 @limit=nil, @precision=nil, @scale=nil>,
     @value=1,
     @value_before_type_cast=1>]=>
    #<ActiveRecord::Result:0x00007fa2ec5021e8
     @column_types={},
     @columns=["id", "name", "description", "created_at", "updated_at"],
     @hash_rows=nil,
     @rows=[[1, "Product A", "Long product description text here...", 2022-10-13 16:59:07.676262 UTC, 2022-10-13 16:59:07.676262 UTC]]>}}

The QueryCache doesn't cache the exact text of the SQL query. It caches the pattern of the SQL query. So if we run these 2 queries...

Product.find(1)
Product.find(2)

...the cache would have one cache key, but it is smart enough to return different results based on the id that was passed in.

ActiveRecord::Base.connection.query_cache.keys
 => ["SELECT \"products\".* FROM \"products\" WHERE \"products\".\"id\" = $1 LIMIT $2"]

The cache is cleared whenever an update action occurs. For example:

Product.find(1).touch
  Product Update (0.6ms)  UPDATE "products" SET "updated_at" = $1 WHERE ...

ActiveRecord::Base.connection.query_cache
 => {}

Note that raw queries that are performed with ActiveRecord::Base.connection.execute will neither be cached nor will they clear the cache if they make updates.

A new QueryCache is used for each controller action or background job. Usually, because the cache is short-lived it doesn't cause problems. But it can eat up a lot of memory if you have functionality that performs a huge number of reads, such as a large file export.

To avoid memory bloat in large read-heavy processes, you can either temporarily disable the cache:

ActiveRecord::Base.uncached do
  # Do a large amount of querying here which would
  # have made the cache excessively large
end

Or you can clear it regularly:

collection.each do |value|
  # Do some small amount of work here where caching will be used

  # Then clear the cache before the next iteration
  ActiveRecord::Base.connection.clear_query_cache
end

References