Data Partitioning Sharding Strategist

---
name: data-partitioning-sharding-strategist
description: 'Design OLTP partitioning and sharding for WRITE/size scale in a multi-tenant SaaS — shard-key selection (tenant-as-shard-key and its hot-tenant limit), range/hash/list partitioning of large tables, resharding/rebalancing a hot tenant, and the cross-shard query/transaction costs you inherit — all gated behind the DON''T-SHARD-PREMATURELY rule: a well-indexed primary plus read replicas can serve a large SaaS while measured write, size, and maintenance limits permit, so shard ONLY on evidence of a real write or size ceiling. Use when a primary has hit (or has a dated forecast to hit) a write/size ceiling that indexing and replicas cannot fix, when one hot tenant dominates a shared table, or when choosing a shard key/partitioning scheme. Do NOT use for per-store ISOLATION scoping (multi-tenant-data-architect), ANALYTICAL-estate partitioning (warehouse-lake-architect), or deciding WHAT leaves the OLTP store (operational-vs-analytical-splitter). Reshapes production data — DESIGNS the plan, does not run it.'
---

# Data Partitioning & Sharding Strategist

Terms: **OLTP** means online transaction processing; **TPS** means
transactions per second; **ACID** means atomicity, consistency, isolation,
and durability. **SaaS** means software as a service.

## Purpose

Design OLTP partitioning and sharding for **write and size scale** — and,
just as often, prove it isn't needed yet. The deliverable is a shard-key
decision (with tenant-as-shard-key and its hot-tenant limit spelled out), a
range/hash/list partitioning scheme per large table, the inherited cross-shard
query/transaction cost, and a resharding/rebalancing runbook — all gated
behind the discipline that governs the whole skill: **don't shard
prematurely.** A well-indexed primary plus read replicas can serve a large
SaaS when measured write load, size, and maintenance windows permit. Sharding
multiplies operational complexity, so it is justified only by evidence of a
real write or size ceiling, never by
anticipation. This skill designs the reshape; it does not execute it against
production.

## Use When

- Use when: a primary has hit — or has a dated, evidenced forecast to hit — a
  real WRITE-throughput or table-SIZE ceiling (write TPS at saturation, table
  too large for maintenance windows, vacuum/index pain, connection ceiling)
  that indexing and read replicas cannot fix.
- Use when: one hot tenant's data has grown so large it dominates a shared
  table and needs its own partition/shard.
- Use when: choosing a shard key and partitioning scheme (range / hash / list)
  for a large, still-growing table.
- Use when: the current shard key is skewed and a reshard/rebalance is on the
  table.
- Do NOT use when: the concern is tenant ISOLATION/scoping per store (pooled
  vs siloed, the tenant boundary) — that is `multi-tenant-data-architect`;
  isolation scoping is not throughput sharding.
- Do NOT use when: partitioning the ANALYTICAL estate (warehouse/lake zones,
  marts, reporting tables) — that is `warehouse-lake-architect`.
- Do NOT use when: deciding WHAT workload should leave the OLTP store (replica
  vs warehouse vs cache) — that is `operational-vs-analytical-splitter`; this
  skill shards what STAYS on the operational store.
- Do NOT use when: there is no evidence of a ceiling — the honest answer is
  DON'T SHARD; recommend the single-primary + replicas + indexing path and stop.

## Inputs to Inspect

1. Write and read throughput at peak, and how close each is to the primary's
   demonstrated ceiling — the evidence that decides whether to shard at all.
2. Table sizes and growth rates; which tables cause maintenance pain (vacuum,
   reindex, backup windows) and which are merely large.
3. The largest tenant's share of the biggest tables — the hot-tenant signal.
4. Current indexes and the real query patterns against the hot tables; whether
   indexing, partial indexes, or read replicas have actually been tried.
5. The concrete pain: lock contention, slow writes, replication lag, or just
   size — different pains have different cheaper fixes.
6. Cross-table transaction boundaries and any global-uniqueness / foreign-key
   requirements — the things sharding makes expensive.

## Workflow

1. **Prove the ceiling first.** Demand evidence: write TPS at saturation, a
   table size causing real maintenance pain, or a dated growth forecast to a
   hard limit. No evidence → recommend the single-primary + read-replica +
   indexing path, explain that recommendation using the Choice guide below,
   name exactly what to measure to revisit, and STOP. This gate is the
   skill's core, not a formality.
2. **Exhaust the cheaper levers.** Indexing and partial indexes, read replicas
   for read pressure, and declarative table PARTITIONING within one node in
   this design (range/hash/list partitions — a table-SIZE and maintenance
   lever) all come
   before SHARDING (distribution across nodes). Distinguish the two explicitly:
   for this design, in-node table partitioning splits a table within one
   database node, while cross-node sharding distributes data across nodes.
3. **Select the shard key.** `tenant_id` is the natural key for multi-tenant
   SaaS — it co-locates a tenant's data and keeps most queries single-shard.
   State its limit up front: a single hot tenant then cannot be split by
   `tenant_id` alone and needs a composite/sub-key. Evaluate cardinality,
   skew, and query-locality for any candidate key.
