Partitioning

This is a relatively popular term and almost all the DBA(s) use this daily.

Let's decode it....

Partitioning is a data organization scheme in which table data is divided across multiple storage objects called data partitions according to values, in one or more table columns. Each data partition is stored separately. It consists of its data, indexes, configuration files, and transaction logs. For example, there is a Salary table with 10 years of data. It can be partitioned yearly. At the backend, at the storage level, this table will be divided into 10 smaller datasets i.e., 1 for each year. Keynote here is, all partitions will be stored wherever the empty slots are, maybe 4 partitions including years 2 & 3 got stored in 1 node of 10 nodes-cluster. This is called Shared-Architecture. Now, a query for years 2 & 3 will go and hit only two partitions making it much faster but as both partitions were on 1 node so one node processing is used.

Image: https://www.digitalocean.com/community/tutorials/understanding-database-sharding

Partitioning Techniques

●       Single-level Partitioning

o   Hash Partitioning

o   Range Partitioning

o   List Partitioning

●       Composite Partitioning

o   Range–Range Partitioning

o   Range–Hash Partitioning

o   Range–List Partitioning

o   List–Range Partitioning

o   List–Hash Partitioning

o   List–List Partitioning

Benefits of Partitioning

●       It advances query functionality. Because queries can be easily and rapidly solved for a collection of partitions instead of solving those for a giant database. Hence, the functionality and performance level gain improvement.

●       The planned intermission time gets abridged.

●       It facilitates information administration procedures like information loading, index formation and restoration, and backup and upturn at the partition stage. As a result, processes become faster.

●       Parallel implementation offers detailed benefits to optimize resource utilization and lessens the implementation time too. Parallel execution next to partitioned substances is a solution for scalability in a crowded setting.

Partitioning techniques not only improve the running and management of very large data centers, but even allow the medium-range and smaller databases to take pleasure in its benefits. Although it can be implemented in all sizes of databases, it is most important for the databases that handle large data.

Cheers.