Add usage count column to CI catalog_resources and components tables
What does this MR do and why?
Our overall objective in the parent issue #440382 (closed) is to implement CI component usage instrumentation for both GitLab.com
and Self-Managed instances. (See #440382 (comment 1821995966) for details.) In !145881 (merged) we created a new table p_catalog_resource_component_usages
to record when a unique project includes a component in its pipeline.
In a later task, we will implement a daily worker that will aggregate data for the last 30 days and then record the count into the last_30_day_usage_count
column of tables catalog_resources
and catalog_resource_components
.
This MR adds the new last_30_day_usage_count
columns to both tables in preparation. It also adds last_30_day_usage_count_updated_at
so we can keep track of when the usage count was last evaluated.
By recording the usage count in their respective tables, it will allow us to more easily and efficiently query the usage data for each component (or catalog resource), and also sort them by popularity without having to JOIN with the usages table.
Database notes:
- Since the usage count is the number of unique projects, a data type of
integer
(rather than bigint) is sufficient for our purposes. - As of 2024-03-18, the row count of:
-
catalog_resources
is ~1720
-
catalog_resource_components
is ~6800
-
This MR partially resolves Child Task #443381 (closed) of Parent Issue #440382 (closed).
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Migration
Up
main: == [advisory_lock_connection] object_id: 120240, pg_backend_pid: 33084
main: == 20240318160548 AddUsageCountToCatalogResources: migrating ==================
main: -- add_column(:catalog_resources, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
main: -> 0.0023s
main: -- add_column(:catalog_resources, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
main: -> 0.0005s
main: == 20240318160548 AddUsageCountToCatalogResources: migrated (0.0059s) =========
main: == [advisory_lock_connection] object_id: 120240, pg_backend_pid: 33084
ci: == [advisory_lock_connection] object_id: 120540, pg_backend_pid: 33086
ci: == 20240318160548 AddUsageCountToCatalogResources: migrating ==================
ci: -- add_column(:catalog_resources, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
ci: -> 0.0039s
ci: -- add_column(:catalog_resources, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
ci: -> 0.0014s
ci: == 20240318160548 AddUsageCountToCatalogResources: migrated (0.0329s) =========
ci: == [advisory_lock_connection] object_id: 120540, pg_backend_pid: 33086
main: == [advisory_lock_connection] object_id: 120740, pg_backend_pid: 33089
main: == 20240318160612 AddUsageCountToCatalogResourceComponents: migrating =========
main: -- add_column(:catalog_resource_components, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
main: -> 0.0010s
main: -- add_column(:catalog_resource_components, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
main: -> 0.0005s
main: == 20240318160612 AddUsageCountToCatalogResourceComponents: migrated (0.0039s)
main: == [advisory_lock_connection] object_id: 120740, pg_backend_pid: 33089
ci: == [advisory_lock_connection] object_id: 120880, pg_backend_pid: 33091
ci: == 20240318160612 AddUsageCountToCatalogResourceComponents: migrating =========
ci: -- add_column(:catalog_resource_components, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
ci: -> 0.0018s
ci: -- add_column(:catalog_resource_components, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
ci: -> 0.0005s
ci: == 20240318160612 AddUsageCountToCatalogResourceComponents: migrated (0.0088s)
ci: == [advisory_lock_connection] object_id: 120880, pg_backend_pid: 33091
Down
main: == [advisory_lock_connection] object_id: 119760, pg_backend_pid: 35342
main: == 20240318160612 AddUsageCountToCatalogResourceComponents: reverting =========
main: -- remove_column(:catalog_resource_components, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
main: -> 0.0014s
main: -- remove_column(:catalog_resource_components, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
main: -> 0.0009s
main: == 20240318160612 AddUsageCountToCatalogResourceComponents: reverted (0.0063s)
main: == [advisory_lock_connection] object_id: 119760, pg_backend_pid: 35342
ci: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 35820
ci: == 20240318160612 AddUsageCountToCatalogResourceComponents: reverting =========
ci: -- remove_column(:catalog_resource_components, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
ci: -> 0.0020s
ci: -- remove_column(:catalog_resource_components, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
ci: -> 0.0012s
ci: == 20240318160612 AddUsageCountToCatalogResourceComponents: reverted (0.0137s)
ci: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 35820
main: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 36280
main: == 20240318160548 AddUsageCountToCatalogResources: reverting ==================
main: -- remove_column(:catalog_resources, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
main: -> 0.0014s
main: -- remove_column(:catalog_resources, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
main: -> 0.0003s
main: == 20240318160548 AddUsageCountToCatalogResources: reverted (0.0068s) =========
main: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 36280
ci: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 36688
ci: == 20240318160548 AddUsageCountToCatalogResources: reverting ==================
ci: -- remove_column(:catalog_resources, :last_30_day_usage_count_updated_at, :datetime_with_timezone, {:null=>false, :default=>"1970-01-01"})
ci: -> 0.0015s
ci: -- remove_column(:catalog_resources, :last_30_day_usage_count, :integer, {:null=>false, :default=>0})
ci: -> 0.0009s
ci: == 20240318160548 AddUsageCountToCatalogResources: reverted (0.0129s) =========
ci: == [advisory_lock_connection] object_id: 119720, pg_backend_pid: 36688
Related to #440382 (closed)