Panos MatsinopoulosSetting Up TimescaleDB on AWS
TimescaleDB is a PostgreSQL extension for high-performance real-time analytics on time-series and event data. It is basically a custom Postgres. Important: This post uses Terraform to create and destroy resources on an AWS account. Make sure that you destroy the resources as soon and as long as you don’t need them any more. Otherwise, you will be charged more than you might want.How do you set up a TimescaleDB on AWS?Unfortunately, you can’t use, at the time of this writing (Jul 2025), the ma...
Panos MatsinopoulosQuerying a Daily Stats Table - Postgres
Hero Image by Panos Matsinopoulos on ZoraThe TableI have a table named daily_stats, which keeps track of some daily metrics. Here it is:Example rows from table \`daily_stats\`The structure of the table:-- CREATE TABLE "daily_stats" ---------------------------------- CREATE TABLE "public"."daily_stats" ( "id" BigInt DEFAULT nextval('daily_stats_id_seq'::regclass) NOT NULL, "metric" Character Varying NOT NULL, "date" Date NOT NULL, "count" BigInt DEFAULT 0 NOT NULL, "lock_version" BigInt DEFAUL...
Panos MatsinopoulosGas Optimization - Yul/Assembly
Cover Image by DESPOINA MATSINOPOULOU from PixabayIntroductionRecently, I followed the Advanced Solidity Bootcamp organized by Encode Club and delivered by Extropy. Among the things we learnt, it was about gas optimization. The two many areas we touched in gas optimization were:Storage optimizationConverting Solidity to Yul/Assembly.Then I decided to apply this knowledge to one Talent Protocol production smart contract. The TalentCommunitySale.sol published in this repository here. https://gi...
Humble Software Reader, Writer and DevOps Builder, with love for Classical Music.
Panos MatsinopoulosSetting Up TimescaleDB on AWS
TimescaleDB is a PostgreSQL extension for high-performance real-time analytics on time-series and event data. It is basically a custom Postgres. Important: This post uses Terraform to create and destroy resources on an AWS account. Make sure that you destroy the resources as soon and as long as you don’t need them any more. Otherwise, you will be charged more than you might want.How do you set up a TimescaleDB on AWS?Unfortunately, you can’t use, at the time of this writing (Jul 2025), the ma...
Panos MatsinopoulosQuerying a Daily Stats Table - Postgres
Hero Image by Panos Matsinopoulos on ZoraThe TableI have a table named daily_stats, which keeps track of some daily metrics. Here it is:Example rows from table \`daily_stats\`The structure of the table:-- CREATE TABLE "daily_stats" ---------------------------------- CREATE TABLE "public"."daily_stats" ( "id" BigInt DEFAULT nextval('daily_stats_id_seq'::regclass) NOT NULL, "metric" Character Varying NOT NULL, "date" Date NOT NULL, "count" BigInt DEFAULT 0 NOT NULL, "lock_version" BigInt DEFAUL...
Panos MatsinopoulosGas Optimization - Yul/Assembly
Cover Image by DESPOINA MATSINOPOULOU from PixabayIntroductionRecently, I followed the Advanced Solidity Bootcamp organized by Encode Club and delivered by Extropy. Among the things we learnt, it was about gas optimization. The two many areas we touched in gas optimization were:Storage optimizationConverting Solidity to Yul/Assembly.Then I decided to apply this knowledge to one Talent Protocol production smart contract. The TalentCommunitySale.sol published in this repository here. https://gi...
Share Dialog
Share Dialog
Humble Software Reader, Writer and DevOps Builder, with love for Classical Music.
Subscribe to Panos Matsinopoulos
Subscribe to Panos Matsinopoulos
Ruby on Rails
Postgres
ActiveRecord
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.
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.
Ruby on Rails
Postgres
ActiveRecord
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.
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.
<100 subscribers
<100 subscribers
No activity yet