Database table partitioning
Hi All,
need some inputs/suggestions on the below qn.
In our application, As part of Archival and Purge strategies, we are supposed to copy data from Pega data schema to Pega Archive schema which holds business data which is older than a specific period.
But, we want to copy a data 2.5TB from work tables in Pegadata schema to Pega Archive schema.
As part of the discussions with internal db team, it is suggested to do partitions of tables to copy the data from one schema to another schema.
Total Pegadata size is 6 TB data to be moved is 2.5 TB
We are looking for some info on impact on application if we do partition.
1. what will be the imapact on application and its performance.
2. Is the partitioning we do in pega applications and what could be the disadvantages w.r.to pega applications.
3. Is this the practice we follow if we want to move data? Import and export and CTAS are expected to take 10-12 hrs. so, not suggested by db team.
4. Generally, who will do if we have to partitions? DB team or Application team?
5. Any experiences on this ?
Appreciate any suggestions.
Accepted Solution
Updated: 7 Jul 2023 11:58 EDT
Pegasystems Inc.
GB
Our SME made some recommendations in the post Purge and Archival strategy
Please review previous suggestions made on the forum, for example Adding partition to database
We would urge you to identify the reason why your DBA made the recommendation and try to resolve that problem first before making any changes to the table. However I do believe that it is a supported manipulation - see suggestion in this post Does Pega allows Database Table partitioning ?
When using partitions, keep these considerations in mind:
Our SME made some recommendations in the post Purge and Archival strategy
Please review previous suggestions made on the forum, for example Adding partition to database
We would urge you to identify the reason why your DBA made the recommendation and try to resolve that problem first before making any changes to the table. However I do believe that it is a supported manipulation - see suggestion in this post Does Pega allows Database Table partitioning ?
When using partitions, keep these considerations in mind:
- The Pega Platform does not include any partitioned tables. During an upgrade, partitioned tables are treated as non-partitioned tables.
- Queries that are included with the Pega Platform are not appended to the customer's partition key column, which means that you might not realize a real-time query benefit by partition pruning.
- Queries that are included with the Pega Platform use a global index. The cost of maintaining the global index must be considered when partitioning. The maintenance options include: online, using a drop partition; update global indexes, or offline rebuild. Oracle also has an asynchronous global index rebuilding feature.
- Do not change the primary key to a local partitioned index. Doing so will trigger locks on all index partitions that use DML and burden the real-time application.
- Partition pruning with the local partition index occurs if a custom SQL's predicate includes the partition key column.
- You can use a partition to archive data. Migrating a partition into a separate database can benefit system performance by reducing real-time system data volume.
- It is not recommended that you use pxUpdateDateTime or pyassignmentstatus as partition key columns because they are volatile.
- When you partition across work tables, if a work table is partitioned by pzInsKey and pxObjClass, other work tables such as Assign- and WorkAttach- can be partitioned by pxRefObjectKey and pxRefObjectClass. This is the same as partitioning by pxCreateDateTime.