My Journey into Postgres Monitoring with Lukas Fittl & Rob Treat

Do you monitor your Postgres error logs for gold? Lukas Fittl and Rob Treat join Claire Giordano and Pino de Candia on the Path To Citus Con* podcast for developers who love Postgres—to discuss their respective journeys into Postgres monitoring. Have you ever asked yourself: “Why is my query so slow?” Or had to figure out which query is slowing things down? Or why your database server is at 90% CPU? There are so many ways to monitor Postgres: pganalyze, pgMustard, pgBadger, pgDash, your cloud provider’s Query Performance Insights, pg_stat_statements, pg_stat_io, & more. If you’re running Postgres on a managed service, what kinds of things do you need to monitor & optimize for (vs. what will your cloud service provider do)? There’s also a segue on monitoring vs. observability: what’s the difference? 

*[Update: July 2024] Path To Citus Con has been renamed to Talking Postgres. All of the past podcast episodes from Path To Citus Con—now called Talking Postgres with Claire Giordano—can be found here: https://talkingpostgres.com

Links mentioned in this episode:

• OpenTelemetry: https://opentelemetry.io/
• pganalyze: https://pganalyze.com/
• pgDash: https://pgdash.io/
• pgMustard: https://www.pgmustard.com/
• pg_stat_statements docs: https://www.postgresql.org/docs/current/pgstatstatements.html
• pg_hint_plan: https://github.com/ossc-db/pg_hint_plan 
• pg_hint_plan hint list: https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_list.md
• Example for PostgreSQL with pg_hint_plan: https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-optimizer_hints
• 5mins of Postgres by pganalyze: https://www.youtube.com/playlist?list=PLhqxwIAgz78HZhWyu3UyKrCWNk7VWjVpj
• Monitoring page on PostgreSQL wiki: https://wiki.postgresql.org/wiki/Monitoring
• PgHero GitHub repo: https://github.com/ankane/pghero
• Insights on pgBadger: A PGSQL Phriday #010 Recap: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911
• Get PostgreSQL Logs Into Honeycomb: https://docs.honeycomb.io/getting-data-in/logs/postgresql/
• Blog post by Lukas Fittl about pg_stat_io by Lukas: https://pganalyze.com/blog/pg-stat-io
• Blog post by Andrew Atkinson about pg_stat_io: https://andyatkinson.com/blog/2023/11/01/PostgreSQL-IO-Visibility-wehack-pg_stat_io
• BPFtrace by iovisor GitHub repo: https://github.com/iovisor/bpftrace
• Trace PostgreSQL locks with pg_lock_tracer: https://jnidzwetzki.github.io/2023/01/11/trace-postgresql-locks-with-pg-lock-tracer.html
• sysdig by draios GitHub repo: https://github.com/draios/sysdig
• Using BPFtrace to trace PostgreSQL vacuum operations: https://www.timescale.com/blog/using-bpftrace-to-trace-postgresql-vacuum-operations/
• PostgreSQL Mailing Lists: https://www.postgresql.org/list/
• psql — PostgreSQL interactive terminal: https://www.postgresql.org/docs/current/app-psql.html
• Ongoing discussion thread about pg_stat_statements: https://commitfest.postgresql.org/46/2837/
• Reconnoiter project referenced by Rob: https://github.com/circonus-labs/reconnoiter/tree/master/sql
• Funny tweet about PostgreSQL pronunciation: https://twitter.com/as_w/status/1648373353214885892
• O11ycast EP63 with Lukas Fittl: https://www.heavybit.com/library/podcasts/o11ycast/ep-63-observability-in-the-database-with-lukas-fittl-of-pganalyze
• Oxide and Friends podcast: https://oxide-and-friends.transistor.fm/