Partitioning and sharding

Part of the free LogicWiz SQL & Databases course, Module 17: Beyond one database. The queries and practice run in the app on real Postgres.

The orders table now has 50 crore rows. "Orders this week" scans all of them, and deleting last year's orders takes all night. Meher splits the table by month, and both problems almost vanish.

You'll learn to

• Create range, list and hash partitioned tables in Postgres
• See partition pruning in EXPLAIN
• Explain sharding and how to pick a shard key
• Tell partitioning and sharding apart

Learn

One table, many pieces

Partitioning splits one big table into smaller tables called partitions, on the same server. You still query the parent table; Postgres sends each row to the right partition.

Two big wins:

• Pruning: a query for July reads only the July partition.
• Cheap deletes: dropping a whole old month is instant, while DELETE … WHERE ordered_at < … touches every row and bloats the table.

Postgres has three kinds: RANGE (dates, ids), LIST (cities, states) and HASH (spread evenly).

Range partitions by month

CREATE TABLE orders_m (
  order_id     INT,
  customer_id  INT NOT NULL,
  ordered_at   TIMESTAMP NOT NULL,
  status       VARCHAR(10) NOT NULL
) PARTITION BY RANGE (ordered_at);

CREATE TABLE orders_2025_06 PARTITION OF orders_m
  FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
CREATE TABLE orders_2025_07 PARTITION OF orders_m
  FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE orders_old PARTITION OF orders_m DEFAULT;

INSERT INTO orders_m
SELECT order_id, customer_id, ordered_at, status FROM orders;

SELECT tableoid::regclass AS partition, COUNT(*) AS orders
FROM orders_m
GROUP BY tableoid
ORDER BY 1;

FROM is inclusive and TO is exclusive, so July is FROM ('2025-07-01') TO ('2025-08-01'). The DEFAULT partition catches any row no other partition fits. tableoid::regclass tells you which partition a row lives in.

Pruning: Postgres skips partitions

EXPLAIN (COSTS OFF)
SELECT COUNT(*)
FROM orders_m
WHERE ordered_at >= '2025-06-01'
  AND ordered_at < '2025-07-01';

The plan scans only orders_2025_06. Remove the WHERE and run it again: now every partition appears. Pruning only works when the filter is on the partition key.

LIST and HASH partitions

CREATE TABLE customers_by_region (
  customer_id INT, name VARCHAR(40), city VARCHAR(30)
) PARTITION BY LIST (city);
CREATE TABLE cust_south PARTITION OF customers_by_region
  FOR VALUES IN ('Bengaluru', 'Chennai', 'Hyderabad');
CREATE TABLE cust_rest PARTITION OF customers_by_region DEFAULT;
INSERT INTO customers_by_region SELECT customer_id, name, city FROM customers;

CREATE TABLE orders_h (order_id INT, customer_id INT)
  PARTITION BY HASH (customer_id);
CREATE TABLE orders_h0 PARTITION OF orders_h FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE orders_h1 PARTITION OF orders_h FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO orders_h SELECT order_id, customer_id FROM orders;

SELECT tableoid::regclass AS partition, COUNT(*) AS rows
FROM customers_by_region GROUP BY tableoid
UNION ALL
SELECT tableoid::regclass, COUNT(*)
FROM orders_h GROUP BY tableoid
ORDER BY 1;

LIST picks the partition from a list of values. HASH runs the key through a hash function and takes the remainder, so rows spread out evenly with no ranges to plan.

Two classic mistakes. Writing July as TO ('2025-07-31') leaves out the whole of 31 July, because TO is exclusive. And with no DEFAULT partition, a row that fits nowhere is rejected with an error ("no partition of relation found for row"), not stored somewhere quietly.

Sharding: partitions on different servers

Partitioning stays on one machine. When one machine is not enough (too much data or too many writes), you shard: put different rows on different database servers.

• The shard key decides where a row lives, for example customer_id.
• A good key spreads load evenly and keeps one request on one shard: a customer's orders, cart and addresses all live together.
• A bad key makes a hot shard: shard by city and Bengaluru's server melts during an IPL final.

The cost: joins and transactions across shards are slow and hard, and adding shards means moving data. Consistent hashing keeps that movement small.

Hash sharding, simulated

SELECT customer_id % 3 AS shard,
       COUNT(*) AS orders
FROM orders
GROUP BY customer_id % 3
ORDER BY shard;

With three shards and shard = customer_id % 3, this is how many orders each server would hold. Real systems use a hash of the key, not the key itself, so neighbouring ids do not cluster.

Partitioning vs sharding

Exams call this horizontal partitioning (split the rows, which is what we did) vs vertical partitioning (split the columns, e.g. move a big photo column into its own table). Sharding is horizontal partitioning across servers.

In one line

Partitioning splits a table on one server (RANGE, LIST, HASH; TO is exclusive); sharding splits rows across servers by a shard key.

Interview corner

What is the difference between partitioning and sharding?

Both split rows into pieces. Partitioning keeps the pieces on one database server, which routes rows and prunes partitions for you. Sharding puts pieces on different servers, so it scales writes and storage beyond one machine, but cross-shard joins and transactions become hard.

How do you choose a shard key?

Pick a key with many distinct values that spreads load evenly and that most requests filter on, so one request touches one shard (for a food app, customer_id or restaurant_id). Avoid keys that create hot shards, like city or a timestamp where all new writes hit the newest shard.

What is partition pruning?

When a query filters on the partition key, the planner skips partitions that cannot contain matching rows. WHERE ordered_at >= '2025-07-01' AND ordered_at < '2025-08-01' reads only the July partition. It does not help when the filter is on another column.

What problem does consistent hashing solve?

With hash(key) % N, adding one server changes N, so almost every key moves. Consistent hashing places servers and keys on a ring; adding a server moves only the keys between it and its neighbour, about 1/N of the data.