Backend: Implement individual component sorting by usage count OR clean up related code/columns
Summary
In #452545 (closed) we implemented usage data aggregation for each catalog resource, for a rolling 30-day window. Then in #452620 (closed), we introduced the USAGE_COUNT_* sort options on the GraphQl ciCatalogResources endpoint. These tasks support the feature to allow users to sort catalog resources (aka "catalog projects") by usage in the UI (i.e. on the CI/CD Catalog index page).
We had originally planned in #440382 (closed) to also aggregate the usage data by individual components, but then decided this was not yet necessary.
In this issue, depending on our future use case, we may either proceed with: (1) supporting the ability to sort the individual components by usage, or (2) removing any code/table columns related to tracking individual component usage.
Proposal
Option 1: Proceed to support sorting by individual component usages
We need to:
- Add batch data aggregation for each individual component, following the same approach in #452545 (closed). It should use a separate worker than the one for aggregation by catalog resource.
- Add the option to sort components by usage in GraphQl, so we can query them like so:
query getSortedComponents {
ciCatalogResource(fullPath: "path/to/resource") {
id
versions(name: "1.0.0") {
nodes {
components(sort: USAGE_COUNT_ASC) {
nodes {
id
last30DayUsageCount
}
}
}
}
}
}Option 2: Remove code/columns related to individual component usage
- Remove the columns
last_30_day_usage_countandlast_30_day_usage_count_updated_atfromcatalog_resource_componentstable. (It was introduced in !147293 (merged).) - Consider removing the
component_idcolumn fromp_catalog_resource_component_usages. If we no longer need to track each individual component, then there would be no need to record such granular data. This would improve the storage and query efficiency of the database table. We would also need to update the serviceCi::Components::Usages::CreateServiceand where it's used.
- NOTE: We may still need to keep this column if we want to support features such an internal component analytics dashboard (#440382 (comment 1793005911)). See related effort in #443681 (closed).