Date: 2024-04-26
Time: 09:45–10:30
Room: Hub1
Level: Intermediate
Attendees of this talk will learn how to create highly efficient queries and indexes, combining a suite of powerful PostgreSQL capabilities. The presenter will engage the audience for a lively discussion of challenges and solutions. Prior to each demo, attendees will learn the necessary context for an improved understanding. During the hands-on demos, we’ll work with SQL, app code, indexes, parameters, and visibility tooling.
Since most app developers work with PostgreSQL from their ORM, we’ll start there. We’ll look at generated SQL queries from the Active Record ORM in Ruby on Rails, patterns with poor scalability, and queries without optimized indexes. With the non-optimized queries identified, we’ll set a goal to make the queries efficient, explaining the benefits of cost efficiency and predictability.
To achieve that goal, we'll iteratively improve the queries using a suite of open source tools in PostgreSQL 16. We’ll look at various index types, operator classes, query execution planning, pg_stat_io, pg_stat_statements, pg_buffercache, auto_explain, and more to collect the evidence needed to confidently optimize.
The presentation will be composed of pre-made ORM code, SQL files with queries, index DDL, and reproducible configuration, to apply on the fly while the presentation is delivered, and show the effect.