Partitioning

Partitioning divides a single logical table into multiple physical segments called partitions. Each partition stores a subset of rows based on a partition key, yet applications query the table as if it were whole. Partitioning is usually done within one database instance, distinguishing it from sharding, which spreads data across servers.

How It Works

Common partitioning schemes include:

• Range partitioning: Rows are split by ranges of a column, such as date ranges — ideal for time-series data.
• List partitioning: Rows are grouped by discrete values, such as region or category.
• Hash partitioning: A hash of the key spreads rows evenly to balance load.
• Composite partitioning: Combines schemes, e.g., range-then-hash.

The database engine uses partition pruning to skip irrelevant partitions during a query, dramatically reducing scanned data. Partitions can be individually indexed, archived, compressed, or dropped — dropping an old date partition is far faster than deleting millions of rows.

Why It Matters

Partitioning improves query performance on large tables, simplifies data lifecycle management, and enables efficient bulk operations. It is heavily used in analytics and data warehouses, where queries scan recent time windows.

The trade-off is added schema complexity and the need to choose a partition key that matches query patterns. A mismatched key prevents pruning and yields little benefit.

Related Terms

Partitioning underpins sharding, complements indexing, and pairs naturally with columnar storage in analytical (OLAP) workloads.