Sharding and Partitioning

Both split a table into smaller pieces. Partitioning keeps the pieces within one database. Sharding splits across machines. Different problems, different trade-offs, often confused in interviews.

For when each fits, distributed transaction trade-offs, and operational reality.

The distinction at a glance

Partitioning first; shard only when you have to.

Partitioning (single DB)

One logical table; multiple physical pieces. The DB engine routes queries to the relevant partition(s).

Vertical partitioning

Split columns into separate tables, joined on PK.

-- "hot" frequently-read columns
CREATE TABLE users_core (
    id BIGINT PRIMARY KEY,
    email TEXT,
    name TEXT
);

-- "cold" rarely-read columns
CREATE TABLE users_profile (
    id BIGINT PRIMARY KEY REFERENCES users_core,
    bio TEXT,
    avatar BYTEA          -- could be megabytes
);

Win: hot table fits in cache; rare reads pay the JOIN cost. Useful for tables with a mix of small-frequent and large-rare columns.

Horizontal partitioning (Postgres declarative)

Split rows across child tables.

CREATE TABLE events (
    id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Partitioning strategies:

Wins from partitioning

• Partition pruning — WHERE created_at >= '2026-02-01' only scans the Feb partition.
• Cheap drops — DROP TABLE events_2025_12 is instant; DELETE WHERE created_at < ... would take hours.
• Smaller indexes per partition — each partition’s index fits in cache.
• Parallel maintenance — vacuum / analyze / reindex per partition concurrently.
• Sequential scans on hot partition — when you’re only touching recent data.

Pitfalls

• Each query must include the partition key in WHERE for pruning to kick in. EXPLAIN to verify.
• UNIQUE constraints must include the partition key.
• Foreign keys referencing a partitioned table need PG 12+.
• Cross-partition UPDATE (changing a row’s partition key) needs PG 11+.
• Too many partitions (>1000) cause planner overhead.

When to partition

• Time-series data with old data dropped regularly.
• Tables exceeding ~10 GB where most queries scope to a slice.
• Large tables where index size matters.
• Audit logs / events with predictable archival.

For a million-row table queried by user_id, partitioning probably won’t help. For a billion-row time-series with WHERE created_at >= '...' queries, it’s transformative.

Sharding (multiple DBs)

Data spans multiple database instances. Application or proxy chooses where to read/write.

Sharding strategies

Each strategy trades off: hash is simple but range queries hit every shard; range allows efficient ranges but creates hot spots; directory adds operational complexity.

Consistent hashing — solving the resharding problem

Naive hashing (hash(key) % N): adding a shard means rehashing — every key may move. Catastrophic at scale.

Consistent hashing: nodes and keys map to points on a hash ring. Each key goes to the nearest node clockwise. Adding a node only moves keys in its arc — ~K/N keys, not all of them.

       0
       |
   N1--+--N2
       |
       N3
       |
     (key X → walks clockwise → lands on N2)

Add N4:
       0
       |
   N1--+--N4
       |    \
       |     N2
       N3
       
   Only keys between N1 and N4 move (formerly went to N2).

Virtual nodes: each physical node owns many points on the ring. Smooths distribution and makes adding/removing nodes affect many keys proportionally.

Used by: DynamoDB, Cassandra, Riak, Memcached client libraries.

Pre-sharding

Hash to many virtual shards (e.g., 1024) up front; place virtual shards on physical nodes. Rebalancing moves virtual shards, not individual keys.

1024 virtual shards
4 physical nodes initially: each owns 256 virtual shards
Add a node → migrate 1024/5 ≈ 205 virtual shards to the new node

Easier to operate than pure consistent hashing. Used by Vitess, Discord, Slack.

Cross-shard queries are hard

Once data spans shards:

JOINs across shards: application-side scatter-gather:

# Pseudocode
def get_user_with_orders(user_id):
    user_shard = shard_for(user_id)
    user = user_shard.query("SELECT * FROM users WHERE id = %s", user_id)
    # Orders might be on different shards (if sharded by order_id)
    all_shards = list_shards()
    orders = []
    for shard in all_shards:
        orders.extend(shard.query("SELECT * FROM orders WHERE user_id = %s", user_id))
    return user, orders

Co-locate by shard key to avoid this: shard both users and orders by user_id so they’re on the same shard.

Aggregates: scatter-gather + merge in application:

def total_orders():
    return sum(shard.query("SELECT count(*) FROM orders") for shard in shards)

For sums, counts, averages this works. For percentiles, top-K, complex group-by, it gets painful. Use OLAP tools (BigQuery, ClickHouse) for analytics; don’t try to do them on sharded OLTP DBs.

Unique constraints: only enforced within a shard. Use globally-unique IDs:

• UUIDs (random, easy, larger).
• Snowflake-style 64-bit IDs (timestamp + machine + sequence).
• A central ID-generation service.

Foreign keys: broken across shards. Replace with soft references (just store the ID; no DB-level FK).

Distributed transactions — the painful part

You write to two shards in one user action. How do you keep them atomic?

Two-Phase Commit (2PC)

1. Coordinator → Shards: PREPARE
2. Each shard locks rows, writes to WAL, replies VOTE_COMMIT or VOTE_ABORT
3. Coordinator: if all vote commit → COMMIT; else → ABORT
4. Each shard finalizes

Problems:

• Slow — multiple round trips, locks held throughout.
• Coordinator is SPOF — if it crashes after phase 1, shards hold locks indefinitely until manual intervention.
• Increasing failure surface — N+1 things that can fail (each shard plus coordinator).
• Operational nightmare — recovery from partial commits is painful.

Modern systems avoid 2PC for OLTP. Use it only for rare, critical operations where atomicity is strictly required.

Saga pattern — eventual consistency alternative

Series of local transactions, each with a compensating action if a later one fails.

1. Reserve inventory               → if fail, abort
2. Charge payment                  → if fail, release inventory
3. Create shipment                 → if fail, refund payment, release inventory
4. Send confirmation email         → if fail, log; no compensation

Each step is a local transaction on one shard. Failure triggers compensating actions to roll back earlier steps. Eventually consistent.

Pros: no 2PC overhead; scales horizontally. Cons: complex to implement correctly; compensations can themselves fail.

See Event-Driven Architecture and Sagas.

Outbox pattern — atomic DB + event publishing

BEGIN;
INSERT INTO orders (...);
INSERT INTO outbox (event_type, payload) VALUES ('OrderCreated', '{...}');
COMMIT;

A separate process reads from outbox and publishes events to other shards / services. Atomic within one DB transaction; eventually consistent across shards.

Used heavily in microservices + event-driven architectures.

Replication vs sharding — different problems

Often confused. They solve different scaling axes:

Most apps scale fine with read replicas for read-heavy workloads. Sharding is for when one machine can’t hold the data (multi-TB) or handle the write throughput.

Both can coexist: shard for capacity, replicate each shard for HA.

Choosing the shard key — the single most important decision

Bad shard key = hot shard = the whole point lost.

Good keys:

• High cardinality — many distinct values so traffic spreads.
• Even access distribution — no value gets disproportionate load.
• Co-locates data accessed together — a user’s data on one shard, not spread across all.
• Stable — doesn’t change, so re-sharding isn’t constant.

Common bad keys:

For SaaS multi-tenant: tenant_id is usually right. Each tenant’s data is co-located; queries scoped to one tenant hit one shard.

For social/consumer: user_id. Their posts, comments, likes — co-located.

Real-world examples

Instagram: shard by user_id with pre-sharding (logical → physical mapping). They started with one Postgres and outgrew it; the sharding strategy let them scale to billions of users without rewriting.

Discord: ScyllaDB (Cassandra-compatible) sharded by user_id. Migrated from MongoDB after hitting scaling limits.

Pinterest: Sharded MySQL with consistent hashing. Each shard ID is a 64-bit composite of (shard, type, local_id), letting them shard once and not worry about cross-shard references.

Slack: Vitess (MySQL sharding) sharded by workspace_id. Each workspace’s data is co-located.

Twitter (X): Manhattan + Gizzard. Shard by user_id for most things; timelines fanned out via separate services.

Common pattern: shard by the natural “tenant” boundary (user, workspace, organization). Avoids cross-shard queries for the 99% case.

Schema migrations across shards

In a single DB: ALTER TABLE ... ADD COLUMN ... runs once.

In a sharded system:

• Run the migration on every shard.
• Coordinate when each shard is at which version.
• Code must handle “some shards have new column, some don’t” during the migration window.

Tools (Vitess, Gh-ost, pt-online-schema-change) help. Operationally, expect schema changes to take days in a sharded fleet vs minutes in a single DB.

For zero-downtime migrations see Zero-downtime migrations.

When to shard (and when not to)

Don’t shard until you’ve exhausted:

1. Indexes + query optimization (EXPLAIN and EXPLAIN ANALYZE).
2. Read replicas for read-heavy workloads.
3. Connection pooling (Connection pooling).
4. Vertical scaling — modern hardware is huge (128 cores, 1+ TB RAM, NVMe).
5. Caching (Redis in front).
6. Partitioning within one DB.
7. NoSQL alternatives if your workload fits.

Sharding adds operational complexity that compounds: backups, failover, schema migrations, debugging, observability, monitoring — everything 10× harder.

The classic mistake: sharding “for scale” before hitting the actual ceiling. Most workloads never reach single-DB limits on modern hardware (16-core, 256 GB RAM, NVMe — easily 50k QPS read-mostly, 10k QPS write-mostly).

When you actually need to shard:

• Data exceeds single-machine capacity (multi-TB, growing).
• Write throughput exceeds what one machine can sustain.
• Geographic distribution required (data residency, low latency).
• Fault isolation — one tenant’s traffic shouldn’t affect others.

Tools and approaches

For new projects needing horizontal scale: distributed SQL (CockroachDB, Yugabyte, Spanner) gives you SQL semantics without manual sharding.

For existing single-DB systems: extend with Citus, Vitess, or migrate to a sharded NoSQL.

For new microservices: shard at the service boundary (each service owns its data).

Common pitfalls

• Sharding too early — operational cost without payoff.
• Wrong shard key — hot shard, can’t rebalance.
• Cross-shard transactions everywhere — defeats the point.
• Not co-locating related data — every query becomes scatter-gather.
• Foreign keys across shards — broken; need to redesign as soft references.
• Single-shard hot key — celebrity tenant with 90% of traffic on one shard. Need rebalancing or directory-based sharding.
• Resharding under load — design for it from day one (consistent hashing or pre-sharding).
• Migration synchronization — schema drift across shards.

Common interview confusions

• “Sharding and partitioning are the same.” — partitioning keeps the pieces in one DB; sharding spans machines.
• “Sharding fixes performance.” — sharding adds operational complexity. It scales capacity, not raw query speed.
• “NoSQL means sharded.” — many NoSQL DBs shard automatically (Cassandra, MongoDB), but it’s a separate concept; sharded SQL exists too.
• “Just use Postgres until 1 TB.” — depends on workload. Some apps hit limits earlier; some go far beyond. Measure, don’t guess.