Select Page

Database Partitioning Options

Patrick Gates | | October 4, 2013

Partitioning is a database design technique that has numerous benefits, among them the reduction of data read time that ultimately reduces the overall response time. How the data is physically stored is one of several possible choices made by the database administrator that can improve an application’s ability to access data.

“It has long been known that database partitioning is the answer to improving the performance and scalability of relational databases,” states CodeFutures, a software development firm.

What are the partitioning options available to database administrators? There are two primary approaches: horizontal and vertical partitioning.

In horizontal partitioning, the database is segmented into table rows. This forms physical row-based datasets that either can be addressed individually — in a single partition — or else can be addressed from one partition to all partitions.

“All columns defined to a table are found in each set of partitions so no actual table attributes are missing,” writes Robin Schumacher, the director of product management for MySQL. “An example of horizontal partitioning might be a table that contains ten years worth of historical invoice data being partitioned into ten distinct partitions, where each partition contains a single year’s worth of data.”

Vertical partitioning is exactly what it sounds like: The data in a table is split vertically. Specific columns in the database are included in a dataset. Each partition includes all rows. This approach can also be used for so-called BLOB data, which is a term used to describe a collection of binary data stored as a single entity in a database.

“An example of vertical partitioning might be a table that contains a number of very wide text or BLOB columns that aren’t addressed often being broken into two tables that has the most referenced columns in one table and the seldom-referenced text or BLOB data in another,” explains Schumacher.

One partitioning strategy administrators could employ would be to segregate data by time. They might also decide to separate the data based on whether it is being actively used.

Partitioning does not merely mean the data is physically separated, explains Theo Schlossnagle, chief executive officer of OmniTI Computer Consulting. “It is often used to simply split our data up so that more hardware can be leveraged to process it. Google’s information, for example, is partitioned all over the place and then they ask all the system components (servers) to participate in answering questions.”

Which approach should you adopt? As Schlossnagle sagely observes:

“The techniques for choosing on which component to store a particular piece of data are wildly varying, each with its own advantages and disadvantages. Understanding how you will be storing data and more importantly what questions you will be asking over the data set dictate the partitioning scheme that is most appropriate.”

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over 100 logins on the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.