We’re rolling out new features at pganalyze, including a streamlined way to compare EXPLAIN plans and tune slow queries. Join our webinar for a full walkthrough. Get all the product updates and our PostgreSQL Performance Checklist inside.
Welcome to the October 2024 edition of the pganalyze newsletter!
We're excited to share some of the latest pganalyze features we've been working on in our upcoming webinar, Compare EXPLAIN Plans & Tune Slow Queries on Wed. Nov. 13 at 10:30 am PT. What used to be a tedious workflow in an external SQL editor is now a streamlined analysis you can do right in pganalyze.
We've made some other big product updates this month, including the release of pganalyze collector v0.60 and v0.61. These updates bring a significant reduction in memory usage for query texts from pg_stat_statements (see product updates below to see how to measure it and here’s how to upgrade).
We've also updated our open-source Postgres parser library, libpg_query, to support Postgres 17. This update ensures compatibility with the latest SQL syntax added (such as MERGE RETURNING), allowing you to take advantage of the new capabilities introduced in PostgreSQL 17.
Finally, we wanted to share a PostgreSQL performance check list to help you stay on top of monthly tasks to maintain system reliability and prevent issues, like bloated indexes, that can build up over time. As always, you can also find the detailed list of product updates at the end.
Struggling with slow PostgreSQL queries? Debugging them can be a complex, time-consuming process. The Postgres planner doesn’t always choose the optimal execution plan, and comparing EXPLAIN plans manually often involves tedious pattern-matching and trial-and-error query adjustments.
In our webinar on Nov. 13, we’ll introduce new pganalyze features designed to make this process easier. You’ll learn how to automate EXPLAIN plan comparisons & conduct root cause analysis to resolve common slow query scenarios, such as inefficient joins, missing indexes, and large datasets.
With the new pganalyze Enterprise Server release we're concluding the work on our new packed statistics format (hint: it involves a lot of Postgres arrays), which brings considerable storage savings for statistics stored within the internal pganalyze database.
Thanks to the new format we are raising Connection tracing retention time to 7 days, up from 24 hours. This new release requires that you have run the 2024.04.X release series before, to ensure the dual writing of statistics has occurred (see release notes).
In this release we are also removing the use of the Object Storage, previously used to store log texts. As we have extracted more of the structured information from the logs and kept them inside the pganalyze database itself, we've gone back to the drawing board and reconsidered whether the separate requirement for S3 or Minio still makes sense.
After multiple months of discussions and testing and rolling this out to our cloud service, we are confident that with the new packed format we can utilize log text storage directly in the pganalyze database. This also simplifies deployment of Enterprise Server, since no S3/KMS or Minio needs to be configured anymore. As an existing user you can remove the associated configuration settings after 7 days.
Many PostgreSQL issues build up over time, such as bloated indexes, slow queries, or replication lag. Regular audits help catch these problems early, ensuring that databases run efficiently and remain available.
That's why we developed our new monthly performance check list to ensure you're being proactive with regular PostgreSQL audits. Follow these steps in a recurring team meeting to catch issues early and prevent disruptions.
Significantly reduces in memory usage for query texts from pg_stat_statements
If SELECT pg_size_pretty(sum(length(query))) FROM pg_stat_statements returns hundreds of megabytes for your database, or you monitor many databases, you may be able to allocate less memory to the collector after upgrading.
Query Details: EXPLAIN Plans tab now respects the date picker selection
Previously, EXPLAIN Plans tab in the query details page was showing only up to the most recent 50 EXPLAIN Plans, regardless of the time specified in the date picker. With this change, it'll now respect the time selected in the date picker.
Also introduce pagination and the ability to search by the plan fingerprint to the list.
EXPLAIN: Tweak plan fingerprinting to improve detection of partition indexes
This will result in the same fingerprint for substantially identical query plans that were previously fingerprinted distinctly.
Schema Statistics: Show matching query scans on index detail page
Now when viewing a specific index, you can see the matching table scans and associated queries.
Advisors
Index Advisor: Support Postgres 15 and 16 specific syntax and logic
VACUUM Advisor: Fix loading vacuum run details when table information is not available
Bug fixes & other improvements
System: Support showing system info and metrics for Azure databases
To enable this, set up the `AZURE_SUBSCRIPTION_ID` with the collector version 0.59.0 and above
