Partitioning - System Design Guide

Overview

Partitioning splits a large table or dataset into smaller pieces called partitions. In a single database, table partitioning (for example by month) keeps each partition small, lets queries skip irrelevant partitions (partition pruning), and makes deleting old data as cheap as dropping a partition. In distributed systems, partitioning spreads data across nodes, which is what sharding means.

Horizontal partitioning splits rows; vertical partitioning splits columns (moving rarely used or large columns to another table). The partition key must match query patterns, just as with indexes and shard keys.

Monthly folders for receipts

Instead of one giant box of receipts, you keep one folder per month. Finding last month's receipts means opening one folder, and throwing away receipts older than seven years means discarding old folders.

When to use it

Very large tables, especially time-series or append-only data.
Queries usually filter by a time range or category.
Data retention: drop old data quickly.
Distributing load across nodes (sharding, Kafka partitions).

Where it shows up in interviews

Time-series data

Recognize it when: logs, events, metrics with retention.

Design a logging system
Design an IoT metrics store

Distributed partitioning

Recognize it when: spread data across nodes by key.

Design a key-value store
Design Kafka topic partitioning

Where it is used in real software

PostgreSQL declarative partitioning

Range, list, and hash partitioning with partition pruning; pg_partman automates creating and dropping partitions.

BigQuery and Snowflake

Partitioned and clustered tables mean queries scan (and bill for) only matching partitions.

Kafka partitions

Topics are split into partitions for parallelism and ordering per key.

Key terms

Range partitioning: By value ranges, often dates.
List partitioning: By discrete values, such as region.
Hash partitioning: By hash of a key for even distribution.
Partition pruning: The planner skips partitions that cannot match the query.
Vertical partitioning: Splitting columns into separate tables.

How it works, step by step

1
Pick the partition key
The column most queries filter on (created_at, tenant_id).
2
Pick the scheme
Range for time, list for categories, hash for even spread.
3
Size partitions
Not too many tiny partitions, not too few huge ones.
4
Automate lifecycle
Create future partitions ahead of time and drop or archive old ones.
5
Index per partition
Indexes are smaller and faster to maintain.

Partition pruning on an events table

events partitioned by month, 24 partitions, 2B rows

Query	Partitions scanned	Effect
WHERE created_at >= '2026-09-01'	1	Scans ~4% of data
WHERE created_at BETWEEN Q2 dates	3	Scans ~12% of data
WHERE user_id = 42 (no date)	24	No pruning: add a date filter or index
DROP old partition	-	Deletes 80M rows instantly, no vacuum

NOWQuery: WHERE created_at >= '2026-09-01' | Partitions scanned: 1 | Effect: Scans ~4% of data

Partitioning helps only when queries include the partition key; otherwise every partition is scanned.

Implementation

1CREATE TABLE events (2  id          BIGINT GENERATED ALWAYS AS IDENTITY,3  user_id     BIGINT NOT NULL,4  type        TEXT NOT NULL,5  created_at  TIMESTAMPTZ NOT NULL,6  payload     JSONB7) PARTITION BY RANGE (created_at);8 9CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');10CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');11 12CREATE INDEX ON events (user_id, created_at);  -- created on every partition13 14-- Retention: remove a month of data instantly15DROP TABLE events_2024_09;

Complexity and performance

Pruned queryScans only matching partitions

Proportional to relevant data.

Drop partitionO(1)

vs DELETE of millions of rows.

Trade-offs

Pruning vs non-key queries

Queries without the partition key scan all partitions, sometimes slower than an unpartitioned table.

Operational overhead

Partitions must be created and dropped on schedule, and unique constraints must include the partition key.

Variants and related techniques

Sub-partitioning

Partition by month, then by hash of tenant.

Sharding

Partitions placed on different servers.

Common mistakes

Thousands of tiny partitions.
Fix: Planning time grows; use larger intervals.
Forgetting future partitions.
Fix: Inserts fail when no partition matches; automate creation or add a default partition.

Interview questions

What is the difference between partitioning and sharding?

Partitioning splits data into pieces; sharding is partitioning where the pieces live on different servers. Table partitioning can happen within a single database.

How would you store 5 years of events and keep only 90 days hot?

Partition by day or month, keep recent partitions on fast storage with indexes, move older partitions to cheaper storage or object storage (Parquet), and drop partitions past retention.

Practice problems

Problem	Difficulty	What it trains
Partition an audit log table with 90-day retention	Easy	Range partitions.
Design storage for 1B events per day	Hard	Partitioning and tiering.