2023.07.0 Release

Docker tag: quay.io/pganalyze/enterprise:v2023.07.0

Notes

• This release requires a database migration when coming from older releases, be sure to follow the recommended upgrade steps

Features

• Index Advisor
  • Support analyzing queries that require the "ltree" extension
  • Add support for IMMUTABLE functions
    • This fixes errors like "functions in index expression must be marked IMMUTABLE" or "functions in partition key expression must be marked IMMUTABLE".
  • Fix query analysis of INSERT INTO SELECT by ensuring correct column order for query analysis
    • This fixes type coercion errors like "column "X" is of type A but expression is of type B" on specific cases of INSERT INTO SELECT statements that relied on the column positions of the target table.
  • Support indexes using custom functions in tables referenced via foreign key constraints
  • Fix some cases of query analysis error "view depth limit (10) exceeded"
  • Support schemas that require custom functions in check constraints/domain types
  • Fix support for handling functions that call citext input/output functions
  • Add support for schemas that include SP-GIST indexes
• Update bundled collector version to release 0.50.1
  • Log Insights: Add autovacuum index statistics information introduced in Postgres 14
    • Previously, if autovacuum logs included such information, the collector failed to match the log line and the events would not be classified correctly in Log Insights
  • In case you have installed the collector separately, we recommend upgrading the collector package/container and reviewing the full changelog.
• Alerts & Check-Up
  • Run each daily check in a separate background jobs for each server
    • This improves resiliency against crashes in individual checks
• Connection Tracing: Increase Connection Traces live refresh timeout from 1 to 5 minutes
• Check-Up: Show full query text instead of truncated one-line query for issue pages of Active Queries and Blocking Queries checks
• Query details page: Update query text unavailable error message to explain how to fix the problem queries

Performance

• Snapshot processing
  • Improve speed of lock graph analysis for blocking queries alert
  • Re-introduce limit of length on query parsing for very large queries (more than 200,000 characters)
    • After the removal of the old 20,000 limit in the prior Enterprise release (v2023.06.0) we've received reports of this causing crashes
    • In our testing the problem case here is when queries are extremely long (sometimes query texts exceeding 1MB in length) - this release re-introduces a limit of 200,000 (10x the old limit), which allows most cases but avoids the extreme cases that cause the snapshot worker to crash
    • Queries exceeding this length limit will not be analyzed by Index Advisor, and will show under the "Other" statement type in the Query Performance view
  • Adjust estimation method used for calculate overview percentile metrics to improve snapshot processing speed
    • This provides a significant speedup for snapshot processing in some edge cases (which may have previously timed out)
    • Note that you may see small differences in top-level percentile numbers going forward compared to historic calculations

Bugfixes

• Config Settings
  • Move autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor from Other Settings config group to Autovacuum config group
  • Update and sync Postgres setting config group with Postgres 15
• Query and table overview: Fix edge case that accidentally counted the same timeseries data point twice
  • This edge case only occurred when viewing a very short time frame (2 hours or less), within certain conditions. If the error occurred, you might have seen the "% of Total Runtime" sum up to more than 100%, or other summary statistics to be higher than what you would get when viewing the detail statistics individually.
• Index Advisor
  • Fix incorrect method/costs shown for scans on query details
    • This bug caused the scan list to pull in costs and methods from other queries in the same database on the same table, that had an identical scan (i.e. same set of WHERE/JOIN conditions)
    • Whilst this information was not utilized for index recommendations, it may have been misleading when reading the scan information on the query details page.
• Object metadata retention
  • Fix accidental early deletion of invalidated objects
    • This fixes a bug that caused invalidated objects to be deleted ahead of retention time, potentially causing an error when visiting a page for an invalidated object. Invalidated objects mean that they are no longer present on the database server, such as deleted tables. This affected only the metadata of these objects, not the statistics.
  • Mark databases that are no longer visible in a snapshot as "invalidated"
    • Previously such databases would never be deleted in pganalyze (despite having been removed on the actual database)
    • Deleted databases will now disappear from lists immediately (e.g. the "All databases" list, and the database dropdown), but direct links will continue to work for the remainder of the retention time
• Snapshot processing
  • Require collector API keys for snapshot API, do not accept read or read/write keys
  • Periodically call malloc_trim to reduce peak memory consumption
  • Remove automatic restart of snapshot workers every 6 hours
    • In the past this was necessary due to instability of the workers running over a long time - in our testing these problems are resolved, and turning off the restarts will avoid edge cases where the old behavior caused snapshots to be partially processed

Security

• Update Rails for CVE-2023-28362
• Fix medium severity security issues discovered in a recent security pentest
  • Upgrading is recommended as part of a general practice of keeping your pganalyze installation up to date, but we do not believe an accelerated update is necessary
  • The issues are limited in scope, and require having access to the pganalyze installation with an existing user account that has elevated privileges, in order to be exploited
  • Additional details are available upon request