Optimizing a schema

You can view possible database optimizations that could improve system performance, database size, or other resource use on the Optimize Schema tab of the System Database landing page. You can view suggested changes by category for the selected database and choose whether to apply the changes.

You must have the ViewAndOptimizeSchema privilege to view this page.

Index suggestions

This section lists columns used in Report Definitions that are not indexed. Consider Existing Index Count and Usage Count when deciding whether to create an index on a column. When Usage Count is high you might be able to improve system performance by adding an index. However, if the Existing Index Count is high on the table, performance might be slower.

Options include:

  • Table – The table in which the column exists.
  • Column – The name of the column to index.
  • Existing Index Count – The number of indexes defined on this table.
  • Usage Count – The number of times this column is included in Report Definitions.
  • Create? – When selected, creates an index for this column.

Unused tables

This section lists tables in the database that are not associated with a Pega Platform class. These tables might have been created for a class that is no longer in the system, or they might have been created manually for some other use. Removing tables that are no longer in use improves resource utilization, including database space, and improves the database schema architecture.

Unused columns

This section lists columns that do not have properties defined on them and so will never be populated, and columns that were created by the Property Optimization wizard, that are not referenced by any report definition. If you are sure that these columns will not be used, you can drop them from the table.

Invalid class mappings

This section lists concrete Pega Platform classes in an application mapped to the pr_other table rather than to a class table. As a best practice, avoid use of the pr_other table except for classes that only have a few rows, little or no activity, and little or no need for reporting.