Welcome to the August 2024 edition of the pganalyze newsletter!
In anticipation of the upcoming release of Postgres 17, we're hosting a webinar to breakdown the changes and enhancements that are set to impact how you manage and optimize your PostgreSQL databases. Save your spot today.
Can't wait for the webinar? Below you can find a roundup of 5mins of Postgres episodes which track our journey "Waiting for Postgres 17."
We'd also like to call out a couple community events happening this month. I'm giving a talk on constraint programming at a virtual event hosted by the SF Bay Area Postgres Meetup Group next week. We will also be at PGConf NYC at the end of the month where I will give a sneak peek at new query optimization features.
As always you can find product updates at the end, and, in an effort to gather more community feedback, we're running a promotion with G2 offering $25 gift cards in exchange for an honest review of pganalyze. We'd be grateful for your participation.
Learn about significant changes and enhancements coming in the release of Postgres 17, and how they impact how you manage and optimize your PostgreSQL databases.
This webinar will dive deep into the final release, examining what features have been included, what was removed in the beta phase due to bugs that were found, and how the changes in this release will impact your database performance and monitoring. Register today as space is limited.
This month, there are a couple of opportunities to connect with the Postgres community and discuss the latest developments. I'll be presenting on constraint programming at a virtual event hosted by the SF Bay Area Postgres Meetup Group. This talk will focus on practical techniques for optimizing database performance and managing complex queries.
Later in the month, we'll have a booth at PGConf NYC where we'll be giving a talk on how to optimize a slow query. For those in attendance, you'll see a sneak peek at upcoming new query optimization features in pganalyze.
We discuss the incremental backup feature that was just committed to the Postgres 17 development branch. To be clear, this is very much off the presses and this may yet change or be removed completely.
We discuss a recently committed change to the Postgres 17 development branch that allows configuring the size of the Postgres SLRU caches, as well as improvements to LWLock contention on SLRU related locks. We review the background on what SLRU caches do in Postgres, and historic performance challenges experienced at scale.
We discuss a recently committed change to the Postgres 17 development branch that introduces an improved dead tuple storage for autovacuum based on adaptive radix trees. This significantly reduces autovacuum memory consumption and avoids the need for multiple index vacuum phases.
We discussfaster B-tree index scans in Postgres 17 for queries that involve IN lists or other cases where multiple array values are being passed to Postgres (ScalarArrayOpExpr). We show how even simple cases now avoid repeated page access, and how turning filters into index conditions and processing like anIndex Skip Scan can yield significant speedups for certain queries.
We discuss streaming and vectored I/O in Postgres 17. This is an important step towards implementing asynchronous I/O in Postgres. In Postgres 17 we do not yet have asynchronous I/O, but we do see a performance benefit from what is essentially a refactoring work around the internal APIs.
The new mechanism is more performant and allows for arbitrary log_line_prefix settings
The new parsing mechanism is the default, but you can set db_log_line_prefix = legacy in the config file or LOG_LINE_PREFIX=legacy in the environment to revert to the old mechanism
Log Insights: Redact parameters from utility statements by default
Statements like CREATE USER u WITH PASSWORD 'passw0rd' can leak sensitive data into Log Insights, so they are now redacted by default.
Note that these statements are usually very fast, and are normally only logged in edge cases, like a lock wait problem relating to the statement.
Ensure correct log handling for all Flexible Server events (don't accidentally treat them as Single Server events)
Support log parsing for Azure Database for Cosmos DB Postgres
Crunchy Bridge:
Fix error handling for error responses from Crunchy Bridge API
Fix hang on exit with the `--discover-log-location` flag
Advisors
VACUUM Advisor: Hide negative bloat estimates
In some situations, bloat can be estimated as negative due to limitations of the bloat estimation heuristics
Hide estimates from these cases to avoid confusion
Index Advisor: Recommend removing unused expression indexes in Unused Index alert
Previously expression indexes were excluded because they produce column statistics that the query planner can benefit from.
But this was confusing for users who knew some indexes weren't being queried, and yet we didn't recommend deleting them.
You may want to additionally `CREATE STATISTICS` for the expression when dropping an expression index if you notice worse query plans.
EXPLAIN plans
Revise EXPLAIN details layout
We've reworked the visual layout of the EXPLAIN plan display to better highlight additional information (node details, EXPLAIN insights, etc)
Improve EXPLAIN plan fingerprints
The fingerprinting logic now utilizes a list of known important fields, instead of a list of fields to be ignored
Previously fingerprints might have been different only due to execution statistics
Going forward, different plan fingerprints should only happen when the plan shape is different (e.g. different node types, different indexes being used, join order, etc)
Other improvements and bugfixes
Allow filtering Server and Database drop-down menus, which can be useful when working with a large number of servers or databases.
Improve date range selection on small screens
Update duplicate snapshot warning logic in Server Settings page
Previously, this was wrongly providing a warning when the test snapshot is executed recently.
