Dive into the latest PostgreSQL 17 release and catch the return of 5mins of Postgres with a new weekly episode on planner quirks with incremental sort.
Welcome to the September 2024 edition of the pganalyze newsletter!
Last week marked the release of PostgreSQL 17 and its many performance improvements. In case you missed it, here is the webinar we did on release day to deep dive into the latest features & updates.
This week our team had a great time at PGConf NYC. Huge shout out to PgUS and all the volunteers that make this event possible. We gave a talk on query optimization, if you missed it, you can find a recording here. It was great to see all the excitement for Postgres in a diverse group of practitioners, contributors & other community members.
Finally, we're excited to share that 5mins of Postgres is back with a new episode where we discuss some planner quirks when using Incremental Sort. Stay tuned for more episodes publishing weekly, and, as always, you can find product updates at the end.
In our latest webinar, we explore the key improvements in PostgreSQL 17, focusing on the performance enhancements that can streamline database management. From faster data access to smarter query planning, this release brings several advancements worth noting. If you're curious about how these updates could impact real-world deployments, this webinar provides a clear breakdown of the most significant changes.
We've also seen lot of excitement around PostgreSQL 17, and many people trying out pgvector. As AI continues to gain momentum, it’s inspiring to see how PostgreSQL thrives and evolves to meet new challenges.
And, some exciting news from the last session of the conference: Next year’s event will be rebranded for a US-wide audience, as it has outgrown its regional roots and is now attracting a broader crowd. Don’t forget to mark your calendars for September 29 to October 1, 2025 in New York City!
We return to our Postgres planner quirks series to talk about Incremental Sort, and when it goes wrong. Incremental Sort can often speed up query plans when you have an existing sort order; however, there can be edge cases where the planner chooses a sub-optimal plan.
Use new WebSocket-based API for snapshot submissions
Long-lived WebSocket connections have lower overhead for individual snapshots that send statistics data to pganalyze, and avoid repeated HTTP connections
In case of errors when connecting the collector will fall back to regular HTTP-based snapshots and emit a warning (e.g. due to a misconfigured proxy, or when connecting to a pganalyze Enterprise Server install without WebSocket support)
Automated EXPLAIN (auto_explain) improvements
Unless filtered, keep query parameters included with auto_explain as part of query samples
Improve handling of newlines with auto_explain "text" format
Azure Database / Cosmos DB for PostgreSQL: Collect system info and metrics
To start using this, you need to supply a new config variable AZURE_SUBSCRIPTION_ID to the collector, as well as setting up managed identity (like is done for Log Insights support)
The managed identity now additionally needs access to the Monitoring Reader role on the Azure Database instance
Improve support for EDB Postgres Advanced Server
Postgres 17: Update pg_stat_progress_vacuum field names
Log Insights: Complete transition to new log parser (introduced in 0.58.0)
Drop supported log_line_prefix check in test
Drop legacy log line parsing mechanism - Fix --analyze-logfile flag
Index Advisor
Fix the count in the "Queries" column in insights list
In the Missing Indexes tab in the Index Advisor page, the column "Queries" was previously showing the number of missing indexes in that insight (table)
Now this column shows the number of unique affected queries due to these missing indexes, as intended
Show affected index count of each table in insights list
Support Postgres 15+ query syntax in Index Advisor
Bug fixes & other improvements
Schema Statistics: Show notice when the column stats are not available for the table due to outdated stats
When the statistics in pg_stats were out of sync with the current table structure, such as when columns have been added or removed since the last statistics collection, they were simply not shown previously. With this change, show notification so that ANALYZE can be run to solve the issue.
System Stats: Fix CPU core count calculation
We previously multiplied the CPU logical core count by the number of sockets to determine this number, but that's not the commonly expected way of calculating this. Also rename the field to "Logical Cores" to clarify the meaning.
Both CVEs were already addressed in earlier releases, this release is intended to unblock deployments of v2024.04.4 if omniauth-saml got flagged despite the underlying library (ruby-saml) already being updated
Remove setuptools and rexml system wide packages, to avoid dependency scanner false positives
