{"id":460650,"date":"2025-05-22T22:32:46","date_gmt":"2025-05-22T22:32:46","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=460650"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=460650","title":{"rendered":"Database performance analysis using pg_profile and pgpro_pwr<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

pgpro_pwr<\/code> 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<\/code>, developed by Andrey Zubkov \u2014 then a database administrator, now an engineer at Postgres Professional.<\/p>\n

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

How it all began<\/h2>\n

We didn’t reinvent the wheel \u2014 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.<\/p>\n

How pg_profile and pgpro_pwr work<\/h2>\n

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

A quick test: do you need this tool?<\/h2>\n

Consider using pg_profile<\/code> or pgpro_pwr<\/code> if you:<\/p>\n

    \n
  1. \n

    Want to assess the stability of a long-running system \u2014 for instance, before adding a new feature.<\/p>\n<\/li>\n

  2. \n

    Need to analyze the results of load testing.<\/p>\n<\/li>\n

  3. \n

    Aim to identify system-intensive activities, such as:<\/p>\n