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.

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.

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.

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.

CREATE OR REPLACE FUNCTION dvcasttime(text)
RETURNS timestamp AS
$$SELECT to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS')$$ -- change the timestamp format to the format being used in the field
LANGUAGE sql IMMUTABLE;

CREATE INDEX dvdDateField1 ON dvtestudr (dvcasttime(udr_json ->> 'dateField1'));