In September a salesperson at Galewatch, the wind farm telemetry company that exists only in this course, closed the biggest contract in its history and the office ordered cake. Ardnave Point: two hundred turbines going up off the west coast, readings flowing from March.
The on-call engineer did the arithmetic before the cake was cut.
Galewatch collects a reading from each turbine every two seconds and sells the owners dashboards, and since June it has kept those readings on sixteen machines instead of one. Lesson 013 has that story. A restore drill ran six hours and twenty minutes against a recovery target of one hour, which fixed the size of a shard at 720 gigabytes, and the company cut the data by farm rather than by turbine so that the panel an engineer opens at a site would still be answered by a single machine. Four and a half terabytes spread over nine hundred turbines is five gigabytes per turbine-year.
A farm is never split. Two hundred turbines is a terabyte. A terabyte restoring at two hundred megabytes a second is eighty three minutes.
The contract Ardnave Point's owners were about to sign says one hour, because every Galewatch contract says one hour.
Nothing was broken. No graph had a bad shape on it, no query had got slower, and the fleet's write rate would go from 450 readings a second to 550, still a ninth of the ceiling lesson 005 wrote down. The problem was three months in the future and already unavoidable, and it had been decided in June by four people in a room who thought they were choosing where rows go.
Divide the 720 gigabyte budget by five gigabytes a turbine-year and you get 144. On the day Galewatch chose to shard by farm it also set a maximum farm size of a hundred and forty four turbines, for the whole company. That number appears in no runbook, no contract and no sales deck. Kilmore Sands, the largest farm Galewatch already had, has a hundred and forty.
The key names what has to stay together
The shard key is the value the routing code reads to decide which machine a row belongs on. Farm identifier, customer identifier, turbine serial, order number: one column, or a few columns treated as one.
That is the definition you will find everywhere and it is the less useful half of it. Here is the other half. Every row that shares a key value lands on the same machine, always, by construction. So the shard key is a promise about what stays together, and everything that hurts later comes out of that promise rather than out of the hashing.
Three things follow from it, and only two of them tend to come up in the design review.
The key decides which queries stay on one machine. Lesson 013 named the two shapes: a targeted query that carries the key and goes to one shard, and a scatter-gather query that doesn't and goes to all of them. Which of your queries is which is entirely a property of the key you picked.
The key decides how evenly the bytes and the writes land, because rows do not distribute themselves. They distribute the way the key's values distribute, and the key's values are usually a fact about the world rather than a fact about your database.
And the key decides the smallest thing you can ever move. This is the one nobody raises. Once farm is the key, a farm is atomic. You cannot put half of Kilmore Sands somewhere else, not because the software refuses but because the key says those rows are one thing. When shard 3 fills up, your options are the ones the key left you.
Galewatch's June meeting argued the first point for an hour, glanced at the second, and never mentioned the third at all. That is the normal distribution of attention and it is upside down.
The fattest value is the floor
Cardinality is the number of distinct values a column takes. Farm has nineteen. Turbine has nine hundred. The textbook advice is to pick a key with high cardinality, which is true and is not the test you want, because cardinality counts the values without weighing any of them.
Here is the test that actually predicts the outage. Take the most common value of your candidate key. Everything with that value is on one machine, so that value alone is the smallest shard you will ever be able to build. Compare it against your shard budget before you compare anything else.
key = farm
fattest value Kilmore Sands, 140 turbines
140 x 5 GB = 700 GB
budget 720 GB
headroom 20 GB, about 3%
key = turbine
fattest value any single turbine, 5 GB
budget 720 GB
headroom 715 GB
A hundred and forty turbines at five gigabytes each is seven hundred gigabytes, against a budget of seven hundred and twenty. By farm, the floor is 97% of the ceiling on the day you choose it. By turbine, the floor is 0.7% of it. Those two keys are not slightly different choices. One of them has already spent everything.
The fattest value is the floor, and the floor is what you cannot fix by buying machines. Add sixteen more shards to a by-farm cut and Kilmore Sands is still seven hundred gigabytes on one machine. You have doubled the fleet and moved nothing that mattered.
There is a second reading of cardinality worth keeping, which is about pieces rather than values. Nineteen farms onto sixteen machines is a placement problem with boulders: thirteen machines get one lump each, three take two small sites doubled up, the lumps are whatever size they are, and there is nothing to shuffle. Nine hundred turbines onto sixteen machines is a placement problem with gravel: about fifty six pieces per machine, and you can move a handful from a full machine to an empty one without anybody noticing. Same data, same sixteen machines, completely different amount of freedom.
Boulders and gravel. Count the pieces per machine before you count anything else, and if the answer is close to one, you have not chosen a shard key so much as an assignment.
Skew, and what it actually costs
Skew is how far the biggest shard sits from the average one. Spread Galewatch's four and a half terabytes over sixteen machines and the average comes out at 281 gigabytes. Kilmore Sands is 700. That is a skew factor of two and a half, and lesson 013's spine says what a skew factor costs you: in a sharded system you never pay the average, you pay the maximum.
Work it through on three quantities and it comes out the same way every time.
Restore is the one lesson 013 finished. The fleet average says twenty three minutes and the runbook says fifty eight, because a sharded restore is done when the slowest shard is done, and the slowest shard is Kilmore Sands.
Writes skew with it. Readings arrive in proportion to turbines, so Kilmore Sands takes a hundred and forty readings every two seconds, seventy a second, against a fleet average of 450 over sixteen, which is 28. Two and a half again, from the same cause.
Capacity is the one that quietly costs money. Assume you buy identical machines, and you should, because the alternative is a fleet where one box is special and a runbook with an exception in it, and exceptions are how three in the morning goes wrong. Identical means every machine has to be able to hold the biggest shard. Sixteen machines each able to hold 700 gigabytes is 11.2 terabytes of provisioned disk to store four and a half, which is 40% used. Lesson 013's four thousand dollars a month bought the machines and said nothing about disk; this is the same skew arriving as capacity you own and cannot fill.
Bytes and writes move together at Galewatch because one thing drives both, and that is a property of telemetry rather than a rule. The general case is worse, because a key can be perfectly even in one dimension and catastrophic in another. Suppose Marlow Books, the four person online bookshop in this course, ever sharded its catalogue by ISBN, which lesson 013 argued at length it should not. The bytes would be beautiful: 1.2 million titles at about two hundred bytes of row each, sliced any way you like. The reads would not be, because on a Thursday in April one title was the radio book club pick and sold forty copies in a day, which lesson 009 points out is the only reason a four person shop sells forty of anything, and one shard would have carried every one of those lookups. Even bytes, one hot machine. Lesson 047 owns hot keys properly.
Measure skew as a ratio, not as a difference, and measure it on every dimension you care about separately: bytes, writes, reads, and the restore that comes out of bytes. A fleet whose biggest shard is 2.5 times the average is a fleet running at 40% of what it paid for, and nobody has ever found that on a dashboard, because every dashboard averages.
The leading column of the whole system
Lesson 010 taught composite indexes and gave one rule for column order: an index serves a condition on its first column, or its first two, or its first three, and stops helping the moment you skip one. Marlow's publisher page needed (publisher_id, published_at) in that order, and written the other way round it would have served "everything published last Tuesday, any publisher", which nobody at the shop was asking.
A shard key is that same decision at a much bigger radius. It is the leading column of the entire system, and the same rule applies with the same sharpness: a query that carries the key gets one machine, and a query that does not gets all of them.
The difference is what failure looks like. Skip the leading column of an index and you get a sequential scan, which is one machine reading more than it should, and lesson 010 showed a case where it was actually the faster plan. Skip the shard key and you get a fan-out, which is sixteen machines each doing the work, one merge you wrote and now maintain, and lesson 013's tail arithmetic: a per-shard read that is slow one time in a hundred makes fifteen percent of fleet queries slow, because 0.99 to the sixteenth is 0.85.
So the question to ask about every important query is not how fast it is. It is whether it carries the key.
Galewatch has one that doesn't, and lesson 013 already named it. Every turbine has a serial number stencilled on the tower, WTG-4471 and eight hundred and ninety nine others, unique across the fleet and not the shard key. A technician standing at the base of a turbine in the rain scans that barcode and wants the last week of readings. Farm is the key, the serial does not carry the farm, and so the honest options are sixteen queries or one extra lookup.
Take the extra lookup. A table of nine hundred rows mapping serial number to farm, cached anywhere you like because it changes when somebody bolts a new turbine to the ground, turns a sixteen way fan-out into two round trips. When a query cannot carry the shard key, the usual answer is a small second structure that translates whatever it does carry into the key. You will meet it again as a secondary index, and every time you build one you are keeping a second copy of a mapping, with everything lesson 009 said about copies going stale.
What you do not get is a second shard key. There is one, the data is physically arranged by it, and every other access pattern pays either a fan-out or the price of a translation you maintain.
The decision you cannot undo cheaply
Changing the number of shards and changing the shard key sound like the same size of job. They are not in the same category.
Changing the count moves rows. It is expensive, it wants a maintenance window or a clever online scheme, and lesson 031 is entirely about doing it without reshuffling everything. But it is an operation. When it finishes, every line of application code still compiles, and every query that was targeted is still targeted.
Changing the key moves rows and changes which rows can be in the same transaction. That is not data movement, that is a rewrite. A join that was local becomes a fan-out. A unique constraint that held becomes a promise nobody keeps. A checkout that was one commit becomes two commits that can half succeed. You do not schedule that for a Saturday night; you run both keys at once for weeks, writing to both, reading from the old one, comparing, and then cutting over service by service. At 450 writes a second that never pause, in the middle of the thing lesson 013 already told you about: you will start this at the moment you have the problem, using the machines that have the problem.
There is one piece of engineering that makes a wrong choice less permanent, and it is worth building on day one because it costs almost nothing then.
Do not map the key to a machine. Map the key to one of a large fixed number of buckets, some power of two in the thousands, say 4,096, and keep a separate map from bucket to machine. The bucket count never changes for the life of the system. Moving load means moving buckets, and the map from bucket to machine is a small piece of data rather than a formula compiled into every service. Lesson 031 owns the algorithm and why the naive version, hashing the key modulo the machine count, moves nearly every row the instant that count changes.
Notice what buckets do not fix. If the key is farm, every row of Kilmore Sands hashes to one bucket, and no amount of indirection splits a bucket. Buckets are a placement tool and they need something to place. They buy you everything when the key is gravel and nothing at all when it is boulders, which is the second time today those two have decided the answer.
The other cheap thing is to measure the right number. Not total size, which grows smoothly and reassuringly, but the size and write rate of your fattest key value and how fast that value is growing. Kilmore Sands crossing 720 gigabytes was always going to be the event, and it was visible for months in a number nobody plotted.
What Galewatch did about Ardnave Point
Three options, and the first one is the one people reach for.
Buy a bigger machine for that shard. It fails immediately, and the way it fails is the most useful thing in this section. The binding constraint is a restore, a restore is bandwidth, and scaling up means more cores and more memory, which is how lesson 005 defined it. Neither of those reads a backup any faster.
The fair objection is that a bigger machine usually arrives with a fatter network card as well, and lesson 013 left it genuinely open whether the two hundred megabytes a second belonged to the machine or to the one pipe all sixteen shards pull their backup through. If it was the pipe, a bigger box buys nothing whatsoever. If it was the machine, getting eighty three minutes under sixty needs 280 megabytes a second, forty percent more, on a rate somebody measured once during one drill, staked against a number in a signed contract. The shortage here was never compute.
Re-key the whole fleet by turbine. The shards come out even at 281 gigabytes, the floor drops to five, and the boulders become gravel. It also makes the panel at Tarrow Ridge, the sixty turbine farm whose engineers work out of a portable building on site, touch 15.6 of the sixteen machines, which lesson 013 worked out and which is the reason the company did not do this in June. Nothing about September makes that panel matter less.
What they took was the third thing: widen the key from farm to the pair (farm, turbine), route on the farm part alone while a farm fits in the budget, and split only the farms that don't. All nineteen existing farms stay whole. Ardnave Point becomes two shards of a hundred turbines, five hundred gigabytes each, restoring in forty two minutes with eighteen to spare, and the fleet goes from sixteen machines to eighteen.
Which looks like the rewrite this lesson just said never to attempt, and isn't, for a reason worth keeping. They appended to the key rather than replacing it. Farm is still the leading part, so every query that carried the old key still carries the prefix of the new one, and by lesson 010's rule a prefix is exactly what still routes. Nothing that was targeted became a fan-out, no transaction changed shape, and the only rows that moved were the hundred turbines on the far side of a split. Widening a key at the end is an operation. Changing what comes first is the rewrite.
What that cost, stated plainly. Ardnave Point's own farm panel is now a two way fan-out, so run lesson 013's sum at N equals two: 0.99 squared is 0.98, and two percent of that panel's loads wait for a slow shard instead of one percent. Nobody will ever notice. The real bill is that routing stops being a function and becomes a table, with a row per farm saying whether it is split and where its halves live, which has to be right on every ingest box at the same time, and which is exactly the production state lesson 013 warned about arriving in a form you now have to store, distribute and back up. Lessons 031 and 048 own that table.
And they told sales about the hundred and forty four.
That last part is not a joke about process. The shard key had been a constraint on what the company could sell since June, in the same way a schema is a constraint on what you can record, and the only thing wrong with it was that it lived in a routing function where no one who signs contracts would ever meet it. A key is chosen against the largest value you could ever be asked to hold, which is a question for the business rather than for a graph, and lesson 005 already made the general version of that point when it said Galewatch grows in steps and has a notice period rather than a runway.
Stagefront, the ticketing service in this course where two hundred thousand people press the same button at ten in the morning, is the case where none of this helps and no key works, and lesson 013 argued it out. Not every system has a good shard key waiting to be found.
Recap
The shard key is a promise about what stays together. Rows sharing a key value are on one machine by construction, so the key fixes which queries are targeted, how evenly the load lands, and the smallest thing you can ever move. That third one is the one design reviews skip.
The fattest value is the floor. Take the most common value of your candidate key and size it: that alone is the smallest shard you can ever have, and no number of machines reduces it. Kilmore Sands at 700 gigabytes against a 720 gigabyte budget was a decision already spent.
Boulders and gravel. Cardinality matters as pieces per machine, not as a count of distinct values. Nineteen farms over sixteen machines leaves nothing to shuffle; nine hundred turbines over sixteen leaves fifty six pieces each and real freedom. The same distinction decides whether bucket indirection helps you.
A shard key is the leading column of the whole system. Lesson 010's rule scales up exactly: carry the key and you get one machine, skip it and you get all of them. When a query cannot carry the key, the answer is usually a small second structure that translates what it does carry, and you now own a copy that can go stale.
Changing the count is an operation; changing the key is a rewrite. One moves rows. The other moves rows and changes which rows can be in a transaction together, which is application code, run live, starting from the day the problem arrives. Appending to the key is the cheap exception, because the old key is still the prefix and a prefix still routes.
Measure the fattest value, not the total. Totals grow smoothly and tell you nothing. The number that ends your architecture is the size and growth of your largest single key value, and it is usually visible for months before anybody plots it.
Check your understanding
A payments service shards by merchant identifier. There are 40,000 merchants across twelve shards, and the largest merchant is 9% of all transactions. Say what the floor is here, whether the key is boulders or gravel, and what single measurement you would put on a dashboard on your first day.
Galewatch's technician lookup by turbine serial is solved with a 900 row translation table. Describe what goes wrong the week a farm is sold and forty turbines move from one owner to another, and say which lesson's problem you have just inherited.
A team proposes sharding an orders table by
order_id, generated randomly. Bytes and writes will be perfectly even. Name two queries that this business certainly runs which would become fan-outs, and say what you would build instead without changing the key.You have a 40 terabyte dataset, a two hour recovery target, and a measured bulk restore rate of 150 megabytes a second. Work out the maximum shard size and the minimum shard count, then say what you need to know about the candidate key before you can tell whether that count is achievable at all.
Somebody argues that bucket indirection (key to 4,096 buckets, buckets to machines) means the shard key no longer matters much, because load can always be moved later. Say precisely when they are right and when they are wrong, and give the property of the key that decides which.
Next lesson
015 Transactions and Isolation: What the Database Promises. Today kept saying that a good key keeps a transaction inside one shard, and lesson 013 said the same thing about Stagefront's four adjacent seats; next lesson opens up what a transaction actually guarantees on one machine, which is the thing you have been protecting all along.