Docs

RDS for PostgreSQL

Extensions

Resource-intensive activity statistics (pg_profile)

pg_profile is a tool that can collect resource usage statistics in the database to help users analyze and optimize the database.

Background information

pg_profile is based on PostgreSQL’s built-in statistics views (pg_stat_statements) and community extension (pg_stat_kcache), fully written in PL/pgSQL, with no external libraries or software required. pg_profile depends on the dblink, pg_stat_statements, and pg_stat_kcache extensions. Among them, pg_stat_kcache is optional. Enabling pg_stat_kcache provides more information collection features.

Prerequisites

Add pg_stat_statements to the shared_preload_libraries parameter.

(Optional) Add pg_stat_kcache to the shared_preload_libraries parameter.

Precautions

Storing sampling information will consume disk space. pg_profile has an automatic cleanup mechanism; pay attention to data preservation.

Sampling is time-consuming and does not require frequent execution; the recommended sampling frequency is 1 to 2 times per hour.

Create and delete extension

Create extension

tip

A super account is required to create the extension.

CREATE EXTENSION pg_profile;

Delete extension

DROP EXTENSION pg_profile;

Extension usage

The pg_profile extension requires coordination between two databases during use: one database is sampled, and the other stores the collected statistical information. These two databases can be created in different PostgreSQL instances or within the same PostgreSQL instance. Below, the server database refers to the sampled database, and the profile database refers to the database storing statistical information, explaining the usage process of the pg_profile extension.

During sampling, the profile database sends a request to the server database, which samples its information upon receiving the request and returns the sampling results to profile. Profile stores the received results in tables within the profile database.

1. Prepare the Server database.

Create a Server database.

CREATE DATABASE server;

Connect to the Server database and create an extension.

\c server
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;

server=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_kcache | 2.2.3 | public | Kernel statistics gathering
pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
...

2. Prepare the profile database.

Create a profile database.

CREATE DATABASE profile;

Connect to the profile database and create an extension.

\c profile
CREATE EXTENSION plpgsql;
CREATE EXTENSION dblink;
CREATE EXTENSION pg_profile;

postgres=> \c profile
You are now connected to database "profile" as user "test_account".
profile=> \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pg_profile | 4.4 | public | PostgreSQL load profile repository and report builder
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
...

3. Create a Server connection.

Parameter	Sample	Parameter description
server	server	Connection name, custom.
host	127.0.0.1	Connection address of the instance hosting the server database: 1) If the profile database and server database are on the same instance, configure this parameter as 127.0.0.1 2) If the profile database and server database are on different instances, configure this parameter as the Public network address of the instance hosting the server database
port	5432	Port of the instance hosting the server database
dbname	server	Database name on the server side
user	test_account	Super account of the instance hosting the server database
password	xxxxxx	Password corresponding to the Account.

Return result example:

tip

After creating the server connection, a profile database connection is automatically created, which is the connection named local in the above return result example.

4. Modify the profile database connection information and configure the Password.

5. Perform initial data collection.

6. Collect data again after conducting stress testing on the server database.

tip

The following commands must be executed in the client command line window. Ensure the client has PostgreSQL client installed.

pgbench is a simple program for running benchmarks on PostgreSQL. For more command usages, refer to the PostgreSQL official documentation.

Parameter	Sample	Parameter description
-U	test_account	Super account of the instance where the server database is located.
-h	postgresxxxxxx.rds-pg.ivolces.com	Server database instance connection address.
-p	5432	Instance port.
-s	100	100 means creating 10,000,000 rows of data in the pgbench_accounts table.
-i dbname	server	Database to be tested.
-T	300	Stress test 300s
-c	100	Client concurrent processes 100.
-j	2	Number of threads per client.

Connect to the instance where the profile database is located and collect data again.

7. Generate a report from the collected results.

At two methods can be used to export the collected results to the local machine.

Method 1: Execute the following command in the profile database using psql:

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

Method 2: Execute the following command in the local client command line:

Report sample: report_server_1_2.html.

Others

For more information, refer to GitHub - zubkov-andrei/pg_profile at 4.4.

Last updated: 2026.01.13 18:44:18