Implement Indexing and Optimization Enhancements for Improved Database Performance

[Sachin-Mamoru]

Describe the issue:
This issue tracks the implementation of several database optimization strategies to enhance query performance and ensure data integrity across various tables. The enhancements will include adding necessary indexes, implementing function-based unique constraints, optimizing primary keys for legacy tables, and analyzing query usage to ensure the applied optimizations are beneficial.

Tasks:

1. Add Missing Indexes:

Add missing indexes in Oracle Rac script.

CREATE INDEX IDX_AT_TI_UD_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), TENANT_ID, TOKEN_STATE, USER_DOMAIN);
CREATE INDEX IDX_AT_CIDAUTID_UD_TSH_TS_LO ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);
CREATE INDEX IDX_AUTH_CODE_AU_TI_LO ON IDN_OAUTH2_AUTHORIZATION_CODE (LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, STATE);
CREATE INDEX IDX_AUTH_USER_UN_TID_DN_LO ON IDN_AUTH_USER (LOWER(USER_NAME), TENANT_ID, DOMAIN_NAME);
CREATE INDEX IDX_OCA_UM_TID_UD_APN_LO ON IDN_OAUTH_CONSUMER_APPS(LOWER(USERNAME),TENANT_ID,USER_DOMAIN, APP_NAME);

2. Proposed Solution for Unique Constraints and Function-Based Indexing:

Implement function-based unique indexes for columns requiring case-insensitivity (e.g., LOWER(USER_NAME)). This approach will support case-insensitive searches while maintaining uniqueness and improving performance.
Optimize Primary Keys for Legacy Tables:

Current Structure

CREATE TABLE IDN_AUTH_USER (
            USER_ID VARCHAR(255) NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            DOMAIN_NAME VARCHAR(255) NOT NULL,
            IDP_ID INTEGER NOT NULL,
            PRIMARY KEY (USER_ID),
            CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID)
)

Proposed Structure

CREATE TABLE IDN_AUTH_USER (
    USER_ID VARCHAR(255) NOT NULL,
    USER_NAME VARCHAR(255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    DOMAIN_NAME VARCHAR(255) NOT NULL,
    IDP_ID INTEGER NOT NULL,
    PRIMARY KEY (USER_ID)
);


-- Function-based unique index for case-insensitivity
CREATE UNIQUE INDEX IDX_USER_STORE_UNIQUE ON IDN_AUTH_USER (
    LOWER(USER_NAME), TENANT_ID, DOMAIN_NAME, IDP_ID
);

3. For tables with composite primary keys, restructure by introducing an auto-generated primary key column, converting existing primary keys to unique indexes. This change aims to enhance update and retrieval performance.
   Analyze Query Usage Before Adding Indexes:

Current Structure

CREATE TABLE IDN_IDENTITY_USER_DATA (
            TENANT_ID INTEGER DEFAULT -1234,
            USER_NAME VARCHAR(255) NOT NULL,
            DATA_KEY VARCHAR(255) NOT NULL,
            DATA_VALUE VARCHAR(2048),
            PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
);

Proposed Structure

CREATE TABLE IDN_IDENTITY_USER_DATA (
    ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    TENANT_ID INTEGER DEFAULT -1234,
    USER_NAME VARCHAR(255) NOT NULL,
    DATA_KEY VARCHAR(255) NOT NULL,
    DATA_VALUE VARCHAR(2048)
);

CREATE UNIQUE INDEX IDX_UNIQUE_LOWER_TENANT_USER_DATA ON IDN_IDENTITY_USER_DATA (TENANT_ID, LOWER(USER_NAME), DATA_KEY);

4. Before applying the proposed indexes, review and analyze query patterns to ensure the new indexes address actual usage requirements and avoid adding unnecessary overhead.

CREATE INDEX IDX_AT_CK_AU_LO ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, LOWER(AUTHZ_USER), TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
CREATE INDEX IDX_AT_AU_CKID_TS_UT_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_UU_LO_UI_UUN_TI ON UM_USER(UM_ID,LOWER(UM_USER_NAME),UM_TENANT_ID);

5. Add a section in deployment configurations and explain the impact of having case sensitive database for usersotre and case insensitive database for the identity and session databases.

• The major impact of token table

[inthirakumaaran]

Performance testing tracked in #21872

[sumedhe]

We conducted a performance improvement analysis for case-sensitive databases, focusing on the impact of functional indexes. The tests involved fetching data using the LOWER function from a table containing 1M records. Below is a summary of the average execution times observed.

Summary for average execution time:

	Without functional index	With functional index
PostgreSQL	50ms	0.040ms
DB2	600ms	7ms
Oracle	70ms	2ms

Removing the Primary Key:
When adding a functional index, a duplicate index is created if a primary key exists on the same column set. To avoid redundancy, we can remove the primary key from such tables. However, doing so may lead to a performance degradation for case-sensitive queries and table joins.

Ref:

Average execution times:

PostgresSQL

DB2