The pgpro_pwr extension depends on PL/pgSQL and the dblink extension.

The target server must allow connections to all databases from the server where pgpro_pwr is running. To connect to the target server, provide a connection string where a particular database on this server is specified. This database is of high importance for pgpro_pwr since the functionality of the pgpro_stats or pg_stat_statements extensions will be provided through this database. Note, however, that pgpro_pwr will also connect to all the other databases on this server.

Optionally, for completeness of gathered statistics:

Use this setup procedure when pgpro_pwr is to be installed on the target cluster to only track its workload as superuser.

Create a schema for the pgpro_pwr installation and create the extension:

CREATE SCHEMA profile;
CREATE EXTENSION pgpro_pwr SCHEMA profile;

Use this setup procedure when you intend to use pgpro_pwr for tracking workload on one or more servers and need to follow the principle of least privilege.

CREATE USER pwr_collector PASSWORD 'collector_pwd';

GRANT pg_read_all_stats TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgpro_stats_statements_reset TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgpro_stats_totals_reset(text,bigint) TO pwr_collector;

GRANT SELECT ON pgpro_stats_archiver TO pwr_collector;

CREATE USER pwr_user;

CREATE SCHEMA profile AUTHORIZATION pwr_user;

GRANT USAGE ON SCHEMA public TO pwr_user;

\c - pwr_user
CREATE EXTENSION pgpro_pwr SCHEMA profile;

SELECT profile.create_server('target_server_name','host=192.168.1.100 dbname=postgres port=5432');

Up to three roles can be distinguished when pgpro_pwr is in operation:

If all the actions with pgpro_pwr are performed by the superuser role postgres, you can skip most of the setup explained below.

\c postgres postgres
CREATE SCHEMA dblink;
CREATE EXTENSION dblink SCHEMA dblink;
CREATE USER pwr_usr with password 'pwr_pwd';
GRANT USAGE ON SCHEMA dblink TO pwr_usr;
CREATE SCHEMA profile AUTHORIZATION pwr_usr;
\c postgres pwr_usr
CREATE EXTENSION pgpro_pwr SCHEMA profile;

\c postgres postgres
CREATE SCHEMA pgps;
CREATE EXTENSION pgpro_stats SCHEMA pgps;
CREATE USER pwr_collector with password 'collector_pwd';
GRANT pg_read_all_stats TO pwr_collector;
GRANT USAGE ON SCHEMA pgps TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgps.pgpro_stats_statements_reset TO pwr_collector;

\c postgres postgres
CREATE SCHEMA pgss;
CREATE SCHEMA pgsk;
CREATE SCHEMA pgws;
CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;
CREATE USER pwr_collector with password 'collector_pwd';
GRANT pg_read_all_stats TO pwr_collector;
GRANT USAGE ON SCHEMA pgss TO pwr_collector;
GRANT USAGE ON SCHEMA pgsk TO pwr_collector;
GRANT USAGE ON SCHEMA pgws TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgss.pg_stat_statements_reset TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgsk.pg_stat_kcache_reset TO pwr_collector;
GRANT EXECUTE ON FUNCTION pgws.pg_wait_sampling_reset_profile TO pwr_collector;

 \c postgres pwr_usr
 SELECT profile.set_server_connstr('local','dbname=postgres port=5432 host=localhost user=pwr_collector password=collector_pwd');

\c postgres pwr_usr
SELECT * FROM take_sample();

*/30 * * * *   psql -U pwr_usr -d postgres -c 'SELECT pgpro_pwr.take_sample()' > /dev/null 2>&1

In postgresql.conf, you can define the following pgpro_pwr parameters:

Use the following pgpro_pwr functions for server management:

The following pgpro_pwr functions relate to sampling:

If a reset of statistics since the previous sample was detected, pgpro_pwr treats corresponding absolute values as differentials; however, the accuracy will be affected anyway.

Sampling functions also maintain the server repository by deleting obsolete samples and baselines according to the retention policy.

To take samples for all enabled servers, call the take_sample() function. Usually, one or two samples per hour is sufficient. You can use a cron-like tool to schedule sampling. Here is an example for a 30-minute sampling period:

*/30 * * * *   psql -c 'SELECT profile.take_sample()' &> /dev/null

However, the results of such a call are not checked for errors. In a production environment, function results can be used for monitoring. This function returns OK for all servers with successfully taken samples and shows error text for failed servers:

SELECT * FROM take_sample();
  server   |                                   result                                    |   elapsed
