CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Define the buffer pool (15 minutes)

Explain, in one sentence, what the buffer pool is.

Done when: the explanation correctly describes an in-memory cache of recently read disk pages.

Hint

The buffer pool is an area of memory caching recently read disk pages for faster repeated access.

Easy 2: Define a dirty page (15 minutes)

Explain, in one sentence, what makes a page dirty.

Done when: modified in memory but not yet written to disk is correctly stated.

Hint

A page is dirty if it has been modified in memory but not yet written back to disk.

Easy 3: Predict the second read (15 minutes)

State whether reading the same row twice in quick succession is typically faster the second time, and why.

Done when: yes is correctly stated, with the buffer pool serving the second read from memory as the reason.

Hint

Yes, since the second read can typically be served from the buffer pool instead of disk.

Medium

Medium 1: Dirty page before eviction (25 minutes)

Explain what must happen before a dirty page can be safely evicted from the buffer pool.

Done when: writing the page back to disk first is correctly described.

Hint

The dirty page must be written back to disk before it is removed from memory.

Medium 2: Predict eviction choice (25 minutes)

Given a full buffer pool and a least-recently-used eviction policy, predict which of two pages, one accessed 10 seconds ago and one accessed 10 minutes ago, gets evicted first.

Done when: the page accessed 10 minutes ago is correctly chosen.

Hint

The page accessed 10 minutes ago is evicted first, since least-recently-used eviction removes the page least recently touched.

Medium 3: Why not rely on the OS cache alone (25 minutes)

Explain one reason a database manages its own buffer pool, rather than relying entirely on the operating system's own file cache.

Done when: a reasonable reason is given, such as the database understanding its own access patterns (which pages matter most, which are about to be needed) better than a general-purpose OS cache can.

Hint

The database knows which pages belong to which table, index, or query pattern, letting it make smarter caching decisions than a general-purpose OS file cache that has no such context.

Hard

Hard 1: A clean page evicted instantly (30 minutes)

Explain why a clean (non-dirty) page can be evicted from the buffer pool immediately, with no disk write needed first.

Done when: the explanation correctly notes a clean page's in-memory copy already exactly matches what is on disk, so discarding it loses nothing.

Hint

A clean page's in-memory content is identical to what is already stored on disk, so there is nothing to lose by simply discarding it.

Hard 2: Why a crash before a dirty page is written matters (35 minutes)

Explain what happens to a change recorded only in a dirty page, still unwritten to disk, if the system crashes at that exact moment, and connect this to why Level 8's write-ahead log exists.

Done when: the explanation correctly notes the change could be lost from the dirty page alone, and correctly points to the write-ahead log as the mechanism that protects against exactly this loss.

Hint

An unwritten dirty page's change exists only in memory, and a crash before it reaches disk loses that change; the write-ahead log, covered in Level 8, records changes durably before they are even applied to the page in memory, protecting against exactly this scenario.

Hard 3: Diagnosing a sudden slowdown after a workload change (40 minutes)

A query that was consistently fast suddenly becomes slow after the application starts scanning a much larger, previously-unused table alongside it. Propose a buffer-pool-based hypothesis for the slowdown.

Done when: a hypothesis is proposed around the large table's pages evicting the previously cached, frequently used pages out of the buffer pool, forcing the once-fast query back to disk.

Hint

The newly scanned large table's pages likely filled the buffer pool and evicted the smaller table's previously cached pages, forcing the once-fast query to read from disk again on every access.