In Rails, what is Active Record's SQL query cache, when is it switched on, and why is it no substitute for Rails.cache?
answer
- CACHE lines in the log
- same SQL and binds in one execution
- any write clears it
- requests and jobs via the executor
- uncached for fresh reads
basics
~20 sActive Record's query cache remembers result sets of identical SELECTs for one request or job, clears them on any write, and discards them at the end. Rails.cache persists values across requests and processes, so the two solve different problems.
solid answer
~50 sThe query cache stores the result of each `SELECT`, keyed by its SQL and bind values, so running the same query again in the same request or job returns the stored rows (the log shows `CACHE Product Load`) while still building fresh model objects. Rails turns it on for every request and every Active Job execution through the executor, and wraps up by clearing it, so nothing survives between requests. Any write through the connection, such as an `INSERT`, `UPDATE` or `DELETE`, clears it for the current thread, `SELECT ... FOR UPDATE` is never cached, and each cache holds up to 100 queries by default, configurable with `query_cache:` in `database.yml`. It saves repeated identical queries within one unit of work; it does nothing for the next request or another process, which is `Rails.cache`'s job. Use `uncached { }` when a loop must see new rows.
code
yaml · 6 lines# config/database.yml
production:
primary:
<<: *default
database: travel_production
query_cache: 200 # Integer: per-cache size; false disables itgo deeper
Recall that Active Record reuses results of identical queries within one request, shown as CACHE in the log.
Explain when the executor turns it on, what clears it, the 100-query default and the query_cache key, and how it differs from Rails.cache.
Show the traps from real systems: polling jobs that read stale results, randomized queries, and why it never fixes N+1.
Judge whether repeated identical queries signal a code structure worth fixing rather than relying on a per-request cache.
## What it is The **SQL query cache** is an Active Record feature that remembers the result of each `SELECT` run through a connection, keyed by the SQL text and its bind values. If the same query runs again in the same unit of work, Active Record returns the remembered rows instead of asking the database. In the development log it shows up as a line prefixed `CACHE`, for example `CACHE Booking Load (0.0ms)`. It does **not** cache model objects: each hit still instantiates new records from the stored rows. And it does **not** persist anything beyond the current unit of work. ## When it is on Rails enables it automatically through the **executor**, the wrapper Rails puts around each unit of application work: - every **web request**; - every **Active Job** execution, since jobs run inside the Rails reloader and executor; - code you wrap yourself in `Rails.application.executor.wrap { ... }`, for example in a custom thread. When the unit of work completes, the executor disables and clears the cache. Outside those contexts, `ActiveRecord::Base.cache { ... }` turns it on for a block. ## What a hit looks like inside Active Record 1. At the start of a request or job, the executor's hook enables the query cache on each connection pool, skipping databases configured with `query_cache: false`. 2. When a relation loads, `select_all` turns it into SQL plus bind values and uses that pair as the lookup key. 3. On a hit, the stored result rows are returned and the log line is prefixed `CACHE`; on a miss, the query runs and its rows are stored. 4. The rows are turned into new model instances either way. 5. When the unit of work completes, the hook disables and clears the cache. The cache is kept per thread or fiber, so two requests served concurrently by one process never see each other's cached rows. ## What clears or bypasses it | Event | Effect | |---|---| | Any write through the connection (`INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, raw `execute`) | clears the query caches for the current thread | | `record.reload` | clears the cache before re-reading | | `ActiveRecord::Base.uncached { ... }` | runs the block with the cache disabled | | `SELECT ... FOR UPDATE` from `lock` | never cached | | Cache full | least recently used query is evicted | The size limit is **100** queries per cache by default. In `config/database.yml`, `query_cache: 200` raises it and `query_cache: false` turns the feature off for that database. ## A trap it creates Because identical queries return identical rows within one unit of work, a job that **polls** the database gets the same answer every time: ```ruby class AwaitPaymentJob < ApplicationJob def perform(booking_id) 10.times do status = Booking.where(id: booking_id).pick(:payment_status) # cached after the first run break if status == "paid" sleep 2 end end end ``` The first `pick` hits the database; the next nine return the cached value, because nothing in this job writes. The fix is to bypass the cache for the read: ```ruby status = Booking.uncached { Booking.where(id: booking_id).pick(:payment_status) } ``` The same applies to queries whose result must differ each time, such as `ORDER BY RANDOM()`. ## Why it is no substitute for `Rails.cache` | | Query cache | `Rails.cache` | |---|---|---| | Lifetime | one request or job | until expiry or eviction | | Shared across processes | no | yes, with a shared store | | What is stored | result rows of a `SELECT` | any serializable value you choose | | Invalidation | automatic, on any write | yours: keys, versions, expiry | | Effort | none | explicit `fetch` calls | In the travel-booking app, the query cache saves the second identical lookup of the same booking within one request. It does nothing for the exchange rate the next request needs, which comes from an external service anyway, or for an expensive report every user asks for. Those need `Rails.cache.fetch` with a key and an expiry. ## How to use this in an interview - Say what it saves: repeated identical queries in one unit of work, typically from helpers and partials asking for the same thing. - Say what it does not: N+1 queries, which are *different* queries with different binds; those need eager loading, not the query cache. - Name the escape hatch, `uncached`, and the reason for it: polling and randomness.
- Does the query cache fix N+1 queries?No. An N+1 issues one query per parent with a different id each time, so every SQL string and bind set differs and nothing is reused. Eager loading with `includes` or `preload` removes those queries; the query cache only helps when the exact same query repeats.
- Why does a write to one table clear cached results for other tables?The query cache does not track which tables a query read. Any write through the connection simply clears the cached results for the current thread, which is coarse but cannot serve rows a write might have changed.
- The query cache shares results between requests until a row changes.
- Query cache hits return the same Ruby objects as the first query.
- The query cache solves N+1 problems automatically.
- Background jobs never get the query cache.
- A write clears only the cached queries for the table it touched.