Limit the size of Insights chart queries

What does this MR do and why?

The Insights query endpoints (POST .../insights/query, for groups and projects) run chart queries that the client supplies, with no limit on their size:

  • period_limit has no maximum. On charts with group_by, a large value pushes the created_after date far enough back that it filters nothing. It also drives a Ruby loop in CountPerPeriodReducer that runs once per period, which no statement timeout can stop.
  • Bar and pie charts without group_by have no date filter. They load every issue or MR in the group hierarchy.
  • projects.only accepts any number of entries.

Behind the insights_query_limits feature flag (gitlab_com_derisk, off by default, actor: the top-level namespace), this MR:

  1. Rejects period_limit above 3 years for its group_by (day ≤ 1095, week ≤ 156, month ≤ 36) with the existing InvalidPeriodLimitError, which renders as 422. Charts without group_by are not checked, because they ignore period_limit today.
  2. Applies a fixed 180-day created_after window to issuable charts without group_by.
  3. Rejects projects.only lists longer than 100 entries (422).

All charts in ee/fixtures/insights/default.yml and in the Insights docs are within these limits. DORA charts are unaffected: Dora::AggregateMetricsService already limits their range.

With the flag off, behaviour is unchanged. The flag is checked only when a value is over a limit, or when a chart has no group_by.

It also fixes bar and pie charts without group_by, which raised ActiveModel::MissingAttributeError whenever a row matched (already broken on master): the ungrouped path selects only id but kept every preload from the finder. It now keeps only labels, which the reducer reads. It also drops includes(:metrics) on that path: on project Insights it became an eager load, so merged MR charts joined merge_request_metrics and selected all its columns on an id-only query. Group charts, including the plans below, were not affected. The fix is behind the flag, together with the 180-day window: without the window, the labels preload would run for every matching issuable in the hierarchy. With the flag off, these charts raise as they do on master.

The three limits are documented in doc/user/project/insights/_index.md, with flag history and flag notes.

Database

Change 2 adds created_at >= ? to the issuable query for bar and pie charts without group_by (IssuableExecutor, select(:id).reorder(id: :desc)). It adds no new query. Plans are for gitlab-org (9970), anonymous user, open state.

