Data Veracity Performance Tuning

Owned by Kevin Wu
Last updated: Jun 15, 2023
3 min read

Tuning on PostgreSQL

This section describes how to tune the PostgreSQL database for use with Data Veracity.

Much like Oracle, Data Veracity allows the user to improve the performance of PostgreSQL queries. The following will be a suggestion on how to improve the performance. It is always important to consult your DBA before proceeding with any of these suggestions or if they are necessary in your solution.

PostgreSQL has certain specific syntaxes that are used for certain data types, the following examples will show how to index in PostgreSQL for string data types, integer data types and timestamps.

The following command will show how a string field can be indexed.

Index for String fields in PostgreSQL
CREATE INDEX dvStrField1 ON dvtestudr ((udr_json ->> 'stringField1'));

For fields located within multiple sublevels of UDR, the following command will create a unique index for a field that is located in within the subUDRs.

Index for nested fields
CREATE INDEX dvStrField1 ON dvtestudr ((udr_json -> 'sub1Field' -> 'sub2Field' ->> 'stringField1'));

Following from the example as shown above, to index the UDR field intField1, the search performance could be improved by creating an index in the PostgreSQL database for this field. The following will be an SQL command to create a unique index meant for an integer field in the JSON record.

Index for Integer fields in PostgreSQL
CREATE INDEX dvIntField1 ON dvtestudr (cast(udr_json ->> 'intField1' AS int));

When it comes to timestamps, a function is the recommended suggestion to make the data type immutable when it is casted for an index. The example will show a sample PostgreSQL function and the command for the index.

Index for timestamps

Tuning on Oracle 

This section describes how to tune the Oracle Database for use with Data Veracity. 

Oracle Installation

Be sure to consult your DBA to ensure that all system configuration tasks for the Oracle installation are completed before getting started on Data Veracity.

Data Veracity uses Oracle 19c's JSON capabilities for storing data. This provides the capability to improve the performance of the SQL queries from Data Veracity, by applying the Oracle's database features for JSON fields indexing. The following are suggestions to how you may improve performance for Data Veracity, it is highly recommended that you consult your DBA before proceeding with any of these suggestions or if they are necessary in your solution.

Example of a table created in Data Veracity, viewed using sqlplus



The Table above shows a table, created using the SQL generation in the Data Veracity profile. The UDR_JSON field is where the record for every UDR is kept. The UDR is kept in a JSON format and Oracle 19c has a way to allow users to create an index of a field located in the JSON format itself.

Example UDR with 3 sub UDR levels



Take for example, when the user frequently searches for the UDR field intField1, the search performance could be improved by creating an index in the Oracle database for this field. The following will be an SQL command to create a unique index meant for a field in the JSON record.

Index for intField1



If fields located within multiple sublevels of UDR are frequently queried by the user, an index could be created to improve the performance for searching this field. The following SQL command will create a unique index for a field that is located in within the subUDRs. 

Index for sub1Field.sub2Field.intField