The PostgreSQL command
VACUUM (documentation) allows for the reclamation of space still used by dead tuples. The following is recommended to be run regularly:
|Reclaims disk space without releasing it and updates statistics used by query planner.||Regularly, can be run during operation|
|Relaims 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;
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 Type||Benefits 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 transformations||Adapter 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 gateways||Reading of entities though these gateways|
|Fields which are set as the user id lookup field on a SCIM gateway||Reading 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.
In general, the default configuration should suffice in most cases. If needed, however, many in-depth resources for performance tuning can be found here.
Customer support service by UserEcho