Partitioning a Database

Partitioning means breaking up a database into sections - to make it more performant, easier to manage or for load balancing.

Partitioning Criteria

RDBMs' support a range of partitioning criteria. They take a partitioning key and assign a partition based on certain criteria. Example criteria are:

• Hash partitioning - membership of a partition is determined by the value of a hash function. This function might return numbers between 0 to 4, for example, if there are 5 partitions.
• Range partitioning - selects partition if partitioning key lies within a given range. For example, if you have a list of countries, you may split on a country with the letter M, say Malawi, Malta or the Maldives.
• List partitioning - the partition is assigned a list of values. If the row satisfies the criteria (e.g. Country column is contained in List A) then it goes into, say, partition A.
• Composite partitioning - hybrid of the above approaches.

Means of Partitioning

"Horizontal partitioning" or "horizontal sharding" involves putting different rows into different tables. A view with a union may be used to create a unified view.

"Vertical partitioning" involves taking normalisation to the next level. It involves creating tables with fewer columns and using additional tables to store the extra columns - a bit like normalisation, except vertical partitioning can be effected even when the tables are normalised. Infrequently used columns might be stored on a different device. This type of partitioning is also known as "row splitting" - the row is split by the columns.