Partitioning

What Partitioning Is

Partitioning means splitting one big table into smaller pieces on the same server. Each piece is a partition. Your app still sees and queries one table; the database decides which pieces to read.

Think of a filing cabinet. Instead of one drawer stuffed with every bill since 2020, you have one drawer per month. To find October's bills, you open only October's drawer. To throw away 2020, you remove whole drawers.

1orders (one table to the app)
2├── orders_2026_08   ← August rows
3├── orders_2026_09   ← September rows
4└── orders_2026_10   ← October rows

Why Our Orders Table Needs It

After a few years, our food delivery app has 2 billion orders. Three things hurt:

Partitioning by month fixes all three: queries touch only the months they need, old months are dropped in one step, and maintenance works on one small partition at a time.

How It Works in PostgreSQL

You declare a partition key (the column that decides where a row goes) and then create one partition per range:

CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  user_id    BIGINT NOT NULL,
  amount     INT    NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)          -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_09 PARTITION OF orders
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE orders_2026_10 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- an index on the parent is created on every partition
CREATE INDEX ON orders (user_id, created_at);

Inserts and queries go to orders as usual. The app code doesn't change:

1INSERT order with created_at = 2026-10-27 → stored in orders_2026_10

Partition Pruning

Partition pruning means the database skips partitions that can't contain matching rows. It works only when the query filters on the partition key.

EXPLAIN SELECT * FROM orders
WHERE user_id = 42
  AND created_at >= '2026-10-01';
-- plan scans only orders_2026_10, all other months are skipped

1Query with created_at filter    → 1 partition  of ~55 million rows → fast
2Query without created_at filter → all partitions, 2 billion rows   → slow

So the partition key should be a column almost every important query filters on. For orders, that's the date.

Dropping Old Data

Removing old data becomes instant:

-- slow: deletes row by row and leaves dead space
DELETE FROM orders WHERE created_at < '2024-11-01';

-- fast: removes the whole month at once
DROP TABLE orders_2024_10;

-- or keep it as a separate table for archiving
ALTER TABLE orders DETACH PARTITION orders_2024_10;

New partitions must exist before data for that month arrives, or inserts fail (unless you add a DEFAULT partition). Teams usually create them ahead of time with a scheduled job or the pg_partman extension.

Partition Types

Partitioning vs Sharding

Partitioning makes a big table manageable. It doesn't add CPU, RAM, or disk. When the whole server is the limit, you need sharding.

Interview Angle

Common mistakes:

• Mixing up partitioning (same server) and sharding (many servers).
• Choosing a partition key most queries don't filter on.
• Forgetting to create future partitions ahead of time.

Summary

1Partitioning   → one table split into pieces on the same server
2App            → still queries one table, no code change
3Pruning        → filter on the partition key → only matching partitions scanned
4Old data       → DROP or DETACH a partition instead of a huge DELETE
5Types          → range (dates), list (categories), hash (even spread)
6vs sharding    → partitioning = same server, sharding = many servers

Partitioning helps one big table, but when the whole server can't keep up, the data has to move to more machines. That's sharding.