4. **Choose the partitioning scheme per table.** Range (time/id ranges — good
   for time-series, risks hotspotting the newest partition), hash (even
   distribution, loses range-scan locality), or list (explicit buckets, e.g.
   region). Pick per table against its dominant query pattern, not globally.
5. **Price the cross-shard cost.** Name what breaks: cross-shard joins,
   cross-shard transactions (no cheap two-phase commit), global uniqueness and
   foreign keys, and fan-out aggregate queries. A design where common queries
   hit every shard has the wrong key — say so and revisit step 3.
6. **Design the reshard/rebalance runbook.** Splitting or moving a hot tenant:
   dual-write or backfill → verify (per-key counts/checksums) → cut over →
   update routing. Name the rollback condition and no-return gate for each
   stage; do not promise that every completed stage is reversible. This
   reshapes production data. This skill DESIGNS the runbook; it does not run it.
7. **Explain the scaling decision to the owner.** Define partitioning,
   sharding, replicas, and unfamiliar terms in plain language. Compare
   practical pros and cons of the viable levers, money, setup/migration time,
   and ongoing operations (including a $0 incremental path where one exists
   and is verified).
   Recommend the least complex option that meets the evidenced ceiling and
   say why; identify costs that still need measurement or current quotes.
   Then ask the owner one clear decision question; do not decide silently.

## Output Format

```
PARTITIONING / SHARDING DESIGN — <store/domain>
Ceiling evidence: <write TPS / table size / forecast — or "no evidence →
  DON'T SHARD; measure X" recommendation and stop>
Cheaper levers considered: <indexing / partial indexes / replicas / in-node
  partitioning — why each is or isn't sufficient>
Choice guide: <terms, scaling reason, money/setup/upkeep cost, pros and cons,
  recommended lever and why; unknown costs to verify>
Shard key: <chosen key; tenant_id + its hot-tenant limit; cardinality/skew>
Partitioning scheme per table: <table → range/hash/list → why, vs query pattern>
Cross-shard cost: <joins / transactions / uniqueness / fan-out — each named>
Reshard / rebalance runbook: <dual-write|backfill → verify → cut over → route;
  rollback condition/no-return gate per stage — DESIGNED, not executed>
Open questions / risks: <each with risk-if-wrong / who answers>
```

## Validation Checklist

- [ ] The write/size ceiling is evidenced (numbers or a dated forecast), not
      assumed; with no evidence the design recommends NOT sharding.
- [ ] Cheaper levers (indexing, replicas, in-node partitioning) are considered
      and explicitly ruled sufficient or insufficient before sharding.
- [ ] Owner-facing options define terms, explain the ceiling, compare money,
      setup and upkeep costs and pros/cons, and justify the recommendation.
- [ ] Partitioning (one node) and sharding (across nodes) are not conflated.
- [ ] The shard key is justified against cardinality, skew, and query-locality;
      the tenant_id hot-tenant limit is stated.
- [ ] The partitioning scheme is chosen per table against its query pattern.
- [ ] Cross-shard joins, transactions, uniqueness, and fan-out costs are named,
      and common queries stay single-shard.
- [ ] The reshard/rebalance plan names rollback conditions and no-return gates
      per stage and is designed, not executed; production-data moves route
      through human approval.

## Gotchas

- Sharding chosen before the primary is even well-indexed: the expensive fix
  applied before the cheap one. The ceiling check exists to stop exactly this.
- A hash shard key kills range scans; a range key hotspots the newest
  partition. There is no free key — every scheme trades away some access pattern.
- `tenant_id` sharding is defeated by one giant tenant: that tenant's data
  still lands on one shard. Plan the sub-key (or a cell/silo) before it happens.
- Without distributed transaction coordination, code that assumes one local
  ACID commit across shards can half-apply. Treat that as a correctness risk,
  not merely a performance cost.
- Resharding is a migration project, not a config change; teams routinely
  underestimate it by an order of magnitude.
- Global uniqueness (a unique email across all tenants) stops being free once
  the space is sharded — it needs a separate global index or a different key.

## Stop Conditions

- No evidence of a write/size ceiling → recommend the single-primary +
  replicas + indexing path and stop; do not shard on anticipation.
- The reshard/rebalance must run against production (dual-write, backfill,
  cutover on live tables) → this skill DESIGNS the runbook; executing it
  follows `human-approval-boundary`. Do not run it.
- A single hot tenant is the whole problem → the answer may be a dedicated
  silo/cell (`multi-tenant-data-architect` / `cell-based-architecture-designer`)
  rather than sharding the shared space; surface that before designing shards.
- The workload is analytical (scans/aggregations) rather than transactional →
  route to `operational-vs-analytical-splitter` and `warehouse-lake-architect`;
  do not shard the OLTP store to serve reporting.

## Supporting Files

- `evals/evals.json` — behavior cases: the evidenced-ceiling shard design, the
  don't-shard-prematurely refusal, the hot-tenant/tenant_id-limit edge, and the
  cross-shard-transaction correctness catch.
- `evals/trigger-evals.json` — discrimination against `multi-tenant-data-architect`
  (isolation scoping), `warehouse-lake-architect` (analytical partitioning), and
  `operational-vs-analytical-splitter` (what leaves the OLTP store).
- No `references/` — the shard-key and scheme guidance above is the complete
  procedure; detail lives in the produced artifacts.