Loading
Speed up the PostgreSQL group read of AI usage events
What does this MR do and why?
On PostgreSQL, aiUsageData.all and aiUsageData.codeSuggestionEvents for a group or project walk every event in the date range on every page. GitLab.com reads both fields from ClickHouse (Gitlab::ClickHouse.enabled_for_analytics? decides instance-wide), so this path serves instances without ClickHouse for analytics. On a Database Lab clone of GitLab.com, the first page of all for gitlab-org, the worst case measured, takes 2.5 minutes over one day. Over seven days, the default window, it ran for over 20 minutes without finishing.
Ai::UsageEvent.for_namespace_hierarchy scoped the hierarchy with the IN-operator optimization, which keeps its order inside a recursive CTE. The GraphQL keyset connection cannot read that order (#378160), so it adds ORDER BY id DESC LIMIT 101 outside the CTE, and PostgreSQL must finish the CTE, one step per event, before it can limit. The optimization also needs an index on (namespace_id, timestamp, id), which ai_usage_events does not have, so each step re-sorts one namespace's events.
This MR filters with a plain namespace_id IN (...), so the relation keeps the finder's timestamp DESC, id DESC order and the keyset connection puts the cursor in the WHERE clause and limits the one query. ClickHouse is unchanged: its connection reads the order from the query.
The first page now takes 119.8 ms over one day. Over seven days it takes 62.9 s from a cold cache and 393 ms from a warm one, reading the same 281,823 buffers each time. Over one month, the longest window the resolver allows, it takes 4.013 min cold and 5.722 s warm, reading 700,629 buffers each time. Database Lab's caches are smaller than production's, so its timings differ from production's while its buffer counts match. Both windows stay over the 100 ms guideline on either cache, and from a cold cache both exceed the 30-second GraphQL request timeout and any statement timeout in the docs' 15 to 60 second range. Those two cold-cache reads are a known limitation this MR does not fix, followed up in #632863.
Where the order is lost
sequenceDiagram
participant R as UsageEventsResolver
participant F as PostgresqlUsageEventsFinder
participant C as Keyset::Connection
participant P as PostgreSQL
R->>F: execute
alt Before
F-->>C: SELECT * FROM (WITH RECURSIVE ...)
Note over C: no keyset order found<br/>falls back to ORDER BY id DESC
C->>P: ORDER BY id DESC LIMIT 101 outside the CTE
Note over P: finishes the CTE for every event<br/>then sorts by id
else After
F-->>C: WHERE namespace_id IN (...) ORDER BY timestamp DESC, id DESC
C->>P: cursor as a row comparison, LIMIT 101
Note over P: reads the range once<br/>keeps the top 101
endOrder and cursor changes
Group and project reads now return events newest first, ties broken by id: the order the finder declares and the instance read already returns. They used to come back in id DESC order, which differs wherever ids do not follow timestamps.
Cursors now carry timestamp and id. A cursor from the other version fails with "Internal server error: Incorrect cursor values were given", so a client paging across the upgrade or a mixed-version deploy has to start again from the first page. There is no schema, writer or frontend change.
Paging through 3,000 group events whose ids follow their timestamps (750 to 1,500 for the narrower reads), both versions return the same events and the same hasNextPage and hasPreviousPage on every page, with no duplicates or gaps, for each of these reads:
- a group, forward and backward
- a project
codeSuggestionEvents- one event type
Where ids do not follow timestamps, both return the same events, each in its own order.
Events reach PostgreSQL through a write buffer flushed every five minutes, so an event can be stored minutes after its timestamp, while a client is paging. Paging forward, such an event now appears on a later page when it is older than every event already returned; id DESC never showed it, and neither version shows it otherwise. Paging backward, it appears when it is newer than every event already returned and never otherwise; id DESC showed every such event, whatever its timestamp, at the end of the walk. Events stored before the first page still appear exactly once on both.
Keeping id DESC would avoid these changes and costs the same on Database Lab, for gitlab-org and for a group with 23 events in the week: the same plan, within three buffers. The order change is for consistency: every read now returns events in the order the finder declares.
The index route, not in this MR
With a test (namespace_id, timestamp, id) index and without the connection's outer sort, the IN-operator query's seven-day first page took 149.1 ms over 75,156 buffers from a warm cache. That is the route's only measurement. !202221 (merged) replaced the same index with (namespace_id, event, timestamp, id) in August 2025. On GitLab.com this path would not scan a (namespace_id, timestamp, id) index.
References
- Related to #466072
- #552360 (comment 2641738681), where the
gitlab-orggroup read timed out on GitLab.com in July 2025 - #378160, making the IN-operator optimization work with GraphQL
- #632863, the follow-up for the cold-cache reads and the index route
Database review
The GraphQL request executes this SQL for gitlab-org (9970) over one day, page size 100.
Before: SQL and plan
SELECT * FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT "namespaces"."id" FROM UNNEST(COALESCE((SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_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 (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces(id)),
"recursive_keyset_cte" AS ((SELECT NULL::ai_usage_events AS records, array_cte_id_array, ai_usage_events_timestamp_array, ai_usage_events_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array, ARRAY_AGG("ai_usage_events"."timestamp") AS ai_usage_events_timestamp_array, ARRAY_AGG("ai_usage_events"."id") AS ai_usage_events_id_array FROM (SELECT "array_cte"."id" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "ai_usage_events"."timestamp" AS timestamp, "ai_usage_events"."id" AS id FROM "ai_usage_events" WHERE "ai_usage_events"."timestamp" BETWEEN '2026-10-05' AND '2026-10-06' AND "ai_usage_events"."namespace_id" = "array_cte"."id" ORDER BY "ai_usage_events"."timestamp" DESC, "ai_usage_events"."id" DESC LIMIT 1) ai_usage_events ON TRUE WHERE "ai_usage_events"."timestamp" IS NOT NULL AND "ai_usage_events"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL (SELECT (SELECT ai_usage_events FROM "ai_usage_events" WHERE "ai_usage_events"."id" = recursive_keyset_cte.ai_usage_events_id_array[position] LIMIT 1), array_cte_id_array, recursive_keyset_cte.ai_usage_events_timestamp_array[:position_query.position-1]||next_cursor_values.timestamp||recursive_keyset_cte.ai_usage_events_timestamp_array[position_query.position+1:], recursive_keyset_cte.ai_usage_events_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.ai_usage_events_id_array[position_query.position+1:], recursive_keyset_cte.count + 1
FROM recursive_keyset_cte,
LATERAL (SELECT timestamp, id, position FROM UNNEST(ai_usage_events_timestamp_array, ai_usage_events_id_array) WITH ORDINALITY AS u(timestamp, id, position) WHERE timestamp IS NOT NULL AND id IS NOT NULL ORDER BY 1 DESC, 2 DESC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."timestamp", "record"."id" FROM (VALUES (NULL, NULL)) AS nulls LEFT JOIN (SELECT "ai_usage_events"."timestamp" AS timestamp, "ai_usage_events"."id" AS id FROM "ai_usage_events" WHERE "ai_usage_events"."timestamp" BETWEEN '2026-10-05' AND '2026-10-06' AND "ai_usage_events"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] AND (("ai_usage_events"."timestamp", "ai_usage_events"."id") < (recursive_keyset_cte.ai_usage_events_timestamp_array[position], recursive_keyset_cte.ai_usage_events_id_array[position])) ORDER BY "ai_usage_events"."timestamp" DESC, "ai_usage_events"."id" DESC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values))
SELECT (records).* FROM "recursive_keyset_cte" AS "ai_usage_events" WHERE (count <> 0)) ai_usage_events
ORDER BY "ai_usage_events"."id" DESC LIMIT 101Plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/61327/commands/166395
Over seven days (BETWEEN '2026-09-29' AND '2026-10-06') it ran for over 20 minutes without finishing, and over one month (BETWEEN '2026-09-07' AND '2026-10-06') it was stopped twice after 10 minutes, so neither has a plan.
After: SQL, variants and plans
SELECT "ai_usage_events".*
FROM "ai_usage_events"
WHERE "ai_usage_events"."timestamp" BETWEEN '2026-10-05' AND '2026-10-06'
AND "ai_usage_events"."namespace_id" IN (
SELECT "namespaces"."id"
FROM UNNEST(COALESCE(
(SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_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 (traversal_ids @> ('{9970}'))) namespaces) consistent_query)
)) AS namespaces(id))
ORDER BY "ai_usage_events"."timestamp" DESC, "ai_usage_events"."id" DESC
LIMIT 101Plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/61327/commands/166396
Every parameter at once:
- one month
- two events
- one user,
andunn - the next-page cursor above
SELECT "ai_usage_events".*
FROM "ai_usage_events"
WHERE "ai_usage_events"."timestamp" BETWEEN '2026-09-07' AND '2026-10-06'
AND "ai_usage_events"."event" IN (2, 3)
AND "ai_usage_events"."user_id" = 28633741
AND "ai_usage_events"."namespace_id" IN (
SELECT "namespaces"."id"
FROM UNNEST(COALESCE(
(SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_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 (traversal_ids @> ('{9970}'))) namespaces) consistent_query)
)) AS namespaces(id))
AND (("ai_usage_events"."timestamp", "ai_usage_events"."id") < ('2026-10-05 12:00:00', 9223372036854775807))
ORDER BY "ai_usage_events"."timestamp" DESC, "ai_usage_events"."id" DESC
LIMIT 101Plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/61727/commands/166847
This plan returns no rows because andunn has no shown or accepted events before the cursor that month.
Execution time on Database Lab after this MR, first page unless noted.
| Request | After | Shared buffers |
|---|---|---|
| One day | 119.8 ms | 68,655 |
| Seven days | 62.9 s cold, 393 ms warm | 281,823 |
| One month | 4.013 min cold, 5.722 s warm | 700,629 |
| Next page | 3.05 s, 2.97 s of it I/O | 44,069 |
last: 100 |
83.9 ms | 68,655 |
| One event type | 51.3 ms | 32,394 |
codeSuggestionEvents |
48.8 ms | 33,887 |
| Project | 27.4 ms | 16,175 |
| Every parameter | 7.94 s, 7.79 s of it I/O | 86,216 |
How to set up and validate locally
- Run
bundle exec rspec ee/spec/requests/api/graphql/analytics/ai_analytics/ai_usage_events_spec.rb. Withmaster's model, the six newsorting and paginationexamples fail.
An AI coding assistant wrote this change. We ran the specs and the paging comparison above in a simulated environment, and measured the timings on Database Lab.
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 Andrew Dunn