-----------+-----------------------------------------------------------------------------+-------------
 ok_node   | OK                                                                          | 00:00:00.48
 fail_node | could not establish connection                                             +| 00:00:00
           | SQL statement "SELECT dblink_connect('server_connection',server_connstr)"  +|
           | PL/pgSQL function take_sample(integer) line 69 at PERFORM                  +|
           | PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment+|
           | SQL function "take_sample" statement 1                                     +|
           | FATAL:  database "postgresno" does not exist                                |
(2 rows)

You can define sample retention at the following levels:

The following pgpro_pwr functions relate to subsampling:

The following settings affect the subsample behaviour:

The subsample behavior can be defined at the server level using the set_server_subsampling function.

The last observed session state is saved in the repository when either of the following threshold-related events happens:

See Chapter 26 for details of the mentioned fields. Every subsample can hold at most pg_profile.topn entries for every threshold type.

Subsamples are fast enough to take them quite often. However usually you do not need more than 2-4 subsamples per minute. Obviously the subsample frequency depends on the shortest used duration of a threshold setting.

Cron only allows one call per minute, so some effort is needed to schedule more frequent subsamples. For example, the \watch psql command can be used:

echo "select take_subsample(); \watch 15" | psql &> /dev/null

The psql call can be wrapped in the systemd service like this:

Description=pg_profile subsampling unit
[Unit]

[Service]
Type=simple
ExecStart=/bin/sh -c 'echo "select take_subsample(); \\watch 15" | /path/to/psql -qo /dev/null'
User=postgres
Group=postgres

[Install]
WantedBy=multi-user.target

Use the following pgpro_pwr functions for baseline management:

The export_data function exports data to a regular table. You can use any method available to export this table from your database. For example, you can use the \copy meta-command of psql to obtain a single csv file:

postgres=# \copy (select * from export_data()) to 'export.csv'

Since data can only be imported from a local table, first, load the data you exported. Using the \copy meta-command again:

postgres=# CREATE TABLE import (section_id bigint, row_data json);
CREATE TABLE
postgres=# \copy import from 'export.csv'
COPY 6437

Now you can import the data by providing the import table to the import_data function:

postgres=# SELECT * FROM import_data('import');

After successful import, you can drop the import table.

Use these functions to export or import data:

Use this function to generate regular reports:

Use this function to generate differential reports:

Generate a report for the local server and interval defined by samples:

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

For any other server, provide its name:

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

Generate a report using time ranges:

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

Generate a relative time-range report:

psql -Aqtc "SELECT profile.get_report(tstzrange(now() - interval '1 day',now()))" -o report_last_day.html

Tables in this section of a pgpro_pwr report are described below.

The report table “Database statistics” provides per-database statistics for the report interval. The statistics are based on the pg_stat_database view. Table G.1 lists columns of this report table.

The report table “Cluster I/O statistics” provides I/O statistics by object types, backend types and contexts. This table is based on the pg_stat_io view of the Cumulative Statistics System, available since Postgres Pro 16. Table G.2 lists columns of this report table. Times are provided in seconds.

The report table “Cluster SLRU statistics” provides access statistics on SLRU (simple least-recently-used) caches. This table is based on the pg_stat_slru view of the Cumulative Statistics System. Table G.3 lists columns of this report table. Times are provided in seconds.

Table “Session statistics by database” is available in the report for Postgres Pro databases starting with version 14. This table is based on the pg_stat_database view of the Statistics Collector. Table G.4 lists columns of this report table. Times are provided in seconds.

In Postgres Pro Standard databases of versions that include pgpro_stats version starting with 1.4, workload statistics of vacuum processes are available. The “Database vacuum statistics” report table provides per-database aggregated total vacuum statistics based on the pgpro_stats_vacuum_tables view. Table G.5 lists columns of this report table. Times are provided in seconds.

If the pgpro_stats extension supporting invalidation statistics was available during the report interval, the "Invalidation messages by database" report table provides per-database aggregated total invalidation message statistics. Table G.6 lists columns of this report table.

If the pgpro_stats extension was available during the report interval, the “Statement statistics by database” report table provides per-database aggregated total statistics for the pgpro_stats_statements view data. Table G.7 lists columns of this report table. Times are provided in seconds.

The “Statement average min/max timings” report table contains per-database aggregated min/max timing statistics from the one of pgpro_stats or pg_stat_statements extensions that was available during the report interval, with the precedence of pgpro_stats. This report is sensitive to the fastest and slowest planning and execution of every statement in a cluster. That is, you can see execution and planning stability in your database. Table G.8 lists columns of this report table.

If the JIT-related statistics was avaliable in the statement statistics extension during the report interval, the “JIT statistics by database” report table provides per-database aggregated total statistics of JIT executions. Table G.9 lists columns of this report table. Times are provided in seconds.

