Last modified: May 06, 2025
This article is written in: πΊπΈ
When a database begins to sag under the weight of its own success, engineers reach for two closely-related remedies: partitioning and sharding. Both techniques carve a huge dataset into smaller slices, yet they do so at very different depths of the stack. By the time you finish these notes you should feel comfortable answering why, when, and how each approach shines, along with the trade-offs hiding beneath the surface.
After reading the material, you should be able to answer the following questions:
Before diving into syntax or strategies, picture a single table stretching far beyond your screen. By splitting that table into partitions the database can prune irrelevant chunks during a query and treat maintenance tasks like backups or index rebuilds piece-by-piece rather than all-at-once. Think of it as shelving volumes in a giant library rather than piling every book on one table.
βββββββββββββββββββββββββββββββ
β big_sales β
β βββββββββββββββββββββββββββ β
β 2023-01-β¦ βΈ Partition p23 β
β 2024-01-β¦ βΈ Partition p24 β
β 2025-01-β¦ βΈ Partition p25 β
βββββββββββββββββββββββββββββββ
Although every engine offers its own bells and whistles, five patterns dominate:
Mathematically you can model a simple range splitter with
$$ partition_{id} = \left\lfloor \frac{row_{value}-\min}{\Delta} \right\rfloor$$
where $\Delta$ is the chosen interval width.
Let's take a look at some examples:
Create the parent (partitioned) table
CREATE TABLE orders (
id bigint,
order_date date,
amount numeric
) PARTITION BY RANGE (order_date);
psql output:
CREATE TABLE
PARTITION BY RANGE (order_date)
, you tell PostgreSQL that orders
wonβt store rows itself but will route them to child tables based on the order_date
value.orders
is automatically redirected to whichever child partition matches.Define a specific partition for the year 2025
CREATE TABLE orders_2025
PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
psql output:
CREATE TABLE
orders_2025
is now a real table that will physically contain all rows whose order_date
β₯ 2025-01-01 and < 2026-01-01.FROM
value is inclusive, the TO
value is exclusive. This ensures partitions donβt overlap and every date is covered exactly once (assuming you add adjacent ranges).
INSERT INTO orders (id, order_date, amount)
VALUES (123, '2025-05-05', 49.95);
will go straight into orders_2025
without touching other partitions.
Verify partition pruning with an EXPLAIN
EXPLAIN SELECT * FROM orders WHERE order_date = '2025-05-05';
psql output:
-> Seq Scan on orders_2025 (cost=0.00..35.50 rows=5 width=...)
WHERE
clause at plan time.orders_2025
in the plan, proving that lookups on orders
automatically get optimized to target just the relevant partition.
Keyword / Option | Meaning | Typical Use-Case |
PARTITION BY RANGE (...) |
Chooses range strategy and the column(s) | Time-series or numeric intervals |
FOR VALUES FROM () TO () |
Declares the lower-inclusive, upper-exclusive bounds | Adjacent partitions must not overlap |
DEFAULT PARTITION |
Catch-all for rows that fit no explicit slice | Shields you from insert errors when ranges lag behind growth |
Query latency drops because only relevant partitions are scanned. Maintenance windows shrink: vacuuming a quiet 2022 table can happen while 2025 receives writes. Resource allocation becomes flexible; you might place cold partitions on slower disks. Even fault isolation improvesβa corrupt partition rarely topples the rest of the schema.
While partitioning rearranges furniture within one house, sharding is more like buying extra houses in new neighborhoods. Each shard is a full database instance containing a subset of the rows, and together they form a loose federation under the application layer.
βββββββββββββββββ
Client βΊ β Router / API β
ββββββ¬ββββ¬βββββββ
β β
ββββββββββββββ ββββββββββββββ
βββββββββββββββ βββββββββββββββ
β Shard A β β Shard B β β¦
β users 1-N/2 β β users N/2+1 β
βββββββββββββββ βββββββββββββββ
A single server inevitably runs out of CPU, memory, or I/O bandwidth. By sprinkling data across multiple machines the system gains horizontal scalability. Query throughput rises because shards answer requests in parallel. Resilience improves as well; if shard B disappears during a nightly redeploy shard A soldiers on, keeping part of the application available.
Below youβll see each shell command, the trimmed MongoDB response, and an expanded explanationβstill in a singleβlevel list for clarity.
Enable sharding on the target database
sh.enableSharding("ecommerce");
Shell output:
{ "ok" : 1 }
ecommerce
database is now eligible to have its collections distributed across shards.Shard a specific collection using a hashed key
sh.shardCollection("ecommerce.orders", { "user_id" : "hashed" });
Shell output:
{ "collectionsharded" : "ecommerce.orders", "ok" : 1 }
{ user_id: "hashed" }
, you tell MongoDB to compute a hash of each documentβs user_id
field and use that to assign it to a chunk.As data grows, the balancer process will migrate chunks among shards to even out storage and load.
Inspect how data is distributed across shards
db.orders.getShardDistribution();
Shell output:
Shard shard0000: 45% data
Shard shard0001: 55% data
orders
collection.
Flag / Parameter | What It Controls | Insight |
"hashed" in shardCollection |
Routing is based on the hash of the key rather than raw value | Avoids regional hot spots |
--chunkSize in mongos config |
Maximum chunk (sub-shard) size in MB | Smaller chunks migrate more smoothly |
balancerStopped |
Boolean to pause automatic rebalancing | Handy during peak traffic windows |
Dimension | Partitioning | Sharding |
Where data lives | One database engine, multiple internal segments | Many engines, each a self-contained database |
Primary goal | Speed up queries and maintenance | Add capacity beyond a single server |
Transaction scope | Usually local and ACID-compliant across partitions | Cross-shard transactions require 2PC or application logic |
Operational complexity | ModerateβDDL and monitoring remain centralized | Higherβorchestration, failover, and backups multiply |
Growth path | Vertical (bigger box) until vertical limits hit | Horizontal from day one |
Large platforms often partition inside every shard, marrying the easy pruning of partitions with the elastic headroom of shards. For example, an e-commerce company might hash-shard by user ID and range-partition each shardβs orders
table by month, yielding fast user look-ups and swift archival of old months.