What happens when a database gets too big for one server? | Database Sharding & Partitioning

Scenario context

When a database outgrows a single server's capacity, horizontal scaling becomes necessary. Partitioning splits tables locally within a single database instance, while Sharding distributes data across completely separate physical database servers. Sharding keys determine how records are routed, utilizing hash-based, range-based, or lookup-based strategies.

Observable outcome: - Cons: - Re-sharding Complexity: If you add a new shard, the modulo denominator changes.

Stage 2 — Deep Dive: Consistent Hashing and the Hash Ring

• To solve the re-sharding limitation of algorithmic sharding (where adding a node requires migrating almost all data), architects use Consistent Hashing .
• Consistent hashing maps both the database shards and the sharding keys onto a logical circular space called a Hash Ring (typically from $0$ to $2^{32} - 1$).
• Mapping Nodes: Each database shard is hashed based on its IP or hostname, and placed at a coordinate on the ring.
• Mapping Keys: When a record is inserted, its sharding key is hashed and mapped to a coordinate on the ring.

            Shard A (Hash: 1000)
             /                \
   Key 1 (Hash: 200)           Key 2 (Hash: 1500)
           /                    \
  Shard C (Hash: 3000) ------- Shard B (Hash: 2000)

Observable outcome: Mapping Keys: When a record is inserted, its sharding key is hashed and mapped to a coordinate on the ring.

Stage 3 — Partitioning Inside One Database

• Partitioning divides one logical table into physical pieces managed by the same database.
• Horizontal partitioning splits rows; vertical partitioning separates groups of columns, often moving large or rarely accessed fields away from the hot row.
• PostgreSQL supports range, list, and hash partitions while preserving one SQL-facing table.
• ~~~sql CREATE TABLE orders ( id bigint NOT NULL, tenant id bigint NOT NULL, created at timestamptz NOT NULL, total cents bigint NOT NULL ) PARTITION BY RANGE (created at); CREATE TABLE orders 2026 07 PARTITION OF orders…

Observable outcome: ~~~sql CREATE TABLE orders ( id bigint NOT NULL, tenant id bigint NOT NULL, created at timestamptz NOT NULL, total cents bigint NOT NULL ) PARTITION BY RANGE (created at); CREATE TABLE orders 2026 07 PARTITION OF orders…

Stage 4 — Choosing a Shard Key

• A useful shard key has high cardinality, stable ownership, even traffic distribution, and alignment with common queries.
• Evaluate data volume and request rate separately: ten equally sized tenants are not balanced if one produces 80% of traffic.
• Before committing, replay a representative query log against the proposed key.
• Measure the percentage of single-shard queries, maximum-to-average shard load, largest tenant, and expected growth for at least the next capacity horizon.

Observable outcome: Measure the percentage of single-shard queries, maximum-to-average shard load, largest tenant, and expected growth for at least the next capacity horizon.

Stage 5 — Online Resharding Without a Long Outage

• Resharding is a migration, not a configuration toggle: 1.
• Create the destination shard and a versioned shard map.
• Snapshot-copy the selected key range while normal writes continue.
• Capture concurrent changes through CDC or an outbox.

Observable outcome: Capture concurrent changes through CDC or an outbox.

Stage 6 — Cross-Shard Operations

• Global uniqueness can use a globally unique ID generator, a dedicated registry, or IDs scoped by shard.
• Cross-shard pagination needs a cursor per shard plus a merge step; an offset applied independently to every shard is neither efficient nor stable.
• Aggregations should usually flow into a separate analytical store rather than fan out across every transactional shard on each request.
• For financial workflows, prefer keeping one invariant inside one shard.

Observable outcome: For financial workflows, prefer keeping one invariant inside one shard.

End-to-end outcome

After working through the stages, you should be able to explain:

• • Partitioning improves manageability inside one database; sharding distributes ownership across databases.
• • A shard key is an operational commitment encoded in every important query.

Alternatives considered

Every database scaling journey begins with a single server running a database engine like PostgreSQL or MySQL. In the early stages, this setup is simple and robust. However, as the application gains traction, read and write traffic increases, and the database file size grows. Eventually, the server will saturate its hardware limits (CPU, Memory, Disk I/O).

At this stage, architects face a choice between two scaling vectors:

1. Vertical Scaling (Scale Up)

Vertical scaling involves adding more power to the existing server: upgrading to faster CPUs, installing more RAM, or moving to higher-throughput NVMe SSD storage arrays.

2. Horizontal Scaling (Scale Out)

Horizontal scaling involves distributing the database workload across multiple independent database servers (nodes).

It is critical to distinguish between database partitioning and database sharding. While they both divide a large dataset into smaller subsets, they operate at different architectural boundaries.

It is critical to distinguish between database partitioning and database sharding. While they both divide a large dataset into smaller subsets, they operate at different architectural boundaries.

Database Partitioning

Database partitioning divides a single large table into smaller, more manageable physical subsets (partitions) within the same database server .

Database Sharding

Database sharding goes a step further: it distributes those table partitions across completely separate physical database instances running on different servers .

Decision Guide: Selecting the Sharding Strategy

Use this matrix to select the sharding strategy for your workload: - Use Algorithmic (Hash-Based) Sharding if: - Your read/write workload is distributed randomly and you need to prevent hot spots.

Failure and degraded modes

Sharding is an architectural last resort. While it solves scaling limits, it introduces severe design challenges:

• Detection: Sharding is an architectural last resort. While it solves scaling limits, it introduces severe design challenges:
• User or operator impact: Sharding is an architectural last resort. While it solves scaling limits, it introduces severe design challenges:

1. Cross-Shard Joins

• Detection: You cannot execute a standard SQL JOIN query across tables located on different physical shards.
• User or operator impact: You cannot execute a standard SQL JOIN query across tables located on different physical shards.

2. Distributed Transactions (Two-Phase Commit)

• Detection: Maintaining ACID properties across multiple database nodes requires a Distributed Transaction Coordinator and protocols like Two-Phase Commit (2PC) .
• User or operator impact: Maintaining ACID properties across multiple database nodes requires a Distributed Transaction Coordinator and protocols like Two-Phase Commit (2PC) .

3. Distributed ID Generation

• Detection: In a single-node database, you can rely on auto-incrementing integer primary keys.
• User or operator impact: In a single-node database, you can rely on auto-incrementing integer primary keys.

Trade-offs: When to Shard

• Detection: Sharding should only be implemented when all other optimization strategies have been exhausted: - Before Sharding, verify if you can: - Optimize Indexing: Ensure your read paths are covered by optima…
• User or operator impact: Sharding should only be implemented when all other optimization strategies have been exhausted: - Before Sharding, verify if you can: - Optimize Indexing: Ensure your read paths are covered by optima…