What Is in pg_gather Version 33 ?

It started as a humble personal project, few years back. The objective was to convert all my PostgreSQL notes and learning into a automatic diagnostic tool, such that even a new DBA can easily spot the problems. The idea was simple, a simple tool which don’t need any installation but do all possible analysis and pin-point the problem. The findings need to be straight to the point with actionable items, rather than providing too much info for an expert to interpret. It has grown in-front of me like like a child, because there is always something to learn from every major incidents. Now excited to announce the release of pg_gather version 33. This new version bringing a wealth of UI improvements, deeper analytical capabilities, and critical compatibility fixes. Personally I could do this because of as “unapologetically open source” philosophy of the company.

Whats New in version 33

There is around 38 commits between version 32 and 33. Some are small fixes and improvements. But some are reasonably big ones with days of effort.  If you are looking for a real quick demo of some of the features, please consider watching the following demo:

Other than Incremental improvements which happens across all parts, few areas are considered for bigger improvements for this version

1. Deeper and Smarter Wait Event Analysis

Understanding what your database sessions are doing, is crucial for performance tuning, addressing stability issues. Version 33 significantly enhances wait event reporting: It was a big effort to collect Wait events appeared in different systems over last several years. Detailed documentation is prepared for each of them. Now all that wealth of information is available on the spot.

1. Color coding of wait event category. Color coding the waits where very effective on Oracle tools. Similar approach is taken. Now each wait event is highlighted with appropriate color code
2. Wait event category summary. Now wait event categories are summaries and presented as a vertical graph. This works as a legend for the information in the database time ans well as easy way to spot the performance bottleneck.
3. Version-Aware Handling: There were changes in the names of Wait Events between major versions. So mapping to relevant documentation  is important. The tool now intelligently handles changes in wait event names between PostgreSQL major versions (like 16 and 17), ensuring accurate reporting regardless of your environment.
4. Handling wait events specific to extensions and PostgreSQL forks and categorizing them to a separate bucket.
5. Interactive Documentation: The HTML report now features an interactive popup box. By simply mousing over a wait event name, you can asynchronously load detailed documentation from the web. This puts the knowledge you need right at your fingertips.

2. Critical analysis and problem detection

1. Orphaned File Detection: A major new feature! The report now highlights potential database size mismatches in the “Findings” section. This can help you identify possible orphaned files—files on disk that aren’t properly tracked by PostgreSQL, which can waste disk space.
2. New improved pg_hba.conf Rule Evaluation: The tool now analyzes your pg_hba.conf rules. It can highlight errors in the rules and even calculate the number of IP addresses within specified network blocks, giving you a clearer picture of your access control landscape.  This analysis is now compatible with EDB Server in Oracle compatibility mode (So called “redwood”) mode.
3. New Parameter Checks: Added checks for newer PostgreSQL parameters like client_connection_check_interval and transaction_timeout.
4. Synchronous Commit Analysis: The report now studies your synchronous_commit setting in relation to synchronous_standby_names and provides guidance when adjustments might be needed.
5. Version Awareness: The “Findings” section will now display the latest available pg_gather version, even if you’re running an older one, gently reminding you when an update is available. This is to ensure that users don’t accidentally miss any critical analysis due to use of older versions.

3. Compatibility and Usability Improvements

There is always continuous effort to ensure that pg_gather is robust and easier to use across different environments. This version is also not different. The pg_gather version 33 supports all PostgreSQL versions starting from 10 for data collection.

1. PostgreSQL 18 Readiness for continuous collection mode: It is challenging to detect the PostgreSQL version reliably when the data collection is partial. This partial collection is default for a continuous collection mode to reduce the collection load and size. New version more reliably detects the PostgreSQL 18
2. Removed /dev/null Dependency: The data collection process no longer depends on /dev/null, making it more portable, especially on restricted or containerized platforms and Operating systems like MS Windows.
3. GUI Enhancements: There are many GUI improvements. The HTML report’s interface is more polished. Popup boxes have been optimized, and the database section now shows sizes and counts in a human-readable format.
4. Fix for Cross tab Report: Resolved an issue where the crosstab report would fail due to same wait event name appearing in different categories. For example, “WALWrite” can be appearing in two categories.

4. Analysis of Index effectiveness across clusters

There are few minor improvements in Analyzing and associated docs. There is a separate video demo on this

This release wouldn’t have been possible without the contributions from the community. A special thanks to @brenobaima (Breno Baima) for their work on the history schema, and to Anup for the fix on average execution time formatting.

Happy gathering the data and analyzing it