Drills 24 to 28: storage

Five drills, ninety seconds each, out loud. Storage answers reward one thing above all: stating the access pattern before the schema. A candidate who designs a table and then discusses queries has the order backwards, and every interviewer in this area is listening for which way round you do it.

The other pattern that runs through all five: the failure is usually a property of the engine that is invisible in the query. Tombstones, hot partitions, shard-key monotonicity, snapshot isolation semantics. None of those appear in the SQL or the API call, which is why knowing them is the whole value.

Drill 24. Design a DynamoDB table for these five access patterns.

I'd start by writing the access patterns down explicitly, because in DynamoDB the key schema is derived from them rather than from the entities. That's the inversion from relational design: you don't model the data and then query it, you enumerate the queries and then derive the keys.

So for something like an order system: get a customer, list a customer's orders newest first, get an order with its line items, list orders by status for fulfilment, and get an item's history. Five patterns.

Single table, generic key names, PK and SK, because different entity types share the table. Customer is PK=CUST#123, SK=PROFILE. Their orders are PK=CUST#123, SK=ORDER#<timestamp>#<id>, so listing a customer's orders newest first is one query on the partition with a descending sort, and getting the profile plus recent orders is one query rather than two.

Line items go under PK=ORDER#456, SK=ITEM#<n>, so an order and its items come back in a single query. That's the item collection pattern and it's the main reason single-table design exists.

Status lookup needs a GSI, because status isn't in the key and it changes. GSI1PK=STATUS# PENDING, GSI1SK=<timestamp>, and I'd note that this is a sparse index: only orders with a status worth querying carry the attribute, so the index stays small.

The thing I'd flag is the hot partition risk. STATUS#PENDING is one partition key, so if pending orders are a large share of writes, that partition takes disproportionate traffic. The fix is a write-sharding suffix, STATUS#PENDING#<0-9>, and fanning the query across ten partitions.

Depth signal: access patterns first as an explicit step, the item collection as the reason for single table, and volunteering the hot partition problem with its fix.

Full treatment: DynamoDB single-table design.

Drill 25. Why did our Cassandra range query start timing out?

Almost certainly tombstones. Cassandra doesn't delete on delete: it writes a tombstone marker, and a range query has to read and skip every tombstone in the range before it can return live rows. So a partition where rows are regularly deleted, or where TTLs have expired, accumulates tombstones and the read cost grows even though the live data doesn't.

The specific numbers: there's a warning threshold at 1,000 tombstones scanned in a query and a failure threshold at 100,000, where the query is aborted rather than timing out slowly. So a query that worked yesterday and fails today usually crossed one of those.

Why they accumulate: tombstones can only be purged after gc_grace_seconds, which defaults to ten days, and only during compaction, and only if the node has been repaired within that window. That last condition is the one that bites, because if repair hasn't run, purging a tombstone could resurrect deleted data on a node that missed the delete, so Cassandra correctly refuses.

So the diagnosis is: check tombstone_scanned histograms, check whether repair is actually running on schedule, and look at the data model, because the real cause is usually a queue-like table where rows are written and deleted from the same partition, which is the documented anti-pattern.

The fix is rarely tuning gc_grace_seconds down, because that trades a correctness guarantee for a performance problem. It's usually remodelling: time-bucketed partitions with a TTL and TimeWindowCompactionStrategy, so whole SSTables expire together and get dropped rather than compacted.

Depth signal: the repair dependency on tombstone purging, and reaching for the data model rather than the tuning knob.

Full treatment: Cassandra tombstones and gc_grace_seconds.

Drill 26. Pick a Mongo shard key for this workload and defend it.

I'd start from the profiler output rather than the schema: the top five queries by count and their share. The shard key's job is to make the dominant query targeted rather than broadcast, and I can't choose it from the data model alone.

Then three tests. Cardinality: can we split far enough? Frequency: is one value dominant? Monotonicity: does every insert land on one shard?

For an order service where 62 percent of queries are "this customer's recent orders", I'd take {customer_id: 1, order_date: 1}. Cardinality is 4.2 million customers, so we can split as far as we ever need. Frequency: the top customer is about 3 percent of orders, which on customer_id alone would be a jumbo chunk risk, and the date suffix removes it because their orders now split by date. And it's not monotonic, so a given day's inserts spread across whichever customers happen to order.

What I'm accepting is that the 9 percent of reporting queries on date alone still broadcast. That's the right trade: they're analytical rather than latency-sensitive, and I'd move them to a secondary rather than distort the key for 9 percent of traffic at the expense of 62.

