Expanding Your Horizons

Horizontal partitioning is a great way of storing and retrieving data from very large tables by breaking them into smaller tables, providing both predictable performance and simple querying. Some vendors offer built-in tools to handle partitions, other vendors have plug-ins available. It can also be implemented as a simple set of tables with management scripts, as I will be describing.

Imagine a retail store or e-commerce site taking many orders, often with multiple line items for each order. This is a very common scenario. The database table that stores the line items quickly grows in size, and after a few months can reach several million rows in a moderately successful store. The web site or point-of-sale software should have no difficulty writing new rows to the database, but the large table slows down ETL and analytics queries. This is the scenario we are addressing.

Once the basic indexing and optimization techniques are no longer sufficient, a very large table becomes a good candidate for partitioning. In short, the large table will be replaced with a number of smaller tables called partitions, and the partitions will be reassembled using a view so it can be queried as a single table. Turning the large table into partitions is a one-time event, and from that point forward there needs to be some process that will maintain the partitions and the view as new data is added.

Let's take the example of table order_item:

order_item
------------
id
order_id
inventory_id
quantity
price

record_create_date

record_update_date

Let's say this table has grown to 75 million records after 2 years of operations. On the production database, the table may be truncated and the data archived, or treated in some other way that does not cause a performance issue for single record inserts and updates. But in the analytics database this table is now causing some slowness, and it is decided to partition it.

The partitions will be called order_item_001, order_item_002, order_item_003, etc. The size of each partition determines the total number of partitions. Let's pick a size of 20 million for this example. The order_item table will be broken into 4 partitions, the last one being the "current" partitions with 15 million records. It can be called order_item_004, or order_item_current, or anything that will allow identifying the last partition. One benefit of using the name order_item_current is that it will remain constant as new partitions are added, which makes inserts seamless for ETL.

In order to query the partitioned table, a view is used to reassemble the partitions, like so:

create view order_item
as
select *, partition = NULL from order_item_current
union all
select *, partition = 1 from order_item_001
union all
select *, partition = 2 from order_item_002
union all
select *, partition = 3 from order_item_003


The view is now taking the place of the table as far as existing SQL queries and scripts are concerned. For optimal performance, it is important that all partitions use the same indexes, it makes the query optimizer's job much easier and gets the best possible performance. The partition number is added in the view instead of being added to the partition table so that the table structure remains unchanged. Adding the partition number is necessary to identify which partition to update during ETL.

As new records are inserted and the count reaches 20 million in the current partition, it will be turned into the next numbered partition, order_item_004, the view adjusted accordingly, and new records will continue to be inserted in the now empty order_item_current table. Because the partitions are of equal size, analytic queries that return a range of data tend to take the same time to execute, and this eliminates the slowness caused by the growing table. In the case of retail transactions, reports are usually retrieving a quarter, a year, or last year plus current year, and the execution time becomes predictable despite data growth and seasonality.

Updating of existing records cannot be done through the view (SQL does not allow it), which is why adding the partition number is important. A simple query that matches the keys and returns the partition number can be used to identify which partitions need updating, and the update can then be applied to only those partitions. A bit of dynamic SQL can be used here to craft queries on the fly.

The key to successfully implementing horizontal partitioning is to carefully select the logical separation of the data. The end result needs to be a set of partitions that are similar in size, so that performance remains constant for similar size result sets. Also, data that is likely to be accessed together should remain in as few partitions as possible. Using time to separate the data sounds good at first, but is not a great approach because spikes like seasonality (Q4 retail season and other holidays for example) can result of partitions of very different size.

Instead, I have used rowcount for tables as large as 6 billion rows, with partition size ranging from 20 to 75 million, with very good and constant performance. A weekly management task is all that is needed to iterate through the partitioned tables:

determine if "current" partition has exceeded the threshold
if so
create the next numbered partition
adjust the view to include new partition
if not, move on to next table

This is a great example of minimal effort with maximum impact.

If your data warehouse is stored in a relational database, and you have the ability to write and schedule stored procedures, then you already have everything you need to implement horizontal partitioning of large tables. It is a great way of solving a common performance problem, is very cost effective for both implementation and maintenance, and will free you up for more pressing tasks, like keeping up with the incessant flow of changes in the source systems feeding your data warehouse.