Skip to content

Database Sharding for Multi-Tenant Backends

A database can be well indexed, correctly configured, and still reach a point where one machine cannot provide enough storage, memory, or write capacity. Sharding distributes subsets of a dataset across independent database nodes so that storage and work can grow horizontally.

That extra capacity comes with a price: the application must know where data lives, queries can cross network boundaries, and operations that were local to one database become distributed workflows. Sharding is therefore a data-model and application-architecture decision, not a configuration switch.

This chapter focuses on multi-tenant backend services. It explains when to shard, how to choose a key, how to preserve tenant-local operations, and how to move data safely as the topology changes.

1. Confirm that sharding solves the actual bottleneck

Do not begin with a target number of shards. First identify the resource that is limiting the current database:

  • CPU saturation caused by query execution or write amplification.
  • A working set that no longer fits in memory.
  • Storage capacity, I/O throughput, or maintenance windows that are no longer acceptable.
  • Write contention on a small set of rows or indexes.
  • A growth rate that exceeds the practical capacity of one database node.

Use query plans, database statistics, connection-pool waits, storage metrics, and representative load tests to locate the constraint. An inefficient query, missing index, oversized result, or unbounded application workload will not become efficient merely because the same query is sent to more machines.

Consider the simpler options first:

  1. Fix query shape, indexes, and transaction lifetime.
  2. Increase the capacity of the existing database when vertical scaling is still economical.
  3. Add a cache for repeatable reads with an explicit freshness policy.
  4. Use read replicas when read capacity is the problem and replica consistency is acceptable.
  5. Use table partitioning when data lifecycle or pruning is the problem.
  6. Introduce distributed sharding when the dataset or write workload must be spread across independent database nodes.

A useful design review records the measured bottleneck, expected growth, and the operational cost of each alternative. "We have a large table" is not, by itself, a sharding requirement.

2. Separate partitioning, replication, and sharding

These terms solve different problems:

Technique What is distributed Main purpose What it does not provide by itself
Table partitioning Rows of one logical table into smaller tables or partitions Pruning, retention, and maintenance More independent database servers
Replication Copies of the same data Availability and, in some designs, read scale Distribution of write ownership
Sharding Different subsets of data across independent database nodes Horizontal storage and write scale Transparent cross-shard joins or global transactions

PostgreSQL declarative partitioning presents a partitioned table as one logical table and routes rows to child partitions based on a range, list, or hash key. Partition pruning can avoid scanning partitions that cannot match a query. The partitions are still part of the same PostgreSQL database deployment; partitioning should not be described as adding database servers. See the PostgreSQL partitioning documentation in the references.

Sharding adds a placement and routing problem. A request must identify the shard that owns the relevant rows, and the application or a database router must send the operation there. Each shard commonly has its own storage, indexes, connection limits, and availability boundary.

A system can use both techniques. For example, a tenant's events may be assigned to one database shard, while each shard partitions its local events by month for retention and time-range queries. The two levels should have separate, explicit purposes.

Replication is also commonly used inside each shard for high availability. A primary and its replicas hold copies of that shard's data; they do not turn the replica set into multiple independent write shards.

3. Choose a shard key from access patterns

A shard key determines where a row belongs. It also influences which queries can be routed to one shard, which joins can remain local, and how evenly work is distributed.

Start with a query matrix rather than a field name:

Access pattern Important question
Tenant reads and writes Does nearly every request include a trusted tenant identifier?
Joins between tenant tables Can related rows be placed together?
Cross-tenant administration How often must an operation visit every tenant?
Global search and analytics Can these queries use a separate index or analytical store?
Uniqueness and references Which invariants must be enforced across shard boundaries?
Hot tenants Can one tenant generate enough traffic to saturate its shard?

For a SaaS product where most operations are scoped to one organization, tenant_id is often a strong candidate. If orders, customers, and order items are distributed by the same tenant key, a tenant-scoped join can be routed to the node holding that tenant's data. Citus documents this pattern as co-location: rows with the same distribution value are placed together so related relational operations can stay on one worker.

A good shard key balances several properties:

  • Cardinality: it must have enough distinct values to use the available shard space. A status field with three values is usually a poor choice.
  • Frequency: high cardinality does not guarantee balance if a small number of values account for most of the rows or requests.
  • Monotonicity: an ever-increasing key can concentrate new writes at one end of a range-based layout.
  • Query coverage: common requests should include the key so the router can target a small number of shards.
  • Stability: changing the key for an existing row can mean moving data and updating every dependent reference.

