That "AHA" Moment: OpenEdge Table Partitioning

Sometimes, you need to hear something several times before you finally realize how practical and applicable what you are being told actually is. This recently happened to me regarding Progress OpenEdge Table Partitioning. Perhaps, it will happen to you too if I share my recent “aha" moment with you.

When the Lightbulb Went Off

Several weeks ago Rich Banville, Progress Fellow for database technology, and I were on a call with a customer, and Rich made a suggestion on how the customer should partition their 4TB table to minimize disruption to their production system. That’s when the lightbulb went off in my head. I had previously heard Rich discuss the approach several times, but it was only at that very moment, in a real world scenario, that I completely understood how valuable and practical the approach was.

The customer, like many of our other customers, has a large multi-terabyte database that cannot be offline for more than 3-4 hours. I suggested that they use Progress OpenEdge Pro2 in combination with OpenEdge Table Partitioning, as I described in my previous blog, Simplify Your Database Needs, to partition their database. While it would have been effective, this solution was not practical for them due to additional hardware they would require to migrate the data to OpenEdge Pro2.

Partitioning by Date

At one point during the conversation the customer said, “Our history table is 4TB in size,” and we can’t afford the load on the system or the downtime in order to split into partitions. Rich responded by asking our customer if this history table could be partitioned by date. Once that was confirmed, Rich proceeded with a possible way forward:

1. Create a new partition for all newly created entries, beyond a future date, in the table. For example, if today is January 5th, create a date range on the partition that goes from February 1, 2017 to April 30th.
2. At the future date, after the new table entries are being placed in the new partition, start partitioning the old data in small increments based on the load your system can handle and the size of the partition you would like to create. For instance, assuming it takes 90 minutes to create a partition consisting of three months of data, and the load on the database is reasonably small between 12-2am every night, run a split operation for three months of data starting at 12am.
   
   Note: In the Progress RDBMS partitioning is an online operation. However, it does create load on the system so doing a full scale split in one shot is not entirely practical depending on how you will be partitioning the data and what your uptime requirements are.
3. Repeat step 2 until your entire multi-terabyte table has been repartitioned.

For over 20 years, I have been working on database products and database technology. In the last six years I have had many discussions about Table Partitioning—several of them with Rich. In fact, I believe he had very eloquently explained this exact migration scenario to me on several occasions. However it wasn’t until that very moment when we were speaking with a customer regarding a real-life scenario and a reasonable set of limitations for migrating to partitioned tables, that I completely understood it. I also realized how awesome it was that Progress thought through this migration scenario with this set of limitations in the design phase of OpenEdge Table Partitioning. 

OpenEdge: Thoughtfully Considered

I find this to be the case with many Progress OpenEdge features. All of the angles are thoughtfully considered to ensure the best possible experience for the customer, and when a practical customer scenario arises for a given design consideration, we get to have that "AHA" moment understanding why something was done just as it was.

Read more about OpenEdge Table Partitioning or contact us to learn more about how Progress can help you meet the demand for continuous operations. OpenEdge Table Partitioning is also a component of the Advanced Enterprise Edition helping organizations to cost-effectively manage their biggest data challenges.