Sharding vs Partitioning: System Design Trade-offs

Side by side

Dimension	Sharding	Partitioning
Meaning	Horizontal partitioning across database nodes	General division of data; may be local or distributed
Typical goal	Scale storage and aggregate write capacity	Improve pruning, maintenance, or data organization
Data placement	Separate nodes or replica groups	For local table partitioning, one database instance
Request routing	Router maps a shard key to an owner	Database typically selects local partitions
Queries	Single-shard access or cross-shard fan-out	Partition pruning can reduce local scans
Transactions	Cross-shard atomicity needs coordination	Local partitions usually share transaction machinery
Growth operations	Move data and update shard ownership	Add, split, detach, or drop local partitions
Failure boundaries	Separate shard groups, with shared dependencies	Local partitions share the instance's failure domain

The differences that matter

The terms overlap, so define them before comparing them. Partitioning can be horizontal, dividing rows, or vertical, dividing columns. Sharding usually means horizontal partitioning across nodes. In the rest of this comparison, the practical contrast is distributed sharding versus local table partitioning. A distributed database may call its shards partitions.

Local partitioning changes how a database organizes and accesses data, but does not by itself add another node's CPU, memory, or write capacity. For example, monthly event partitions let the engine skip irrelevant months when the query predicate supports pruning. Dropping an expired partition can also avoid deleting old rows individually, though locking and cleanup behavior depend on the database.

Sharding adds a placement and routing problem. A hash of tenant_id can distribute tenants across shard groups, while a directory can explicitly map tenants to owners. Queries containing tenant_id can go directly to one shard; queries without it may contact every shard and merge results. Sharding does not automatically solve a single oversized tenant or hot key.

Transaction boundaries become a design constraint with sharding. Keeping a customer's orders and order items together can preserve local transactions and joins. Operations spanning owners may require database-supported distributed transactions or an application workflow with explicit intermediate states. Each shard can also use local time partitions, so the two techniques often belong in the same design.

Choose Sharding when

The working dataset or sustained write workload exceeds the practical capacity of one database node after reasonable indexing and query improvements.
Most requests include a stable routing key, such as account_id, that keeps related reads and writes on one shard.
You need to add storage and processing capacity incrementally and can support data movement, routing changes, and per-shard operations.
Tenant placement matters, and you want to move large tenants to dedicated shard groups while enforcing resource limits.

Choose Partitioning when

The workload still fits on one database node, but queries should scan only a small time range or data category.
Retention requires regularly removing old data, and the database supports efficient partition detach or drop operations.
You want to preserve single-database transactions and joins without introducing cross-node routing and coordination.
You already use sharding and need local partitions inside each shard for time-based pruning and retention.

A worked example

Shard by tenant, partition locally by time

Suppose an analytics service retains 12 months of events, totaling 24 TB, and serves mostly tenant-scoped queries over the last seven days. Assume load testing shows one database node cannot sustain the required ingestion rate. Monthly partitions on that node would simplify retention, but would not remove its write bottleneck. Instead, distribute tenants across eight shard groups and create monthly event partitions inside each shard. A tenant-scoped query reaches one shard and prunes irrelevant months. The average is 3 TB per shard, but capacity planning must account for tenant skew, indexes, and replicas. A tenant generating 30% of writes may need dedicated placement or further subdivision.

In the interview

Open with the taxonomy: sharding is distributed partitioning, not its opposite. State that you are comparing it with local table partitioning so the discussion has a clear scope.
Estimate retained bytes, write rate, index overhead, and query shapes before adding shards. Explain which measured or assumed single-node limit motivates distribution rather than claiming a universal size threshold.
Name the shard key and walk through one read and one write. Expect follow-ups about queries that omit the key, tenant skew, hot keys, and uniqueness constraints.
Describe how shard ownership changes safely: copy existing data, catch up changes, fence old writers, and switch routing. Explain how stale clients and retries are handled.
State the transaction boundary and recovery model. Interviewers may ask what happens when a cross-shard operation partially succeeds or when one shard becomes unavailable.

Common mistakes

Treating sharding and partitioning as mutually exclusive, or assuming partitioning always means one server.
Claiming local table partitions automatically increase write capacity or guarantee faster queries, regardless of pruning and access patterns.
Choosing a shard key only for even distribution while ignoring joins, transaction locality, range queries, and unusually large tenants.
Assuming more shards fix a hot key or provide durability. Replication, failover, and hot-key mitigation are separate design decisions.

Where it comes up

Interview questions where choosing between Sharding and Partitioning comes up.

Questions people ask

Is sharding the same as horizontal partitioning?

Sharding is generally horizontal partitioning across database nodes. Horizontal partitioning can also divide rows into local table partitions on one instance, so it does not always imply sharding. Terminology varies by database.

Can you use sharding and partitioning together?

Yes. A service can shard by tenant_id across nodes and partition each shard's tables by month. The shard key controls placement and routing, while the local partition key supports pruning and retention.

When should you shard instead of partitioning a table?

Shard when you need capacity across nodes and have a workable routing key and transaction boundary. Use local table partitioning when the main problems are scanning irrelevant data or managing retention within a database that still has enough capacity. Validate the bottleneck first, because sharding adds operational and query complexity.