pgpro_pwr is a strategic database workload monitoring tool that helps DBAs identify the most resource-intensive operations. The first version of this module, designed for PostgreSQL, was released in 2017 under the name pg_profile, developed by Andrey Zubkov — then a database administrator, now an engineer at Postgres Professional.

The key difference between pg_profile and pgpro_pwr is that the former works with open-source PostgreSQL (as of 2024, the pg_profile package has been included in PostgreSQL 17), while the latter collects advanced statistics and is integrated into Postgres Pro releases.

How it all began

We didn't reinvent the wheel — similar tools existed before. However, using them in web applications was inconvenient, as they required separate deployment from the DBMS. Database administrators, especially those familiar with Oracle AWR, longed for a tool that could work directly within Postgres. Andrey responded to this need by developing a platform-independent extension in pl/pgSQL.

How pg_profile and pgpro_pwr work

Our tools do not provide alerting capabilities. Instead, they monitor database workload metrics using counters that continuously increment from the last reset—often from the time the cluster was created. While raw counter values are not very useful, their increments over time, across different perspectives — such as clusters, databases, individual functions, or tables — offer valuable insights. The tools capture counter values at specified intervals and store the differences in their repository.

A quick test: do you need this tool?

Consider using pg_profile or pgpro_pwr if you:

Want to assess the stability of a long-running system — for instance, before adding a new feature.
Need to analyze the results of load testing.
Aim to identify system-intensive activities, such as:
- Frequently used tables requiring constant VACUUM operations.
- Queries that run too frequently or take too long to execute.
- Inefficient execution plans.
- Spoiler
  Currently, only pgpro_pwr can detect “inefficient” execution plans, but we hope to teach pg_profile this capability as well.

If you answered “yes” to any of these questions, you will definitely need pg_profile or pgpro_pwr.

Now, let’s take a closer look at what our pg_profile and pgpro_pwr tools consist of and what they can do.

Tool structure

pg_profile and pgpro_pwr include the following components:

Repository tables for storing snapshot data
Data collection functions for snapshots
Reporting functions
Service tables and functions

The tools serve two primary purposes:

Taking snapshots. These tools maintain independently of the operating system and cannot configure anything on their own, so a dedicated scheduler must be set up to perform snapshots.
Generating reports, which are HTML documents providing summarized statistics over a specific time period. The reports include a variety of metrics, some of which are exclusive to pgpro_pwr, such as database cleanup statistics, per-plan statistics, expression-level statistics, workload distribution, and invalidation statistics.

How to monitor performance

To prepare for monitoring, follow a few steps detailed in the documentation:

Install the extension (pg_profile / pgpro_pwr).
Configure roles (pg_profile / pgpro_pwr).
Set up the extension parameters (pg_profile / pgpro_pwr).

Once installed, both extensions create a single active server called local, corresponding to the current cluster. The server can be managed using functions (a complete list is available for both pg_profile and pgpro_pwr). For example, the create_server function creates a server definition:

create_server(server_name text, server_connstr text, server_enabled boolean DEFAULT TRUE, max_sample_age integer DEFAULT NULL, description text DEFAULT NULL);

Here’s an example of this function in action:

SELECT profile.create_server('omega', 'host=name_or_ip dbname=postgres port=5432');

The database load statistics are stored in snapshots, which can also be managed (pg_profile / pgpro_pwr). Collected snapshots can be exported from one instance of the extension and imported into another, allowing you to transfer accumulated server information or share it with support specialists for analysis.

Generating reports

Once the extensions, servers, and snapshots are set up, you can generate reports. Reports are divided into:

Standard reports (pg_profile / pgpro_pwr) – provide workload statistics for a specified time period.
Comparison reports (pg_profile / pgpro_pwr) – compare statistics for the same objects across two different time intervals.

Example of generating a report for the local server over a specific interval:

psql -Aqtc "SELECT profile.get_report(480,482)" -o report_480_482.html

Generating a report for another server:

psql -Aqtc "SELECT profile.get_report('omega',12,14)" -o report_omega_12_14.html

Generating a report for a time range:

psql -Aqtc "SELECT profile.get_report(tstzrange('2020-05-13 11:51:35+03','2020-05-13 11:52:18+03'))" -o report_range.html

Reports can be opened in any web browser. Detailed descriptions of the types of reports and statistics included in each are available in the documentation (pg_profile / pgpro_pwr).

Some examples are:

What’s next

We are currently working on submitting a patch with vacuum statistics to vanilla PostgreSQL. We strive to upstream as much as possible, as resolving future merge conflicts can be quite challenging.

If you have any questions or difficulties while using the monitoring tools, we are happy to help. Share your thoughts and ideas in the comments!

Database performance analysis using pg_profile and pgpro_pwr