Hello! Majority of DBAs and companies care about query performance and efficiency, and they are right. In this article we will talk about Postgres monitor query performance.

Query Performance

Change on a query performance is very important indicator of database condition, monitoring queries with highest costs is an important strategy for maintaining stable performance.

Most of the databases label utilized resources to complete a query as cost, of course each database has a different terminology but in general, higher cost means higher resource usage. I’ll write another article about EXPLAIN statement, but it is a helper statement to measure cost of the parts of the statement.

Understanding Query Performance

Costs are the arbitrary units to measure performance but what effects that cost? There are many factors effect the query performance. There are much more other effects but to sum up:

Hardware and Server Resources:

• CPU: Faster processors can handle more complex queries and calculations more quickly.
• RAM: Sufficient memory allows for caching frequently accessed data, reducing disk I/O.
• Storage: Faster storage (e.g., SSDs) can significantly improve read and write speeds, especially for large datasets.
• Network: A fast and reliable network connection is essential for distributed databases and remote queries.

Query Structure and Complexity:

• Table joins: The number of tables joined and the types of joins (e.g., inner, outer, cross) can significantly affect performance.
• Filtering conditions: The complexity of WHERE clauses, the use of functions, subqueries, and the selectivity of filters all play a role.
• Aggregations and grouping: Operations like SUM, COUNT, AVG, GROUP BY, and HAVING can be computationally intensive.
• Ordering: The use of ORDER BY clauses involves sorting data, which can impact performance, especially with large datasets.

Database Maintenance and Tuning:

• Statistics: Current table and index statistics are essential for the query optimizer to make accurate cost estimations. Outdated statistics can lead to poor query plans.
• Vacuuming and defragmentation: Regular maintenance tasks to reclaim unused space and reorganize data can improve performance over time.

Reference Point

Since we need to compare performance values with previous values, we need to have reference points. We use those reference points as a beginning point of a performance management.

I always suggest my customers to have a proper monitoring toolkit to monitor the environment. As an open source tool you can use Zabbix, or you can buy commercial software licenses. You can save performance data as time series.

How to Monitor PostgreSQL Query Performance

Postgresql has a cumulative statistics system which can be configured separately for each statistics collection. Collecting statistic adds some overhead to database performance. Those settings can be configured using postgresql.conf file.

The parameter track_activities enables monitoring of the current command being executed by any server process.

The parameter track_counts controls whether cumulative statistics are collected about table and index accesses.

The parameter track_functions enables tracking of usage of user-defined functions.

The parameter track_io_timing enables monitoring of block read and write times.

The parameter track_wal_io_timing enables monitoring of WAL write times.

Postgres Monitor Query Performance using Views

Database engine uses views to provide data for postgres monitor query performance. Some of the views provides provide dynamic data which shows what is going on in the system right at that point. Some of the views are collected information and gathered cumulatively.

There are many views to monitor query performance in PostgeSQL, since this is an article for introduction, i will not cover each of them in depth, but you can refer to this article for in depth documentation.

pg_stat_activity

pg_stat_activity is the main view for postgres monitor query performance. pg_stat_activity view represents the data for query performance.

Example for pg_stat_activity

In this section i want to show you an example about pg_stat_activity, i believe this will be helpful to understand postgres monitor query performance.

Screenshot above shows the result of the query select * from pg_stat_activity. As you see, output can be scrolled to the right. Hopefully, i have some ready to use query to analyze current workload and bottlenecks.

Fınding Current connections

Connection sources give us clues about the origin of the problem. You can find active connections via querying pg_stat_activity view.

Query i’ve used in this example is: SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

In a problemmatic situation, i would suggest you to group and count connections by IP address. Most of the modern application servers are configured to manage a connection pool, and limit number of connections simultaneously, if you find an exceeding number of connections in this query, that would point out there is a bug / misconfiguration about connection management.

Finding Waiting Queries

Waiting means a query is waiting for a sufficient resources to run. If you monitor waiting queries closely, you can understand, which physical resources are causing a bottleneck.

My test system do not have a waiting query, i only can share query with you. SELECT datname,usename,query FROM pg_stat_activity WHERE state=’waiting’;

You can use above query to find waiting queries, which is a great addition to monitoring query performance in postgresql. Let me give an example for this, if tables on a tablespace is listing on the waiting queries, we can understand this tablespace has an issue which should be inspected separately.

Finding Long Running Queries

“This query is running slow” this is the most common complaint of the users. In most of the case, reason is not obvious and requires time to understand.

In most of the cases, users report a slow query don’t know the source of the problem, where is the wait? What causes this result to return slow? Hopefully, we have a query for this!

select current_timestamp – query_start as runtime, datname, usename, query from pg_stat_activity where state != ‘idle’ order by 1 desc;

The first column, shows the runtime of the query, the longer it gets shows worse performance.

Long running is a relative term, as i mentioned in other articles, collect this performance metrics periodically to monitor the “trend”. Without having references, “faster-slower”, “longer-quicker” means only a little, define cronjobs or scheduled jobs to collect this information closely.

pg_stat_statements

Official documentation describes this views as “The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server.”

pg_stat_statements and pg_stat_activity are very imporant views to analyze database performance. If you need to analyze buffer, replication, WASL, SSL etc, performance you can refer to the related view about desired category.

By default pg_stat_statements is not available to use. You need to make a configuration change and create it’s extension to enable pg_stat_statements.

Enabling pg_stat_statements

It is not a long procedure but requires manual intervention to enable pg_stat_statement view. First of all edit postgresql.conf file:

shared_preload_libraries = 'pg_stat_statements'

And run the psql command.

postgres=#  create extension pg_stat_statements;
CREATE EXTENSION

And you are now ready to use pg_stat_statements view.

External Tools

Postgresql has a very detailed article about external postgres monitor query performance tools.

PostgreSQL community created may open source and commercial solutions to monitor Postgres performance.

pg_activity

pg_activity is a tool written in Python to monitor Postgresql, which has a very clean UI and easy to setup and use.

pg_view

pg_view is another tool like pg_activity to monitor PostgreSQL performance. pg_view provides easy to interpret data.

To Sum Up

Monitoring a database’s performance is a vital activity for database systems management and PostgreSQL provides many different options to monitor the infrastructure.

The most critical points about monitoring a database performance is:

• Collecting data periodically (At least once a week)
• Analyzing periodic data to see the trends
• Save the changes to a local excel file to keep notes
• Using internal and external facilities

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch