Video of a talk by Ryan Booz delivered at Citus Con: An Event for Postgres. Abstract: Do you know which Postgres queries are acting abnormally today vs. yesterday? Which Postgres queries are fast but running 100,000 times per hour? Are there certain times per day that performance lags unexpectedly?
The pg_stat_statements extension is our most valuable tool for understanding the current state of query workloads within your PostgreSQL cluster. Unfortunately, all of the tracked metrics are cumulative until they are reset (either manually or with a restart), making it difficult to use for point-in-time tuning and observability.
In this talk, I'll review the metrics that pg_stat_statements provides and then demonstrate how to save the data to a table periodically for better visibility into your queries' performance and resource usage over time, including sample Grafana dashboards. We'll conclude the talk by discussing the additional benefits of storing this data in a TimescaleDB hypertable, which provides native compression (stores more data longer) and automatic data retention policies.
Ryan is a Developer Advocate at TimescaleDB. Prior to Timescale, Ryan worked for more than 17 years as a developer, DBA, and product manager in multiple ISVs delivering SaaS products based on time-series data. Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server and the #SQLFamily and has a desire to bring some of that community spirit into the growing PostgreSQL world.
► Video bookmarks:
⏩ 00:00 Introduction
⏩ 01:21 pg_stat_statements primer
⏩ 10:47 How to track historical data
⏩ 14:42 Demo of pg_stat_statements
⏩ 22:57 pg_stat_monitor & pganalyze
✅ Learn more:
Watch more Citus Con talks: aka.ms/citusco...
📕 Everything you need to know about Citus Con: An Event for Postgres can be found at: aka.ms/cituscon
📌 Let’s connect:
Twitter - @CitusCon, / cituscon
#CitusCon #PostgreSQL #Database