These are not independent checks. A random-looking key may distribute writes evenly but scatter every query. A tenant key may keep joins local but allow one unusually large tenant to become a hotspot. MongoDB's shard-key guidance explicitly considers cardinality, frequency, monotonicity, and query patterns; the same reasoning is useful even when the database product is different.

Avoid selecting a key because it is already the primary key. A globally unique order ID can be useful for identity while tenant_id remains the distribution key. Identity and placement are related design decisions, but they do not have to be the same column.

4. Choose a routing strategy

Three common strategies are useful to understand.

Range-based placement

A range strategy assigns ordered key intervals to shards. It can make range queries efficient when the range matches the workload, but uneven value distributions can create imbalanced shards. Monotonically increasing values can also send most new writes to the newest range.

Hash-based placement

A hash strategy maps a key to a bucket or shard using a deterministic hash. It can spread many keys across the available space, but a range query may need to visit many shards because adjacent key values are not necessarily colocated.

Do not use a language's process-randomized hash as a persistent placement contract. Python's built-in hash(), for example, is not a stable cross-process routing format. Use a documented deterministic algorithm and keep its parameters stable.

Directory-based placement

A directory stores an explicit mapping such as tenant_id → shard_id. It makes tenant moves and dedicated placement easier, but the directory becomes part of the routing path. It needs availability, caching, versioning, and a clear update protocol. A stale directory entry can send a write to the wrong owner.

Use virtual buckets to decouple tenants from physical shards

A practical compromise is to hash a tenant into a fixed set of virtual buckets, then map each bucket to a physical shard:

  1. Compute a stable bucket from the tenant identifier.
  2. Look up the bucket's current shard owner in a versioned routing table.
  3. Route the request using that mapping.
  4. Move selected buckets when capacity needs to change, instead of changing the hash modulus for every tenant.

If an application uses hash(tenant_id) % physical_shard_count, changing the number of physical shards can remap a large fraction of tenants. A fixed virtual-bucket space limits placement changes to the buckets deliberately reassigned.

The following reference router demonstrates the separation. It is an in-process example, not a distributed control plane. A production service must distribute one authoritative routing-table version, reject stale writes where ownership has changed, and coordinate data movement before publishing a new mapping.

from dataclasses import dataclass
from hashlib import blake2b
from types import MappingProxyType
from typing import Mapping


@dataclass(frozen=True)
class RoutingTable:
    epoch: int
    bucket_count: int
    bucket_to_shard: Mapping[int, str]

    def __post_init__(self) -> None:
        if self.epoch < 1:
            raise ValueError("epoch must be positive")
        if self.bucket_count < 1:
            raise ValueError("bucket_count must be positive")

        mapping = dict(self.bucket_to_shard)
        if set(mapping) != set(range(self.bucket_count)):
            raise ValueError("every virtual bucket must have exactly one owner")
        if any(not isinstance(value, str) or not value.strip()
               for value in mapping.values()):
            raise ValueError("shard names must be non-empty strings")

        object.__setattr__(self, "bucket_to_shard", MappingProxyType(mapping))

    def bucket_for(self, tenant_id: str) -> int:
        if not isinstance(tenant_id, str) or not tenant_id:
            raise ValueError("tenant_id must be a non-empty string")

        digest = blake2b(tenant_id.encode("utf-8"), digest_size=8).digest()
        return int.from_bytes(digest, "big") % self.bucket_count

    def shard_for(self, tenant_id: str) -> str:
        return self.bucket_to_shard[self.bucket_for(tenant_id)]

Run the tests from the repository root:

python -m unittest discover -s interview-handbook/examples/python/sharding -p 'test_*.py' -v

The code deliberately does not move rows when a bucket owner changes. Routing configuration and data migration are separate concerns. Publishing a new owner before the data is ready can create missing reads or split writes between two databases.

5. Model tenant data for locality and integrity

A tenant-scoped schema should carry the tenant key through related records. A local database can then enforce tenant-scoped identifiers and relationships:

CREATE TABLE orders (
    tenant_id           text NOT NULL,
    order_id            uuid NOT NULL,
    external_reference  text NOT NULL,
    created_at          timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, order_id),
    UNIQUE (tenant_id, external_reference)
);

