pg_profile

1. Overview

pg_profile is an extension tool for PostgreSQL database performance analysis. It is mainly used to collect resource-intensive activities in a target database, helping users gain in-depth insight into database runtime status, identify performance bottlenecks, and perform optimization. Its report is essentially a "delta analysis between two sample points", deriving the incremental load within the sampling interval, with sample point sequence numbers starting from 1.

pg_profile hard-depends on two extensions: dblink and plpgsql (it is written entirely in SQL and PL/pgSQL and requires no external libraries or software). Additionally, it is recommended to install the pg_stat_statements extension to obtain SQL statement-level statistics in the reports. The version of pg_profile is strongly tied to the version of PostgreSQL, currently supporting up to PG 18. For the specific version support, please refer to the README in the official pg_profile github repository.

Both the PG mode and Oracle compatibility mode of IvorySQL have been adapted for pg_profile. Note that in Oracle compatibility mode, before compiling pg_profile, you need to manually modify the control.tpl file in the extension source code to add a configuration item pg_dialect = true, otherwise the installation will fail.

Project address: https://github.com/zubkov-andrei/pg_profile

License: PostgreSQL License

2. Installation and Enabling

2.1. Source Code Compilation

To install pg_profile in IvorySQL’s Oracle compatibility mode, you need to first modify the control.tpl file of pg_profile to add the pg_dialect configuration item:

# pg_profile/control.tpl
pg_dialect = true

Perform source code compilation and installation:

# Build and install pg_profile and its dependent extensions
make -C contrib/dblink install
make -C contrib/pg_stat_statements install
make -C contrib/pg_profile install

The installation artifacts are pg_profile.control and pg_profile—​4.11.sql. In IvorySQL’s Oracle compatibility mode, you can verify that the generated pg_profile.control file carries the pg_dialect declaration:

default_version = '4.11'
requires = 'dblink,plpgsql'
superuser = false

pg_dialect = true

2.2. Modify Configuration

The pg_stat_statements that pg_profile depends on must be loaded at server startup via shared_preload_libraries.

Edit ivorysql.conf and append pg_stat_statements to the end of the shared_preload_libraries configuration item:

# ivorysql.conf
shared_preload_libraries = 'liboracle_parser, ivorysql_ora, pg_stat_statements'

2.3. Restart the Service

pg_ctl -D $PGDATA restart

2.4. Install the Extension

The commands are identical in both PG mode and Oracle mode sessions:

CREATE SCHEMA profile;
CREATE SCHEMA dblink;
CREATE SCHEMA statements;
CREATE EXTENSION dblink SCHEMA dblink;
CREATE EXTENSION pg_stat_statements SCHEMA statements;
CREATE EXTENSION pg_profile SCHEMA profile;

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_profile';
 extname    | extversion
------------+------------
 pg_profile | 4.11

In Oracle compatibility mode, you can run the following query to confirm that the extension functions of pg_profile have been pinned to the PG syntax parser:

SELECT proname, proconfig FROM pg_proc
WHERE proname = 'take_sample' AND proconfig IS NOT NULL LIMIT 1;
   proname   |                          proconfig
-------------+-------------------------------------------------------------
 take_sample | {ivorysql.compatible_mode=pg,search_path=profile}

3. Usage Workflow

3.1. Create Samples

-- First sampling
SELECT * FROM profile.take_sample();
 server | result |   elapsed
--------+--------+-------------
 local  | OK     | 00:00:01.34

-- ...after the business workload runs for a while, sample again
SELECT * FROM profile.take_sample();

In production environments, periodic sampling via scheduled tasks is typically used (e.g., once every 30 minutes), which can be combined with pg_cron or an external crontab:

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

3.2. View Samples

SELECT sample, sample_time, sizes_collected FROM profile.show_samples();
 sample |      sample_time       | sizes_collected
--------+------------------------+-----------------
      1 | 2026-06-12 09:00:00+00 | t
      2 | 2026-06-12 09:30:00+00 | t

3.3. Generate Reports

-- Generate the workload report between sample 1 and sample 2 (HTML text)
\o report_1_2.html
SELECT profile.get_report(1, 2);
\o

The above functions are invoked and produce identical results in an Oracle mode session (Oracle port/1521).

4. Notes

4.1. Adaptation Description

IvorySQL can directly install and use the pg_profile extension in PG mode. However, in Oracle compatibility mode, you need to add the pg_dialect configuration item to the control.tpl file of pg_profile at compile time to enable the PG syntax parser and avoid installation failure.

4.2. Adaptation Boundary

The pg_dialect adaptation mechanism protects the extension’s own code; the SQL written in the caller’s session is still parsed according to the session dialect. When calling pg_profile in an Oracle mode session, the user’s own expressions in the statements must conform to Oracle mode syntax, for example:

-- In an Oracle session: PG-style interval literals will cause an error during session parsing
SELECT profile.set_server_size_sampling('local', current_time - interval '10 minute', ...);  -- ERROR

-- Use a syntax acceptable to the session dialect, or perform management operations in a PG mode session