pg_profile PostgreSQL Extension

pg_profile is a PostgreSQL extension designed to identify resource-consuming activities by utilizing PostgreSQL statistic views and several contrib extensions. It operates by taking periodic 'samples' of database performance data and storing them in a 'Historic repository' (a set of extension tables).

Key concepts:

• Samples: Data snapshots captured by calling the take_sample() function. To automate this, you must use an external scheduler like cron.
• Historic Repository: The storage for all sampling data.
• Servers: Remote PostgreSQL clusters that pg_profile can monitor. A local server is automatically created for the cluster where the extension is installed.
• Side Effects: When a sample is taken, pg_stat_statements_reset() is called to prevent data loss from reaching pg_stat_statements.max. If pg_wait_sampling is present, it also resets pg_wait_sampling_profile. These resets may affect other monitoring systems.
• Extension Architecture: Composed of a Historic repository, a Sample management engine, a Report engine, and Administrative functions (for managing servers and baselines).

The pg_profile Grafana dashboard provides several views to analyze PostgreSQL performance. The metrics are derived from pg_stat_statements and other extensions. Key metrics include:

• Query ID: Hexadecimal queryid from pg_stat_statements. A compatible identifier for pgcenter is also provided in square brackets (hash of dbid, userid, and queryid).
• Time (s): Total time spent in a statement, broken down into:
  • Elapsed: total_plan_time + total_exec_time.
  • Plan: total_plan_time.
  • Exec: total_exec_time.
• JIT time (s): Sum of jit_generation_time, jit_inlining_time, jit_optimization_time, and jit_emission_time.
• I/O time (s): Sum of blk_read_time and blk_write_time.
• CPU time (s): User (Usr) and Kernel (Sys) space time, provided by the pg_stat_kcache extension.
• Plan types: Indicates if the statement used a Generic or Custom plan.
• %Cvr: Coverage, representing the statement stats collection duration as a percentage of the report duration.

pg_profile is a PostgreSQL extension written in pure pl/pgsql that identifies resource-intensive activities by leveraging PostgreSQL statistics views and the pg_stat_statements and pg_stat_kcache extensions.

Core Concepts

• Historic Repository: The extension creates a repository in your database to hold statistics "samples".
• Sampling: A sample is captured by calling the _take_sample() function. Because PostgreSQL lacks a built-in job engine, you must use a tool like cron to call this function periodically.
• Reporting: You can generate reports between two samples to analyze the load profile during a specific period (e.g., during a performance degradation event).
• Automatic Resets: Every time a sample is taken, pg_stat_statements_reset() is called. This prevents statistics from being lost due to reaching the pg_stat_statements.max limit, but it will affect statistics collected by other monitoring tools that rely on the pg_stat_statements view.
• Multi-Server Monitoring: A single pg_profile installation can collect data from remote clusters (called servers). You define these servers by providing names and connection strings. A local server is automatically created for the cluster where the extension resides.

-- Conceptually, you trigger sampling via:
SELECT _take_sample();

The pg_profile report is organized into several sections that provide deep visibility into PostgreSQL performance.

Key Capabilities:

• Object Highlighting: Most objects mentioned in a report can be selected to highlight all related entries across different sections.
• Sub-string Filtering: You can use a sub-string based filter to limit the report content to specific objects (e.g., a specific database or user).

Main Report Categories:

• Server Statistics: High-level overview including Database, Cluster I/O, Cluster SLRU, and Cluster-wide statistics.
• Database Statistics: Granular metrics per database, including transactions, checksums, block I/O, tuples, parallel workers, and temporary files.
• Session Statistics: Data regarding session timings and session counts (established, abandoned, fatal, killed).
• Statement Statistics: Aggregated data from pg_stat_statements, including execution/planning times, I/O, and WAL usage.
• JIT Statistics: Metrics on JIT compilation, inlining, optimization, and emission.