Database partitioning and database archiving are both methods for improving application performance. Many IT organizations use one or the other, but using them together can provide additional incremental value to an organization.
Database partitioning is a well-known method to DBAs and is supported by most of the commercially available databases. The benefits of partitioning include:
- Reducing database retrieval time: A smaller and more manageable partition makes it possible to perform any type of action (such as data loads, index creation and rebuilding) on the individual units of the database thereby saving time and improving performance.
- Minimizing backup and recovery windows: Smaller sized partitions offer more options to perform backup and recovery for selected partitions.
- Improved query performance: Partitions can be created on column ranges resulting in a query, accessing only the partitions that match the query filters, rather than the entire table.
- Storage cost reduction: Partitioning can reduce storage costs by placing the tablespace associated with a partition on lower cost storage devices
- Increased application availability: Maintenance operations can be done independently for different partitions of the same table or index. Dividing critical tables and indexes into partitions shortens maintenance windows; recovery times, and reduces the impact of failures, thereby increasing the availability of mission critical applications.
Database archiving when used together with database partitioning can further improve application performance and increase cost savings. The value proposition goes beyond partitioning and helps enterprises achieve data governance and meet compliance requirements for data retention and disposal.
- Database archiving can improve query performance, regardless of what the query conditions are– by relocating data sets from your database and eliminating those data sets from being processed entirely. Database archiving also ensures that data and referential integrity across tables and business entities will be maintained after the data is archived.
- Database archiving reduces the backup window by simply shrinking the size of the production database, so that less data needs to be backed up.
- Enforcement of retention and disposal polices, as per regulations, mandates that the data is retained for a certain amount of time and then disposed of once the retention expires. Database archiving solutions can help organizations to comply with such regulations easily.
For a database administrator the obvious next questions are “Can I start with database partitioning and as my partitioned data ages and becomes less frequently accessed, can I archive it and remove the data entirely from the production system for longer term retention?” The answer is “Yes”, but to get to a broader information lifecycle management strategy, some of the keys to success are:
- For packaged applications like Oracle E-Business Suite, PeopleSoft, Siebel, and SAP, table and entity relationships are very complex. So instead of just partitioning or archiving one table at a time it’s better to partition entire business entities (generally logically related set of tables) so that when there is a need to archive and retrieve, all the related data can be easily accessed and relocated together. The key is to leverage the data relationships (beyond physical primary key and foreign key associations) so both partitioned data and the related transactions are understood and data can be moved in concert into a database or file archive for long term retention.
- For simple applications and data warehouses, table-based partitioning may be adequate, depending on the complexity of record relationships as well as the application logic. But even for simple applications, archiving should be used in conjunction with partitioning to remove aged data that are infrequently accessed from the production system for more effective data growth management and long term data retention.
- The whole process of partitioning and archiving is managed centrally from a single information lifecycle management solution so that the two strategies go hand in hand. That means your archiving strategy can be based on your initial partitioning strategy, so that you can archive an entire partition or manage your archive optimally based on partitions as well.
- A robust record level security and audit logging mechanism to track all partitioning and archiving process, as well as any access, changes to retention policies, and purging of the data at the end of the lifecycle is key to establishing a chain of custody for compliance.
- Effectively assigning and enforcing retention policies for different classes of data and managing the entire information lifecycle through the different stages and storage tiers, all the way to final purging once the retention period expires. It is important that expired data is automatically purged in a timely manner (after a review and approval process), so that it doesn’t become a legal risk.
- The cost savings associated with each tier and performance improvements should be quantified and demonstrated. This can help you to tune your overall information lifecycle management strategies and to make the business case and prioritize your project to upper management.
Partitioning and archiving are complimentary methods for managing data growth, improving performance, and efficient long term data retention to support compliance. A data management strategy that uses both methods will help database administrators and application owners to effectively reduce infrastructure and maintenance costs, as well as improve productivity and compliance to retention regulations.