The report table “Cluster statistics” provides data from the pg_stat_bgwriter view. Table G.10 lists rows of this report table. Times are provided in seconds.

Table “WAL statistics” is available in the report for Postgres Pro databases starting with version 14. This table is based on the pg_stat_wal view of the Statistics Collector. Table G.11 lists columns of this report table. Times are provided in seconds.

The report table “Tablespace statistics” provides information on the sizes and growth of tablespaces. Table G.12 lists columns of this report table.

If the pgpro_stats extension was available during the report interval, the report table “Wait statistics by database” shows the total wait time by wait event type and database. Table G.13 lists columns of this report table.

If the pgpro_stats extension was available during the report interval, the report table “Top wait events” shows top wait events in the cluster by wait time. Table G.14 lists columns of this report table.

This section of a pgpro_pwr report is based on the pgpro_stats_totals view of the pgpro_stats extension if it was available during the report interval. Each table in this section provides data for the report interval on load distribution for a certain kind of objects for which aggregated statistics are collected, such as databases, applications, hosts, or users. Each table contains one row for each resource (for example, total time or shared blocks written), where load distribution is shown in graphics, as a stacked bar chart for top objects by load of this resource. If the bar chart area that corresponds to an object is too narrow to include captions, point that area to get a hint with the caption, value and percentage. The report tables “Load distribution among heavily loaded databases”, “Load distribution among heavily loaded applications”, “Load distribution among heavily loaded hosts” and “Load distribution among heavily loaded users” show load distribution for respective objects. Table G.15 lists rows of these report tables.

This section of a pgpro_pwr report provides information about session states captured by subsamples during the report interval.

Tables of this report section are described below.

The report subsection “Chart with session state” visualizes session states captured by subsamples. It is the timeline chart showing captured session states in backends and transactions. Every state contains a popup with session state attributes. Click on a state to see this state in the table of session states.

The report table “Session state statistics by database” shows the aggregated data on session states. Only session states captured in subsamples are counted. Table G.16 lists columns of this report table.

The report table “Top 'idle in transaction' session states by duration” shows top pgpro_pwr.topn longest idle in transaction states that were last observed in the pg_stat_activity view for each session. Table G.17 lists columns of this report table.

The report table “Top 'active' session states by duration” shows top pgpro_pwr.topn longest active states that were last observed in the pg_stat_activity view for each session. Table G.18 lists columns of this report table.

The report table “Top states by transaction age” shows top session states by transaction age that were last observed in the pg_stat_activity view for each session. Table G.19 lists columns of this report table.

The report table “Top states by transaction duration” shows top longest session states that were last observed in the pg_stat_activity view for each session. Table G.20 lists columns of this report table.

This section of a pgpro_pwr report provides data for the report interval on top statements by several important statistics. The data is mainly captured from views of the one of pgpro_stats and pg_stat_statements extensions that was available during the report interval, with the precedence of pgpro_stats. Each statement can be highlighted in all SQL-related sections with a single mouse click on it. This click will also show a query text preview just under the query statistics row. The query text preview can be hidden with a second click on a query.

Tables of this report section are described below.

The report table “Top SQL by elapsed time” shows top statements by the sum of total_plan_time and total_exec_time fields of the pgpro_stats_statements or pg_stat_statements view. Table G.21 lists columns of this report table. Times are provided in seconds.

The report table “Top SQL by planning time” shows top statements by the value of the total_plan_time field of the pgpro_stats_statements or pg_stat_statements view. Table G.22 lists columns of this report table.

The report table “Top SQL by execution time” shows top statements by the value of the total_time field of the pgpro_stats_statements or pg_stat_statements view. Table G.23 lists columns of this report table.

The report table “Top SQL by mean execution time” shows top pgpro_pwr.topn statements by the value of the mean_time or mean_exec_time field of the pgpro_stats_statements or pg_stat_statements view. Table G.24 lists columns of this report table.

The report table “Top SQL by executions” shows top statements by the value of the calls field of the pgpro_stats_statements or pg_stat_statements view. Table G.25 lists columns of this report table.

The report table “Top SQL by I/O wait time” shows top statements by read and write time, i.e., sum of values of blk_read_time and blk_write_time fields of the pgpro_stats_statements or pg_stat_statements view. Table G.26 lists columns of this report table. Times are provided in seconds.

The report table “Top SQL by shared blocks fetched” shows top statements by the number of read and hit blocks, which helps to detect the most data-intensive statements. Table G.27 lists columns of this report table.