CREATE TABLE order_items (
    tenant_id    text NOT NULL,
    order_id     uuid NOT NULL,
    line_number  integer NOT NULL,
    sku          text NOT NULL,
    PRIMARY KEY (tenant_id, order_id, line_number),
    FOREIGN KEY (tenant_id, order_id)
        REFERENCES orders (tenant_id, order_id)
);

The composite foreign key prevents an item for one tenant from referencing an order belonging to another tenant. The same pattern appears in the runnable PostgreSQL example.

Remember what these constraints guarantee: they are enforced by the database that owns the rows. A unique constraint on every individual shard does not automatically provide global uniqueness across all shards. Use globally unique identifiers, a dedicated allocation mechanism, or a carefully scoped uniqueness service when the business contract truly requires global uniqueness.

The application must still derive the tenant from authenticated identity and include it in every tenant-scoped query. A client-supplied tenant ID is a requested resource scope, not proof of access. Sharding is not an authorization boundary.

Co-locate tables that are commonly joined by the same tenant key. Small, slowly changing reference data can sometimes be copied to every shard, but then update propagation and version compatibility become operational responsibilities. Avoid copying large or frequently changing tables without measuring the write and consistency costs.

6. Understand the cost of cross-shard queries

A query containing the shard key can often be routed to one shard. A query without it may become a scatter-gather operation: the router sends work to multiple shards and merges the results.

Scatter-gather can be reasonable for bounded administrative work or parallel analytical queries. It is expensive as the default path for interactive requests because:

  • Latency depends on the slowest participating shard and the merge step.
  • Every additional shard adds network, connection, and coordination work.
  • Global sorting, pagination, and top-N queries require careful merge semantics.
  • A partial shard failure can turn a query into an incomplete result unless the API defines a failure policy.

Do not hide this cost behind a repository method that looks like an ordinary local query. Make fan-out explicit in the service contract and monitor how many shards each request touches.

For frequent cross-tenant reporting, consider a separate analytical store, precomputed aggregates, or a search/indexing pipeline. Do not make every customer-facing request query every transactional shard just to produce a global dashboard.

Cross-shard transactions have a similar boundary. A transaction that touches one shard can use that database's local guarantees. A workflow that must atomically update several shards requires distributed coordination or a different business design. Prefer keeping an aggregate's invariant within one shard. Where that is impossible, define an explicit workflow with idempotent steps, compensation, and reconciliation rather than assuming a local transaction can cover remote databases.

7. Handle skew and the unusually large tenant

Tenant-based placement works well when many tenants have broadly comparable workloads. Real systems rarely stay perfectly uniform.

Measure both data size and request intensity per tenant. A tenant with modest storage can still be hot because it generates frequent writes, expensive reports, or a small number of heavily contended records.

Common responses include:

  • Dedicated placement: move a large tenant to its own shard. This isolates its resource use but increases fleet and operational overhead.
  • Sub-sharding: split one tenant by a second stable dimension such as account, project, or time range. This increases parallelism but turns some tenant-wide queries into fan-out operations.
  • Write sharding: distribute a hot append workload across multiple keys or buckets. Reads that need the combined view must merge the buckets.
  • Admission controls: protect each shard from overload while the data model or placement is being corrected.

Do not automatically split every tenant. Keep the default path simple and introduce special placement only when measured skew justifies it. Record the reason for a tenant move and make the routing and ownership state observable.

8. Plan resharding as a data migration

Adding a database node is not the same as moving existing data to it. A safe resharding plan needs a defined source owner, target owner, copy position, change-capture mechanism, routing epoch, verification step, and rollback policy.

A general migration sequence is:

  1. Create the destination. Provision the target database, schema, indexes, credentials, monitoring, backups, and capacity.
  2. Copy a consistent snapshot. Copy the selected tenants or virtual buckets while recording the source position that defines the snapshot boundary.
  3. Capture changes. Continue applying writes that occur after the snapshot boundary using a database-supported replication or migration mechanism. Do not improvise independent dual writes without a conflict and retry protocol.
  4. Verify the target. Compare row counts, checksums, key ranges, and business invariants. Counts alone can miss missing or duplicated rows.
  5. Catch up and fence. Bring the target close to the source, stop or fence writes to the old owner for the cutover boundary, and confirm the target has applied the final changes.
  6. Switch routing atomically. Publish a new routing-table epoch and make services reject stale ownership where necessary. Ensure in-flight requests cannot continue writing to the old owner after cutover.
  7. Observe before cleanup. Monitor errors, latency, replication lag, and reconciliation results. Retain the old copy and a tested rollback path for an agreed period.

