With pg_stat_monitor, you can get better insights into PostgreSQL query performance.

PostgreSQL

The cornerstone for query performance adjustment understands query performance patterns. It influences how a database cluster develops in numerous ways. There are also obvious financial consequences, both direct and indirect.

Can easily add various catalog views and extensions for PostgreSQL to provide more detailed query statistics. The picture is usually permanently stitched together by integrating separate datasets because each perspective focuses on a different component. That takes time and effort, and even then, the picture may not be complete.

The pg stat monitor extension tries to provide a complete picture by presenting much-needed query performance information in one place. Also, the extension has been in the works for over a year and is almost ready to be public.

Some Useful Extensions

To understand how a query acts, the time spent in the planning and execution phases, min/max/meantime values, index hits, query plan, and client application data, you may be relying on a variety of extensions at the moment. Here are a few extensions you may be familiar with.

​​pg_stat_activity 

​
PostgreSQL comes with this view pre-installed. It has one entry for each server process and current activity and query text.

If you’re interested in learning more, visit the official PostgreSQL documentation.

pg_stat_statements
​
The PostgreSQL server includes this extension as part of the contrib packages. You would, however, need to construct the extension manually. It’s a query-by-query aggregation of statistical data, providing min/max/mean/standard deviation for execution and planning times, as well as other helpful statistics and query text.

You may learn more about pg stat statements at the official PostgreSQL documentation site.

auto_explain
​
The PostgreSQL server also has a helpful extension. Any query that exceeds a time threshold set by a GUC dumps query plans in the server log.

(The GUC stands for “Grand Unified Configuration.”)
pg_stat_monitor

While all of the above-listed views/extensions are useful in and of themselves, to fully understand query performance patterns, one must manually combine client/connection information from pg stat activity, statistical data from pg stat statements, and query plan from auto analyze.

And that is precisely the problem that pg_stat_monitor solves.

Over the last year, the feature set has expanded, and it now provides all performance-related information that you could need to diagnose a low-performing query in a single view. See our GitHub repository for additional details about the extension or our user guide for user-specific documentation.

Feature Set

Although several features from previous releases have already been addressed in this blog, I’ll go through them again here for completeness.

• Rather than delivering a single set of ever-increasing numbers, pg_stat_monitor computes statistics over a specified number of time intervals or time buckets. This improves data accuracy significantly, particularly in high-resolution or unstable networks. 

• pg_stat_statements groups counters by (userid, dbid, queryid), however, pg stat monitor utilizes a more detailed group for greater precision: 

1. Client IP Address (client ip), 
2. Plan ID (planid), 
3. Application Name (application name),
4. Bucket ID (bucket), 
5. User ID (userid), 
6. Database ID (dbid), 
7. Query ID (queryid). 

This enables you to dive down into the performance of queries originating from specific client addresses and apps, which Percona has found extremely useful in various situations.

• pg_stat_monitor gives you the option of seeing queries with placeholders for parameters or actual query instances.
• Query Plan: Each SQL now has its execution plan. We also discovered that having query parameter values is helpful because it allows you to run EXPLAIN on it or play around with altering the query to make it execute better and make communication about the query clearer when discussing it with other DBAs and application developers.
• Tables For a statement, makes it simple to discover all queries that reference a specific table. The information offered by pg_stat_statements is comparable to this collection.
• Histogram: When it comes to identifying problems, the visual depiction is beneficial. A timing/calling data histogram can now be displayed in response to a SQL query using the histogram function. Yes, psql is supported.
• Functions: It may surprise you, but we understand that functions can execute statements internally!!! To make monitoring and analysis more accessible, pg_stat_monitor now has a column that keeps track of the top query for an account so you can go back to the function that started it all. 
• Relation Names: The “relations” column in the pg stat monitor view contains the names of the relations utilized in a query. This decreases your workload and makes analysis more accessible and faster. 
• Query Types: Analysis is simplified when queries are classified as SELECT, INSERT, UPDATE, or DELETE. It saves you even more time and adds to the simplicity of the pg stat monitor. 
• Query Metadata: Sqlcommenter from Google is a great tool that bridges the gap between ORM frameworks and database performance. And we are in favor of it. As a result, you may now insert any critical value data in the comments in your SQL statements using the /*… */ syntax (for more information, see the Sqlcommenter documentation), and the information will be interpreted by pg stat monitor and made available in the comments column in the pg stat monitor view.
• Most monitoring/statics collector tools/extensions track successful requests, as demonstrated in several monitoring/statics collector tools. However, monitoring ERROR, WARNING, and LOG provide helpful troubleshooting information in many circumstances. pg stat monitor collects data on these queries and monitors the ERROR/WARNINGS/LOG. There is an error level (elevel), SQL Code (sqlcode), and an error message attached to PostgreSQL queries with ERROR/WARNING. Pg_stat_monitor gathers all of this data and its aggregates.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.