Comprehensive PostgreSQL Monitoring with a Unified Vendor Approach: Part 1

Note: This the first of two articles on PostgreSQL problem resolution. 

What Is PostgreSQL and Why Is It So Widely Used? 

PostgreSQL is an advanced open-source relational database management system (RDBMS) known for its robustness and flexibility. PostgreSQL is designed to handle various data types and custom functions, making it suitable for a wide range of scenarios—from small-scale applications like supply chain management systems to large-scale financial systems. PostgreSQL’s open-source nature makes it a cost-effective choice for organisations across various sectors. 

Who Uses PostgreSQL? 

A diverse range of professionals rely on PostgreSQL: 

• Database administrators (DBAs) appreciate its robust management features 
• Application developers use it for its rich functionality in web and mobile applications 
• DevOps professionals employ PostgreSQL to streamline operations and enhance collaboration 

At SolarWinds, we understand that most PostgreSQL database users have their own way of working. And when something goes wrong, they have their preferred tools and methods for troubleshooting. This is why we have several products designed to help you on your journey of improved performance and customer satisfaction.  

SolarWinds Database Performance Analyzer (DPA) 

DPA is a self-hosted solution designed to optimise the performance of a range of databases. DPA first added PostgreSQL support in its 2020.2 release. Since then, DPA has continued developing new features to support our customers. 

DPA now supports diverse PostgreSQL environments, from traditional setups to cloud services like Amazon RDS and Azure DB. DPA is particularly noted for its ability to dissect and tune queries, assess workload efficiencies, and even assist in database migrations. 

In a lot of database environments, certain queries perform an excessive number of logical or physical, reads but end up only retrieving a minimal amount of data. This inefficiency can lead to increased I/O, extended wait times, and heightened resource contention. 

The new PostgreSQL table tuning advisors address these challenges with a systematic approach to refining query performance. This is done by analysing query plans and identifying tables burdened by inefficient workloads. DPA offers a consolidated view of performance data and inefficient queries for each affected table. This allows database administrators to consider the potential benefits and costs of index modifications or adjustments. 

SolarWinds Hybrid Cloud Observability 

The SolarWinds Hybrid Cloud Observability (HCO) platform integrates comprehensive monitoring tools within a user-friendly interface ideally suited for managing complex IT infrastructures. HCO provides detailed insights into PostgreSQL performance metrics, such as transaction success rates and server connections, alongside advanced application and server monitoring capabilities. 

Highlights of HCO include: 

• Application and server monitoring: Monitors the health and performance of applications and servers that rely on PostgreSQL databases. 
• Change tracking: Detects and documents changes in the PostgreSQL setup, aiding in quickly identifying alterations from established baselines. 
• Synthetic transactions: Executes synthetic transactions to test and measure the responsiveness of web applications connected to PostgreSQL. 

Troubleshooting a Slow-Running Web Application 

Let’s walk through a common use case where a web app is running slow, and we need to figure out why. To resolve the issue, we’re going to use Hybrid Cloud Observability and DPA. 

When a web application is running slowly, the first step is to confirm the issue using web performance monitoring tools. Set up diagnostics to run on several pages to assess responsiveness and functionality—ensuring that user interactions, such as logging in, function correctly. If the application doesn’t get past the login screen in a reasonable time, it's clear an issue needs addressing. 

Now comes the fun part: finding the root cause of the issue. Suspecting a database issue (very often the culprit) we can break the investigation down into crucial areas: 

1. Server and database status: Verify that the server and the database are operational.

2. Recent changes: Check for any recent changes to the PostgreSQL database that might have affected performance. 

3.Query performance: Evaluate how queries perform and whether response times are higher than expected. 

If the server and application are confirmed to be online and performance counters appearing normal, but with reported changes to PostgreSQL and increased query response times, the next step is to analyse these elements in detail. 

Analysing Performance with PerfStack 

Using the PerfStack tool, plot the most critical metrics to visualise the problem areas clearly. This collaborative approach allows you to gather insights from various teams and gives you a comprehensive understanding of the issue. 

If poor query performance is identified as the bottleneck, the new tuning advisors in the Database Performance Analyzer (DPA) will give you crucial insights—by pinpointing inefficient queries and suggesting optimisations. Thereby giving you the power to improve the overall performance of the web application in question.  

In the next article we’ll look at how you can monitor the performance of your PostgreSQL databases using SolarWinds Observability. 

In the meantime, if you want to learn more about our support for PostgreSQL, check out this page: https://www.solarwinds.com/database-performance-analyzer/use-cases/postgres-performance-tuning