Profile PostgreSQL Workloads & Automate Reports with pg_profile - PolarDB

When a user reports that the database was slow two hours ago, real-time monitoring tools can't help—the moment has passed. pg_profile solves this by storing periodic snapshots of database activity, so you can build a report covering any past interval and identify the top resource-intensive queries and wait events during that window.

pg_profile is a third-party PostgreSQL extension written entirely in PL/pgSQL with no external library dependencies. Combine it with pg_cron to automate periodic sampling.

How it works

pg_profile has three components:

Historic repository: Extension tables that store snapshot data over time.
Sample management engine: Functions that take snapshots (take_sample()) and prune obsolete data automatically.
Report engine: Functions that generate HTML reports from stored snapshots (get_report()).

The extension uses a two-database architecture based on dblink:

A profile database stores the historic repository and hosts the pg_profile extension.
A server database is the target you want to monitor. It runs pg_stat_statements and optionally pg_stat_kcache to expose query statistics.

When you call take_sample() from the profile database, it connects to the server database via dblink, reads the statistical views, and saves the results locally. Both databases must be on the same PolarDB cluster.

Supported versions

Supported version: PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.18.0 or later.

To check your revision version:

SHOW polar_version;

Prerequisites

Profile database:

The pg_profile extension depends on dblink, which is installed automatically when you use the CASCADE option during creation.

Server database:

Requirement	Status	Notes
`pg_stat_statements`	Required	Add to `shared_preload_libraries` so it loads at startup. Create it in the server database.
`pg_stat_kcache`	Recommended	Provides kernel-level I/O and CPU statistics that enrich reports.

Important

Modifying shared_preload_libraries requires a cluster restart. Plan for downtime before making this change. For configuration steps, see .

Security: The profile database and server database must reside on the same PolarDB cluster. dblink connections use a privileged account on the server database.

Set up pg_profile

Step 1: Create and configure the server database

CREATE DATABASE server;

Connect to the server database and install the required extensions:

\c server
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

Step 2: Create the profile database

CREATE DATABASE profile;

Connect to the profile database and install pg_profile. The CASCADE option automatically installs dblink and other dependencies:

\c profile
CREATE EXTENSION IF NOT EXISTS pg_profile CASCADE;

Step 3: Register the server database as a sampling target

SELECT create_server(
   'server',
   'dbname=server channel_name=localhost user=<privileged-account> password=<password>'
);

Parameter	Example value	Description
`server` (first argument)	`server`	A name you assign to this sampling target. Used in all subsequent `take_sample()` and `get_report()` calls.
`dbname`	`server`	The server database name.
`channel_name`	`localhost`	Connection channel. Only `localhost` is supported.
`user`	`test_user`	A privileged account on the cluster that hosts the server database.
`password`	`T123456!`	Password for the account.

Expected output:

                                    show_servers
---------------------------------------------------------------------------------
 (server,"dbname=server channel_name=localhost user=test_user password=****",t,)
(1 row)

To update the connection string later, call set_server_connstr() with the same parameters:

SELECT set_server_connstr(
   'server',
   'dbname=server channel_name=localhost user=<privileged-account> password=<password>'
);

Parameter	Example value	Description
`server` (first argument)	`server`	The name of the connection. You do not need to change this.
`dbname`	`server`	The server database name.
`channel_name`	`localhost`	Connection channel. The value is fixed as `localhost`.
`user`	`test_user`	A privileged account on the cluster that hosts the server database.
`password`	`T123456!`	Password for the account.

Collect samples

Take an initial sample to establish a baseline:

SELECT take_sample();

Expected output on success:

      take_sample
-------------------------
 (server,OK,00:00:02.81)
(1 row)

After collecting an initial sample, generate some database activity, then take a second sample to create a reportable interval. The following example uses pgbench to run a load test on the server database:

pgbench -s 100 -i server \
   -U test_user \
   -h pc-****.pg.rds.aliyuncs.com \
   -p 5432

Parameter	Example value	Description
`-s`	`100`	Scale factor. A value of `100` creates 10,000,000 rows in the `pgbench_accounts` table.
`-i`	`server`	Database name to initialize and test.
`-U`	`test_user`	Privileged account on the cluster.
`-h`	`pc-****.pg.rds.aliyuncs.com`	Cluster endpoint.
`-p`	`5432`	Port. Set this to the value shown in the console.

Take a second sample after the load test:

SELECT take_sample();

View all collected samples for the server:

SELECT * FROM show_samples('server');

 sample |      sample_time       | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+------------------------+-----------------+---------------+-----------------+-----------------
      1 | 2024-02-23 07:29:53+00 | t               |               |                 |
      2 | 2024-02-23 08:11:39+00 | t               |               |                 |

Generate reports

Reports cover the interval between two samples. Use the sample IDs from show_samples() to specify the interval.

Method 1: Export from a psql interactive session:

\o report_1_2.html
SELECT get_report('server', 1, 2);

Method 2: Export directly from the command line:

psql -Aqtc "SELECT get_report('server', 1, 2)" \
   -o report_server_1_2.html \
   -d profile \
   -h <profile-database-endpoint> \
   -p <port> \
   -U <privileged-account>

Operational notes

Topic	Guidance
Collection frequency	Take snapshots once or twice per hour. More frequent sampling rarely improves report resolution and increases storage consumption.
Automated sampling	Schedule `take_sample()` with `pg_cron`. A cron expression of `/30 * * *` takes a sample every 30 minutes.
Disk usage	The historic repository consumes disk space as snapshots accumulate. `pg_profile` includes automatic cleanup, but monitor disk usage on clusters with high query volumes.