UNIFYBroker PostgreSQL Database Recommendations

Maintenance Tasks

Vacuum

The PostgreSQL command VACUUM (documentation) allows for the reclamation of space still used by dead tuples. The following is recommended to be run regularly:

CommandResultWhen
VACUUM ANALYSEReclaims disk space without releasing it and updates statistics used by query planner.Regularly, can be run during operation
VACUUM FULLRelaims and releases disk space.Irregularly, after substantial changes to the database, should not be performed while database is in use.

There is also the autovacuum daemon which can be utilized to manage the running of VACUUM ANALYSE once table-specific thresholds are passed. By default, autovacuum is enabled and it is recommended to leave it so.

If autovacuum is enabled the following query can be used to see statistics for each table on the last manual and automatic vacuums, the current number of dead tuples, and the autovacuum threshold.

SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1;

Reindexing

While PostgreSQL will manage its indexes, they can become bloated over time. Thus it is recommended to occasionally perform a REINDEX (documentation). Assuming a standard database name:

REINDEX DATABASE "Unify.IdentityBroker"

A summary of index information, including row count, size and usage, can be generated with the following query:

SELECT 
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

Optimising Performance of Entity Reads

Depending on how UNIFYBroker is configured and the number of entities being stored by the PostgreSQL, performance may be improved by adding indexes to certain schema fields. The addition of unneeded indexes will reduce performance in other areas and so should not be done without consideration. Key candidates for schema fields that should be considered are:

Adapter reflection with relational transformations

Field TypeBenefits Observed In
Connector schema fields marked as Key.

For best results, if multiple fields on a connector are key fields, all should be indexed.
Processing connector deletes, adapter reflection with relational transformations
Right-side relationship keys of Group, Multi-Value Group, Foreign Multi-Value Group and Join transformationsAdapter reflection with relational transformations
Fields which contribute to fields used as either right or left side relationship keys in the above transformations

For example: Connector field groupname renamed with a Rename Transformation to gname which is used as a Join Transformations left-side relationship key. groupname should be considered for an index. Furthermore, if groupname was, instead of being a connector field, generated by another transformation, the field or fields it is generated from should also be considered for indexes. 
Adapter reflection with relational transformations
Fields being filtered by on an OData or SCIM gatewaysReading of entities though these gateways
Fields which are set as the user id lookup field on a SCIM gatewayReading of entities though this gateway

Once the schema fields to index have been determined, the index can be created with the following SQL query:

CREATE INDEX unique_name_of_index            -- Give the index a unique name ie: entity_fieldname_index
    ON public."Entity" USING btree
    (("EntityValues"->'field_name'))         -- Replace field_name with schema field name.
    TABLESPACE pg_default;

After adding schema field indexes and a period of standard operation has passed, the index statistics query should be used to validate if the new indexes should be kept.

Server Performance

In general, the default configuration should suffice in most cases. If needed, however, many in-depth resources for performance tuning can be found here.

Is this article helpful for you?