A nightly reporting job scans a table far larger than the server's memory, and for a while afterwards the normal transactional workload runs much slower. Explain the effect this has on the engine's page cache and what mechanisms engines use to prevent it.
answer
- scan = millions of one-shot pages, all 'recently used'
- working set evicted -> misses on the boring queries
- recovery is gradual, that tail is the tell
- ring buffer: give the scan a few MB, not the pool
- real fix: report off a replica
basics
~20 sThe scan reads pages used once and lets them displace the hot working set - cache pollution. The OLTP workload then misses on pages it used to hit until the pool refills. Engines defend with midpoint insertion, small ring buffers for bulk reads, and marking scan pages for immediate reuse.
solid answer
~60 sThe scan touches millions of pages exactly once. With a naive policy each of those pages enters the pool ranked as recently used, so it evicts pages the transactional workload relies on: index roots, small lookup tables, hot leaf pages. When the job ends the pool is full of data nobody will read again, and the OLTP workload has to fault its working set back in one page at a time. Latency stays bad for as long as re-warming takes - the classic **cache pollution** signature is a slow recovery, not an instant one. Defences: - **Midpoint / segmented insertion**: a first touch only buys residency in the cold half; promotion needs a later re-access, so scan pages never reach the hot end. - **Ring buffers**: bulk sequential reads, bulk writes, and vacuum-style maintenance are confined to a small fixed set of frames that they recycle among themselves. - **Access hints**: pages read by a scan are marked so they drop to zero usage credit immediately. Operationally you can also separate the reporting workload onto a read replica.
go deeper
Know the term: a big scan can push useful pages out of the cache and make later queries slower.
Explain the mechanism through the eviction policy and name midpoint insertion or ring buffers as the defence.
Diagnose it from metrics - the lingering tail after the job ends - and give both the engine-level mitigations and the workload-separation answer.
Treat it as a workload-isolation problem: which workloads share a cache at all, replicas versus resource pools, cost of re-warming, and how it constrains the batch schedule.
## The symptom A batch job finishes at 03:00. From 03:00 to 03:20 the application's p99 latency is several times normal, then it decays back. Nothing is locked, CPU is not saturated, and the slow queries are ordinary point lookups that are normally instant. Physical read counts are elevated and the buffer cache hit ratio has dipped. This is the fingerprint of **cache pollution**: the working set has been evicted and is being paged back in. ## Why a big scan is uniquely destructive Eviction policies rank pages by evidence of use, and a sequential scan generates an enormous amount of misleading evidence. Consider a 500 GB table on a server with a 64 GB pool: - The scan reads roughly 60 million 8 KB pages. - Each one is genuinely accessed, so a recency-based policy places it among the most-recently-used pages. - None of them will be read again. Within minutes every frame holds a page whose future value is zero, and the pages that used to be there - the top levels of hot B-trees, the small dimension tables joined on every request, the frequently updated account rows - are gone. Each subsequent OLTP query now performs physical I/O for things it used to get for free. Because the working set is refilled only by demand, one page per miss, recovery is gradual: this is why the slowdown *lingers* rather than ending with the batch job. A second-order effect: while the scan runs, the eviction machinery itself is busy. Victim selection runs on every page read, dirty victims must be written out, and the write-back path competes for the same disks the scan is reading from. ## Engine-level defences **Midpoint / segmented insertion.** Rather than trusting a first touch, newly read pages are admitted to the cold portion of the replacement structure and only promoted to the protected portion if they are referenced again after some delay. A scan page is read once, never re-referenced later, and is reclaimed from the cold portion - so it cycles through a bounded slice of the pool without ever displacing the hot portion. The delay requirement matters because a scan touches the same page many times in quick succession while reading its rows; only a *later* re-reference is evidence of real reuse. **Ring buffers (bulk-access strategies).** The stronger defence is to refuse the scan most of the pool at all. When the planner knows an operation is a large sequential read, a bulk write (a big COPY/INSERT ... SELECT), or a maintenance sweep, the engine allocates a small fixed set of frames - typically a few hundred kilobytes to a few megabytes - and the operation recycles that ring for the whole scan. The scan still gets full read-ahead throughput because it is sequential, and the rest of the pool is untouched. This is why an engine with such a strategy can scan a table ten times the size of RAM with almost no effect on concurrent OLTP. **Usage hints / immediate reuse.** Pages faulted in by a scan can be marked so that their usage credit is zero from the start, making them the next victims. Some systems additionally let synchronized scans share pages: a second scan of the same table joins an in-progress one mid-stream instead of starting from block zero and reading everything twice. **Prefetch / read-ahead sizing.** Aggressive read-ahead helps throughput but also increases how many one-shot pages are resident at once. Where read-ahead is tunable, it interacts directly with pollution. ## Architectural and operational defences Engine mechanisms reduce the damage; workload separation eliminates it. - **Run reporting on a replica.** The classic answer: analytical scans against a physical read replica leave the primary's cache intact. This is usually the right production answer and interviewers expect it alongside the mechanism. - **Resource isolation.** Some engines allow multiple named buffer pools or per-object cache assignment, so a batch table can be pinned to its own small pool. - **Shape the query.** If the report only needs recent rows, partition pruning or a covering index turns a 500 GB scan into a bounded range scan; a materialized aggregate turns nightly recomputation into an incremental update. - **Schedule and rate-limit** so that even an unavoidable scan happens in the deepest trough, and consider deliberately re-warming critical objects afterwards. ## How to confirm the diagnosis Correlate three signals in time: the batch job's window, a rise in physical reads / I/O wait for *unrelated* queries, and a drop in hit ratio that recovers gradually. If the slowdown ends the instant the job ends, the cause was contention for I/O or locks, not pollution - pollution has a tail.
- How would you distinguish cache pollution from plain I/O contention caused by the same batch job?Look at the shape in time. I/O contention starts and stops with the job, so latency returns to baseline the moment it finishes. Pollution leaves a tail: physical reads for unrelated queries stay elevated after the job ends and decay as the working set is faulted back in. Correlating hit ratio, physical read counts, and the job window over a window that extends past the job end separates the two.
- A ring buffer confines a sequential scan to a few megabytes of frames. Why doesn't that cripple the scan's own performance?Sequential scans get their speed from read-ahead and large sequential I/O, not from cache residency, and each page is consumed once and never needed again. A small recycled window is enough to keep the prefetcher ahead of the executor. The pages would have delivered no future hits anyway, so the only thing given up is cache space the scan never benefited from.
saying these in an interview costs you the question
- Blaming locking or CPU when the signature is misses on unrelated queries after the job ends
- Proposing to simply enlarge the buffer pool when the scanned table is far larger than RAM
- Claiming a scan is harmless because pages are read only once - that is precisely why it is harmful under naive policies
- Suggesting 'pin the hot tables in cache' as though every engine offers that
- Assuming the OS page cache will protect the working set