Has your transaction or activity log table crossed hundreds of millions to billions of rows? If so, welcome to the club. 😅
At first, when the data was a few thousand rows, every SELECT ran at lightning speed. But once the table swelled into tens of gigabytes, everything changed:
- The B-Tree index no longer fits in RAM (buffer pool).
- Every new
INSERTstarts to feel slow, because of heavy index balancing. - A monthly report query spikes database CPU to 100% and locks other rows.
Once standard optimization techniques like indexing and connection pooling stop helping, the next architectural step is to split the data.
The two terms that come up most here are Partitioning and Sharding. They sound similar, but the architecture, purpose, and difficulty are very different. Let's take them one at a time, so you pick the right one.
Key takeaways
- Table Partitioning splits a giant table into smaller partitions inside the same database instance, so indexes stay light and query pruning works.
- Database Sharding spreads data across many separate servers (horizontal scaling) once one machine's capacity is exhausted.
- The choice of sharding key, whether range-based, hash-based, or directory-based, decides whether load spreads evenly or creates a hotspot.
- The price of sharding is high: cross-shard joins disappear, distributed transactions need 2PC or the Saga Pattern, and re-sharding is sweaty work.
What Is the Analogy?
To keep it simple, think about managing paper files in an office.
Table Partitioning is one big filing cabinet with dividers in the drawers. The files have piled up mountain high, so you install dividers marked Drawer 2024, Drawer 2025, and Drawer 2026. Everything stays in the same physical cabinet, one database server. When a staff member looks for the May 2025 letter, they open Drawer 2025 without combing the other years. That instant process is what we call Partition Pruning.
Database Sharding is a different story. If the cabinet is full and the office has no more room, you rent 3 separate warehouse buildings: Jakarta, Surabaya, and Medan. Customer files from west Java go to Jakarta, east Java to Surabaya, Sumatra to Medan. The data is genuinely spread across different physical machines. So if the Jakarta warehouse floods, Surabaya and Medan keep running normally.
What Is Table Partitioning, Really?
Partitioning is a built-in feature of modern relational database systems like PostgreSQL and MySQL.
Logically, the application still sees the data as one whole table, say orders. But physically, at the disk level, the database splits it into smaller partition tables.
There are three popular partitioning types:
- Range Partitioning: splits data by a value range, most commonly by date. For example
orders_2026_q1,orders_2026_q2. - List Partitioning: splits data by a list of discrete values, say country codes: ID, SG, MY.
- Hash Partitioning: splits data evenly using a modulo hash function on the key column.
In PostgreSQL, the partition declaration is refreshingly simple:
-- 1. Create the master table with a date Range partition
CREATE TABLE orders (
order_id BIGSERIAL,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(12, 2),
status VARCHAR(20),
PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);
-- 2. Create physical partitions for Q1 & Q2 of 2026
CREATE TABLE orders_2026_q1 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
When the application runs SELECT * FROM orders WHERE order_date = '2026-02-15', PostgreSQL automatically scans only orders_2026_q1 and ignores the other partitions. That is the Partition Pruning mentioned earlier. 😎
Oh, and note that the order_date column has to be part of the primary key. PostgreSQL requires the partition column inside its unique key. Otherwise, CREATE TABLE complains immediately.
And How Is Database Sharding Different?
If Partitioning splits a table inside one server, Sharding distributes data across many independent database servers called shards.
Each shard server is a complete database instance holding a specific subset of the data. This architecture is the true form of Horizontal Scaling (Scale-Out).
The key to success is picking the right sharding key to map data to its destination shard:
- Hash-Based Sharding: compute the hash of the ID, for example
hash(user_id) % jumlah_shard. Distribution is even, but range queries become hard. To add or remove shards without scrambling data, the consistent hashing technique is often used. - Range-Based Sharding: split by an alphabetical range or a region, for example users A-M in Shard 1 and N-Z in Shard 2. Prone to a Hotspot Shard when one data group is far more active.
- Directory / Lookup-Based Sharding: keep a centralized mapping table (lookup service) that records which ID lives on which shard. Very flexible, but the lookup table can become a single point of failure and a new bottleneck.
What Makes Sharding Hard?
Do not rush into sharding before you are truly cornered. The architectural complexity is serious. 😅
First, you lose Cross-Shard JOIN. The customer table in Shard A and the transaction table in Shard B can no longer be JOINed with plain SQL. The query has to be merged manually at the backend application level (Application-Level Join).
Second, distributed transactions become a problem. Guaranteeing the ACID principle across several physical servers needs the Two-Phase Commit (2PC) protocol or the Saga Pattern, both fiddly and slow.
Third, there is Re-Sharding. When the capacity of 4 shards is full and you want to grow to 8, moving old data between servers (data rebalancing) without downtime is sweaty work.
To save yourself the headache, the modern cloud ecosystem offers distributed middleware like Vitess for huge-scale MySQL, Citus Data for PostgreSQL, or NewSQL databases like CockroachDB.
When to Use Partitioning, When to Move to Sharding?
Database scaling is a gradual journey, so the order matters:
- Optimize the cheap things first: tidy your queries, add composite indexes, and cache hot data in Redis.
- Then use Table Partitioning when historical data starts piling up on one server, such as logs, audit trails, and old orders, and its disk and RAM capacity is still enough. The bonus: deleting expired data gets fast,
DROP TABLEon an old partition is far quicker thanDELETE FROM ... WHERE date < .... - Database Sharding only when data volume and write throughput have passed the capacity of the largest machine you can rent (Vertical Scale Limit), or the system needs data isolation per geographic region (data residency compliance).
So, When Does Data Really Need Splitting?
Partitioning is the tidy step for managing a giant table that still fits on one machine. Sharding is the last weapon, used when a single server genuinely cannot cope.
Start with the cheap things: query optimization, connection pool, and caching. Move up to partitioning when it feels needed. Only then consider sharding, when the system has crossed millions of transactions per second on a global stage.
Happy scaling, and may your data always stay neat in its shard drawers. 👋