Vitess documents a product-specific reshard workflow that creates a VReplication workflow, monitors progress, verifies copied data with VDiff, switches traffic, and only then completes cleanup. That is a useful illustration of why copy, verification, cutover, and cleanup are separate phases—not a universal command sequence for every database.

Rollback needs particular care. After the new shard accepts writes, pointing traffic back to the old shard can lose those writes unless reverse replication or another reconciliation path is already operating. Define the rollback boundary before the migration begins.

9. Operate shards as separate failure and capacity domains

A sharded service needs per-shard visibility. At minimum, track:

  • CPU, memory, disk capacity, I/O latency, and storage growth.
  • Read and write throughput, transaction duration, lock waits, and connection-pool usage.
  • p50, p95, and p99 latency by shard and by request class.
  • Tenant count, data size, and request distribution per shard.
  • Router cache age, routing epoch, unknown-owner errors, and cross-shard fan-out.
  • Replica health and lag within each shard when replication is used for availability.

Connection pools deserve special attention. If every application instance opens a large pool to every shard, the total connection count can grow as the product of application instances, shards, and pool size. Use bounded per-shard pools and calculate the database connection budget before increasing the shard count.

A single shard outage should affect the tenants or requests that depend on it, not silently produce incomplete global results. Define whether the API fails the request, serves explicitly stale data, or degrades a non-critical feature. Retry only when the operation is safe and the retry budget leaves enough time for the request to finish.

Backups, restore tests, schema changes, and failover procedures must work for every shard. A design is not horizontally scalable if routine operations require an engineer to perform a different manual procedure on each database.

10. Interview questions

Q1. What is the difference between partitioning and sharding?

Answer: Partitioning splits one logical table into smaller pieces managed by the same database system, often to improve pruning or data lifecycle operations. Sharding places different subsets of data on independent database nodes and requires routing. A system can use both, but partitioning alone does not add independent database servers.

Q2. How would you choose a shard key for a multi-tenant SaaS application?

Answer: Start with the query matrix. If most reads, writes, and joins are tenant-scoped, tenant_id provides useful locality. Check cardinality, tenant frequency, skew, and whether cross-tenant queries are common. Test the candidate against representative traffic rather than choosing it from the schema alone.

Q3. Why can a high-cardinality key still produce a hot shard?

Answer: Cardinality counts distinct values, not how often they occur. A few tenants may account for most requests or rows. A monotonic range key can also concentrate new writes. Measure frequency and load distribution, then consider dedicated placement, sub-sharding, or a different key.

Q4. Why is hash(tenant_id) % shard_count difficult to scale?

Answer: Changing the physical shard count changes the modulus and can remap many tenants. A fixed virtual-bucket space with a separate bucket-to-shard map allows selected buckets to move without changing every tenant's hash calculation.

Q5. How do you keep joins efficient?

Answer: Distribute related tables using the same key and co-locate rows that participate in common joins. Include the shard key in the query. If the query must cross shards, make the fan-out explicit and consider a precomputed or analytical representation.

Q6. How do you preserve uniqueness across shards?

Answer: A unique constraint on one shard only protects that shard's rows. Use globally unique identifiers when uniqueness is about identity, or a dedicated coordination mechanism when the business rule requires a globally unique natural key. State the scope of every constraint.

Q7. How would you reshard without losing writes?

Answer: Copy a consistent snapshot, capture subsequent changes, verify the target, fence the source at cutover, wait for the target to catch up, and switch routing through a versioned ownership map. Keep a rollback strategy that accounts for writes accepted after cutover.

Q8. When should you avoid sharding?

Answer: Avoid it when query tuning, vertical capacity, caching, read replicas, or table partitioning can meet the workload. Sharding adds routing, cross-shard query, migration, backup, and incident complexity. The operational cost must be justified by a measured scaling constraint.

Design checklist

  • [ ] Identify a measured single-node capacity limit.
  • [ ] Compare sharding with indexing, vertical scaling, caching, replicas, and table partitioning.
  • [ ] Build a query matrix and select a key that balances locality with distribution.
  • [ ] Model tenant-scoped keys and relationships consistently.
  • [ ] Define routing-table ownership, versioning, and cache invalidation.
  • [ ] Test cross-shard queries, hot tenants, and partial shard failures.
  • [ ] Budget connections, storage, backups, and maintenance per shard.
  • [ ] Rehearse snapshot copy, change catch-up, verification, cutover, and rollback.

References