Which tables would you put in a shared identifier-keyed cache, which never, and how would you size it?

This is the question the whole mechanism exists for, and interviewers ask it because the answer is arithmetic first and a list of table names second. ## The arithmetic An entry earns its place when the row is **read by identifier many times between the writes that void it** and the set of live entries fits the memory you are willing to spend. Three numbers decide it: - **reads by key per row per unit of time** — only reads by identifier can ever hit, so predicate traffic does not count - **writes to that row per unit of time** — each one voids the entry and forces the next read to miss - **footprint per entry multiplied by live entries** — the memory the cache occupies and the pressure it puts on everything else in the process Hundreds of reads by key per write makes a strong candidate. Around parity it is a liability. In between, measure rather than argue. ## Strong candidates - small **reference and lookup tables** read on nearly every request and edited by a human a few times a month - **configuration rows** read by key at the start of most operations - rows in a large table with a badly skewed access pattern — a few thousand hot identifiers out of millions — where the hot set fits comfortably - **insert-only rows never updated afterwards**, read back by key many times ## What does not belong in it - tables written at roughly the rate they are read: every write pays invalidation and nearly every read still misses - tables reached only through filtered searches: the store is not consulted for a predicate, so the hit rate stays near zero - very large rows, or a working set far bigger than the budget: they evict everything useful and turn the cache into overhead - rows read once by one request and never again — per-request working data has no reuse to harvest - data written often by paths outside the mapper: the layer cannot maintain entries for writes it never sees, so the caching decision is simply no - any value the system must read from the row itself for correctness — do not leave a copy where code can find it ## Sizing, warming and the cold start 1. **Size from the working set, not the table.** Estimate the distinct identifiers read by key in a busy window and the bytes per entry; their product is your floor. Sizing to the whole table is how a cache becomes the reason a process runs out of memory. 2. **Leave headroom for everything else.** A cache competing with request handling for memory buys latency in one place and pays for it, with interest, in another. 3. **Decide deliberately whether to warm.** Loading a known key set at start-up is worth it when the miss path is expensive and the hot set is small and predictable; it is wasted when the hot set is large or unpredictable, and it always delays start-up. 4. **Plan for the cold start anyway.** Deploys, restarts and scale-outs all begin empty. If the system only stands up with a warm cache, an ordinary rolling deploy is an outage waiting to happen; the honest test is whether the miss path holds at full traffic. 5. **Then measure.** Hits, misses and evictions per type. A high eviction rate with a low hit rate means the working set does not fit. A low hit rate with few evictions usually means the reads were never by identifier in the first place. ## A worked check A currency table of 180 rows read by key on nearly every request — say two thousand reads a minute — and edited twice a month has millions of reads by identifier per write, occupies a few hundred kilobytes resident, and receives essentially no predicate traffic: cache it, and the simplest mechanism suffices. An order table of forty million rows taking eight hundred writes a minute, read almost entirely through filtered searches over a customer and a date range, has fewer than one read by key per write, an unbounded working set and traffic the store cannot answer: leave it out. Most real tables sit between the two, and the only honest way to place one is to count its two read paths for a day before deciding. ## Talking it through State the ratio you need before you name a single table. Then give two or three plausible candidates and — this is the part interviewers weight most — name what you would **refuse** to cache and why. Finish with the operational half: what you would look at a week later, and what you would do if the hit rate came back at a few per cent. Turning a cache off is a legitimate outcome, and saying so signals that you treat it as an optimisation with a cost rather than as an architectural badge.