Stackbook Logo
scalingestablished · high operational burden

Sharding by Tenant

Also known as: tenant-partitioning, shard-per-tenant

Intent

Distribute data and load across shards using the tenant identifier as the partition key.

Problem

A multi-tenant database grows beyond a single node, and a few large tenants degrade performance for everyone. Cross-tenant queries are rare, yet all data shares one failure and scaling domain.

Forces

  • •Queries almost always filter by tenant
  • •Tenant sizes are highly skewed
  • •Isolation and compliance may need data placement control
  • •Rebalancing live data is risky

Solution

Overview

Tenant sharding uses the tenant as the unit of partitioning. Because most SaaS queries belong to a single customer, almost all reads and writes hit exactly one shard, avoiding scatter-gather.

How it works

request(tenant=acme) -> router -> directory: acme -> shard-7
                                  -> connect shard-7 -> query WHERE tenant_id = acme

A directory table (cached in the router) stores tenant id, shard id and state. Hash-based routing is simpler but cannot move one tenant without remapping others; a directory allows targeted moves, and it can be combined with consistent hashing for initial placement.

Tenant moves follow a standard sequence: mark the tenant as migrating, bulk copy, replicate ongoing changes, pause writes briefly, verify checksums, update the directory, resume, and drop the old copy. Keep tenant id as the leading column of primary keys and indexes so data stays clustered and queries are efficient.

Provision shards by capacity, not count. Track per-tenant storage, queries per second and connection use so you can pre-emptively move a growing tenant.

Pitfalls

  • Treating all tenants equally. Skew is the norm; plan dedicated shards for the top few percent.
  • Queries without tenant id that fan out to every shard.
  • Global unique constraints and sequences that cannot be enforced per shard. Use tenant-scoped uniqueness or generated ids.
  • Running migrations by hand across hundreds of shards. Automate with progress tracking and rollback.
  • A single directory database without caching or replicas.

Key takeaways

  • Partition by tenant, route by directory, and put tenant id in every key.
  • Keep the ability to move one tenant without touching the rest.
  • Isolate whales and monitor per-tenant resource use.
  • Build analytics on a separate cross-tenant store.

✓ When to Use

  • •B2B SaaS where queries are scoped to one tenant
  • •Tenants with data residency or isolation requirements
  • •When one database no longer fits all tenants

✗ When Not to Use

  • •Workloads dominated by cross-tenant analytics or joins
  • •Few tenants where one is overwhelmingly large and cannot be split
  • •Small datasets that fit comfortably on one node

Pros

  • +Queries stay on a single shard
  • +Blast radius limited to a shard
  • +Enables per-tenant placement, upgrades and pricing tiers

Cons

  • −Large tenants create hot shards
  • −Cross-tenant reporting needs a separate pipeline
  • −Schema migrations must run across many shards

Cost Profile

Infrastructure

More database clusters; dedicated shards for big tenants

Operational

High: routing, migrations, rebalancing

Cognitive

High: directory and move tooling

Failure Modes

  • A whale tenant saturates its shard

  • Directory lookup outage blocks all routing

  • Failed tenant move leaves data split between shards

Real-World Examples

Alternatives

    Related Patterns

    • partitioning
    • multi-tenancy-patterns
    • tenant-isolation

    Competency Domains

    data stateeconomics evolution