Choosing a shard key

Day 26: Choosing a shard key is a strategic bet, not a schema detail

The shard key you pick in year one will be drawing your org chart by year four. It decides which queries stay cheap and where hot spots form. It decides what fails together. Over time it also decides how teams, on-call rotations and the roadmap get carved up. And you almost always pick it early, when you know less about your workload than you ever will again. Treat it as a decision you can barely undo, because that's what it is.

1. The meeting where 'swap the shard key' meant 'reorg the company'

I've sat in this meeting. Engineering showed up with a clean migration doc, about forty lines: new key, backfill, dual-write, verify, cut over. It read like a quarter's work for a small team. Then someone stood up at the whiteboard and started listing everything that had quietly grown around customer_id over the years. The doc described a data migration. The whiteboard described an organisational one.

MIGRATION DOC (the part we planned)        WHAT HAD GROWN AROUND customer_id (the part we didn't)
-----------------------------------        ------------------------------------------------------
1. Add routing for new key                 - Team ownership: 'Pod A owns shards 0-7'
2. Backfill into new layout                - On-call rotations paged per shard group
3. Dual-write old + new                    - Alerts and SLO dashboards keyed by shard
4. Shadow-read and compare                 - Billing jobs that ran per shard, per customer
5. Cut reads over                          - Support runbooks: 'find the customer's shard'
6. Stop old writes                         - API docs promising per-account ordering
7. Decommission                            - Roadmap: features scoped 'per tenant'
                                           - Contracts: data residency per customer shard

We hadn't picked the wrong key. What happened was that the key had become load-bearing in places no schema review ever looks.

2. Rewind to day one: why customer_id was the right call

The original decision deserves a fair hearing. You'll probably make the same one, and at the time you'll probably be right.

• ▹The queries were per tenant. Almost every request carried a customer context, so each one landed on exactly one shard.
• ▹Isolation was nearly free. A noisy or broken customer only hurt its own shard, and deleting or exporting a customer's data meant touching one shard.
• ▹The business had the same shape. Sales, support and contracts were all organised around the customer, so the system matched how people already thought.
• ▹The tooling backed it. Sharding by tenant ID is the standard multi-tenant pattern in Citus and Vitess, and it's the textbook answer for B2B SaaS.

For that year's access patterns, it was the right call. Where we went wrong was assuming it would stay right every year after, and building nothing that would make changing our minds cheap.

3. The core idea: what a shard key actually controls

Earlier in the series we looked at how partitioning splits data across nodes. Today goes one level up, to what you're actually signing up for when you choose the key. It comes down to four things.

• ▹Locality. If a query includes the key in its predicate, it goes to one shard. If it doesn't, it becomes scatter-gather, and the slowest shard sets its latency. The average shard doesn't matter.
• ▹Load distribution. Load can only be as even as the key's values are. A hash function can't break up one enormous key value, so that value becomes one hot shard.
• ▹Blast radius. Rows on the same shard share its outages, its noisy neighbours and its maintenance windows. You're choosing who goes down together.
• ▹Rebalancing cost. Adding capacity or splitting a hot key means moving data. With some mapping schemes that's cheap. With others it becomes a project.

4. Three ways to map a key to a shard

Picking the key is half the job. You also need a function that maps each key value to a shard, and that function decides how much it will cost to change your mind later.

STRATEGY          HOW IT MAPS                         GOOD AT                          REAL FAILURE MODE
----------------  ----------------------------------  -------------------------------  ----------------------------------------------
Range             key in [a, b) -> shard N            Range scans, time-ordered data   Monotonic keys (timestamps, auto-increment IDs)
                                                                                       send all new writes to the last shard
Hash              hash(key) mod N or consistent       Even spread of many small keys   One whale key is still one shard; range queries
                  hashing ring                                                         fan out; naive mod-N reshuffles nearly every row
                                                                                       when N changes
Directory/lookup  table: key -> shard, consulted on   Moving one key without moving    Lookup becomes a critical hop: if it's slow,
                  every route                         the rest; per-tenant placement   stale or down, every request is too

If you remember one thing from this table, make it this: directory-based sharding was the escape hatch we never built. A lookup layer lets you move one tenant, split a whale or pin a customer to a region without rehashing everyone else. Nothing has to be routed through it on day one. What you need is the seam, so that later, adding a row to a lookup table costs less than rewriting your routing.

5. What broke when the business changed shape

It started with a move to usage-based features, and AI made it worse. Once customers could run agents, every tool call, token count and trace turned into a row. Write volume no longer followed seat count. It followed how much automation each customer ran.

• ▹A whale became a hot partition. One customer running big agent fleets wrote far more than everyone else on its shard combined. Hashing was no help because it was a single key value. The neighbours felt the latency, and our pages pointed at the shard rather than the cause.
• ▹Cross-tenant analytics became scatter-gather. Usage pricing, model-cost dashboards and abuse detection all needed answers across customers: 'top spenders this hour', 'which tenants hit this model'. Each of those queries fanned out to every shard, and p99 was whatever the slowest shard said it was.
• ▹Per-customer consistency had crept into the API contract. With each customer on one shard, we'd promised ordering and read-your-writes within an account. Then enterprise customers asked for parent organisations with many child accounts and consolidated billing. A guarantee we'd been getting for free from the physical layout now had to hold across shards.

None of these is a bug. In every case the shard key did exactly what we'd told it to do. We were just pointing it at a workload that didn't exist when we chose it.

6. Conway's law running backwards

Conway's law says a system mirrors the communication structure of the organisation that builds it. With shard keys the arrow also points the other way: the partition function ends up shaping the organisation.

• ▹A shard is an easy thing to own, so teams end up owning shard groups.
• ▹Alerts fire per shard, so on-call rotations line up with shard groups.
• ▹Dashboards, SLOs and capacity plans get built per shard, so planning happens in shard terms.
• ▹Everyone knows cross-tenant features are slow, so product scopes work 'per tenant', and that locks the key in further.

Give it a few years and changing the key means redrawing team boundaries, rotations, dashboards, runbooks and customer contracts. That's a socio-technical migration. Copying the data is the easy bit. It's also why founders and CTOs need to own this decision, rather than leaving it to whichever engineer writes the first migration.

7. What we actually did, and the order I'd do it in again

We kept customer_id. What we added was indirection in front of it, so every tenant no longer had to be treated the same way.

# Routing: lookup override first, hash as the default
def shard_for(tenant_id: str) -> str:
    override = placement.get(tenant_id)   # small, cached directory table
    if override:
        return override                   # e.g. 'shard-whale-01'
    # consistent hashing for everyone else; the ring must use a stable hash
    # (e.g. murmur3), not Python's built-in hash(), which is salted per process
    return ring.lookup(tenant_id)

• ▹Step 1: Add the lookup layer with no overrides. Every route passes through it and always falls through to the hash. You ship the seam with no change in behaviour, and you can watch its latency and cache hit rate in production.
• ▹Step 2: Build a separate read path for cross-tenant questions. Stream changes (CDC) into an analytics store and stop asking those questions of the OLTP shards. That takes the scatter-gather pressure off before anything moves.
• ▹Step 3: Move the whale onto its own shard. Dual-write to old and new locations, backfill, shadow-read and compare, flip the directory entry for reads, then stop writing to the old location. The directory controls placement, so only this one tenant moves.
• ▹Step 4: Rewrite the API contract on purpose. Write down which guarantees are per account and which aren't before the parent/child account feature decides it for you.
• ▹Step 5: Re-align ownership last. Assign on-call and dashboards by service and tenant tier, not by physical shard number, so the next move doesn't page the wrong people.
• ▹Doing it again, I'd ship step 1 on day one, when it costs almost nothing, and step 2 the first time anyone asked a cross-tenant question.

8. Design-review checklist: five questions before you commit

• ▹What will your top three queries be in two years? Today's don't count. Ask product and sales what they plan to sell, then check whether those queries would include the key.
• ▹What's the biggest single key value you could get? Estimate your largest plausible tenant, device or user. If one value could outgrow a shard, plan how you'd split it: a composite key, a sub-key or a directory override.
• ▹What has to fail together, and what mustn't? Choose the blast radius deliberately: per tenant, per region or per tier. Compliance and data residency are part of this answer.
• ▹Can you add indirection now while it's cheap? A lookup table that falls through to a hash costs very little on day one and a lot in year three.
• ▹Who will own each shard? If the answer is 'whoever owns shards 0-7', you're about to wire Conway's law into your pager rotation. Own by service or tier instead.

9. ASE aside: agents give the textbook answer

Ask an AI coding agent to design a sharded multi-tenant schema and it will usually suggest tenant_id or customer_id, because that's the textbook answer it has seen most. For today's workload that's often fine. It's also exactly how the five questions get skipped. The agent won't ask them unless you make it. Write them into your agent spec as hard constraints, so any design it proposes has to answer them in writing.

## Constraints: data partitioning (required in any sharding proposal)
- List the top 3 expected queries at 2-year scale; state which are single-shard.
- Estimate the largest single key value; propose a split strategy if it can exceed one shard.
- State the blast radius: what fails together.
- Routing MUST go through a lookup layer (directory override, then hash fallback).
- Propose ownership by service or tier, not by physical shard number.
- Do not finalise a shard key without a human design review.

10. Bridge to Day 27

When data sits on many shards, some operations need to touch more than one. Moving credits between a parent and a child account, billing across tenants and the dual-write window during a migration are all examples. A single shard can't guarantee any of these alone, so they need coordination. Tomorrow covers how that coordination works with distributed transactions and sagas, and what each one costs you.

Extend your knowledge

• ▹The partitioning (sharding) chapter of Martin Kleppmann's 'Designing Data-Intensive Applications'. Focus on the sections about skewed workloads and rebalancing.
• ▹The Vitess docs on VSchema and vindexes. Lookup vindexes are a production-grade version of the directory escape hatch from this lesson.
• ▹The Citus docs on choosing a distribution column and on tenant isolation, which walk through moving one large tenant onto its own shard.
• ▹The Figma and Notion engineering blog posts on horizontally sharding Postgres. Look at how they sequenced the migration and how they handled cross-shard queries.