Using PostgreSQL database with BMC Helix Innovation Suite

PostgreSQL is one of the databases that you can use with because of its high efficiency with managing large data and a high number of concurrent users.

supports case-sensitive and case-insensitive (default) PostgreSQL 10.x, 11.x, and 13.x database.

You must set up your PostgreSQL database before you deploy the platform and applications. However, if you are already using with a different database, you can change to PostgreSQL. Before you change to PostgreSQL, see Changes-in-BMC-Helix-Innovation-Suite-to-support-PostgreSQL-database.

(On-premises deployments) Case sensitivity in queries

By default, the PostgreSQL database is case sensitive. However, PostgreSQL supports a special datatype called citext for case insensitive matching. If the database case sensitivity is false, the server appends ::citxet to the string columns and string literal values in Where clauses for case insensitivity matching.

Considerations when working with PostgreSQL

When you use the PostgreSQL database engine, you must use specific operators and functions to perform certain actions in . For more information about the list of these functions, see Changes-in-BMC-Helix-Innovation-Suite-to-support-PostgreSQL-database.

Remediation with deadlock and blocking while DDLs are in progress

Data Definition Language (DDL) defines the structure of the database and the relationships between the data objects in the database.

When you upgrade to a latest version of , multiple DDL calls are made to update the database. In the context of Zero Down Time (ZDT), DDL calls are made when the metadata is being updated such as an update to a new patch, a change made in or , or a D2P deployment.

Running DDL calls requires exclusive lock on the underlying objects that are being changed. When a DDL gets an exclusive lock, it updates the database quickly. However, because PostgreSQL acquires shared locks even for SELECT queries, a PostgreSQL query might prevent a DDL from having an exclusive lock and block that DDL call.

In SaaS environments, specific scripts are run in case a DDL is blocked, and the database query is run seamlessly.

In on-premises environments, a database administrator might monitor the query patterns to decide if they want to create and run appropriate scripts to handle DDL blocking.

Methods to improve performance of with PostgreSQL database

Use any of the following methods to improve the performance of the PostgreSQL database:

Index types

Indexes are database structures that enhance query performance by enabling quick data retrieval. Analyze your application's data and query patterns to determine which columns would benefit from indexing. Use the information in the following table to understand the usage of different index types:

Index type	Description
B-Tree	The B-Tree index is the default index type in PostgreSQL. This index type is suitable for most scenarios. We recommend you use this index type when exact matches are required, such as when you use the = operator and when the expected results are less than 50 characters. Depending on where you want to create the B-Tree index, choose or . For example, if you want to create an index for a record definition, use and to create an index for AR System forms, use .
Hash	If B-Tree index is not optimal, create a hash index when exact matches are required for the data query such as when you use the = operator and when the expected results are more than 50 characters.
GIN	Create a GIN index when you want to run a query which has a larger data match for the query such as when you use the LIKE operator.

For more information about different types of indexes in PostgreSQL, see the PostgreSQL online documentation.

Tip

The number of characters (50 characters) might not always be a criteria to decide between B-Tree or Hash index. We recommend you try different indexes based on the available data and use the index that provides better performance.

Casting with citext

If you are using any custom queries for generating reports in AR System, or running direct SQL queries in Pentaho jobs or in workflows, where the database might convert the data type for case sensitivity, use ::citext to enable casting for the citext data type. For example, the following query in a Pentaho job might perform poorly:

SELECT Name as AppRel_L1_Name, InstanceId as AppRel_L1_InstanceId, source_instanceid as AppRel_L1_source_instanceid,source_classid as AppRel_L1_source_classid, destination_instanceid as AppRel_L1_destination_instanceid, destination_classid as AppRel_L1_destination_classid FROM BMC_CORE_BMC_BaseRelationship  WHERE Source_InstanceId = ? AND (Destination_ClassID = 'BMC_SOFTWARESERVER' OR Destination_ClassID = 'BMC_DATABASE')

However, appending ::citext to this query improves the performance of this query. The following query is the corrected query that works faster:

SELECT Name as AppRel_L1_Name, InstanceId as AppRel_L1_InstanceId, source_instanceid as AppRel_L1_source_instanceid,source_classid as AppRel_L1_source_classid, destination_instanceid as AppRel_L1_destination_instanceid, destination_classid as AppRel_L1_destination_classid FROM BMC_CORE_BMC_BaseRelationship  WHERE Source_InstanceId = ?::citext AND (Destination_ClassID = 'BMC_SOFTWARESERVER' OR Destination_ClassID = 'BMC_DATABASE')

Collation support for unicode languages

Improve the performance of the database by specifying how special characters in unicode languages are matched with the query. For example, specify if the letters Ö, Õ, ø, and o should be treated the same as the letter O, or a generic selection such as -global- in a menu list is placed at the top of the list or alphabetically in the list.

The default collation support for is English. However, you can modify the collation for the specific database columns to improve the performance of the queries. For more information about the collations that are supported in PostgreSQL, see the PostgreSQL online documentation.