<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

How dbWatch saves the day when you need to solve PostgreSQL performance problems

Posted by Chad Pabalan on Jan 11, 2023 8:30:00 AM

A day in the life of the accidental DBA!

You have been working primarily on SQLServer databases for the past few years. One day the database admin in charge of the PostgreSQL databases resigned, and the IT manager re-assigned the task of maintaining the PostgreSQL databases to you. Now it is up to you to monitor and manage those databases.

Familiar situation? This could happen to any of us – suddenly being tasked with managing a new platform you are unfamiliar with.

Working as a DBA for a company that supports an e-commerce site, one of your top priorities should be to keep track of your database performance. If it is performing well, it is also crucial that it would be able to cater to all the traffic and user transactions it will continuously process.

You need a database monitoring system that can help you keep track of all your database servers and all the platforms you have to manage in critical aspects such as performance, resource usage, and reporting.

The DB team is using dbWatch Control Center, and you were wondering how dbWatch Control Center can monitor PostgreSQL databases performance as you've never worked on the platform before. You need to know the key performance indicators of a healthy PostgreSQL database.

As it turns out, with dbWatch Control Center, database admins can quickly analyze the different factors that cause performance impact in their PostgreSQL database environment, using the available performance jobs to keep track of your database server's health.

We are starting with the management module of dbWatch Control Center. In this view, if you expand the Test Group, you can see that we have - a T001-Postgresql13 instance that we monitor.

This view shows different information about the PostgreSQL database instance. Information such as the database management system name/platform, database management system version, server, and IP/Port.

It also contains information on the database sizes and graphs for logical reads per/sec, total sessions, and memory pool configuration.


When you click on sessions, you will see the active sessions in the PostgreSQL instance. The view displays the active processes showing PID, username, database, wait event type, wait event, query start, current query, and other information.

You can see in this view that there is an ongoing blocking scenario; existing queries are blocking one another. If you right-click on a session, you can check the explain query to know more about this session or query that causes the blocking, or maybe you can kill the process if you are confident that it isn't a critical query process.


If you expand on database, then choose Schemas. A new view will appear, which gives you information on which schema consumes the most disk space and how many tables are in a specific schema.


If you go to the Monitoring module of dbWatch Control Center, click on the instance, which is T001-Postgresql13. Expand the performance folder; you will find jobs that track your database performance.


Let's look at the job details – Locks held and statistics. Right-click on it and choose Details.

The report shows the objects most locked within the last 24 hours. The query blocked earlier was a select query that selects data from the "items” table. Which is shown in this report that it has a total of 5 lock counts during the last 24 hours.


This information helps you identify whether you need to do a table partition, create proper indexes, or archive some of your old data to lessen rows of data in a table to avoid long-running queries or continuous blocking scenarios such as this.

Long-running queries could lead to deadlock processes – which affect transaction processing inside your database and affect your database performance.

With dbWatch Control Center, database admins can now monitor their PostgreSQL database performance and monitor other important information in their database. As a DBA, you can now analyze sessions and blocking occurrences in your database and identify which objects are usually affected by these scenarios.  

dbWatch Control Center assists database admins in efficiently running their databases, from proactive database performance monitoring to managing tasks.  

You can try dbWatch Control Center today. Use this link to download a version of dbWatch Control Center that can monitor up to 5 instances for 30 days for free:  dbWatch Control Center free license.   

If you have any questions or would like to know more about how dbWatch can assist you in your current enterprise database monitoring and management situation, feel free to contact presales@dbWatch.com

For more information, visit www.dbWatch.com or the dbWatch wiki pages.  


#DatabaseMonitoring #PostgreSQL #PostgreSQLMonitoring #DatabaseReporting #DBA #DatabaseManagement #DatabasePerformance #DatabaseAdmin

Topics: sql monitoring tools, sqlperformance, databaseoperations, postgreSQL