PostgreSQL database is a very popular free and open-source relational database management system. It is said to be as good as most commercial database products. In fact, some even affirm that it is better in several ways. But no matter what, a database’s primary purpose is holding data. And just like data is often an organization’s most important asset, monitoring databases is often an administrator’s most important task.
The main point of monitoring PostgreSQL databases is to ensure that the data they hold is available whenever it is needed and that their performance—i.e. how fast they respond to queries—remains within acceptable parameters. Today, we’re having a look at a few of the best PostgreSQL monitoring tools.
We’ll start off by briefly explaining what PostgreSQL is, where it is coming from and how it came to be. After all, it can only help to know a bit more about what we’re trying to monitor. Then, we’ll specifically discuss the monitoring of PostgreSQL databases. We’ll learn how database servers should be considered in their entirety and that the best monitoring will not only include the actual database software but also the underlying operating system and hardware. We’ll then get to the core of this post as we introduce the best PostgreSQL monitoring tools we could find and give you a brief review of each one.
PostgreSQL In a Nutshell
PostgreSQL claims to be the most advanced open-source database. It’s probably more than just a marketing thing, though. It is truly at par with the most powerful commercial databases available. Also known as Postgres, the free and open-source relational database management system (RDBMS) emphasizes extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. Furthermore, it is the default database for macOS Server and is also available for Linux, FreeBSD, OpenBSD, and Windows.
PostgreSQL evolved from the Ingres project at the University of California, Berkeley. As a matter of fact, the PostgreSQL name was originally just Postgres and it stood for post-Ingres. It was later changed to PostgreSQL to better reflect its SQL standard compliance. So, back in 1982, Michael Stonebraker, the leader of the Ingres team left Berkeley to make a proprietary version of Ingres only to return a few years later in 1985 and began a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. Recognizing his accomplishments, Stonebraker was granted the Turing Award in 2014 for these and other projects and the techniques they pioneered.
INTERESTING READ: 7 Best Database Performance Analysis Tools
About Monitoring PostgreSQL Databases
Since PostgreSQL is, before all, a standard SQL database, monitoring it is no different from monitoring any other SQL database. And the primary goal of PostgreSQL database monitoring is, more than anything, ensuring that data is available when it is needed. A database and the data it contains can be somewhat of an abstract concept. It is, however, made of concrete elements. PostgreSQL monitoring tools, therefore, must always ensure that all those elements—including both hardware and software components—are working and also operating within their normal range.
The most basic element of a PostgreSQL server—or any database server, for that matter—is the actual server it runs on, hardware and operating system included. Like any server, there are many operational parameters worth monitoring on a PostgreSQL server. For example, CPU load, available memory or processor core temperature are all interesting parameters. Monitoring a server running a PostgreSQL database instance is, from a hardware perspective, exactly the same as monitoring any other server.
The underlying operating systems is another element that is worth monitoring. Regardless of what operating system your PostgreSQL server is running on, there are several important parameters to be monitored such as disk space or disk usage, for example. And just like hardware monitoring, the OS-level monitoring of a PostgreSQL server is not different from that of any other server.
The last thing that needs monitoring is the actual PostgreSQL server instance. By that, I mean the Relational Database Management System software. In its simplest expression, a PostgreSQL server is just another program running on a server. The most basic monitoring, then, is to ensure that this specific program and/or its associated service(s) are running at all times. However, many monitoring and analysis tools go further than that. Some, for instance, will check the structure of databases while some will measure its response time. A PostgreSQL server has several handfuls of parameters that can be monitored to your benefit.
The Best Tools For Monitoring PostgreSQL Databases
We’ve probed the market for the very best PostgreSQL database monitoring tools and found a wide variety of products. Thanks to PostgreSQL standards adherence, any SQL-compliant monitoring tool should have no problem monitoring a PostgreSQL database. We’ve done our best, as we often do, to include different types of tools on our list. While some are simpler SQL monitoring tools, others are full-featured tools that will monitor every possible parameter and even analyze the structure of your databases, potentially identifying trouble spots.
SolarWinds is one of the best-known makers of network and system administration tools. With a broad array of tools covering almost every topic, many administrators have at least heard of the company. Its flagship product, the Network Performance Monitor, consistently scores among the best network bandwidth monitoring tool. The company is also famous for its free tools, each addressing a specific need of network administrators.
The SolarWinds Database Performance Analyzer (DPA) monitors and analyzes your PostgreSQL database instances or any SQL-compliant database. The system uses a Response Time Analysis method which puts the primary focus on the time between a query requests and the corresponding response from the database and analyzes wait types and events to pinpoint the bottlenecks of databases.
- FREE TRIAL: SolarWinds Database Performance Analyzer
- Official Download Link: https://www.solarwinds.com/database-performance-analyzer/registration
The tool’s easy to use interface will assist you in finding issues quickly and easily. But as good as it is a troubleshooting tool (and it is great) it’s also an excellent monitoring tool. Its main screen lets you visualize database instances, wait times, query advice, CPU (with warning and critical alerts), memory, disk and sessions. The trend dashboard of any specific database instance shows you in a graphical format the total wait times (or your choice of average or typical day wait times) for users over the course of a month, with each colour representing an individual SQL statement. This gives you a visually appealing representation of which statement takes the longest to run.
There so many features in the SolarWinds Database Performance Analyzer that we could write a whole post about it. For now, let’s have a look at some of the most significant.
Single Point Of Monitoring For All Your Databases
The SolarWinds Database Performance Analyzer works with PostgreSQL databases (of course) as well as most major databases including Microsoft SQL Server, Oracle, MySQL, DB2, and ASE. It also supports physical, on-premises servers, Virtual Machines (running under Vmware or Hyper-V) and cloud-based installations.
The tool lets you monitor as many database instances as you need. The software easily scales from one to a thousand database instances. You just need to upgrade the license.
Monitoring Of All Active Sessions
The tool’s native polling engine records everything that’s happening within your database instances with no additional load to the database themselves and without requiring a local monitoring agent. This dramatically reduces—if not eliminates—the risk that the tool could impact the databases’ performance.
Tracking, Monitoring, And Analysis Of Database Components
This tool automatically correlates queries, users, files, plans, objects, wait times, storage and date/time/hour/minute to accurately discover and diagnose problems on the fly.
Database Tuning Advice
The SolarWinds Database Performance Analyzer can give you sound advice for tuning individual database instances to make sure they run at their peak performance. The tool will analyze all parameters of an instance and recommend a course of action to optimize certain queries or even whole database instances.
Prices for the SolarWinds Database Performance Analyzer start at $1 995 and vary according to the number and type of database instances to monitor. If you want to try the product before purchasing it, a fully functional 14-day trial version is available.
2. PRTG Network Monitor
The PRTG Network Monitor from Paessler A.G. is a well-known all-in-one monitoring platform that you can use to monitor systems, devices, traffic, databases, and applications. It is built on a clever sensor-based architecture where various sensors allow for different types of monitoring. This tool can monitor anything there’s a sensor for. You can think of sensors as add-ons although, in the case of this tool, they’re already built-in. Pre-configured database sensors are available out-of-the-box. Most database engines are supported with specific sensors including PostgreSQL, Microsoft SQL Server, MySQL, and Oracle. The tool will let you monitor the execution time of a SQL query by measuring the time taken from the moment the connection is established to the end of the connection. It will let you clearly see how your databases perform under pressure.
The PRTG Network Monitor lets you pick from several client user interfaces. There’s a Windows enterprise console, an Ajax-based web interface, and mobile apps for Android, iOS, and Windows Phone. The tool is known to be one of the quickest and easiest to install with Paessler claiming you could start monitoring within 2 minutes. While our experience shows that it could take longer than that, it is still a very quick and easy installation process.
One of PRTG’s best features—and one of the reasons why it is so quick and easy to install—is its auto-discovery engine which will scan your network and automatically recognize a wide range of devices and systems, creating appropriate sensors from predefined device templates. The tool also has a map feature that will let you create custom maps with your monitoring data superimposed, offering a unique way of displaying it. When it comes to alerts, PRTG’s are highly flexible and customizable. The mobile client apps allow you to get push notifications directly to your device. You can also set up notifications via email or SMS and there’s a powerful API that lets you write your own notification scripts.
The PRTG Network Monitor is available in a full-featured free version which limits your monitoring ability to 100 sensors. Monitoring a typical PostgreSQL server requires one sensor per database instance plus sensors for any other server and OS parameter you may want to monitor. For more than 100 sensors, a license is necessary. Their prices vary according to the number of sensors and start at $1 600 for 500 sensors. A free, fully-featured 30-day trial version that allows for unlimited sensors is available but it will revert to the 100 sensors free version once the trial period is over.
Read the full review for more info.
3. SQL Sentry
Next on our list is SQL Sentry from SentryOne. This is a rather popular solution for database performance monitoring. The tool collects and presents actionable performance metrics and alerts and displays essential events on a calendar-style display which allows for easy cross-referencing In addition, the tool can be used to quickly resolve issues by running detailed analysis right from its dashboard. The tool’s performance dashboard—which features an exclusive, patented view of storage performance—can display the results of the detailed analysis of high-impact queries.
SQL Sentry is certainly not the most modern-looking tools and its dashboard has been criticized for its antiquated look. However, it will show you all the critical SQL components and services in a neat layout and it will help you manage your SQL database instances and ensure their availability. You also have the option of viewing the server’s historical data, comparing it to current real-time data. You could use this feature for generating a baseline and then measuring the results of your database optimization efforts.
The base price for SQL Sentry is $2 495 per database instance plus an annual maintenance fee of $499. The product is also available as a subscription-based service at a cost of $125/month per database instance. You can also take the product for a test run, thanks to an available free 15-day trial.
4. Idera Diagnostic Manager For SQL Server
Don’t let the Idera Diagnostic Manager for SQL Server’s name fool you. This tool will actually let you monitor the performance of any SQL database, including PostgreSQL. It offers performance statistics, metrics and alerts from physical servers as well as virtual machines and their underlying host hardware. The tool can monitor numerous parameters such as CPU, memory, hard disk usage and space, and network usage. Proactive monitoring of SQL queries is also included along with transactional SQL monitoring. Furthermore, the tool can provide recommendations for the health of SQL databases.
For user interaction, there’s a choice of a web-based interface and mobile apps for Android, iOS, and Blackberry phones and tablets. The tool provides both real-time and historical data about SQL server instances, letting administrators make changes on the fly within the mobile app. Other tasks that can be performed from the feature-reduced mobile apps include viewing and killing processes, starting and stopping jobs as needed and running queries. Other features of Idera Diagnostic Manager for SQL Server include easy integration with SCOM using the Management Pack add-on, a predictive alerting system built to avoid any false alerts and an excellent query performance monitoring function that can pinpoint queries that are not efficient and performing poorly.
Prices for the Idera Diagnostic Manager for SQL Server start at $1 996 per database instance and a fully functional 14-day trial is available.
5. SQL Monitor (Red Gate)
SQL Monitor from Red Gate Software can be used to monitor, manage, change, manipulate and optimize SQL databases. It will help find issues before they become problems. Feature-wise, a lot can be said about the product. First, it provides administrators with an overview of their SQL server farm. The tool also features powerful alerting capabilities, letting you know about issues before they have a chance to have any noticeable impact. Reporting is another place where this product shines. It will generate tailored reports about your servers’ health. Furthermore, the tool’s performance analysis capabilities allow you to quickly see what has the biggest impact on your system. As a diagnostic tool, this feature can help you uncover obstacles and find root causes.
SQL Monitor features an easy to use dashboard that lets you view all your database instances in one convenient place. The tool also lets you directly access them within seconds without having to remotely connect to them using a VPN or another method. There’s a dashboard with drill-down functionality which allows you to display detailed statistics about your databases such as wait times, CPU usage and disk I/O in just a couple of clicks.
The base price of SQL Monitor is $1495 per database instance. You are eligible for a 15% discount when you purchase between five and nine licenses simultaneously and a 20% discount when you purchase ten or more. All prices include the first year of support from Red Gate. A 14-day trial is available if you want to give the tool a test run and so is an online demo.
6. SQL Power Tools
SQL Power Tools from SQL Power is an agentless database performance analysis solution whose publisher claims has the lowest impact on your servers. It is said that monitoring with SQL Power Tools won’t load your databases by more than 1%. Also, the product inserts no table and no stored procedure in any of your databases. All analysis is done completely remotely. It especially won’t load your servers with millions of queries which could seriously affect their performance. And by virtue of being agentless, you don’t need to install any monitoring component on your servers.
This tool is packed with features. It has both dashboard and heat map views of your server farm performance which can be viewed from a browser or a mobile device. The platform computes a daily baseline analysis of SQL wait times, performance counters and job run times. It can also perform an instant comparison of SQL and I/O wait times, performance counters and job run times between servers and time periods. The tool allows you to drill down into the SQL wait times of poorly performing queries, helping you pinpoint resource contention which can be causing them not to complete on a timely basis.
SQL Power Tools’ price varies between $2 000 and $750 per database instance depending on how many you purchase simultaneously. If you prefer to try the product, a 3-week evaluation version can be obtained by contacting SQL Power.