And the answer that fails is _id, because ObjectId is monotonic in its leading timestamp bytes, so every insert goes to the shard holding the top chunk and you have a six-shard cluster with one shard's write throughput.

Depth signal: starting from the profiler, running all three tests explicitly, and naming what you are giving up rather than claiming a costless choice.

Full treatment: Choosing a MongoDB shard key.

Drill 27. Reindex OpenSearch with zero downtime.

The whole answer rests on one thing: applications read and write through an alias, never a concrete index name. If that's true, the switch is one atomic _aliases call and the rollback is the same call reversed. If it isn't true, that's the first change, and it's the thing to have done before you ever need this.

The sequence: create the new index with the new mapping, reindex with slices=auto for parallelism, handle writes that arrive during the copy, verify, then swap the alias atomically.

Handling concurrent writes is the interesting part. Three options: dual-write from the application, which is simplest if you control the write path; repeated delta passes filtered on updated_at, which needs version_type: external so a delta can't overwrite a newer document with an older one, making the passes idempotent and re-runnable; or replay from the source of truth if you have change data capture.

Two settings that roughly halve the copy time: zero replicas and refresh_interval: -1 during the bulk load, because both multiply indexing work and neither is needed while nobody is reading the new index. Restoring them before the swap is the step people forget, and the replica rebuild afterwards is a segment copy rather than re-indexing, so it's much cheaper than the work it replaced.

Verification before the swap: document counts, a sample of documents compared field by field, and a set of representative queries run against both indexes with the results compared. Then swap, keep the old index for the rollback window, and delete it deliberately rather than automatically.

Depth signal: the alias as the precondition rather than a step, version_type: external for idempotent delta passes, and verification before the swap.

Full treatment: Zero-downtime reindex in OpenSearch.

Drill 28. Postgres write skew under REPEATABLE READ. What happens?

REPEATABLE READ in Postgres is snapshot isolation, and snapshot isolation permits write skew, which is the anomaly where two transactions each read an overlapping set, write disjoint rows, and together violate an invariant that neither violated alone.

The canonical case is on-call coverage. The rule is at least one doctor on call. Two doctors both on call, both try to go off call simultaneously. Each transaction reads the count, sees two, concludes it's safe to remove itself, and updates its own row. Neither wrote the same row, so there's no write-write conflict and snapshot isolation lets both commit. Now zero doctors are on call and no constraint was violated by either transaction individually.

What makes it specifically a snapshot-isolation problem is that each transaction is reading from a snapshot taken at its start, so neither sees the other's write, and the conflict is on the predicate rather than on any row.

Postgres's SERIALIZABLE uses Serializable Snapshot Isolation, which tracks read-write dependencies between concurrent transactions and aborts one when it detects a dangerous structure. So it prevents write skew, at the cost of serialization failures that the application must catch and retry. That retry loop is not optional: SERIALIZABLE without retry handling turns an anomaly into an error.

The alternatives if you don't want SSI: SELECT ... FOR UPDATE on the rows you read, which materialises the conflict; or a constraint the database can enforce directly, which is better when the invariant can be expressed that way.

Depth signal: naming that the conflict is on a predicate rather than a row, and that SERIALIZABLE requires a retry loop rather than being a free upgrade.

Full treatment: Write skew and snapshot isolation.

How to practise these

Storage drills have a distinctive tell: candidates who have operated the system name the number, and candidates who have read about it name the concept. Tombstones is a concept; "the failure threshold is 100,000 scanned in one query" is operational. Both drills 25 and 26 are effectively testing which one you are.

Three tests for your own answer:

1. Did you state the access pattern or the query profile before the design? In drills 24 and 26 this is the whole answer, and doing it in the other order is the most common failure in this area.
2. Did you name the engine behaviour that is invisible in the query? Tombstones needing repair before purging. ObjectId being monotonic. Snapshot isolation conflicting on predicates. That is where the depth is, because none of it appears in the code.
3. Did you say what you gave up? The shard key that broadcasts 9 percent of queries. The reindex that needs an alias to already exist. A design presented as costless is a design that has not been thought through.

And a delivery note specific to this chapter: resist the tuning knob. In drill 25 the tempting answer is lowering gc_grace_seconds, and it trades a correctness guarantee for a performance problem. In drill 26 it is adding shards. The stronger answer is almost always the data model, and saying "the tuning parameter would help and the real cause is the model" is the move that separates an operator from someone who has read the tuning guide.