Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Optimizing database properties

Updated on July 8, 2022

You can improve reporting and search performance in your Pega application by optimizing (exposing) Single Value properties as distinct columns.

By default, your Pega database stores property values in a Storage Stream column called pzPvStream, using a Binary Large Object (BLOB) database type. You cannot retrieve the data for these properties without first decompressing the column data, which can significantly slow down data retrieval and processing in other operations.

Optimization tools

Use property optimization to enhance your application performance by avoiding retrieving property data directly from the BLOB. You can optimize properties in various ways, depending on your application structure and data retrieval requirements. To optimize (expose) these properties after the property has values, use the Pega-provided optimization tool or tools described in the following sections that best fits your application.

Property Optimization tool (recommended)

In Dev Studio, use the Property Optimization tool in the Application Explorer. This tool exposes and queues a background job, which populates the newly exposed columns as a single step. For more information, see Optimizing properties from the user interface.

Modify Schema wizard

To expose properties but not schedule a background job to populate the column with values from the BLOB, use the Modify Schema wizard in Dev Studio. This tool exposes the column without immediately populating it with values. After exposing the column, populate it using one of the Column Populator tools.

You can also view schema, increase the size of columns, and generate an SQL text file that defines a table, that you can later use to modify the schema. Use this wizard to review the current database table structure, understand class-to-database table mapping, and provide additional context. For more information, see Schema modification using the Modify Schema wizard.

Create a column using your SQL client

Pega supports using your SQL client to create a new column for your data that you need to expose. Before you do so, check the specific guidance for manual column creation. For more information, see Adding columns to your database schema.

Use a Column Populator tool

After exposing a column with the Modify Schema wizard or manually creating the column, populate the new database column for existing instances using one of the command-line Column Populator tools:

Note: These tools do not lock case objects, and so it is possible, although rare, for the column to contain a property that is different than what is in the BLOB. This can happen if another process updates the case object during the brief period of time in between the BLOB read and column update steps of the Column Populator tool that you are using.

Other property optimization details

The Column Inclusion field on the Property form indicates the opinion of the developer who created or updated the property as to whether the property might need to be exposed. This value does not affect any runtime behavior of the property. For more information, see Property form — Completing the Advanced tab.

Exposed properties that have been fully implemented are shown on the Database Class Mappings tab. For more information, see Database Class Mappings tab and Adding columns to your database schema.

  • Optimize a property from the user interface

    Use the Property Optimization tool to improve data retrieval performance from your Pega database. This tool creates a dedicated database column for a selected property and starts a background process to populate the values for the new column from existing BLOB values. This eliminates the need to read the BLOB when fetching property data.

  • Adding columns to your database schema

    Pega supports the manual addition of columns to expose additional properties in your database schema for optimized reporting and search performance.

  • Removing the optimized status from a property

    Deoptimize a property that you previously optimized to completely clear the optimized property from a database schema. A deleted optimized property falsely retains its optimized status through its property-optimization log associations upon its recreation, and Pega Platform cannot optimize the property again. Deoptimize the property to completely clear its status from the schema and table in order to optimize the property again.

  • Modifying a schema by using the Modify Schema wizard

    You can view and modify the PegaRULES database schema by using the Modify Schema Wizard. You can determine which concrete classes are mapped to each database table, which properties in each class are exposed as distinct columns, and which Single Value properties in each class are not exposed but are contained in the Storage Stream (BLOB) column.

  • Populating properties by using the Column Populator tool

    After adding a new column to a table schema, you can populate values for that new column by using the Database Column Populator or the service-enabled Column Populator command-line tools.

  • Optimizing properties by using the Select values gadget

    To improve value retrieval performance, you can add and remove optimizations for fields in the Select values gadget on report definitions and the Report Editor. When you add or edit a filter in a report definition rule, you use the Select values gadget to select a value that the property of a filter is equal to. If the property is equal to many values, populating the Select values gadget can result in poor performance. By optimizing the property of the filter, the number of values that are queried is reduced, resulting in improved retrieval

  • Using declarative indexes

    Create a Declare Index rule to define criteria under which Pega Platform automatically maintains index instances for faster access. An index can improve search and reporting access for properties that cannot be exposed as database columns because they are embedded within an aggregate property. Creating a Declare Index rule manually is an alternative to the integrated approach used by the Property Optimization tool.

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us