Fall back to PostgreSQL for AI governance sessions without ClickHouse
What does this MR do and why?
The aiGovernanceSessions GraphQL field (on Group and Project) only read from ClickHouse. Instances without ClickHouse, which includes many self-managed ones, got an empty list.
A PostgreSQL finder, Ai::Governance::SessionsFinder, was merged in !257556 (merged), but nothing called it. This MR uses it when ClickHouse is not enabled for analytics, along with session auditEvents and auditEventsCount, and fixes the gaps that showed up. The ClickHouse path and the ai_governance_sessions_api flag check are unchanged.
References
Closes https://gitlab.com/gitlab-org/gitlab/-/work_items/632264
- Tracking issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/630862
- Epic: https://gitlab.com/groups/gitlab-org/-/work_items/21540
- Split from: https://gitlab.com/gitlab-org/gitlab/-/work_items/630095
- PostgreSQL finder: !257556 (merged)
Screenshots or screen recordings
Not applicable, backend only.
How to set up and validate locally
-
Disable ClickHouse for analytics on your GDK (or use an instance without it). In a Rails console, enable the flag with
Feature.enable(:ai_governance_sessions_api). -
Create a session with
Ai::Governance::Session.sync_from_workflow!(Ai::DuoWorkflows::Workflow.last), or use an existing one. -
Run this query in
/-/graphql-exploreras a user withread_agent_artifactson the group:query { group(fullPath: "gitlab-org") { aiGovernanceSessions(first: 10) { count nodes { id source sessionStartedAt auditEvents { nodes { id eventName } } } } } } -
Confirm sessions are returned from PostgreSQL with the right
source.
Database review
Migration: a post-deploy migration adds the partial partitioned index idx_ai_audit_events_on_ai_governance_session_id_created_at_id on ai_audit_events (ai_governance_session_id, created_at DESC, id DESC) WHERE ai_governance_session_id IS NOT NULL. db:gitlabcom-database-testing passes in 23 s (+112 KiB): !259922 (comment 3999446659)
Local migration up and down
Migration up:
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: migrating =======
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- index_name_exists?(:ai_audit_events, "idx_ai_audit_events_on_ai_governance_session_id_created_at_id")
main: -> 0.0434s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- index_exists?("gitlab_partitions_dynamic.ai_audit_events_000000", [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "index_fec68dd42d", where: "ai_governance_session_id IS NOT NULL", algorithm: :concurrently})
main: -> 0.0027s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0004s
main: -- add_index("gitlab_partitions_dynamic.ai_audit_events_000000", [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "index_fec68dd42d", where: "ai_governance_session_id IS NOT NULL", algorithm: :concurrently})
main: -> 0.0094s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
(same index_exists? and concurrent add_index for partitions 202605 to 202704)
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- add_index(:ai_audit_events, [:ai_governance_session_id, :created_at, :id], {order: {created_at: :desc, id: :desc}, name: "idx_ai_audit_events_on_ai_governance_session_id_created_at_id", where: "ai_governance_session_id IS NOT NULL"})
main: -> 0.0056s
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: migrated (0.1968s)Migration down:
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: reverting =======
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- index_name_exists?(:ai_audit_events, "idx_ai_audit_events_on_ai_governance_session_id_created_at_id")
main: -> 0.0385s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- remove_index(:ai_audit_events, {name: "idx_ai_audit_events_on_ai_governance_session_id_created_at_id"})
main: -> 0.0021s
main: == 20261006072427 AddAiGovernanceSessionIndexToAiAuditEvents: reverted (0.0761s)ai_governance_sessions queries
Plans on gitlab-org (about 10,800 namespaces in the hierarchy), gitlab-production-main, with a warm cache.
| Query | Time | Buffers | Plan |
|---|---|---|---|
| Sessions list, page 1 | 101 ms | 53,576 hits | 172322 |
Lookup by id |
17.9 ms | 18 hits, 7 reads | 172318 |
Total count |
171 ms | 75,826 hits | 172324 |
count(limit: 1000) |
14 ms | 815 hits | 172326 |
agentClass: EXTERNAL list, page 1 |
46 ms | 20,666 hits | 172328 |
- Lookup by
idno longer uses the in-operator query, which read about 42,000 buffers for one row (before). - Total
countuses a plainnamespace_id IN (<hierarchy>)count. The in-operator count did not finish in 29 minutes. The UI does not requestcount. agentClass: EXTERNALnow also requiresexternal_xid IS NOT NULL, so it uses the existing partial index onexternal_xidinstead of reading every GitLab Duo session (727 ms warm before).- Cold cache: on a fresh clone the list took 10.4 s, the
EXTERNALlist 10.8 s and the total count 7.3 s. Most of that is the namespace lookup, about one page read per namespace.Ai::DuoWorkflows::SessionArtifacts::PostgresqlFinderonmasterruns the same lookup. This path only runs without ClickHouse, which means self-managed instances, usually with much smaller hierarchies.
ai_audit_events queries
ai_audit_events is empty on GitLab.com, so these plans use 3 million synthetic events on a clone, skewed so the busiest session has about 49,000 events.
| Query | Time | Buffers | Plan |
|---|---|---|---|
| Audit events list, busiest session | 0.6 ms | 39 | 167728 |
auditEventsCount, page of the 20 busiest sessions |
57 ms | 40,082 | 167730 |
The list stops at LIMIT 21 on the new index. The count uses an Index Only Scan, and its cost grows with the number of events on the page.
SQL
Sessions list, page 1:
SELECT * FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT "namespaces"."id" FROM "namespaces" WHERE (traversal_ids @> ('{9970}'))), "recursive_keyset_cte" AS ((SELECT NULL::ai_governance_sessions AS records, array_cte_id_array, ai_governance_sessions_session_started_at_array, ai_governance_sessions_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array, ARRAY_AGG("ai_governance_sessions"."session_started_at") AS ai_governance_sessions_session_started_at_array, ARRAY_AGG("ai_governance_sessions"."id") AS ai_governance_sessions_id_array FROM (SELECT "array_cte"."id" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "ai_governance_sessions"."session_started_at" AS session_started_at, "ai_governance_sessions"."id" AS id FROM "ai_governance_sessions" WHERE NOT (EXISTS (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."id" = "ai_governance_sessions"."workflow_id" AND ((duo_workflows_workflows.messaging_callback_context ->> 'adapter') IN ('slack')))) AND "ai_governance_sessions"."namespace_id" = "array_cte"."id" ORDER BY "ai_governance_sessions"."session_started_at" DESC, "ai_governance_sessions"."id" DESC LIMIT 1) ai_governance_sessions ON TRUE WHERE "ai_governance_sessions"."session_started_at" IS NOT NULL AND "ai_governance_sessions"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(SELECT (SELECT ai_governance_sessions FROM "ai_governance_sessions" WHERE "ai_governance_sessions"."id" = recursive_keyset_cte.ai_governance_sessions_id_array[position] LIMIT 1), array_cte_id_array, recursive_keyset_cte.ai_governance_sessions_session_started_at_array[:position_query.position-1]||next_cursor_values.session_started_at||recursive_keyset_cte.ai_governance_sessions_session_started_at_array[position_query.position+1:], recursive_keyset_cte.ai_governance_sessions_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.ai_governance_sessions_id_array[position_query.position+1:], recursive_keyset_cte.count + 1 FROM recursive_keyset_cte,
LATERAL (SELECT session_started_at, id, position FROM UNNEST(ai_governance_sessions_session_started_at_array, ai_governance_sessions_id_array) WITH ORDINALITY AS u(session_started_at, id, position) WHERE session_started_at IS NOT NULL AND id IS NOT NULL ORDER BY 1 DESC, 2 DESC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."session_started_at", "record"."id" FROM (VALUES (NULL, NULL)) AS nulls LEFT JOIN (SELECT "ai_governance_sessions"."session_started_at" AS session_started_at, "ai_governance_sessions"."id" AS id FROM "ai_governance_sessions" WHERE NOT (EXISTS (SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows" WHERE "duo_workflows_workflows"."id" = "ai_governance_sessions"."workflow_id" AND ((duo_workflows_workflows.messaging_callback_context ->> 'adapter') IN ('slack')))) AND "ai_governance_sessions"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] AND (("ai_governance_sessions"."session_started_at", "ai_governance_sessions"."id") < (recursive_keyset_cte.ai_governance_sessions_session_started_at_array[position], recursive_keyset_cte.ai_governance_sessions_id_array[position])) ORDER BY "ai_governance_sessions"."session_started_at" DESC, "ai_governance_sessions"."id" DESC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
)) SELECT (records).* FROM "recursive_keyset_cte" AS "ai_governance_sessions" WHERE (count <> 0)) ai_governance_sessions LIMIT 21 OFFSET 0
External session audit events query (session 123, a page of 20 plus one; the created_at bound is the session start minus one day):
SELECT "ai_audit_events".* FROM "ai_audit_events" WHERE "ai_audit_events"."ai_governance_session_id" = 123 AND "ai_audit_events"."created_at" >= '2026-10-05 10:00:00' ORDER BY "ai_audit_events"."created_at" DESC, "ai_audit_events"."id" DESC LIMIT 21Count queries for auditEventsCount (one per page and kind; 20 sessions per page shown):
SELECT COUNT(*) AS count_all, "ai_audit_events"."ai_governance_session_id" AS ai_audit_events_ai_governance_session_id FROM "ai_audit_events" WHERE "ai_audit_events"."ai_governance_session_id" IN (1000, 1001, ..., 1019) AND "ai_audit_events"."created_at" >= '2026-10-01 00:00:00' GROUP BY "ai_audit_events"."ai_governance_session_id"
SELECT COUNT(*) AS count_all, "ai_audit_events"."workflow_id" AS ai_audit_events_workflow_id FROM "ai_audit_events" WHERE "ai_audit_events"."workflow_id" IN (1000, 1001, ..., 1019) AND "ai_audit_events"."created_at" >= '2026-10-01 00:00:00' GROUP BY "ai_audit_events"."workflow_id"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.