Cluster Recommendations Innovation Release

Recommendations

The Recommendations tab shows a scorecard and a list of recommendations for a cluster which can help to improve the Postgres cluster performance.

The Scorecard and Recommendation are directly related. A cluster will only score lower than Excellent if one or more recommendations are available. Once these recommendations are applied or dismissed the score will return to Excellent.

Scorecard

In the Scorecard, a score is assigned to a given cluster for each grading category:

• Indexes
• Statistics
• Configuration
• Security
• Workload

Generally, grades are assigned on an A-F scale.

For Indexes:

• Excellent (A), if no recommendations were generated to tune indexes,
• Good (B), Fair (C) and Poor (D), depending on how many recommendations were generated.

For Indexes, the scores are computed by considering only primary nodes in the case of PSR and write leaders in the case of PGD/AHA. Furthermore, the score is related to the database in the PG cluster that is performing the worst (not an average over all databases created in such a cluster).

For Statistics:

• Excellent (A), if no recommendations were generated to add statistics,
• Good (B), if at least one recommendation was generated.

For Configuration:

• Excellent (A), if no recommendations were generated to alter configuration,
• Good (B), Fair (C) and Poor (D), depending on how many recommendations were generated.

For Security:

• Excellent (A), if no recommendations were generated to improve the database's security configuration,
• Good (B), Fair (C) and Poor (D), depending on how many recommendations were generated.
• Failed (F), if the cluster's security state requires immediate remediation.

For Workload:

• Support for workload scorecards will be added in a later release.

When selecting a specific category, text is displayed to help the user understand the relevance of these recommendations, beyond providing more context on how the score was computed.

A radar chart shows the global score of the cluster across all categories.

Recommendations list

Below the scorecard, a list of recommendations is displayed depending on the category selected in the scorecard:

• Status - the status of a recommendation (New, Ignored, Applied)
• Node ID
• Database Name
• Size Increase (for indexes) - estimated index size
• Cost Reduction (for indexes) - estimated percentage cost reduction computed as (abs_benefit/total_cost) * 100, which is relative to all the workloads executed in a database
• Total Cost (for indexes) - execution time plus plan time for running this query
• Absolute Benefit (for indexes) - absolute cost reduction that applying this index could produce
• Evaluation Time - creation time for this recommendation
• Query - text of the query to create an index or a statistic.

Use the settings icon to show/hide columns.

Managing recommendations

CTA buttons in each row let you:

• Mark the recommendation as applied after you have use the recommendation to adapt the database.
• Mark the recommendation as ignored by hiding it.
• Copy the query for the recommendation.

Filtering, sorting and searching recommendations

The filters allow users to restrict data by:

• Database
• Node
• Status

Use the search bar on the left side to search for a substring in the query text.

Sort rows in the table by column in ascending or descending order with drop-down menu on the right side.

Understanding Recommendations

What do the "mark the recommendation as applied/ignored" buttons do?

Use the 'applied' option to tell Hybrid Manager that you have applied the recommended change. If you mark a recommendation as applied, it will disappear from the list and when the page is next reloaded your score will increase commensurately. If the same recommendation is received again (suggesting the change was not actually applied or has been undone), the recommendation will reappear and the score will drop.

Use the 'ignored' option to tell Hybrid Manager that you have reviewed the recommended change and decided it is not appropriate to apply it. If you mark a recommendation as ignored, it will disappear from the list and when the page is next reloaded your score will increase commensurately. If the same recommendation is received again it will be ignored and will not reappear.

Where does this data come from?

The Index and Statistics recommendations are sourced from the Query Advisor extension, as such this extension must be enabled in the monitored database.

The Configuration and Security recommendations are sourced from the Postgres configuration itself.

In future releases, recommendations will be generated from other data sources including historical metrics and Query Diagnostics.