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_limithas no maximum. On charts withgroup_by, a large value pushes thecreated_afterdate far enough back that it filters nothing. It also drives a Ruby loop inCountPerPeriodReducerthat runs once per period, which no statement timeout can stop.- Bar and pie charts without
group_byhave no date filter. They load every issue or MR in the group hierarchy. projects.onlyaccepts any number of entries.
Behind the insights_query_limits feature flag (gitlab_com_derisk, off by default, actor: the top-level namespace), this MR:
- Rejects
period_limitabove 3 years for itsgroup_by(day≤ 1095,week≤ 156,month≤ 36) with the existingInvalidPeriodLimitError, which renders as 422. Charts withoutgroup_byare not checked, because they ignoreperiod_limittoday. - Applies a fixed 180-day
created_afterwindow to issuable charts withoutgroup_by. - Rejects
projects.onlylists 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) toidx_issues_on_project_id_and_created_at_and_id_and_state_id(its bigint copy on .com), withcreated_atin the index condition (25K rows). The per-rowbanned_usersandproject_featureslookups 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_atjoins the index condition as a range after the two equality columns. The gain is small because most open MRs ingitlab-orgare recent. - Labels preload: now that these charts render, the reducer's
labelspreload runs on the matched IDs, usingindex_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,
IssuesFinderfilters onnamespace_traversal_ids[1]throughidx_issues_root_namespace_created_at. That index has nostate_id, so open and closed charts scan the same window and filter by state, andbanned_users,projectsandproject_featuresare looked up once per issue. Merge requests addproject_authorizationschecks 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 maximumperiod_limitare 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" DESCMerge 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" DESCKnown limits and follow-ups
CountPerLabelReducerandCountPerPeriodReducerstill 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_bycount issuables by creation date, matching how grouped charts already filter.
References
- Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/636125
- Rollout issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/637997
- Follow-up: https://gitlab.com/gitlab-org/gitlab/-/work_items/638790
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
-
Make sure GDK has an Ultimate license, then enable the flag in the Rails console:
Feature.enable(:insights_query_limits) -
Commit this as
.gitlab/insights.ymlin a project, for examplegitlab-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] } -
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) -
Visit
http://gdk.test:3000/gitlab-org/gitlab-test/insights/.- "Over the cap" shows the
period_limiterror. - "Ungrouped pie" counts only issues from the last 180 days.
- "Over the cap" shows the
-
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 onmaster. -
To check the
projects.onlylimit, 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.