Skip to content
←Data & Databases

// Data & Databases · 05

Sharding

Writes outgrow one serverleads toData split by shard key

Split one dataset across databases by a key, each owning a slice.

How it fits together

Sharding structureShard routerroute by tenant_idShard 1hash(tenant_id) % 3 = 0Shard 2hash(tenant_id) % 3 = 1Shard 3hash(tenant_id) % 3 = 2Cross-shard queryno tenant_id filterhits all 3 shards1 by tenant_idcostly fan-out
uses / callsfails / error path· numbered steps replay in order
  • Shard router uses / calls Shard 1 (1 by tenant_id)
  • Shard router uses / calls Shard 2
  • Shard router uses / calls Shard 3
  • Cross-shard query fails / error path Shard 1
  • Cross-shard query fails / error path Shard 2 (costly fan-out)
  • Cross-shard query fails / error path Shard 3

Spot it

  • Write load exceeds the largest instance
  • One table holds billions of rows
  • Tenants are isolated and rarely queried together

It costs

Cross-shard joins, transactions and rebalancing become your problem.

In code

CREATE TABLE orders (    tenant_id  BIGINT NOT NULL,    id         BIGINT NOT NULL,    total      NUMERIC(12, 2) NOT NULL,    PRIMARY KEY (tenant_id, id));​-- The shard key leads the primary key and every query filters on it,-- so each request touches exactly one shardSELECT id, totalFROM ordersWHERE tenant_id = @tenant_id  AND total > @min_total;
Go deeperThe full explanation and what to ask for · 1 min readExpand

Each shard holds the rows for a range or hash of the shard key. Hash keys spread load evenly but scatter related rows; range keys keep related rows together but invite hot spots, such as every new row landing on the newest shard.

The key is the hardest decision to undo. Pick one most queries filter on, like tenant_id, so a request touches a single shard. The classic mistake is sharding before exhausting the cheaper options: indexes, caching, read replicas, a bigger machine, table partitioning on one server.

Once sharded, a transaction across shards needs a saga or two-phase commit, unique constraints only hold within a shard, and moving data between shards needs a plan from day one.

Ask for it

  • shard key
  • hash vs range partitioning
  • hot partition
  • cross-shard query
  • resharding
  • tenant-per-shard

Our multi-tenant database is hitting write limits. Evaluate tenant_id as a shard key: list queries that would go cross-shard, how to route connections per tenant, how to move a big tenant to its own shard, and whether table partitioning or a sharding extension buys time first.

Connects to