Welcome to the May 2025 edition of the pganalyze newsletter!
Our team had a great time at PGConf.dev this past month—connecting with the community, sharing ideas, and digging into what’s next for Postgres observability. In today's newsletter, we’re highlighting three key sessions that sparked a lot of interest.
First, a packed unconference session we co-hosted with Sami Imseih from the AWS Postgres Contributors team, focused on the future of pg_stat_statements, including efforts to move query text into shared memory for better performance and manageability.
In a talk earlier at the conference, we shared a behind-the-scenes look at a new extension we're building to capture per-plan statistics—similar to Aurora's aurora_stat_plans extension, but open-source and usable in any environment. We'll be sharing more news on this extension soon, as the Postgres 18 release gets closer.
In a lightning talk, I introduced the prototype of a Postgres 19 proposal to track unique shared buffer hits in EXPLAIN ANALYZE using HyperLogLog (HLL). It’s a small change that could make a big difference in how to determine how much data needs to be loaded in a cold cache situation for a given query.
As always, check out our product updates and list of upcoming events at the end of this email.
All the best,
Lukas
What’s Next for pg_stat_statements?
At PGConf.dev, we co-hosted an unconference session to explore Postgres 19 improvements for pg_stat_statements. The pg_stat_statements extension is developed as part of Postgres, and one of the primary data sources that pganalyze relies on to show query statistics over time.
One significant proposal is moving query text from a file on disk into shared memory, eliminating expensive garbage collection and reducing lock contention during query text file rewrites. Hybrid options (memory plus optional disk fallback) and on-the-fly compression were also discussed to balance performance with full retention of query text.
Amongst the developers present, a consensus emerged against truncating query text: Operations teams need the complete SQL for debugging and analytics. To support this, future releases may let you set a total shared-memory budget for text storage, and potentially use compression for query text stored in memory. With these changes we expect pganalyze integrations to benefit as well, especially in edge cases where query texts become very large today, and require a recurring pg_stat_staements_reset or application-side changes to reduce bloat in pg_stat_statements.
The session also tackled complexity of the number of metrics tracked (pg_stat_statements has 55+ fields today), including some low-usage stats (e.g. JIT), and feature requests like cancelled-query tracking and reloadable configs. pg_stat_statements is a key module in Postgres observability, and we are looking forward to collaborate on it's development with the rest of the Postgres development community.
Coming soon: A faster, open-source extension to track plan statistics over time
Building on the foundation of pg_stat_statements, pganalyze is developing a new extension to mirror Aurora’s plan-statistics capture feature we integrated with a few months ago. The aim of this extension is to understand when the plan for a given query changes, and how different plans for the same query perform over time.
Compared to auto_explain, which is good for tracking outliers, this kind of tracking aims to give a comprehensive view of which plans are in use, for both slow and fast query extensions.
Compared to other existing open-source extensions, our new approach hashes the plan tree to calculate a plan ID (similar to Postgres' queryid), and, in our benchmarks, results in low overhead, making it suitable for production use.
We discussed an early version of this at PGConf.Dev. We’re refining this prototype now and plan full integration with pganalyze so you can get aggregate per-plan metrics wherever your database runs. More on this in a future newsletter!
Bringing More Accuracy to Postgres Buffer Hit Metrics in Postgres 19
In a lightning talk at PGConf.dev I presented an idea for improving shared buffer cache hit observability in Postgres.
To illustrate what this is about, let's discuss an example. In a simple query that joins two tables (a and b) using a Nested Loop Join, you might see an EXPLAIN ANALYZE output like this when the cache is warm:
Nested Loop (...) Buffers: shared hit=13540 -> Index Scan using b_id_idx on b (...) Index Cond: ((id >= 500) AND (id <= 5000)) Buffers: shared hit=36 -> Index Only Scan using a_id_idx on a (...) Index Cond: (id = b.a_id) Buffers: shared hit=13504
And an output like this in a cold cache case (after a restart):
Nested Loop (...) Buffers: shared hit=13493 read=47 -> Index Scan using b_id_idx on b (...) Index Cond: ((id >= 500) AND (id <= 5000)) Buffers: shared hit=2 read=34 -> Index Only Scan using a_id_idx on a (...) Index Cond: (id = b.a_id) Buffers: shared hit=13491 read=13
Note how the warm cache example might indicate that there are 13504 blocks (buffer pages) of data to be loaded (~100MB), when in reality only 13 blocks (~0.1 MB of data) were actually read from disk. This is explained by the fact that the buffer hit counter counts each buffer access, even those caused by repeated retrieval of the same buffer page, which can inflate the hit counter and make it hard to interpret.
Our upcoming proposal for Postgres 19 is to invent a new "BUFFERS DISTINCT" option for EXPLAIN. This would add a counter to EXPLAIN ANALYZE output that estimates distinct buffer hits per plan node, using a HyperLogLog (HLL) data structure behind the scenes when requested.
As always, we will have to see if this upcoming patch actually gets merged into Postgres, but early conversations were positive. We'll keep you updated on where this goes!
Support schemas that include multi-line CHECK constraints
Bug Fixes & Other Improvements
Alerts & Check-Up: Fix a query stats loading issue on New Slow Query alert detail pages that could prevent associated stats from displaying.
Schema Statistics: Show index last used date on list views, making it easier to prioritize unused indexes by their size
System: Hide unavailable system metrics data for Crunchy Bridge instances
Connection tracing: Don't show incorrect information from newer backend
EXPLAIN: Fix node selection in the Node Tree view
pganalyze-collector 0.66.1
Add support for Google AlloyDB IAM connections
This allows monitoring of Google AlloyDB databases using the existing db_use_iam_auth / DB_USE_IAM_AUTH setting: If enabled, the collector fetches a short-lived token for logging into the database instance from the GCP API, instead of using a hardcoded password in the collector configuration file
