Splitting a database table into multiple segments for performance or manageability.
A database table may be split into multiple parts but still queried as a single table. Partitioning allows the parts to be managed independently, stored on separate disk volumes and excluded from query processing operations where they are not needed.
The most common partitioning strategy is known as horizontal partitioning and splits the table into sets of rows based on ranges of values of one or more columns (often called a partition key). The partitioning rule can also be based on hash values or lists for some databases. Horizontal partitioning has a number of benefits:
If the query includes a partition key range the optimiser can exclude partitions that are not needed to process the query. This can substantially reduce the work needed to process the query, improving the overall query performance.
The partitions can be stored on multiple disk volumes, allowing the storage to be distributed across those volumes. This was a common storage strategy on older systems with limitations on individual disk volume sizes. It can also be used to distribute a table across multiple disk controllers for performance.
Individual partitions can be added and removed from the table without the overhead of expensive delete operations. This facilitates managing large tables, such as archiving data based on date.
Horizontal partitioning is often sold as an 'enterprise' feature, only available on more expensive editions of database management systems.
A less commonly used feature is vertical partitioning, which splits individual database records across multiple storage units, with some columns held in one place and others held in a different storage allocation. This is used where a table has a wide column or large set of columns that is only used in certain, uncommonly used queries.
Many database management systems do not support explicit vertical partitioning, although it can happen implicitly with facilities such as BLOB storage.
