Skip to content

Product analytics: Count number of users per service

Arturo Herrero requested to merge 225678-users-per-service-type-telemetry into master

What does this MR do?

We need to count for each service type the number of active users. This count the total number of users that have access to an active integration via project authorization, introducing new counters following this pattern: "projects_#{service_name}_active_users".

Event Dictionary updated.

Migration script output

rails db:migrate

$ rails db:migrate
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: migrating ==
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:project_authorizations, [:project_id, :user_id], {:name=>"index_project_authorizations_on_project_id_and_user_id", :algorithm=>:concurrently})
   -> 0.0027s
-- transaction_open?()
   -> 0.0000s
-- indexes(:project_authorizations)
   -> 0.0016s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:project_authorizations, {:algorithm=>:concurrently, :name=>"index_project_authorizations_on_project_id"})
   -> 0.0045s
-- execute("RESET ALL")
   -> 0.0002s
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: migrated (0.0103s)
rails db:migrate:down VERSION=20201123111156

$ rails db:migrate:down VERSION=20201123111156
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: reverting ==
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:project_authorizations, :project_id, {:name=>"index_project_authorizations_on_project_id", :algorithm=>:concurrently})
   -> 0.0028s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:project_authorizations, :project_id, {:name=>"index_project_authorizations_on_project_id", :algorithm=>:concurrently})
   -> 0.0037s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- indexes(:project_authorizations)
   -> 0.0013s
-- remove_index(:project_authorizations, {:algorithm=>:concurrently, :name=>"index_project_authorizations_on_project_id_and_user_id"})
   -> 0.0024s
== 20201123111156 ReplaceIndexOnProjectAuthorizationsForUsageData: reverted (0.0114s)

SQL Queries

SELECT MIN("project_authorizations"."user_id") FROM "project_authorizations"

Query plan: https://explain.depesz.com/s/R1Pg


SELECT MAX("project_authorizations"."user_id") FROM "project_authorizations"

Query plan: https://explain.depesz.com/s/ZO6H


SELECT COUNT(DISTINCT "project_authorizations"."user_id")
FROM "project_authorizations"
INNER JOIN projects ON projects.id = project_authorizations.project_id
INNER JOIN services ON services.project_id = projects.id
WHERE (services.active = TRUE)
  AND (services.type = 'JiraService')
  AND (projects.pending_delete = FALSE)
  AND (projects.archived = FALSE)
  AND "project_authorizations"."user_id" >= 905000
  AND "project_authorizations"."user_id" <  910000

Query plan: https://explain.depesz.com/s/sf5g

Related to #225678 (closed)

Edited by Arturo Herrero

Merge request reports