Shard an ad platform on advertiserID and you make one class of query beautifully cheap. Everything for an advertiser, its campaigns, spend, impressions, and dashboards, can live together. A request arrives with an advertiser ID, the router sends it to one shard, and the database does its work locally.

Then someone asks for the top campaigns across every advertiser.

Now the exact choice that made the first query fast makes the second one expensive. You fan out to every shard, collect partial results, merge them, and wait for the slowest node. Nothing went wrong; the system is behaving exactly as designed.

That is the core idea behind partition-key design: a key does not simply distribute data. It decides which access patterns the system subsidizes and which ones it taxes. This is why "I'd shard the database" is not really an answer. The next question is always: on what? A complete answer names the queries that become cheap, the queries that become expensive, the skew the key can create, and what happens when the system grows beyond the assumptions behind it.

Every partition key creates winners and losers

It is tempting to treat the partition key like another schema field: pick a column with high cardinality, hash it, and move on. But the key is closer to an architectural boundary. It determines which rows can be colocated, which operations can stay inside a single node, where traffic accumulates, and what the smallest movable unit of data will be later.

That last part matters because partitioning decisions are unusually expensive to reverse. Changing a column in a schema is one thing. Changing the key that decides where every row physically lives means moving live data while reads and writes continue.

So the useful question is not "which column distributes data most evenly?" It is "which queries must remain cheap, which ones can become expensive, and which assumptions about traffic am I willing to encode into the physical layout of the system?"

Consider three common answers for the same ad platform.

Bet one: colocate the tenant

Suppose advertisers own campaigns and campaigns generate impression events. Shard the whole hierarchy by advertiserID.

For a multi-tenant product this is immediately attractive. "Show me this advertiser's campaigns" lands on one shard. Joining campaigns to their events can happen locally. A workflow that pauses a campaign and updates the advertiser's remaining budget may stay within one shard's transaction machinery rather than requiring distributed coordination, assuming the write truly stays inside that advertiser. The physical shape of the data matches the logical shape of the product.

But the convenience only exists when the request already knows the parent. Ask for campaign C123 without an advertiser ID and the router no longer knows where to go. You need some other mechanism: encode the advertiser into the campaign identifier, maintain a lookup service from campaign to advertiser, or scatter the query across every shard. All three can work; none is free.

The second weakness is more dangerous because it often appears only after success: advertisers are not the same size. If one agency becomes 100 times larger than the median customer, advertiserID sharding preserves that skew. The tenant is the unit of placement, so the database cannot spread that advertiser across several nodes without changing the scheme.

Parent-ID sharding is a strong fit when tenant-scoped queries dominate and the largest tenant will comfortably fit on one shard. That second condition is part of the design, not a detail to verify later.

Bet two: optimize for direct lookup

Now shard campaigns by a hash of campaignID instead.

The distribution story gets cleaner. Given a campaign ID, hash it and route to exactly one shard. Large advertisers no longer drag all of their campaigns onto the same node. With enough keys and a reasonable hash function, placement becomes much more even. If the workload is mostly point reads, this is excellent.

You paid for that distribution by destroying tenant locality. "All campaigns for advertiser A" is now a scatter-gather operation. Campaigns may live on every shard, so the application queries all N shards, waits for the responses, merges the results, and inherits the tail latency of the slowest participant. The cost also grows with the cluster: adding shards gives the query more nodes to contact and more opportunities for one of them to become the straggler.

This is fine when cross-entity queries are rare, and disastrous when the supposedly exceptional fan-out is actually the main product experience. Entity-ID sharding works well for systems whose natural access pattern is "I know the ID; fetch the object": event stores, URL shorteners, caches, and similar key-value workloads. It is a poor default for relational products chosen because hashing looks balanced on a whiteboard. Even distribution is not the same thing as good partitioning.

Bet three: optimize for time

Impression data creates a different temptation. Events arrive continuously and analysts ask for time windows, so partition by timestamp or by time range. Now "show me impressions from last Tuesday" can touch only the relevant partitions. Retention also gets simpler: deleting old data may be as easy as dropping an expired partition instead of issuing billions of row deletes.

Then write traffic arrives. Because nearly every new event has the current timestamp, nearly every write targets the newest range. Historical partitions sit mostly idle while the newest partition absorbs the ingest load of the entire system. You have built a rolling hotspot.

The useful lesson here is that hash and range partitioning are not necessarily competing answers; they often operate on different dimensions. A compound design such as (hash(advertiserID), timestamp) can spread advertisers across shards while preserving time order within each advertiser's local event stream. A dashboard asking for one advertiser's last seven days remains well aligned with the partition layout. The tradeoff simply moves again: a global query for all impressions in a time window now fans out across the advertiser shards. The cost is never eliminated; you are choosing where to put it.

The key has to survive your biggest tenant

Most partitioning discussions focus on average distribution. Production failures live in the tail.

