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/