Add relative_positioning_namespace_id to work_item_positions

Vedant Jainrequested to merge
vj/work-item-positions-root-namespace-index into master

What does this MR do and why?

Adds a relative_positioning_namespace_id column to work_item_positions (the work item's positioning root — Namespace#work_item_positioning_root) plus an index on (relative_positioning_namespace_id, relative_position).

Work-item manual ordering is scoped to the whole top-level group, but the current index leads with the leaf namespace_id, so an ordered read fans out across every namespace in the group. Keying by the root turns it into a single contiguous range scan.

No application / read behaviour change — nothing reads the new column yet (no relative_positioning_namespace_id references in app code). The sync trigger is extended to also populate it (one extra namespaces lookup per issue relative_position/namespace_id write). This is groundwork; the read/write cutover onto the column is a follow-up (#594236).

Sequencing note: reads already source relative_position from work_item_positions behind the read_relative_positions_from_work_item_positions flag (!240241 (merged), merged, default off), but still under the old namespace_id IN (…) scope — so the flag is not faster yet. The speed-up lands only when a follow-up changes the read scope to filter on relative_positioning_namespace_id (this index). The flag must not be enabled on production until the follow-up backfill of relative_positioning_namespace_id is finished and finalized (and transfer handling is in place), or un-backfilled/stale rows would drop out of ordered lists. Rollout tracked in #607801.

Contents:

  • the column (nullable),
  • a trigger update to populate it on write (project namespace for personal projects, root ancestor otherwise),
  • the index.

Why the index — before/after (DB Lab, gitlab-org)

The before/after below is Issue.order_by_relative_position (the manually-sorted list/board load). The same root-leading index also serves Issue#next_object_by_relative_position — the neighbour lookup behind top/bottom drags — since both filter by the group scope (relative_positioning_query_base) and order/range on relative_position. Both get rewired to the new column in the cutover (#594236); this MR only adds the column + index.

Same ordered read (first page of a group's manually-sorted items), keyed by leaf namespace vs root:

Buffers Time
Before — issues, namespace_id IN (<all descendants>) fan-out ~742k (5.5 GiB) 15.7 s
After — work_item_positions, filter by relative_positioning_namespace_id + (relative_positioning_namespace_id, relative_position) index ~36 (288 KiB) ~1.4 ms

Query plans (DB Lab):

Backfill (follow-up)

Existing rows are not backfilled in this MR — the sync trigger populates new/changed rows, and the one-off backfill of existing rows lands in a follow-up MR (after transfer handling keeps the column fresh on moves). Tracked in #594236.

References

Related to #604341 (closed)

Screenshots or screen recordings

Before After

How to set up and validate locally

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 Vedant Jain

Merge request reports