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
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