Hi there,

Introducing Plan Statistics for Amazon Aurora

Understanding query plans and how they evolve over time is key to database performance. Historically this has been challenging to track comprehensively for all queries, especially those that are fast. Good news! This just got easier for Amazon Aurora PostgreSQL users. We've integrated with the new Amazon aurora_stat_plans function to gain visibility into query execution statistics at the plan-level, making it easier than ever to optimize Aurora PostgreSQL performance with pganalyze.

By tracking execution plans historically, you can identify patterns and pinpoint issues before they impact production workloads. This feature captures changes in key execution metrics like rows, I/O time, and total execution time for every query, so you can understand exactly how your queries interact with the database. pganalyze Plan Statistics is available now to all pganalyze Cloud customers running Amazon Aurora PostgreSQL version 14.10, 15.5, or higher, and will be included in the next Enterprise Server release.

We are also working on an improved open-source extension that will allow collecting this data on any Postgres server, in the cloud on on-premise - more on that in a future newsletter!

Webinar Recording: Compare EXPLAIN Plans & Tune Slow Queries with pganalyze

In case you missed last month's webinar, the recording, presentations slides, and resource list are now available. We discuss upcoming features in pganalyze that take query plan comparisons from a painstaking, manual process to a streamlined analysis, making debugging slow Postgres queries more accessible. We also previewed the aurora_stat_plans integration that was just released for Amazon Aurora customers.

Tracking Buffer Cache Statistics Over Time

When a query runs in Postgres, performance often hinges on whether its data resides in memory or needs to be fetched from disk. With our new Buffer Cache Statistics feature, pganalyze introduces a way to monitor and analyze the contents of the Postgres buffer cache over time, leveraging the pg_buffercache extension.

This feature empowers you to pinpoint performance bottlenecks by revealing how cache contents shift in response to workloads. Whether you're investigating why a query is sometimes slow or tracking the impact of unrelated jobs evicting critical tables from the cache, Buffer Cache Statistics makes it easy to correlate cache changes with query slowdowns.

This feature is available now to all pganalyze Cloud customers and will be included in the next Enterprise Server release.

Subscribe on YouTube

We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.

Product updates

• Add plan statistics tracking for Amazon Aurora (see details earlier in the newsletter)
• Query Details: Group query plans collected via automated EXPLAIN mechanism

• Track Postgres buffer cache usage (see details earlier in the newsletter)
• After upgrading to collector version 0.63.0 and enabling the pg_buffercache extension, pganalyze will track buffer cache usage on a per-server basis so you can see which tables use the most memory and how the workload changes over time.

• Indexing Engine: Add support for queries using simple expressions involving CURRENT_DATE that may need to be evaluated at plan time - Update query parser to support Postgres 17 syntax
• EXPLAIN plan details: Omit overly long parameter lists to avoid breaking the page layout

• Improve load time for query details when the query has not been analyzed yet for Index Advisor

• Previously the page would do the analysis on demand if the query has not yet been analyzed yet ahead of time

• To avoid timeouts, the page now instead enqueues analysis in the background, and shows a message indicating this

• Improve load time for per-table statistics page by optimizing storage of aggregated table index size

• Adjust snapshot API authentication quota to avoid excessive rate limiting errors in common cases

• Fix clearing of stale table storage options that were removed (e.g., through ALTER TABLE ... RESET ...)

• Track Postgres buffer cache usage (see details earlier in the newsletter)
• Collect query plan information on Amazon Aurora (see details earlier in the newsletter)
• Improve partitioned table stats handling
• Partitioned table stats are now reported as aggregations over child partition stats
• Add new options DB_URL_FILE and DB_PASSWORD_FILE
• This allows passing sensitive DB passwords through files instead of environment variables. This makes collector work better with systemd credentials and NixOS flakes.
• Thanks to Philip Munksgaard for this contribution to the collector code!
• Fix WebSocket error handling
• If the WebSocket mechanism hit an error at the wrong time, this could cause a stuck collector state, where the collector would keep running but stop processing and sending snapshots
• Fix PII filtering for detail log lines
• Due to a bug in 0.60.0, all detail log lines (lines that add additional context to the primary log message) were unnecessarily redacted 
• Update packaging scripts to use the groupadd command instead of addgroup when installing
• Update systemd file to use MemoryMax instead of MemoryLimit
• Add collector query runner
• This provides a mechanism for the collector to help pganalyze users run EXPLAIN ANALYZE queries on-demand in future versions of pganalyze
• This is currently in early access, and disabled by default - more details to come in a later announcement

Earn a $25 Gift Card for a Review on G2

Help us get more community feedback and qualify for a free gift card while supplies last.