PGConf NYC 2023: Lightning Talk — Query Identifier

Transcript

1. Lightning Talk: End
   to End Query
   Observability PG 16
   Andrew Atkinson
   #PGRailsBook
   pgrailsbook.com Slide 1 of 7
   

   View Slide

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
   

   View Slide

3. pgrailsbook.com Slide 3 of 7
   

   View Slide

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
   

   View Slide

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
   

   View Slide

6. pgrailsbook.com Slide 6 of 7
   

   View Slide

7. Thanks!
   pgrailsbook.com
   #PGRailsBook
   pgrailsbook.com
   Slide 7 of 7
   

   View Slide