Webinar Recording: Hands on Postgres 17: What's New & How It Impacts Performance

Find the presentation slide deck and all other shared material below! On September 26, 2024, we hosted our webinar on the release of Postgres 17, it's updates & how they're set to impact performance. You can find the presentation slides and references here: https://pganalyze.com/webinars/thank-... Additional Q&A Are you going to do a 5mins of Postgres on the B-Tree IN/ANY improvements? Yes! We did a 5mins of Postgres episode on these Postgres 17 improvements back in April:    • Waiting for Postgres 17: Faster B-Tree Ind...   For the Planner improvements with NOT NULL in PG 17, does it also exclude NaN values? In my understanding, NaN values (either when querying for them, or when they are present in the data) are not affected by the planner changes to avoid odd plans when IS NOT NULL conditions are present. Does the Postgres community have plans to create a Postgres kernel driver for raw device usage or some think like that for increase a storage performance like in Oracle ASM layer? To my knowledge there are no such projects within the core Postgres community. The efforts around asynchronous I/O will enable the use of direct I/O in a future release, which I'd imagine would be a prerequisite to directly interfacing with storage, further reducing the role of the OS. That said, I'd expect this to be a few years in the future at the very least, and someone would need to start a new focused effort to make this work. When do you think Postgres will implement server-side aggregation for wait events? (e.g. "pg_stat_wait..." n Server level, and for PID/Connection) Aggregating wait event data is challenging in my understanding because the code paths where the current wait events are set are often times in critical hot paths where any additional instruction (e.g. incrementing a counter) would be considered too expensive. As such, I'd imagine the only universal solution that handles all wait events would have to be sampling based, such as done by the pg_wait_sampling extension. I'm not aware of any current efforts to bring this in-core, but could see that happening in the future. I have a procedure in PostgreSQL which has refcursor to output data from the table. When I run that procedure to get a higher load like 1M it degrades performance while retrieving data from refcursor. What would be the way to optimise it? Broadly speaking, when optimizing functions it can be helpful to utilize the auto_explain extension with the auto_explain.log_nested_statements set to "on" (default is off). This will log both the initial Function Call, as well as the individual query activity inside the function. Cursors introduce additional complexity here, but this would allow you to understand if e.g. Postgres switches the query plan here when you retrieve 1M records.