The report table “Top SQL by shared blocks read” shows top statements by the number of shared reads, which helps to detect the most read-intensive statements. Table G.28 lists columns of this report table.

The report table “Top SQL by shared blocks dirtied” shows top statements by the number of shared dirtied buffers, which helps to detect statements that do most data changes in the cluster. Table G.29 lists columns of this report table.

The report table “Top SQL by shared blocks written” shows top statements by the number of blocks written. Table G.30 lists columns of this report table.

The report table “Top SQL by WAL size” shows top statements by the amount of WAL generated. Table G.31 lists columns of this report table.

The report table “Top SQL by temp usage” shows top statements by temporary I/O, which is calculated as the sum of temp_blks_read, temp_blks_written, local_blks_read and local_blks_written fields. Table G.32 lists columns of this report table.

The report table “Top SQL by invalidation messages sent” shows top statements by the number of invalidation messages sent. Table G.33 lists columns of this report table.

If the pgpro_stats extension was available during the report interval, this section of the report will contain a table that is split into sections, each showing top statements by overall wait time or by wait time for a certain wait event type. Table sections related to specific wait events follow in the descending order of the total wait time in wait events of this type. Table G.36 lists columns of this report table. Times are provided in seconds.

If the JIT-related statistics was avaliable in the statement statistics extension during the report interval, the report table “Top SQL by JIT elapsed time” shows top statements by the sum of jit_*_time fields of the pgpro_stats_statements or pg_stat_statements view. Table G.37 lists columns of this report table. Times are provided in seconds.

The “Complete list of SQL texts” section of the report contains a table that provides query and plan texts for all statements mentioned in the report. Use an appropriate Query ID/Plan ID link in any statistics table to see the corresponding query/plan text. Table G.38 lists columns of this report table.

Tables in this section of the report show top database objects by statistics from the Postgres Pro's Statistics Collector views.

The report table “Top tables by estimated sequentially scanned volume” shows top tables by estimated volume read by sequential scans. This can help you find database tables that possibly lack some index. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. The data is based on the pg_stat_all_tables view. Table G.39 lists columns of this report table.

In the report table “Top tables by blocks fetched”, blocks fetched include blocks being processed from disk (read) and from shared buffers (hit). This report table shows top database tables by the sum of blocks fetched for the table's heap, indexes, TOAST table (if any) and TOAST table index (if any). This can help you focus on tables with excessive processing of blocks. The data is based on the pg_statio_all_tables view. Table G.40 lists columns of this report table.

The report table “Top tables by blocks read” shows top database tables by the number of blocks read for the table's heap, indexes, TOAST table (if any) and TOAST table index (if any). This can help you focus on tables with excessive block readings. The data is based on the pg_statio_all_tables view. Table G.41 lists columns of this report table.

The report table “Top DML tables” shows top tables by the number of DML-affected rows, i.e., by the sum of n_tup_ins, n_tup_upd and n_tup_del (including TOAST tables). The data is based on the pg_stat_all_tables view. Table G.42 lists columns of this report table.

The report table “Top tables by updated/deleted tuples” shows top tables by tuples modified by UPDATE/DELETE operations, i.e., by the sum of n_tup_upd and n_tup_del (including TOAST tables). The data is based on the pg_stat_all_tables view. Table G.43 lists columns of this report table.

The report table “Top tables by removed all-visible marks” shows top tables by the number of times that the all-visible mark was removed from the visibility map by any backend. This report section is only shown when corresponding statistics are available. Table G.44 lists columns of this report table.

The report table “Top tables by removed all-frozen marks” shows top tables by the number of times that the all-frozen mark was removed from the visibility map by any backend. This report section is only shown when corresponding statistics are available. Table G.45 lists columns of this report table.

The report table “Top tables by new-page updated tuples” shows top tables by the number of rows updated where the successor version goes onto a new heap page, leaving behind an original version with a t_ctid field that points to a different heap page. These are always non-HOT updates. Table G.46 lists columns of this report table.

The report table “Top growing tables” shows top tables by growth. The data is based on the pg_stat_all_tables view. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. Table G.47 lists columns of this report table.

In the report table “Top indexes by blocks fetched”, blocks fetched include index blocks processed from disk (read) and from shared buffers (hit). The data is based on the pg_statio_all_indexes view. Table G.48 lists columns of this report table.

The report table “Top indexes by blocks read” is also based on the pg_statio_all_indexes and pg_stat_all_indexes views. Table G.49 lists columns of this report table.

The report table “Top growing indexes” shows top indexes by growth. The table uses data from the pg_stat_all_tables and pg_stat_all_indexes views. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. Table G.50 lists columns of this report table.

