Add partial index for MCP audit events on group_audit_events (!257148) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Adds a small partial index to group_audit_events that contains only MCP setting changes (event_name = 'mcp_server_enabled_updated').

The GA catch-up migration in !257102 checks each top-level group's most recent MCP audit event to see whether an Owner turned MCP off. The existing (group_id, created_at, id) index finds a group's events, but event_name isn't part of it. So for a group with many audit events and no MCP change, PostgreSQL reads every one of that group's events to rule one out. On GitLab.com that took 17.7 s for a single busy group, and the limit for a query in a background migration is 1 s.

With this index, the lookup only ever reads MCP events, and there are very few of them. The index stays small and costs almost nothing to maintain, because only MCP setting changes are added to it.

The background migration in !257102 shouldn't run until this index exists on GitLab.com, so it depends on the follow-up in #631175.

References

Migration that uses the index: !257102
Work item: https://gitlab.com/gitlab-org/gitlab/-/work_items/630175

Database review

Migration

The post-deployment migration uses prepare_partitioned_async_index, which schedules the index to be built on GitLab.com during the weekend instead of building it during deployment:

CREATE INDEX tmp_idx_group_audit_events_on_group_id_created_at_mcp_enabled
  ON ONLY group_audit_events USING btree (group_id, created_at, id)
  WHERE (event_name = 'mcp_server_enabled_updated'::text);

Why async: Building the index in a regular post-deployment migration took 34 minutes in db:gitlabcom-database-testing, over the 20-minute limit. The finished index is only about 2 MiB, but every partition has to be scanned to build it. The full results are in the testing comment on this MR.
Follow-up: #631175 adds the index with add_concurrent_partitioned_index once it exists on GitLab.com. !257102 now waits on that follow-up instead of this MR.
Rollback: unprepare_partitioned_async_index_by_name removes the scheduled entry.
Temporary: only the McpServerDefaultTrueForGroups batched migration reads MCP audit events by name, so the index has a tmp_ prefix and is removed in 19.7 by #631178.

Query it supports

SELECT e.details LIKE '%:to: false%'
FROM group_audit_events e
WHERE e.group_id = :group_id
  AND e.created_at >= '2026-06-18'
  AND e.event_name = 'mcp_server_enabled_updated'
ORDER BY e.created_at DESC, e.id DESC
LIMIT 1

Before (existing indexes), for a busy group: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58371/commands/163162

It took 17.7 s on a cold cache. Each partition uses *_group_id_created_at_id_idx and then filters on event_name row by row.

Plan

 Limit  (cost=2.99..2344.84 rows=1 width=17) (actual time=17664.290..17664.296 rows=0 loops=1)
   I/O Timings: read=17535.414 write=0.000
   ->  Append  (cost=2.99..63232.99 rows=27 width=17) (actual time=17664.288..17664.293 rows=0 loops=1)
         ... (partitions 202610 to 202703 are empty) ...
         ->  Index Scan Backward using group_audit_events_202608_group_id_created_at_id_idx on gitlab_partitions_dynamic.group_audit_events_202608 e_3  (actual time=6111.699..6111.699 rows=0 loops=1)
               Index Cond: ((e_3.group_id = 9970) AND (e_3.created_at >= '2026-06-18 00:00:00+00'::timestamp with time zone))
               Filter: (e_3.event_name = 'mcp_server_enabled_updated'::text)
               I/O Timings: read=6066.023 write=0.000
         ... (same shape for partitions 202606, 202607, 202609) ...

After (with this index), same group: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58876/commands/164013

It takes 4.8 ms and reads 23 buffers. Each partition uses the new partial index, with no event_name filter step.

Plan

 Limit  (cost=1.88..3.96 rows=1 width=17) (actual time=4.753..4.756 rows=0 loops=1)
   Buffers: shared hit=15 read=8
   I/O Timings: read=4.607 write=0.000
   ->  Append  (cost=1.88..58.07 rows=27 width=17) (actual time=4.751..4.754 rows=0 loops=1)
         ... (partitions 202610 to 202703 are empty) ...
         ->  Index Scan Backward using group_audit_events_202608_group_id_created_at_id_idx1 on gitlab_partitions_dynamic.group_audit_events_202608 e_3  (actual time=1.213..1.213 rows=0 loops=1)
               Index Cond: ((e_3.group_id = 9970) AND (e_3.created_at >= '2026-06-18 00:00:00+00'::timestamp with time zone))
               Buffers: shared read=2
               I/O Timings: read=1.194 write=0.000
         ... (same shape for partitions 202606, 202607, 202609) ...

How to set up and validate locally

Run bundle exec rails db:migrate.
In gdk psql, run SELECT count(*) FROM postgres_async_indexes WHERE definition LIKE '%mcp_server_enabled_updated%'; and confirm there is one row for each group_audit_events partition.
Run bundle exec rails db:migrate:down:main VERSION=20260922150000 and confirm the same query returns 0.

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.

Database review required.

🤖 Generated with Claude Code

Edited Sep 28, 2026 by Jessie Young

Add partial index for MCP audit events on group_audit_events