Point-in-time query tuning and observability with pg_stat_statements | Citus Con 2022

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/cituscon-playlist
📕 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

[@lukasjerabek2504]

For anyone wondering how he did the query being shown in panel, you need to use Dynamic Text plugin for grafana.

[@lukasjerabek2504]

Hi, thank you for a great guide! May I ask you how you solved the bigint problem in grafana? I get last three digits zeroed in every queryid in the table. Typecasting to text did not solve it for me... I am using grafana 8.4.6., what version did you use for the presentation?

[@lukasjerabek2504]

Fixed it with overrides - add field override - fields with name - standard options - unit - string

[@harikrishnatalapaneni6891]

i have installed dynamic text plugin and unable to visualize query data but with tabular format i am able to

[@harikrishnatalapaneni6891]

can any one give the query for dynamic text plugin tsadmin@tsdb last you printing