Database Partitioning Simply Explained

Photo by authot

Pop quiz! Within 1 second, can you help me count how many 5s are there in the picture above?

Difficult, isn’t it? If you can do it, hats off to you. I couldn’t do it myself because scanning over every number to count takes a little bit more than 1 second.

How about this?

Photo by author

Much easier, right? You can immediately tell the answer is 6. So what makes the difference?

In the second picture, the same sequence of numbers has been nicely split into different boxes according to their values. Since all the 5s are together, you didn’t have to waste time looking at 1s, 2s, 3s or 4s.

And that’s exactly what database partitioning is: splitting one big table into smaller chunks based on date, time or whatever numeric values to speed up the search process. For example, let say we split all sales records based on transaction year, 2020 records are grouped together in one box, while 2019 records are in another box and so on.

If we only care about last year sales, we don’t have to scan the entire 10-year historical sales record.

If we need sale records in 2000, the result immediately returns zero (without going through all historical records) because based on the partition, the database knows that the earliest transaction year captured is 2010. Quick and easy, correct?

Simply put, with database partitioning, we can focus on things we truly need and ignore everything else. This means getting answers from data faster, easier and cheaper (because getting billed by the amount of data processed is real for cloud platform).

If you would like to know the specific syntax for database partitioning, check out this article.

Please feel free to drop me a note if you have any comment or just simply wanna say Hi. That would mean a lot to me. Thank you and stay tuned for upcoming post for my new series “Explain Like I’m 5” where I translate technical concepts to plain English so that everyone can understand easily!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s