Skip to content

Use namespace_traversal_ids in WorkItemsFinder

MR: Use namespace_traversal_ids in work_items finder (!206102 - merged)

Problem Statement

The current WorkItemsFinder implementation suffers from severe performance issues when querying work items for large organizations like the GitLab organization. The existing queries time out due to expensive joins between the issues and namespaces tables to determine group hierarchies.

Current Performance Issues

  • Queries timeout for large organizations with complex group hierarchies
  • Expensive subquery for namespaces.ids IN issues create performance bottlenecks
  • Multiple complex database queries are required to traverse group structures
  • Poor scalability as organization size and group nesting increases
WITH namespace_ids AS (
  SELECT id
  FROM namespaces
  WHERE traversal_ids @> ('{9970}')
)
SELECT * from issues WHERE issues.namespace_id IN (
  SELECT id FROM namespace_ids
)
ORDER BY created_at, id LIMIT 100;

Solution Context

Since we have moved the traversal_ids from the namespaces table directly to the issues table as namespace_traversal_ids, we can now eliminate the expensive subqueries and query the namespace_traversal_ids field directly. This denormalization allows for much more efficient queries that can handle large-scale group hierarchies without performance degradation.

Acceptance Criteria

  • Implement new query method using namespace_traversal_ids field directly from issues table
  • Add feature flag to control rollout of the new query implementation
  • Ensure new queries return identical results to existing implementation
  • Maintain all existing permission and security checks
  • Preserve existing filtering for work item types, states, banned users, and project features
  • Add comprehensive test coverage for new query methods
  • Verify performance improvements over current implementation

References

Edited by Daniyal Arshad