Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Name what gets updated besides the table (15 minutes)
State what else must be updated, besides the table itself, when a row is inserted into a table with indexes.
Done when: every index covering the inserted columns is correctly named.
Hint
Every index that covers one of the inserted row's columns.
Easy 2: Define over-indexing (15 minutes)
State, in one sentence, what over-indexing means.
Done when: having more indexes than the query workload justifies, paying maintenance for little read benefit, is correctly described.
Hint
Having more indexes on a table than its actual query patterns justify, paying write cost for little or no read benefit.
Easy 3: Name the write-amplification multiplier (15 minutes)
A table has four indexes. State how many physical write operations one INSERT triggers in total.
Done when: five is correctly given, one for the table plus one for each of the four indexes.
Hint
Five: one for the table, plus one for each of the four indexes.
Medium
Medium 1: Explain why an unused index still costs something (25 minutes)
Explain why an index that no query ever uses still has an ongoing cost to the system.
Done when: the explanation correctly notes the index still must be maintained on every write that touches its columns, regardless of whether any query ever reads from it.
Hint
It still must be kept consistent on every write touching its columns, even though no query ever benefits from that work.
Medium 2: Calculate maintenance cost for a workload (25 minutes)
A table with six indexes receives ten thousand inserts per hour. Estimate the total number of physical write operations per hour this causes.
Done when: seventy thousand is correctly calculated, from ten thousand table writes plus sixty thousand index writes.
Hint
Seventy thousand: ten thousand table writes plus six times ten thousand index writes.
Medium 3: Identify an over-indexing candidate (30 minutes)
A table has an index on a column that no report, application query, or dashboard has filtered on in over a year. Decide whether this index is a good candidate to keep or to remove, and explain why.
Done when: removal is correctly recommended, citing the ongoing maintenance cost with no corresponding read benefit.
Hint
Remove it, since it is paying ongoing maintenance cost with no query ever reading through it.
Hard
Hard 1: Weigh an index's benefit against its cost (30 minutes)
A proposed index would speed up one report that runs once a month, on a table that receives a hundred thousand writes per day. Explain the trade-off involved in adding this index.
Done when: the explanation correctly weighs the very high write volume's constant maintenance cost against the comparatively rare benefit, noting this would usually be a questionable index to add.
Hint
A monthly report is a rare benefit compared to constant daily write costs, making this index a questionable addition unless the report is extremely valuable.
Hard 2: Explain why index count matters more on write-heavy tables (35 minutes)
Explain why over-indexing is a more serious problem on a table that receives constant heavy writes than on a table that is rarely written to.
Done when: the explanation correctly ties index maintenance cost to write frequency, so a rarely written table pays that cost rarely, while a heavily written table pays it on every single write.
Hint
Maintenance cost is paid once per write, so a table written to constantly pays that cost far more often than a rarely written table does.
Hard 3: Design a policy for deciding whether to add an index (40 minutes)
Propose a general policy a team could use to decide whether a new index should be added to a frequently written table.
Done when: the proposed policy reasonably weighs the expected read benefit (how often and how valuably the index would be used) against the expected write cost (how frequently the table is written to), and recommends against adding indexes that fail that comparison.
Hint
A reasonable policy compares how often and how valuably an index would actually be used against how often the table is written to, only adding the index when the expected read benefit clearly outweighs the ongoing write cost.