Set up traceparent query tag within your application

In order for a tracing span created by the pganalyze collector to correctly attach to the existing tracing on the application side, the query needs to have a traceparent query tag, as defined in the W3 Trace Context specification.

Query tags are key-value pairs appended to SQL statements as query comments. The pganalyze collector currently supports the following three formats to read a traceparent tag:

• sqlcommenter format: key='value'
• sqlcommenter format without single quotes: key=value
• marginalia format: key:value

Add traceparent with Go

Prerequisite

Set up OpenTelemetry SDK with your application. Read the OpenTelemetry documentation for details.

database/sql

The easiest way to add a query tag with Go is to use a drop-in replacement for go's database/sql library, provided by sqlcommenter.

That library provides an option EnableTraceparent: you can enable it to append a traceparent query tag as a query comment. Please note that this is only available when you're using OpenTelemetry for your application's tracing too, as it is going to reference the context and the trace context should be filled by the application side already.

Add traceparent with Ruby

Prerequisite

Set up OpenTelemetry SDK with your application. Read the OpenTelemetry documentation for details.

Rails 7+

Rails 7 introduced Active Record Query Logs which automatically appends comments (query tags) to SQL queries. You can enable this via Rails configuration in config/application.rb or an initializer:

config.active_record.query_log_tags_enabled = true

By default, this doesn't have a traceparent query tag, so you'll need to add it manually. New comment tags can be defined by adding them in a Hash to the tags Array. You can append the traceparent tag like the following:

config.active_record.query_log_tags = [
  :application,
  :controller,
  :action,
  :job,
  {
    traceparent: -> () {
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      span_context = current_span.context
      trace_flag   = span_context.trace_flags.sampled? ? '01' : '00'
      format(
        '00-%<trace_id>s-%<span_id>s-%<trace_flags>.2d',
        trace_id: span_context.hex_trace_id,
        span_id: span_context.hex_span_id,
        trace_flags: trace_flag
      )
    }
  }
]

With above configuration, queries will have the comments like the following:

SELECT "articles".* FROM "articles" /*application='myblog',action='index',controller='articles',traceparent='00-953c157ad37a81d94fe83bb95cfc8add-3cf784b99bd43910-01'*/

Marginalia

If you are using Rails 6 and below, you can attach SQL comments using the marginalia gem.

You can define a transparent tag in the initializer (e.g. config/initializers/marginalia.rb):

module Marginalia
  module Comment
    def self.traceparent
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      span_context = current_span.context
      trace_flag   = span_context.trace_flags.sampled? ? '01' : '00'
      format(
        '00-%<trace_id>s-%<span_id>s-%<trace_flags>.2d',
        trace_id: span_context.hex_trace_id,
        span_id: span_context.hex_span_id,
        trace_flags: trace_flag
      )
    end
  end
end

Marginalia::Comment.components = [:application, :controller, :action, :job, :traceparent]

Add traceparent with Python

Prerequisite

Set up OpenTelemetry SDK with your application. Read the OpenTelemetry documentation for details.

In Python, you can use an automatic instrumentation using a Python agent. This approach offers less control over what to instrument, including the option to enable sqlcommenter at the moment. To enable sqlcommenter, you will need to use a manual instrumentation. This will also attach the appropriate traceparent as a query comment automatically.

Django

You can enable sqlcommenter in Django by passing is_sql_commentor_enabled=True:

DjangoInstrumentor().instrument(is_sql_commentor_enabled=True)

Flask

For Flask application, you can enable sqlcommenter by passing enable_commenter=True:

FlaskInstrumentor().instrument(enable_commenter=True, commenter_options={})

SQLAlchemy, Psycopg

In addition to framework-level settings, you can also enable sqlcommenter at the ORM level (SQLAlchemy) or the database driver level (Psycopg).

SQLAlchemyInstrumentor().instrument(enable_commenter=True, commenter_options={})

Psycopg2Instrumentor().instrument(enable_commenter=True, commenter_options={})

When performing manual instrumentation, these instrumentations won't be enabled automatically. To have better tracing spans around the queries, it is recommended to enable instrumentations related to ORMs and/or database drivers.

Add pganalyze tracestate (optional)

When the Postgres database has limited precision in its log_line_prefix timestamp, timing data of spans exported by pganalyze may not line up with other spans exported by the application. This issue will typically occur with Amazon RDS and Amazon Aurora, which currently do not allow changing the timestamp precision of the log_line_prefix (it's always set to%t:%r:%u@%d:[%p]:t).

To workaround this, you can use the pganalyze tracestate and pass the query start time with it. You can learn more about the tracestate in OpenTelemetry document TraceState section.

The pganalyze tracestate should use the pganalyze key, with the value being a semicolon separated list of key-value pairs such as:

pganalyze=key1:value1;key2:value2

Currently, t is the only supported key-value pair to specify the query start time.

Rails 7+

Assuming that you already have the traceparent set up with Rails 7+, you can append the tracestate tag like the following:

config.active_record.query_log_tags = [
  :application,
  :controller,
  :action,
  :job,
  {
    traceparent: -> () {
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      span_context = current_span.context
      trace_flag   = span_context.trace_flags.sampled? ? '01' : '00'
      format(
        '00-%<trace_id>s-%<span_id>s-%<trace_flags>.2d',
        trace_id: span_context.hex_trace_id,
        span_id: span_context.hex_span_id,
        trace_flags: trace_flag
      )
    },
    tracestate: -> () {
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      tracestate = current_span.context.tracestate.set_value('pganalyze', "t:#{Time.now.utc.to_f}")
      tracestate.to_s
    }
  }
]

Marginalia

Assuming that you already have the traceparent set up with Marginalia, you can append the tracestate tag like the following:

module Marginalia
  module Comment
    def self.traceparent
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      span_context = current_span.context
      trace_flag   = span_context.trace_flags.sampled? ? '01' : '00'
      format(
        '00-%<trace_id>s-%<span_id>s-%<trace_flags>.2d',
        trace_id: span_context.hex_trace_id,
        span_id: span_context.hex_span_id,
        trace_flags: trace_flag
      )
    end

    def self.tracestate
      current_span = ::OpenTelemetry::Trace.current_span
      return if current_span == ::OpenTelemetry::Trace::Span::INVALID

      tracestate = current_span.context.tracestate.set_value('pganalyze', "t:#{Time.now.utc.to_f}")
      tracestate.to_s
    end
  end
end

Marginalia::Comment.components = [:application, :controller, :action, :job, :traceparent, :tracestate]