ContextQMD
Back to Gitlabhq

Database query comments with Marginalia

doc/development/database/database_query_comments.md

18.11.22.3 KB
Original Source

The Marginalia gem is used to add query comments containing application related context information to PostgreSQL queries generated by ActiveRecord.

It is very useful for tracing problematic queries back to the application source.

An engineer during an on-call incident has the full context of a query and its application source from the comments.

Metadata information in comments

Queries generated from Rails include the following metadata in comments:

  • application
  • correlation_id
  • endpoint_id
  • db_config_database
  • line

Queries generated from Sidekiq workers include the following metadata in comments:

  • application
  • jid
  • correlation_id
  • endpoint_id
  • db_config_database
  • line

endpoint_id is a single field that can represent any endpoint in the application:

  • For Rails controllers, it's the controller and action. For example, Projects::BlobController#show.
  • For Grape API endpoints, it's the route. For example, /api/:version/users/:id.
  • For Sidekiq workers, it's the worker class name. For example, UserStatusCleanup::BatchWorker.

db_config_database is a single field that represents the database field in the database configuration hash in database.yml.

line is not present in production logs due to the additional overhead required.

Examples of queries with comments:

  • Rails:

    sql
    /*application:web,controller:blob,action:show,correlation_id:01EZVMR923313VV44ZJDJ7PMEZ,endpoint_id:Projects::BlobController#show,db_config_database:gitlabhq_production*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 75 AND "routes"."source_type" = 'Namespace' LIMIT 1

  • Grape:

    sql
    /*application:web,correlation_id:01EZVN0DAYGJF5XHG9N4VX8FAH,endpoint_id:/api/:version/users/:id,db_config_database:gitlabhq_production*/ SELECT COUNT(*) FROM "users" INNER JOIN "user_follow_users" ON "users"."id" = "user_follow_users"."followee_id" WHERE "user_follow_users"."follower_id" = 1

  • Sidekiq:

    sql
    /*application:sidekiq,correlation_id:df643992563683313bc0a0288fb55e23,jid:15fbc506590c625d7664b074,endpoint_id:UserStatusCleanup::BatchWorker,db_config_database:gitlabhq_production,line:/app/workers/user_status_cleanup/batch_worker.rb:19:in `perform'*/ SELECT $1 AS one FROM "user_statuses" WHERE "user_statuses"."clear_status_at" <= $2 LIMIT $3