SQL Query Annotation
April 5th, 2025

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:

Possibly Long SQL Statements Identified by AWS RDS Performance Insights
Possibly Long SQL Statements Identified by 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:

AWS RDS Performance Insights Shows Me the SQL Query Application Source Code Location
AWS RDS Performance Insights Shows Me the SQL Query Application Source Code Location

Extremely Useful!


gm!

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

Subscribe to Panos Matsinopoulos
Receive the latest updates directly to your inbox.
Nft graphic
Mint this entry as an NFT to add it to your collection.
Verification
This entry has been permanently stored onchain and signed by its creator.
More from Panos Matsinopoulos

Skeleton

Skeleton

Skeleton