gitlab-foss/doc/development/database/database_query_comments.md

---
stage: Data Access
group: Database Frameworks
info: Any user with at least the Maintainer role can merge updates to this content. For details, see https://docs.gitlab.com/development/development_processes/#development-guidelines-review.
title: Database query comments with Marginalia
---

The [Marginalia gem](https://github.com/basecamp/marginalia) 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
  ```