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/
Creators and Guests
Host
Claire Giordano
Claire Giordano is head of the Postgres open source community initiatives at Microsoft. Claire has served in leadership roles in engineering, product management, and product marketing at Sun Microsystems, Amazon/A9, and Citus Data. At Sun, Claire managed the engineering team that created Solaris Zones, and led the effort to open source Solaris.
Host
Pino de Candia
Pino de Candia is a software dev manager at Microsoft since 2020 and is currently working on the Citus open source project. Pino previously worked on the managed PostgreSQL database service in Azure Cosmos DB for PostgreSQL, which includes Citus on Azure support for distributed PostgreSQL. Pino has lived in New Orleans since 2017.
Producer
Aaron Wislang
Open Source Engineering + Developer Relations at Microsoft + Azure ☁️ | Go (golang), Cloud Native, Linux 🐧 🐍 🦀 ☕ 🍷📷 🎹 | Toronto 🇨🇦🌎 | 💨😷💉 | https://aaronw.dev/hello/
Producer
Ariana Padilla
Program Manager at Microsoft in the Azure Database for PostgreSQL team | Avid Traveler 🛫 & Foodie 🍽️🍹
