Adding partition to database

Pega-as-a-Service
, , , , , ,
1

In our table in our center , our database admins suggest that we add partition according to pxoutcometime column .

Does this change have a problem in the working logic of pega or what are your suggestions

.We are getting a lot of select queries . Name of Our center table =Pega_data_prod.pr_data_ıh_fact

2

@EmirhanD please can you provide the reason for your DBA’s suggestion?

Are you experiencing any product issue?

Take a look at the available documentation about that table:

Properties in the pr_data_ih_fact table

Values of the pxOutcomeTime property are not unique in the Oracle database

Deleting the records from the FACT table

Please see the caution advised in a similar post here: Change the primary key of IH_Fact table

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.

If you decide to log a support incident, please provide the INC reference here.

************* DB partitioning is no longer supported in Pega for Pega 8.4 and later ************************

Latest information:

DB partitioning is no longer supported in Pega for Pega 8.4 and later since it breaks the update process so clients have to manually apply schema.

Beginning in Pega Platform version 8.4.x, Pega stopped supporting database partitioning because it forces clients to use a non-standardized update process to take advantage of software updates.

For questions concerning database partitioning, contact Pega support.

You can find the latest info here:

https://docs.pega.com/bundle/platform/page/platform/system-administration/high-availability-split-database-overview.html
https://docs.pega.com/bundle/platform/page/platform/system-administration/database-tables-overview.html

3

@EmirhanD Do you still need any help or can you accept solution above?

Feel free to post your question in the Ask the Expert - Pega Database with Jie Long session which is currently open.