Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PGConf NYC 2023: Lightning Talk — Query Identifier

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)

Andrew Atkinson

October 04, 2023
Tweet

More Decks by Andrew Atkinson

Other Decks in Programming

Transcript

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

    View full-size 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 full-size slide

  3. pgrailsbook.com Slide 3 of 7

    View full-size 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 full-size 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 full-size slide

  6. pgrailsbook.com Slide 6 of 7

    View full-size slide

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

    View full-size slide