What is table partitioning, and how does it differ from sharding? Describe the main partitioning types and when you would apply each one to improve performance at scale.

Question

Accepted Answer

Table partitioning divides a single logical table into multiple physical segments called partitions, all managed within the same database instance. Unlike sharding, which distributes data across multiple database servers, partitioning organizes data within a single server to improve query performance, simplify maintenance, and enable efficient data lifecycle management.

The key distinction is that partitioning is transparent to the application. Queries against the partitioned table work exactly as they would against a non-partitioned table. The database engine automatically directs queries to the relevant partitions through a process called partition pruning, which eliminates unnecessary partitions from the query plan based on the WHERE clause conditions.

Range partitioning divides data based on ranges of a column value, most commonly date or timestamp columns. A transaction table might have monthly partitions: transactions from January 2026 in one partition, February 2026 in another, and so on. This is the most common partitioning strategy because many workloads are time-based. Queries filtering by date range only scan the relevant partitions rather than the entire table. Archival becomes a simple partition drop or detach operation rather than a massive DELETE statement that generates enormous transaction logs.

List partitioning divides data based on discrete values of a column. A multi-tenant application might partition by tenant ID, a geographically organized dataset by region code, or an order table by status. This is effective when queries frequently filter by the partition key value and the number of distinct values is manageable.

Hash partitioning distributes rows across a specified number of partitions based on a hash of the partition key. This provides even data distribution when there is no natural range or list to partition on. It is useful for spreading IO across multiple tablespaces or filegroups on different physical disks. However, it does not support efficient range queries on the partition key.

Composite partitioning combines two strategies, such as range-list or range-hash. A common pattern is range partitioning by date with sub-partitioning by region. This enables partition pruning on both dimensions, providing maximum selectivity for queries that filter on both date and region.

I apply partitioning in several scenarios at scale. Tables exceeding hundreds of millions of rows where full table operations like statistics updates, index rebuilds, or backups become prohibitively slow benefit enormously because these operations can be performed partition by partition. Tables with strong time-based access patterns where queries predominantly access recent data while historical data is retained for compliance. Tables where data lifecycle management requires efficient archival or purging of old data. Tables with mixed workloads where partitioning can separate hot data on fast storage from cold data on economical storage through partition-level tablespace placement.

The operational benefits are as significant as the performance benefits. Index maintenance can be performed on individual partitions during off-peak hours. Backup strategies can focus on recently changed partitions. Statistics updates target partitions with new data rather than sampling the entire table. These operational improvements are often the primary justification for partitioning in large-scale environments.