Retrofitting Partitioning into an Existing Application: 3. Scripting & Archiving

This post is part of a series about the partitioning of database objects.

• Introduction
• What kind of partitioning can you do?
• Scripting and Archiving
• Examples from Real Life
1. General Ledger reporting: Typical example of partitioning for data warehouse-style queries
2. Payroll: Avoiding the need for read-consistency in a typical transaction processing system.
3. Workflow: Separate active and inactive rows, and partial indexing.
• Conclusion

Scripting

If you introduce partitioning, you need to look after it.

• It is common to partition a table by date, or another column that is a proxy for the date, such as an accounting period. Often that implies a regular but not relatively infrequent maintenance activity, perhaps only annually. 
• You are likely to have to add, remove and possibly compress partitions. There may be groups of tables that have to be similarly partitioned.  You can easily end up in a hellish world of manual scripting.
• This can make interval partitioning attractive because Oracle automatically creates the partitions on demand. However, you are still responsible for any subsequent compressing and purging
• Interval partitions (other than the ones you explicitly specify, and the whole point is that you need only specify the first one in the range) will be given system-generated names. 
• On the other hand, explicit partition names, with a consistent naming convention, can be very helpful when you come to partition-wise operations during archive/purge operations. 
• If you are going to manage partition DDL scripts manually, then you need strict version control. 

For PeopleSoft, I created a utility to generate partition DDL from the PeopleSoft metadata. It was only worth my while doing this because I was solving the same challenge with partitioning different PeopleSoft product at many different customers. It is unlikely that you will be willing to put the investment into that sort of utility for a single implementation of an application.

• Manual scripting opens the possibility for manual errors to creep it.
• Generating DDL guarantees a degree of consistency.

Archiving

Never let the archiving tail wag the performance dog.  

You pay for and implement partitioning for the benefit of the application users.  Archiving is frequently done for much the same reasons.  Partitioning can make archiving much easier if you can archive whole partitions at a time.  However, making the archiving experience better is not the same as making the user experience better.

Where you have partitioned by time, it is frequently the case that you can also archive by time, and you have a rolling window of partitions that you both add, remove, and sometimes compress and possibly merge on a regular basis.  It may be that the partitioning design that is best for application performance will also lend itself to partition-wise archive.  Partition-wise archiving is attractive, but not at the expense of application performance.

In the next posts, I will look at some real-life examples of how partitioning was introduced into an application.