Partitioning

In Turkish: bölümleme
Pronunciation: par-TISH-uh-ning

Updated October 3, 20262 min read

In short

Partitioning splits a large table into smaller partitions by a rule such as date ranges, so queries can skip irrelevant data and old data is easy to remove.

What is database partitioning?

A table with billions of rows of orders or logs becomes slow to query and painful to maintain. With partitioning, the database stores it as many smaller tables behind one name, for example one partition per month. Applications still query the single orders table; the database decides which partitions hold the rows.

The rule is the partition key. Range partitioning uses ranges such as dates; list partitioning uses explicit values such as country codes; hash partitioning spreads rows evenly by a hash of the key. When a query filters on that key, the database uses partition pruning to read only the partitions that can contain matching rows.

Partitioning also makes data management cheap. Dropping last year's partition is instant compared with deleting millions of rows, indexes stay smaller, and maintenance can run partition by partition. PostgreSQL, MySQL, SQL Server and Oracle all support it, and analytical warehouses partition large tables by date as a matter of course.

A common misconception is that partitioning and sharding are the same. Partitioning usually splits a table within one database server, while sharding spreads data across several servers. Partitioning also doesn't help queries that don't filter on the partition key, which may have to scan every partition.

Key takeaways

Partitioning splits a large table into smaller partitions behind one name.
Range, list and hash partitioning use different rules on the partition key.
Partition pruning lets queries read only the relevant partitions.
Old data can be dropped a whole partition at a time.
Partitioning is within one server; sharding spreads data across servers.

Example

Range partitioning by month in PostgreSQLsql

CREATE TABLE events (
    id         bigint      NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- Only events_2026_10 is scanned (partition pruning)
SELECT count(*) FROM events WHERE created_at >= '2026-10-01';

-- Removing a month of data is instant
DROP TABLE events_2026_09;

Readers ask

What is the difference between partitioning and sharding?

Partitioning divides a table into pieces, usually inside one database server. Sharding distributes data across multiple servers, so each one holds only part of it. Sharding is a form of horizontal partitioning across machines.

When should I partition a table?

When a table is very large, queries usually filter by one column such as date, and old data is regularly archived or deleted. For small tables it mostly adds complexity.

What is partition pruning?

The database skipping partitions that cannot contain matching rows, based on the query's filter on the partition key. It is what makes partitioned queries fast.