Tip

Horizontal partitioning of large databases

Barrie Sosinsky, Contributor

Last time, we looked at partitioning large databases by vertically partitioning the dataset into different tables contained in other databases. This scheme offers you logical unification through views, but operational complexity managing data references, additional statements and backups. Horizontal partitioning schemes represent the alternative.

You can horizontally partition your database by splitting a table into complete datasets and placing those datasets into other databases. Typically a field is used to create that separation, such as an ID field, a data field, location and so on. You can horizontally partition your data using a logical separation such as a year or an office location. Each partition of the data has the same structure as the original database. You would use horizontal partitioning to archive a year's data and remove it from the main table. So whereas both partitioning types create new tables in different databases, in horizontal partitioning you are maintaining your original architecture.

Horizontal partitioning has both advantages and disadvantages. From a performance standpoint, any user requiring access to a record uses the device that has access to the table with that data. You can create multiple paths to the data and cut down on contention and record locking making user access faster. Partitioning data horizontally also lets you physically and logically protect data by limiting access to that data.

Separating data through

    Requires Free Membership to View

a logical division is certainly convenient, but it adds the complexity that now your code has to account for where the data is stored when a user requires access. Sometimes that is easy to do, other times it isn't. However, logical data division is certainly easier to access than the alternative method of partitioning your dataset using a hash partition. When you use a hash key to create the partition the hash key normally generated from a random number algorithm provides an even data split, maximizes your performance and lowers your access issues, but makes finding the data's location difficult. Consider what happens when a RAID set fails. You can restore that set, but that requires restoring the entire data set. For a logical partition you know which data was lost and you only need to restore that particular part of your data set.

The other problem with horizontally partitioning a data set is that you have to access the data using something like a UNION operator to create a unified query resultset. You can store the necessary access details into a unifying query, but INSERTs, UPDATEs, and DELETEs are still problematic.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.


This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.