Context:

• Ruby on Rails

• Postgres

• ActiveRecord

Problem:

When I inspect the SQL queries stats in my Postgres database, I want to know where each one is coming from, in terms of application code. As precisely as possible.

The pg_stat_activity , for example, has the column query which shows the SQL statement:

- START_REPLICATION SLOT "..." 539/C0000000 TIMELINE 1
- COMMIT 
- SELECT "github_accounts".* FROM "github_accounts" WHERE "github_accounts"."name" = '...' ORDER BY "github_accounts"."account_created_at" ASC LIMIT 1 
- SELECT value FROM rds_heartbeat2
- COMMIT
- COMMIT 
- COMMIT 
- COMMIT

Or AWS RDS Performance Insights:

But I can’t tell which point in the code is generating these queries.

Knowing the exact line of code that generates these queries is very useful when I want to troubleshoot problematic queries such as slow ones.

Solution:

I enable the ActiveRecord query_log_tags. In my config/application.rb file I have the following configuration:

module TalentProtocol
  class Application < Rails::Application
    ...
    config.active_record.query_log_tags_enabled = true
    config.active_record.query_log_tags |= [:namespaced_controller, :source_location]
    config.active_record.verbose_query_logs = true
  end
end

Bingo!

Then pg_stat_activity includes SQL comments that have the precise file and line of code (from my Ruby on Rails application) that generates this particular SQL statement.

- COMMIT /*action:index,application:TalentProtocol,namespaced_controller:api/v2/passport_credentials,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- SELECT "github_accounts".* FROM "github_accounts" WHERE "github_accounts"."name" = '...' ORDER BY "github_accounts"."account_created_at" ASC LIMIT 1 /*application:TalentProtocol,job:BuilderGrants::CalculateUserScoreJob,source_location:app/services/builder_grants/calculation_methods/base_org_v1.
rb:202:in `calculate_github_score'*/
- COMMIT /*action:show,application:TalentProtocol,namespaced_controller:api/v2/passports,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- COMMIT /*action:index,application:TalentProtocol,namespaced_controller:api/v2/passport_credentials,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- COMMIT /*action:refresh_token,application:TalentProtocol,namespaced_controller:api/v2/auth,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/

And in AWS RDS Performance Insights, I can see these comments too:

Extremely Useful!

gm!

I am Panos M., humble, back-end software reader and writer at Talent Protocol.