Partition (database)
Encyclopedia
A partition is a division of a logical database
or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance
or availability
reasons.
A popular and favourable application of partitioning is in a distributed database management system
. Each partition may be spread over multiple nodes, and users at the node can perform local transactions on the partition. This increases performance for sites that have regular transactions involving certain views of data, whilst maintaining availability and security.
The partitioning can be done by either building separate smaller databases (each with its own tables
, indices
, and transaction
logs), or by splitting selected elements, for example just one table.
Horizontal partitioning (also see shard
) involves putting different rows into different tables. Perhaps customers with ZIP code
s less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view
with a union might be created over both of them to provide a complete view of all customers.
Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization
also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning. Done explicitly or implicitly, this type of partitioning is called "row splitting" (the row is split by its columns). A common form of vertical partitioning is to split dynamic data (slow to find) from static data (fast to find) in a table where the dynamic data is not used as often as the static. Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.
s provide for different criteria to split the database. They take a partitioning key and assign a partition based on certain criteria. Common criteria are:
Range partitioning : Selects a partition by determining if the partitioning key is inside a certain range. An example could be a partition for all rows
where the column
List partitioning : A partition is assigned a list of values. If the partitioning key has one of these values, the partition is chosen. For example all rows where the column
.
Hash partitioning : The value of a hash function
determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.
Composite partitioning allows for certain combinations of the above partitioning schemes, by for example first applying a range partitioning and then a hash partitioning. Consistent hashing
could be considered a composite of hash and list partitioning where the hash reduces the key space to a size that can be listed.
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance
Optimization (computer science)
In computer science, program optimization or software optimization is the process of modifying a software system to make some aspect of it work more efficiently or use fewer resources...
or availability
Availability
In telecommunications and reliability theory, the term availability has the following meanings:* The degree to which a system, subsystem, or equipment is in a specified operable and committable state at the start of a mission, when the mission is called for at an unknown, i.e., a random, time...
reasons.
A popular and favourable application of partitioning is in a distributed database management system
Distributed database management system
A distributed database management system is a software system that permits the management of a distributed database and makes the distribution transparent to the users. A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network...
. Each partition may be spread over multiple nodes, and users at the node can perform local transactions on the partition. This increases performance for sites that have regular transactions involving certain views of data, whilst maintaining availability and security.
The partitioning can be done by either building separate smaller databases (each with its own tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, indices
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
, and transaction
Database transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
logs), or by splitting selected elements, for example just one table.
Horizontal partitioning (also see shard
Shard (database architecture)
A database shard is a horizontal partition in a database or search engine. Each individual partition is referred to as a shard or database shard.- Database architecture :...
) involves putting different rows into different tables. Perhaps customers with ZIP code
ZIP Code
ZIP codes are a system of postal codes used by the United States Postal Service since 1963. The term ZIP, an acronym for Zone Improvement Plan, is properly written in capital letters and was chosen to suggest that the mail travels more efficiently, and therefore more quickly, when senders use the...
s less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
with a union might be created over both of them to provide a complete view of all customers.
Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization
Database normalization
In the design of a relational database management system , the process of organizing data to minimize redundancy is called normalization. The goal of database normalization is to decompose relations with anomalies in order to produce smaller, well-structured relations...
also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning. Done explicitly or implicitly, this type of partitioning is called "row splitting" (the row is split by its columns). A common form of vertical partitioning is to split dynamic data (slow to find) from static data (fast to find) in a table where the dynamic data is not used as often as the static. Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.
Partitioning criteria
Current high end relational database management systemRelational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
s provide for different criteria to split the database. They take a partitioning key and assign a partition based on certain criteria. Common criteria are:
Range partitioning : Selects a partition by determining if the partitioning key is inside a certain range. An example could be a partition for all rows
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
where the column
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
zipcode has a value between 70000 and 79999.List partitioning : A partition is assigned a list of values. If the partitioning key has one of these values, the partition is chosen. For example all rows where the column
Country is either Iceland, Norway, Sweden, Finland or Denmark could build a partition for the Nordic countriesNordic countries
The Nordic countries make up a region in Northern Europe and the North Atlantic which consists of Denmark, Finland, Iceland, Norway and Sweden and their associated territories, the Faroe Islands, Greenland and Åland...
.
Hash partitioning : The value of a hash function
Hash function
A hash function is any algorithm or subroutine that maps large data sets to smaller data sets, called keys. For example, a single integer can serve as an index to an array...
determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.
Composite partitioning allows for certain combinations of the above partitioning schemes, by for example first applying a range partitioning and then a hash partitioning. Consistent hashing
Consistent hashing
Consistent hashing is a special kind of hashing. In contrast, in most traditional hash tables, a change in the number of array slots causes nearly all keys to be remapped...
could be considered a composite of hash and list partitioning where the hash reduces the key space to a size that can be listed.