The report table “Unused indexes” shows top non-scanned indexes (during the report interval) by DML operations on underlying tables that caused index support. Constraint indexes are not counted. The table uses data from the pg_stat_all_tables view. Table G.51 lists columns of this report table.

Tables in this section of the report show top functions in the cluster by statistics from the pg_stat_user_functions view. Times in the tables are provided in seconds.

The report table “Top functions by total time” shows top functions by the total time elapsed. The report table “Top functions by executions” shows top functions by the number of executions. The report table “Top trigger functions by total time” shows top trigger functions by the total time elapsed. Table G.52 lists columns of these report tables.

The report table “Top tables by vacuum time spent” is available if pgpro_stats can provide extended vacuum statistics. This table shows top tables by total time spent vacuuming them. The data is based on the pgpro_stats_vacuum_tables view. Table G.53 lists columns of this report table.

The report table “Top indexes by vacuum time spent” is available if pgpro_stats can provide extended vacuum statistics. This table shows top indexes by total time spent vacuuming them. The data is based on the pgpro_stats_vacuum_indexes view. Table G.54 lists columns of this report table.

The report table “Top tables by blocks vacuum fetched” is available if pgpro_stats can provide extended vacuum statistics. This table shows top tables by blocks fetched vacuuming these tables. The data is based on the pgpro_stats_vacuum_tables view. Table G.55 lists columns of this report table.

The report table “Top indexes by blocks vacuum fetched” is available if pgpro_stats can provide extended vacuum statistics. This table shows top indexes by blocks fetched vacuuming underlying tables. The data is based on the pgpro_stats_vacuum_indexes view. Table G.56 lists columns of this report table.

The report table “Top tables by blocks vacuum read” is available if pgpro_stats can provide extended vacuum statistics. This table shows top tables by blocks read vacuuming these tables. The data is based on the pgpro_stats_vacuum_indexes view. Table G.57 lists columns of this report table.

The report table “Top indexes by blocks vacuum read” is available if pgpro_stats can provide extended vacuum statistics. This table shows top indexes by blocks read vacuuming underlying tables. The data is based on the pgpro_stats_vacuum_indexes view. Table G.58 lists columns of this report table.

The report table “Top tables by dead tuples vacuum left” is available if pgpro_stats can provide extended vacuum statistics. This table shows top tables by the number of dead tuples left by vacuum due to their visibility in transactions. The data is based on the pgpro_stats_vacuum_tables view. Table G.59 lists columns of this report table.

The report table “Top tables by WAL size generated by vacuum” is available if pgpro_stats can provide extended vacuum statistics. This table shows top tables by the amount of WAL generated by vacuum operations performed on them. The data is based on the pgpro_stats_vacuum_tables view. Table G.60 lists columns of this report table.

The report table “Top tables by vacuum operations” shows top tables by the number of vacuum operations performed (vacuum_count + autovacuum_count). The data is based on the pg_stat_all_tables view. Table G.61 lists columns of this report table.

The report table “Top tables by analyze operations” shows top tables by the number of analyze operations performed (analyze_count + autoanalyze_count). The data is based on the pg_stat_all_tables view. Table G.62 lists columns of this report table.

The report table “Top indexes by estimated vacuum load” shows top indexes by estimated implicit vacuum load. This load is calculated as the number of vacuum operations performed on the underlying table multiplied by the index size. The data is based on the pg_stat_all_indexes view. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. Table G.63 lists columns of this report table.

The report table “Top tables by dead tuples ratio” shows top tables larger than 5 MB by the ratio of dead tuples. Statistics are valid for the last sample in the report interval. The data is based on the pg_stat_all_tables view. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. Table G.64 lists columns of this report table.

The report table “Top tables by modified tuples ratio” shows top tables larger than 5 MB by the ratio of modified tuples. Statistics are valid for the last sample in the report interval. The data is based on the pg_stat_all_tables view. When there are no relation sizes collected with pg_relation_size(), relation-size estimates are based on the pg_class.relpages field. Since such values are less accurate, they are shown in square brackets. Table G.65 lists columns of this report table.

This section of the report contains a table with Postgres Pro GUC parameters, values of functions version(), pg_postmaster_start_time(), pg_conf_load_time() and the system_identifier field of the pg_control_system() function during the report interval. The data in the table is grouped under Defined settings and Default settings. Table G.66 lists columns of this report table.

pgpro_pwr collects detailed timing statistics of taking samples when the pgpro_pwr.track_sample_timings parameter is on. You can get the results from the v_sample_timings view. Table G.67 lists columns of this view.