Table Partitioning and Pro2 Simplify Your Database Needs

OpenEdge Table Partitioning simplifies maintenance and adds database flexibility. When combined with OpenEdge Pro2 you can migrate to a partitioned database and test its benefits with almost no downtime. If you haven’t upgraded, you’re missing out on some big wins for your database.

Just over two years ago when I joined the Progress OpenEdge development team, they were completing the first release of the Table Partitioning feature. I was extremely impressed by how complete the first release of this feature would be. Before Progress, I did a lot of work with PostgreSQL’s database partitioning functionality to help customers solve a variety of problems. Specifics included:

1. Managing extremely large indexes taking up a lot of run time memory
2. Implementing data retention policies (I once heard Josh Berkus of the PostgreSQL core group say, “Nobody wants a data retention policy until they run out of disk space!”)
3. Providing the ability to dump and load strategic portions of data between databases

In all cases, the customer had run up against a scalability issue and properly partitioning the data helped address this. We were able to achieve excellent results with table partitioning, even though PostgreSQL did not at the time (and I believe still does not) provide the following capabilities available in OpenEdge Table Partitioning:

1. An on-line split of a partition (or partition of the data)
2. A global index that could be used on queries that spanned partitions
3. The ability to update a partition key and have it dynamically move to a different partition
4. The ability to manage partition inserts without having to rely on user written Triggers

I considered the Progress implementation to be far more complete then the PostgreSQL implementation and a fantastic product. Despite all of this, customers and partners have been a little slow to adopt OpenEdge Table Partitioning. It has been more aggressively adopted within the PostgreSQL world. I have heard that OpenEdge customers are typically conservative in their adoption of new features. They love the fact that the database just works and it runs. If it works, why upgrade? Well, I would like to tell you why:

Table Partitioning provides you with a multitude of options for managing large data sets not available to you in previous releases. Here are just a few off the top of my head:

1. You can keep the last 24 months of data online and archive the rest of the transactions (partition by transaction date) to help simplify maintenance
2. You can keep indexes very small to allow for fast rebuild time. In addition, this keeps the majority of the data available while the index is rebuilt. To be more concrete, if you rebuild and index on a 2TB table, the data is offline for a long time and the index rebuild could take upwards of 6 hours. However, if the 2TB table is properly partitioned into 200 10GB tables, the index rebuild of a single partition will likely be completed in less than an hour.
3. You can place frequently accessed data on a fast set of disk drives
4. You can force unrelated and frequently accessed data onto different spindles for enhanced performance

The biggest objection I have heard regarding the use of data partitioning is that people don’t want to pay the downtime or performance expense required to re-partition the data. There is a simple solution for that: Progress OpenEdge Pro2. OpenEdge Pro2 can easily be used to migrate a data non-partitioned database to a partitioned database. The beauty of this is that it can be done with limited impact on performance and with almost zero downtime. The formula I would use goes like this:

1. Partition the target database as desired
2. Install Pro2 on the source database to create a set of Triggers
3. Let the target catch up to the source while update, insert and delete transactions continue to execute against the source database
4. Test query and index rebuild performance against the target database
5. When I am satisfied with the results in step 4, I schedule a small maintenance window and subsequently make the target database the new source

The outage time for something like this is quite minimal, and I now have the wins that OpenEdge Table Partitioning can bring.

If you are interested in taking this step forward, please let me know. I will personally work with you (and by extension so will several others on our team) to help make your efforts a success.

Want to learn more about Table Partitioning? Read our comprehensive Solution Brief: Table Partitioning for Improved Manageability and Availability. For advice on how to best implement Table Partitioning for success, check out this whitepaper drafted by Richard Banville and Dapeng Wu of the OpenEdge Development team: Table Partitioning: Improve Performance through Increased Concurrency.