Query Plan Execution (warm) Buffers Rows
Issues, before 188129 438.8 ms 1,108,887 103,928
Issues, after 188130 192.9 ms 268,202 25,223
Merge requests, before 188135 77.7 ms 81,204 5,215
Merge requests, after 188136 77.2 ms 76,710 4,870
Issues closed, 180 days (reviewer's run) n/a 531,678 54,364
Merge requests merged, 180 days (reviewer's run) n/a 553,678 53,022
Issues opened, group_by: day, period_limit: 1095 (grouped worst case) (reviewer's run) n/a 695,404 70,349
Labels preload, issues opened, 180 days 569392 (reviewer's run) n/a ~132,000 123,449 label_links
Labels preload, merge requests merged, 180 days 569390 (reviewer's run) n/a ~413,900 449,262 label_links

Plans for a signed-in member of gitlab-org, which is the path most Insights users take:

Query (member, 180 days) Plan Execution (warm) Buffers Rows
Issues opened 191726 198.0 ms 645,989 38,017
Issues closed 191728 283.4 ms 1,256,953 84,661
Merge requests opened 191731 138.6 ms 148,082 6,093
Merge requests merged 191735 n/a (cold on 3 runs) 750,126 69,201
Labels preload, merge requests merged 191736 n/a (cold) ~474,800 506,896 label_links
  • Issues: the filter moves the issues lookup from idx_open_issues_on_project_and_confidential_and_author_and_id (index-only scan, 148K rows) to idx_issues_on_project_id_and_created_at_and_id_and_state_id (its bigint copy on .com), with created_at in the index condition (25K rows). The per-row banned_users and project_features lookups shrink with it.
  • Merge requests: both plans use idx_mrs_on_target_id_and_created_at_and_state_id (target_project_id, state_id, created_at, id). created_at joins the index condition as a range after the two equality columns. The gain is small because most open MRs in gitlab-org are recent.
  • Labels preload: now that these charts render, the reducer's labels preload runs on the matched IDs, using index_label_links_on_target_id_and_target_type. The preload plans pass the IDs as = ANY(ARRAY(<id query>)) instead of the literal list Rails sends, so read their buffers as an upper bound. #638790 replaces the preload with a per-label count in SQL.
  • Members: for a member of the root group, IssuesFinder filters on namespace_traversal_ids[1] through idx_issues_root_namespace_created_at. That index has no state_id, so open and closed charts scan the same window and filter by state, and banned_users, projects and project_features are looked up once per issue. Merge requests add project_authorizations checks for the user. These plans read 2 to 3 times the buffers of the anonymous ones.
  • Ceilings: the member plans, closed and merged charts without group_by, and grouped charts at the maximum period_limit are the heaviest shapes these limits leave in place. The member plans are above the 100 ms guideline even with the window. The rate limit in !267890 (merged) bounds how often they can run, and #638790 removes the labels preload.

No new index is needed.

Raw SQL (after) Issues:
SELECT "issues"."id" 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 "projects"."visibility_level" = 20 AND ("project_features"."issues_access_level" IN (20, 30) OR "project_features"."issues_access_level" IS NULL) AND "issues"."created_at" >= '2026-05-08 07:37:08.308128' AND "issues"."state_id" = 1 AND "issues"."confidential" = FALSE ORDER BY "issues"."id" DESC

Merge requests:

SELECT "merge_requests"."id" FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_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 = (merge_requests.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 "projects"."visibility_level" = 20 AND ("project_features"."merge_requests_access_level" IN (20, 30) OR "project_features"."merge_requests_access_level" IS NULL) AND "merge_requests"."created_at" >= '2026-05-08 08:05:26.361842' AND "merge_requests"."state_id" = 1 ORDER BY "merge_requests"."id" DESC

Known limits and follow-ups

  • CountPerLabelReducer and CountPerPeriodReducer still load every matching row into Ruby. These limits bound the date range but not the row count. #638790 moves the per-label count into SQL.
  • Rate limit for the endpoint: !267890 (merged), merged.
  • With the 180-day window, closed and merged charts without group_by count issuables by creation date, matching how grouped charts already filter.

References

Screenshots or screen recordings

Backend-only change. With the flag on, an over-limit chart returns:

{ "message": "Invalid `:period_limit` option: `37`. Maximum for `:group_by` `month` is 36!" }

The Insights page shows this message in place of the chart.

How to set up and validate locally

  1. Make sure GDK has an Ultimate license, then enable the flag in the Rails console:

    Feature.enable(:insights_query_limits)
  2. Commit this as .gitlab/insights.yml in a project, for example gitlab-org/gitlab-test:

    limits:
      title: Limits test
      charts:
        - title: Over the cap
          type: bar
          query:
            data_source: issuables
            params: { issuable_type: issue, group_by: month, period_limit: 40 }
        - title: Ungrouped pie
          type: pie
          query:
            data_source: issuables
            params: { issuable_type: issue, collection_labels: [bug] }
  3. Make some issues older than 180 days:

    Project.find_by_full_path('gitlab-org/gitlab-test').issues.limit(10).update_all(created_at: 1.year.ago)
  4. Visit http://gdk.test:3000/gitlab-org/gitlab-test/insights/.

    • "Over the cap" shows the period_limit error.
    • "Ungrouped pie" counts only issues from the last 180 days.
  5. Disable the flag (Feature.disable(:insights_query_limits)) and reload. "Over the cap" renders as before, and "Ungrouped pie" shows an error, as it does on master.

  6. To check the projects.only limit, run this in the browser console on the Insights page. It should return 422 with the flag on:

    fetch('/gitlab-org/gitlab-test/insights/query', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'X-CSRF-Token': document.querySelector('meta[name=csrf-token]').content },
      body: JSON.stringify({ type: 'bar', query: { data_source: 'issuables', params: { issuable_type: 'issue', group_by: 'month', period_limit: 3 } }, projects: { only: [...Array(101).keys()] } }),
    }).then((r) => console.log(r.status));

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tanishq Jain

Merge request reports

Loading
Loading