Count Issue Analytics issues by month in SQL

What does this MR do and why?

The Issue Analytics JSON endpoints load every issue's created_at into Ruby and count each month once for every day in the range. For one project request on GitLab.com that was 1.06 s of CPU in a 1.29 s request, plus the memory for every timestamp. This MR counts by month in one grouped SQL query instead, behind the issues_analytics_sql_aggregation feature flag (type gitlab_com_derisk, off by default) so the counts can be compared on GitLab.com before cutover.

Part of https://gitlab.com/gitlab-org/gitlab/-/work_items/636126 (Project Bulkhead, https://gitlab.com/gitlab-org/gitlab/-/work_items/635036). This is the "aggregate in SQL" MR of the three in the issue's plan.

This does not fix the timeouts on the largest groups. The database scans the same rows with or without it. See the evidence below.

Changes

  • IssuablesAnalytics#data has a new path: one GROUP BY month count, then a hash with a key for every month in the range, zero where there are no issues. With the flag off, the previous code runs unchanged.
  • issues.created_at is a timestamp without time zone that holds UTC. The month is formatted in Time.zone, the same zone the old strftime used, with created_at AT TIME ZONE 'UTC' AT TIME ZONE <zone>.
  • Relations sorted by label priority carry their own SELECT and GROUP BY. The new path removes both with unscope(:select, :group), because they are only there for the ordering that without_order already drops.
  • Both controllers pass the group or project as the flag actor.
Evidence

Production, one gitlab-org/gitlab project request (correlation ID a58309c15c1a8fb3-KNU): 1.29 s total, 0.29 s database, 1.06 s CPU. Most of the time is Ruby, which is what this change removes. I expect such requests to drop to roughly the database time. That is an estimate until the flag is enabled.

Database Lab, gitlab-org group (9970), 12 months, one user, same query otherwise:

Plain created_at pluck Grouped by month
Rows from the join 252,856 252,856
Rows returned 252,856 12
Buffer hits plus reads 2,581,841 2,578,751
Shared reads 271,522 233,789
Time (cold cache) 3.88 min 3.30 min

The buffer totals match to within 0.1%. The lower reads and time in the second run come from cache state. The cost is a per-project index lookup on issues that reads about one heap page per issue, so it grows with the number of issues in the range. At 6 months the same plan took 1.40M buffer accesses, and at 3 months 0.97M.

Query plans: TODO add the Database Lab links and the full plans for both queries.

Queries

Before (the part that differs is the first line; the WHERE clause is the one the controller builds, shown here for group 9970 and one user):

SELECT "issues"."created_at" FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + 0)))) AND "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM UNNEST(
  COALESCE(
    (SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids FROM "namespace_descendants" WHERE "namespace_descendants"."outdated_at" IS NULL AND "namespace_descendants"."namespace_id" = 9970) cached_query),
    (SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))
) AS namespaces(id)
) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 43196403 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10, 20)) AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20, 30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 43196403 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 10)))) AND ("issues"."confidential" = FALSE OR "issues"."confidential" = TRUE AND ("issues"."author_id" = 43196403 OR EXISTS (SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."user_id" = 43196403 AND (issue_id = issues.id)) OR (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 43196403 AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 15))))) AND (issues.created_at >= '2025-12-01 00:00:00')

After: the same statement with the first line replaced by the following and GROUP BY 1 appended. In the Database Lab run the month expression had a single AT TIME ZONE 'UTC', which gives the same result and plan when Time.zone is UTC. The application adds the second AT TIME ZONE <zone>.

SELECT to_char("issues"."created_at" AT TIME ZONE 'UTC' AT TIME ZONE 'Etc/UTC', 'YYYY-MM') AS month, COUNT(*) FROM "issues" ...
... GROUP BY 1

Plan:

  1. New plan after aggregation : https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84816/commands/191881
  2. Old plan for 1 year range : https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84816/commands/191821
  3. Plan for 6 months range : https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84816/commands/191828
  4. Plan for 3 months range : https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/84816/commands/191831

Rollout

  1. Merge with the flag off. Behaviour is unchanged.
  2. Enable the flag for a few groups on GitLab.com and compare the JSON for each group month by month with the flag on and off.
  3. Enable globally, then remove the flag and the old code path.

How to test

  1. In GDK: Feature.enable(:issues_analytics_sql_aggregation).
  2. Open /groups/<group>/-/issues_analytics.json and note the response.
  3. Feature.disable(:issues_analytics_sql_aggregation) and open it again. The two responses should be identical.

Not covered here

  • The biggest groups, where the database scan itself reaches the 15 s statement timeout. That needs a shorter range, precomputed or cached counts, or a covering index, and is being discussed on the issue.
  • No changelog entry, because the change is behind a default-off flag.

Checklist

  • Specs: both flag states, same output with and without the flag, month boundaries in a non-UTC time zone
  • Add the Database Lab links and plans
  • Point introduced_by_url in the flag definition at this MR
  • Database review
  • Remove [skip ci] from the title and start a pipeline when ready
Edited by Fardeen Khan

Merge request reports

Loading
Loading