Database partitioning? What is it, why should I use it?

What is database partitioning?

Partitioning is dividing your tables and indexes into smaller pieces, and even subdivide it into even smaller pieces.
Think of it as having several million different magazines of different topics and different years (say 2000–2019) all in one big warehouse (one big table). Partitioning would mean that you would put them organized in different rooms inside that big warehouse. They still belong together inside the one warehouse, but now you group them on a logical level, depending on your database partitioning strategy.

What’s the advantage of using database partitioning?

A good partitioning strategy is feasible for several reasons, for instance better performance, better manageability, higher availability, or for load balancing.

• Better performance: If, in the example of the magazine warehouse from above, you are looking for the October 2009 issue of “Sports Illustrated”, and you have partitioned your warehouse on issued year, you will only have to look into the room or the partition of 2009 to find what your looking for. Now, imagine that inside that one “2009” room inside your warehouse, you’ve put several big boxes, and you put the magazines inside these boxes…