Module 19 · Storage, indexing and recovery · Drill

Storage, indexing and recovery drill

Scenario prompts that check whether storage, indexing and recovery is usable under pressure. Answer out loud, then compare.

4 prompts25 minNew

0 of 4 graded

Prompt 1
A table of 20 million orders (about 250 bytes per row, 8 KB pages) is queried three ways: by order_id, by customer_id for a customer's recent orders, and by status for a dashboard of the 2 percent of orders that are 'pending'. Estimate pages per query with and without indexes, and choose index types and clustering.
Say your answer out loud first. Thirty seconds is enough.
Prompt 2
Explain step by step how a database guarantees that a committed transfer survives a power cut that happens 5 milliseconds after COMMIT returns, and that a half-finished transfer at the same moment leaves no trace. Name the log records, policies and recovery passes involved.
Say your answer out loud first. Thirty seconds is enough.
Prompt 3
A query joining a filtered 'premium_regions' list of 30 rows to a 50-million-row events table ran in 40 ms for months, then suddenly takes 4 minutes after a large data load. EXPLAIN shows a hash join with a full scan of events where it used to show a nested loop. Explain both plans' costs and the likely cause, and fix it.
Say your answer out loud first. Thirty seconds is enough.
Prompt 4
An engineer accidentally ran DELETE FROM customers without a WHERE clause on the production primary at 14:02. Backups are nightly at 02:00, WAL is archived continuously, and there are two streaming replicas. Explain what each of these protects against here, and walk through recovering the data with minimal loss.
Say your answer out loud first. Thirty seconds is enough.