PGConf NYC 2023: Lightning Talk — Query Identifier

Slides supporting Live Demos of query observability
- Query Identifier (queryid) propagation to more places
- Logging in postgresql.log
- pg_stat_statements
- automatic query plans (auto_explain)

1. Lightning Talk: End to End Query Observability PG 16 Andrew Atkinson #PGRailsBook pgrailsbook.com Slide 1 of 7
2. Context and Why PostgreSQL 14 added compute_query_id Query Identifier / queryid postgresql.log pg_stat_statements auto_explain Rideshare Rails app pgrailsbook.com Slide 2 of 7
3. pgrailsbook.com Slide 3 of 7
4. # Compute the Query Identifier compute_query_id = on # Logging duration log_duration = on # Logging Query Identifier (%Q) log_line_prefix = 'pid=%p query_id=%Q: ' # slow query logging, all queries for demo (don't do this in prod) log_min_duration_statement = 0 # auto_explain (ms), > 10ms for demo auto_explain.log_min_duration = 10 # Equivalent to VERBOSE when using EXPLAIN # https://www.postgresql.org/docs/current/auto-explain.html auto_explain.log_verbose = on pgrailsbook.com Slide 4 of 7
5. Live Demos Enabling postgresql.conf logging parameters Commit - Michael C. / postgres.fm Query activity in postgresql.log , pg_stat_statements , and manual and automatic query plans Generate some interesting queries in Rideshare Show queryid in exec plans w/ auto_explain in PG 16 pgrailsbook.com Slide 5 of 7
6. pgrailsbook.com Slide 6 of 7
7. Thanks! pgrailsbook.com #PGRailsBook pgrailsbook.com Slide 7 of 7