Discover the latest Postgres insights in our January 2025 newsletter, including new EXPLAIN plan comparison features, a guide to mapping Oracle hints with pg_hint_plan, and a peek at our upcoming community events and product updates.
Welcome to the January 2025 edition of the pganalyze newsletter!
With world events given what they are, it's sometimes nice to distract ourselves with other things, and, in our case, all things Postgres. Current events make me even more grateful to be part of the international Postgres community. I was proud to be named as a Postgres contributor in January, together with other community members. Our team at pganalyze is looking forward to sponsoring and joining more community events this year, including PGDay Chicago & PGConf.dev (more details below).
We also wanted to share a bit about the journey developing our new EXPLAIN plan comparison feature and show recent improvements to make it work better with plans collected via Automated EXPLAIN. Maciek from our team walks through why it's useful to compare plans, when challenges arise with existing tools, and how it influenced our product roadmap to expand our EXPLAIN plan comparison functionality.
One of the common topics we hear when tuning Postgres is that it can be challenging to understand how to tune queries when coming from other databases such as Oracle. For example: What is the equivalent of Oracle Database hints in Postgres? If you're in an Oracle-to-Postgres migration, or have more familiarity with tuning Oracle, you will find our new guide on how to map Oracle hints to Postgres using pg_hint_plan & best practices for hinting useful. And while it may seem tempting to migrate every Oracle hint over, we discuss why this is probably a bad idea.
As always, you can find a complete list of product updates at the end. And if you'd be interested in joining our fully remote team at pganalyze driving these updates, we just opened several new engineering roles.
All the best,
Lukas
Comparing EXPLAIN plans is hard (& how pganalyze does it)
In this post, Maciek walks through EXPLAIN plan fundamentals, why it’s helpful to compare EXPLAIN plans and the challenges presented by existing tools such as EXPLAIN itself, and how that influenced our product roadmap at pganalyze to create a text-based diff interface, which we first rolled out as part of an earlier beta release. Now, we’re expanding that same functionality to the EXPLAIN plan list under query details and adding a new comparison metric, buffers.
Replacing Oracle Hints: Best Practices with pg_hint_plan on PostgreSQL
If you are moving from Oracle Database to PostgreSQL, you are likely accustomed to using hints to optimize queries. Unlike Oracle, hints are not built-in to Postgres, but there is a community-built extension called pg_hint_plan. It may be tempting to replicate every hint with a pg_hint_plan equivalent, but in this post, we'll walk through why that may be overkill, how to map Oracle hints to pg_hint_plan equivalents, and how to debug and test when to use hints in your Postgres environment.
We're excited to be sponsoring both PGDay Chicago and PGConf.dev this year. The schedules haven't been released yet, but it's a great time to secure your ticket. More news to come, including talks by Lukas and Keiko from our team at the events. We hope to see you there:
Support extracting parameters from queries that re-use parameter references
Add ability to rename parameter names
Allow changing all planner-related settings when creating a query variant
Allow editing parameter settings for new parameters added when creating a variant
Enable going back to earlier steps (and discarding progress), e.g. to edit the query after an EXPLAIN ANALYZE failure
Support editing variant names and descriptions after variants were created
EXPLAIN Plans
Automated EXPLAIN: Link to plan comparison from per-query EXPLAIN plan sample list
Support parsing WindowAgg node with Run Condition with text format EXPLAIN result
Index Advisor
Support custom extension schemas
This ensures query analysis can complete when extension data types are utilized where the extension is installed in a custom (i.e. non "public") schema.
Fix edge cases where query analysis did not succeed on Postgres 16 or 17
Fix highlight of menu item when some tabs are selected
Bug fixes & other improvements
Active query alert: Ignore connections from pg_repack - Fix bug with setting up new Slack connections for alerting (introduced in December 2024)
Organization Dashboard: Show inactive servers if no active servers present
The Organization Dashboard includes a list of servers, with a toggle for showing servers with no recent snapshots recorded. This sets the toggle to on by default if no servers have recent snapshots.
Improve main menu layout for users who belong to many organizations
Server Settings: Show pg_stat_statements info data in Debug Info panel when available
These stastistics will be shown starting with the next collector release
We have retired the pganalyze demo hosted at demo.pganalyze.com
This demo had recurring maintenance issues and only showed a pgbench workload, which is not representative for how pganalyze is used on production systems
For a self-directed demo of pganalyze visit our product tour instead (based on real-world examples from our own databases), or reach out to our team for a demo call.
.png?width=1200&upscale=true&name=pganlalyze_newsletter_header_NL(1).png)
