David Bitner

pg_stat_statements is an extension that ships included with Postgres as part of the contrib set of extensions. It's incredibly handy for analyzing performance of your Postgres database. If you don't already have it enabled you can enable it with: CREATE EXTENSION pg_stat_statements Once enabled it records all queries that are run, excluding constant values within the queries along with a lot of stats about them including number of times they're run, total time of execution, info about which blocks are written, read, dirtied, and more. This makes it easy to get a sense of performance of queries that consume the most time in aggregate or have a high average execution time. For example this will give you the most time consuming queries in aggregate: SELECT d.datname, round(s.total_exec_time::numeric, 2) AS total_exec_time, s.calls, s.rows, round(s.total_exec_time::numeric / calls, 2) AS avg_time, round((100 * s.total_exec_time / sum(s.total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu, substring(s.query, 1, 50) AS short_query FROM pg_stat_statements s JOIN pg_database d ON (s.dbid = d.oid) ORDER BY percentage_cpu DESC LIMIT 5; Want to get which queries have the highest average time of execution? SELECT (total_time / 1000 / 60) as total, (total_time/calls) as avg, query FROM pg_stat_statements ORDER BY 2 DESC LIMIT 10; If you haven't already enabled pg_stat_statements do so today. If you do have it enabled when is the last time you checked in on the query performance of your database?

Postgres 17: Expect *serious* real world perf gains in apps making heavy use of eager loading. We benchmarked an average complexity endpoint and saw +30% throughput and a 20% drop in request time. We're expecting that across the board on final release. crunchydata.com/blog/real-worl…

Elizabeth Garrett Christensen (@sqlliz): Window Functions for Data Analysis with Postgres postgr.es/p/6Df

YouTube

This will be live streamed! Tune in and cast your bets for whether my live demo will work or not 🎲 youtube.com/live/TQcALWR3d…