Retrofitting Partitioning into Existing Applications: Conclusion

This is the last post in 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

Conclusion

The decisions you make when you introduce partitioning into an existing application are similar to those when you design an application and partitioning together.  The essential difference is that by the time it has been built you probably can't make changes to the application.   So partitioning either works with the application as it is or it doesn't.

• Make sure you understand what your application is doing. 
• Match the partitioning to the way the application accesses data so that the application queries prune partitions. Even if that means that it is harder to archive data later on.
• If you are not getting partition elimination, you probably should not be partitioning. 
• Range and list partitioning keep similar data values together, so it follows that dissimilar data values are kept apart in different segments. That can avoid I/O during scans, but if it keeps transactions apart it can also avoid read consistency. 
• Hash partitioning spreads data out across segments and can be used to avoid some forms of contention.
• Partitioning can separate data with different usage profiles, such as active rows from inactive rows. They might then have different indexing requirements. 
• Partial indexing of partitioned tables allows you to choose which partitions should be built in a locally partitioned index.