Skip to main content

Module Quiz

Complete this quiz after finishing all concept and practice pages. Save your answers before opening separate quiz calibration. Answer in writing, in full sentences, before checking against the model answers.

Current Module Questions​

Question 1: Random vs Sequential I/O​

Why does a database treat a random 8 KB read and a sequential 8 KB read as qualitatively different costs, even on an SSD?

Question 2: Slotted Page Access​

An 8 KB page holds 40 variable-length records in a slotted layout. An UPDATE grows one record by 200 bytes. Describe the two possible outcomes and what each does to the record's RID.

Question 3: Buffer Pool​

With a buffer pool of 1000 frames and pin_count > 0 on 980 frames, a query needs a page that is not resident. What happens and why?

Question 4: B+-Tree Height (Access-Path Analysis)​

A B+-tree has fan-out 200 and 10^9 keys. What is its approximate height, and how many page reads does a point lookup cost on a cold cache?

Question 5: B+-Tree Split​

Starting from an order-4 B+-tree leaf containing [10, 20, 30], inserting 25 causes a split. Draw the leaves before and after and state the separator promoted to the parent.

Question 6: Clustered vs Secondary (Access-Path Analysis)​

A users table is clustered by id. A query is SELECT name, email FROM users WHERE email = ?. You add a secondary B+-tree on email. Describe the full access path and its page cost, then describe the path if the index is a covering index on (email) INCLUDE (name, email) vs (email, name).

Question 7: LSM Write Path​

Describe every place a single write goes in an LSM engine before the caller returns, and explain why the write does not wait for SSTable I/O.

Question 8: Bloom Filter Arithmetic​

A Bloom filter with m = 10^5 bits and k = 7 hash functions has been populated with n = 10^4 keys. What is the approximate false-positive probability? If your LSM read needs to check 40 SSTables for a nonexistent key, how many SSTables do you expect to actually read on disk?

Question 9: Specialty Index Selection​

A jsonb column is filtered by containment: WHERE data @> '{"status": "open"}'. Which index type fits and why does a B+-tree fail here?

Question 10: Execution Model​

When does a vectorized engine outperform a volcano iterator, and when does it fail to?

Question 11: Join Algorithms​

A hash join is chosen on two tables A (10^7 rows) and B (10^3 rows). The build side is B. The build hash table does not fit in memory. What plan does the engine fall back to, and why might it prefer to switch algorithms entirely?

Question 12: Statistics and Cost Estimation​

A query planner predicts 10 rows for a filtered subquery; the real count is 10^6. Name two specific errors that commonly cause this kind of miss, and one consequence for the chosen join algorithm.

Question 13: 2PL and Deadlock​

Two transactions each touch rows in the opposite order under strict 2PL and deadlock. Who aborts, and can the application retry safely?

Question 14: MVCC Visibility​

Under snapshot isolation, explain why a long-running read transaction may see version V1 of a row while a concurrent short transaction sees version V2, and under what condition V1 becomes eligible for garbage collection.

Question 15: WAL and Recovery​

A transaction writes a record, appends a commit log record, and crashes before the data page reaches disk. What does recovery do, and what would happen if the engine had no WAL?

Interleaved Review Questions​

Prior Module Question 1​

What is the difference between a logical read and a physical read?

Prior Module Question 2​

Why is a table scan sometimes faster than an index scan?

Prior Module Question 3​

What is the essential difference between a B-tree and a B+-tree?

Prior Module Question 4​

Why does doubling the memtable size typically improve LSM write amplification?

Prior Module Question 5​

What does the WAL rule guarantee that naive file writes do not?

Self-Assessment and Remediation​

Mastery Level (90-100% correct):

  • Ready to move to Module 3 (Replication & Partitioning).

Proficient Level (75-89% correct):

  • Redo practice pages 2 and 3, and the joins/statistics concepts.

Developing Level (60-74% correct):

  • Re-read Cluster 2 (B-tree) and Cluster 5 (concurrency), redo all katas, and build the EXPLAIN reading log.

Insufficient Level (<60% correct):

  • Return to the concept sequence starting at Cluster 1 and build the storage-engine reasoning habit before advancing.

Progression boundary​

Quiz performance calibrates retrieval and reasoning. It does not replace runnable work, changed-case transfer, the module assessment or required semester evidence. Consult practice calibration for the additional independent cases.