Imagine your largest advertiser is a major agency whose traffic keeps growing. Under advertiserID sharding, all of that agency's traffic belongs to one partition key. If that key eventually generates more reads or writes than one node can sustain at your latency target, adding more shards to the cluster does nothing. You cannot rebalance half an advertiser when advertiserID is the atomic placement unit.

The number that matters is therefore not shard count. It is the reads and writes per second the largest single key can generate, compared with the throughput one shard actually sustains at the latency target, ideally measured with a load test rather than read off a spec sheet, and with headroom left for growth and failover. Shard count is configuration; per-shard sustained throughput at the latency target is capacity. If the biggest key crosses that line, the partitioning strategy has failed even if every other shard is perfectly balanced.

The common fixes reveal the underlying problem. You might move to (advertiserID, campaignID) so one advertiser can span multiple shards. You might manually split a jumbo tenant with special routing. Either way, you are changing what counts as the unit of placement for some portion of the data. That is a live migration problem, and it usually arrives when the system is under the most pressure.

A design review should therefore ask about the customer-size distribution before selecting a tenant key. If the distribution is heavy-tailed, "one tenant per shard" has an expiration date unless you already have a plan for the outliers.

A hot shard and a hot key are different failures

A reasonable objection to all of this: modern databases rebalance automatically, so the key choice is forgiving. That is true for one class of hotspot and not the other, and the word "hotspot" hides the difference.

A hot shard is overloaded because too many keys or too much aggregate traffic happened to land on one node. The problem exists above the individual key, so a rebalancer has something to work with: split the shard, move ranges, or relocate logical partitions to other nodes. This is the case automation handles well.

A hot key is more stubborn. Suppose one campaign goes viral and suddenly receives a large fraction of all impression traffic. If campaignID is the partition key, that single campaign hashes to one place. Splitting the shard only moves the hot campaign somewhere else, and hashing cannot help because hashing is specifically designed to keep one key in one location. No rebalancer can subdivide the atomic unit of placement.

The remedy has to change the unit of work: cache the hot object in front of the database, salt the write key so one logical campaign spreads across several physical subkeys and pay the cost of reaggregation on reads, or special-case the campaign in routing.

The boundary is simple. The partition key defines the atomic unit of placement. The shard is the unit the system can rebalance and fail over. Skew above the key can often be fixed operationally. Skew inside one key cannot.

Resharding is the bill for assumptions that changed

Eventually the cluster grows, and partitioning choices stop being diagrams and become migration plans. Resharding means copying data to new nodes while the system is still serving traffic, keeping old and new copies consistent, changing routing, verifying correctness, and cutting over without blowing up latency or losing writes.

Naive modulo-N routing makes this particularly painful because changing the number of shards can remap almost every key. Consistent hashing and fixed logical partitions reduce the amount of movement, but they do not eliminate the underlying work. Data still has to move, and safe movement requires machinery: backfills, change data capture or dual writes, verification, cutover, and rollback planning. Some databases automate much of this. Automation changes who writes the machinery; it does not make physical data movement free.

Two design habits follow. First, over-partition logically early: create many logical partitions and map them onto a smaller number of physical machines, so that growth usually means remapping partitions rather than redefining the key and rewriting the entire layout. Second, include migration cost in the original key decision. A theoretically elegant key that depends on a fragile traffic assumption can be worse than a slightly less efficient one that will survive years of growth.

Four questions before you commit

When someone asks "how would you shard this?", do not race to name a column. Walk through four questions first.

1. Which queries matter most? Identify the highest-frequency and most latency-sensitive access patterns. Which of them become single-shard under this key? Which are you deliberately turning into fan-outs?
2. Can the biggest key fit on one node? Compare the largest tenant, campaign, user, or entity against measured single-shard throughput at the latency target you care about. Average load is not enough.
3. Where can skew appear? Is it many keys accumulating on one shard, or one key becoming too large? Those are different failures and require different remedies.
4. What happens when the cluster grows? Know whether adding capacity means remapping logical partitions, moving key ranges, or re-keying live data. Name the migration machinery before you need it.

In an interview, "I would shard by advertiserID" is a weak stopping point. A stronger version of the same answer: "I would shard by advertiserID because the dominant requests are tenant-scoped and I want campaign joins and budget updates to stay local. That breaks campaign-only lookup, so I need a routing index or an encoded parent ID. It also creates a jumbo-tenant ceiling, so I would validate the largest advertiser against single-shard capacity and have a split strategy before that ceiling becomes urgent."

That answer names the subsidy, the tax, and the conditions under which the design expires, which is the actual skill. Partitioning is not about finding a key with no downside; there is no such key. It is about deciding which costs your product can afford, then making sure you understand when those costs will come due.

If you want to pressure-test that judgment before an interview, Formation's Persistent Storage track has fellows work through partition-key decisions on real product data models with senior engineers pushing on exactly these tradeoffs and follow-up questions.