Hello! Monitoring running queries is important for monitoring performance issues and bottlenecks in the database. In this article we will talk about how to check running queries in Postgres and help you to understand pg_stat_activity catalog view.

Keeping PostgreSQL database performance over a line is a database administrators primary job. Finding and understanding low performing queries is the first step of improving performance of queries and database.

How to check running queries in Postgres? Actually, PostgreSQL provides very good built in facilities to check running queries.

Using pg_stat_activity to Check Running Queries

pg_stat_activity is an invaluable resource for monitoring and managing running queries and other activities within the database. This view serves as an essential entry point for gaining insights into the ongoing operations within the database system. By leveraging the data provided by pg_stat_activity, database administrators and developers can effectively analyze and comprehend the real-time state of the database, enabling them to make informed decisions and optimizations. From identifying long-running queries to understanding the overall activity within the database, pg_stat_activity plays a crucial role in ensuring the optimal performance and stability of the database environment.

If you are facing with high CPU utilization in PostgreSQL you may want to read my article about How to find high CPU utilization query in PostgreSQL?

Querying pg_stat_activity

Simply you can run the statement below to query ongoing activity.

Output of this command shows ongoing activity on the database. Each row shows a database process.

pid column shows same pid with the process on the operating system. So, you can use ps -ef | grep pid command to retrieve process. Also, top command can be used to monitor CPU usage of the process.

current_timestamp – query_start is an important metric, that shows how long query is going on. Longer execution times show worse performance.

Monitoring Running Queries in Postgres

If you are managing or if you will manage an environment for a long time, from the first day, start saving queries taking longer than 1 seconds everyday. In the beginning you might not know if there is a performance bottleneck, but in time you can use this historical data to analyze.

You can refer to Postgres pg_stat_activity documentation.

Create a table to like below to save historical data:

CREATE TABLE query_history (id int generated always as identity, query_string text, query_time interval, query_date date not null default CURRENT_DATE, PRIMARY KEY(id));

You can use following query to insert long queries to the historic data table.

INSERT INTO query_history (query_string, query_time) SELECT query, current_timestamp - query_start from pg_catalog.pg_stat_activity where (current_timestamp - query_start) > '1 second';

query_history table can be queried for each query and date, now you have a historic data about each query.

Summary of Running Queries

Today we talked about checking running queries in postgresql. Also i highlighted importance of saving history performance data for queries.

Have Questions? Ask Away!

I hope this article has been informative and helpful for you. If you have any questions or need further clarification on any of the topics discussed, please don’t hesitate to leave a comment below. Your insights and queries are valuable, and I encourage you to share your thoughts with the community. Let’s keep the conversation going!

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