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:

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

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.