Draft: Add index for generationStatus workflow lookups

Stefanos Xanthopoulosrequested to merge
duo-workflows-issue-definition-index into master

What does this MR do and why?

Adds a database index to speed up resolution of the generationStatus field added for the Agent Plan widget in !249588 (merged).

Resolving that field runs one batched query per GraphQL request, filtering duo_workflows_workflows by issue_id (up to 100 IDs) and workflow_definition = 'workplan/v1', then taking the newest row per issue with DISTINCT ON (issue_id).

The only index that fits this query today covers issue_id alone, so Postgres reads every Duo workflow attached to those issues and discards the ones with a different definition. That cost scales with all Duo activity on a work item, including chat sessions and other flow types, not with the number of workplan runs specifically.

The new index adds workflow_definition to the seek and provides the (issue_id, id DESC) ordering the query needs, so no separate sort is required either.

No application code changes are included in this MR, only the migration.

Database

Migration: db/migrate/20260902103413_add_issue_definition_index_to_duo_workflows_workflows.rb, milestone 19.4.

Adds index_duo_workflows_workflows_on_issue_id_definition_id on duo_workflows_workflows (issue_id, workflow_definition, id DESC), using add_concurrent_index with disable_ddl_transaction!. The down method removes it with remove_concurrent_index_by_name.

Table: duo_workflows_workflows, database gitlab_main_org, table_size: small.

Query the index serves:

SELECT DISTINCT ON (issue_id) id, issue_id, status
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."workflow_definition" = 'workplan/v1'
  AND "duo_workflows_workflows"."issue_id" IN (100 work item IDs)
ORDER BY "duo_workflows_workflows"."issue_id" ASC, "duo_workflows_workflows"."id" DESC;

Measurements below are from a local database with seeded data. Each before/after pair was run inside a transaction that was rolled back afterward. The issue_id IN (...) list has 100 issue IDs in every case.

Realistic shape, 3 workplan runs plus 20 workflows of another definition per issue:

  • Before: index scan on the issue_id-only index, 2,300 rows read to return 300, buffers shared hit=22,036, execution 6.548 ms
  • After: index scan on the new index, buffers shared hit=328 read=19, execution 0.201 ms

Deliberately heavy shape, 177 workplan runs per issue, far beyond anything realistic:

  • Before: buffers shared hit=19,719, execution 10.459 ms
  • After: buffers shared hit=17,720 read=124, execution 8.262 ms

The heavy shape barely improves, because DISTINCT ON still has to walk every matching row. The real gain shows up in the realistic shape, where most of the rows read are of other, unrelated workflow definitions.

Index size on the seeded table (222,734 rows): 11 MB. A partial index, (issue_id, id DESC) WHERE workflow_definition = 'workplan/v1', measured 6,184 kB on the same seed and would be smaller still in production, where workplan runs are a small minority of Duo workflow rows. It was rejected because it serves only this one query and hardcodes a workflow definition string into the schema.

Plans come from a local database rather than Database Lab because workplan/v1 has no rows on GitLab.com: creating that workflow is gated by the duo_workplan_async_flow feature flag, which defaults to off, so a Database Lab plan for this predicate would return no rows.

  • Query plan before (realistic shape): TODO paste depesz link
  • Query plan after (realistic shape): TODO paste depesz link
  • Query plan before (heavy shape): TODO paste depesz link
  • Query plan after (heavy shape): TODO paste depesz link

Testing

Ran the migration up and down locally against the development database. Confirmed the index was present after up and absent after down. No new specs, since the migration only adds an index and no application code changed.

Merge request reports