Version 2.0.0 (2023-03-20)

Release date	March 20, 2023
Installation	Install pg_stat_monitor

Release highlights

• pg_stat_monitor 2.0.0 is Generally Available with Percona Distribution for PostgreSQL, enabling you to use it in production environments. Its improved internal architecture leads to fewer lock acquisitions, and therefore an improved performance by approximately 20% when tested using pgbench.

• Added support for PostgreSQL 15 by adding the following columns to pg_stat_monitor view:

  • temp_blk_read_time
  • temp_blk_write_time
  • jit_functions
  • jit_generation_time
  • jit_inlining_count
  • jit_inlining_time
  • jit_optimization_count
  • jit_optimization_time
  • jit_emission_count
  • jit_emission_time

• Improved consistency with pg_stat_statements - Now pg_stat_monitor view has exactly the same column names, columns and data types as the pg_stat_statements view for every major version of PostgreSQL from 11 to 15.

• Histogram output now allows users to see the whole data range, including the queries with the execution time lower than the minimum time, and exceeding the maximum time specified by the user.
• The query text is now stored in memory, improving the memory buffer management and preventing its overflow
• CPU and user time are now captured for utility statements, helping you understand the resource consumption for these statements execution.
• PMM integration improvements:
  • An indication of buckets as “done” vs “current” eliminates the need for the tool to evaluate the bucket status and facilitates the accurate data display
  • The generation of a unique ID of a query, independent from PostgreSQL versions, database, users or schemas, improves observability of query planning, execution and performance

Incompatibility with previous releases:

• The pg_stat_monitor_settings view is deprecated and removed. To retrieve the configuration parameters, use pg_settings.
• The following columns are removed for PostgreSQL 12 and earlier: wal_records, wal_fpi, wal_bytes
• Changes to pg_stat_monitor view for all PostgreSQL versions:

Data type changes	Columns added	Columns renamed	Columns removed
userid - oid queryid - bigint top_queryid - bigint bucket_start_time - timestamp with time zone	dbid user	rows_retrieved to rows plans_calls to calls	state state_code

New Features

• PG-310 - Add an indication of buckets as “Done”
• PG-400: Add timezone to bucket_start_time in the pg_stat_monitor view
• PG-435, PG-543 - Make pg_stat_monitor view compatible with that of pg_stat_statements for PostgreSQL 15.
• PG-545 - Generate database/scheme/user-agnostic query ID to add visibility into query performance behavior
• PG-586 - Capture CPU and user timing for utility statements

Improvements

• PG-354 - Remove obsolete pg_stat_monitor_settings view
• PG-312, PG-313 - Include queries that take less and more than histogram min/max time to histogram
• PG-321 - Remove redundant cmd_type/cmd_type_text columns in pg_stat_monitor view.
• PG-373 - Remove unused wal fields for PG12 and below
• PG-488 - Improve the storage mechanism of buckets and query texts using dynamic shared memory for PostgreSQL 15 and onward
• PG-606: Add the ability to enable/disable pgsm_query_id calculation via a GUC parameter
• PG-607: Allow histogram to track queries in sub-ms time brackets

Bugs Fixed

• PG-306 - Fixed datatype for bucket_start_time by changing it to timestamp
• PG-320 - Removed state/state_code fields from pg_stat_monitor_view.
• PG-518 - Hide internal functions for non-superusers in pg_stat_monitor API.
• PG-552 - Remove unnecessary columns from PostgreSQL 11 and 12 views
• PG-576 - Fixed the issue with incorrect query length in case of a normalized query when the query length exceeds the PGSM_QUERY_MAX_LEN value
• PG-579 - Added a check if pg_stat_monitor is loaded in shared_preload_libraries before querying the view to prevent the server from crashing
• PG-583, PG-597 - Removed rounding off for floating point values
• PG-588 - Fixed missing normalization for some queries
• PG-582 - Fixed rounding for blk_read_time and blk_write_timecolumns

---

Last update: December 5, 2023
Created: December 5, 2023

Contact Us

For free technical help, visit the Percona Community Forum.

To report bugs or submit feature requests, open a JIRA ticket.

For paid support and managed or consulting services , contact Percona Sales.