### Problem to Solve Staging.gitlab.com and gitlab.com still uses `integer` for ten canonical `issues` ID columns while `db/structure.sql` and the target database use `bigint`. The ten bigint conversion columns and their synchronization trigger are still active. This type mismatch blocks schema-compatible PostgreSQL logical replication. The cleanup delivered by https://gitlab.com/gitlab-org/gitlab/-/merge_requests/233564 is recorded as applied, but it correctly skipped staging: migration `20260505080750` returns when any canonical column is still `integer`. ### Proposed Solution - Determine which existing conversion phase must run for staging and why it has not completed. - Complete the supported bigint backfill, index and foreign-key preparation, validation, and column swap sequence for all ten columns. - After the swap and the required release interval, remove the old integer columns and synchronization trigger. - Verify the final schema matches `db/structure.sql` before configuring logical replication. <details> <summary><b>Agent context</b> -- extended analysis, alternatives considered, related-code dumps</summary> The canonical columns still using `integer` are: - `id` - `author_id` - `closed_by_id` - `duplicated_to_id` - `last_edited_by_id` - `milestone_id` - `moved_to_id` - `project_id` - `promoted_to_epic_id` - `updated_by_id` Each corresponding `_convert_to_bigint` column is `bigint`. Trigger `trigger_22262f5f16d8` copies writes from the canonical integer columns to those bigint columns. Migration versions `20241002185802` and `20260505080750` are recorded as applied. Do not drop the conversion columns directly: they remain load-bearing until the canonical columns are swapped. Related work: - https://gitlab.com/gitlab-org/gitlab/-/issues/499221 explains why redundant conversion columns were created on already-bigint installations and why the cleanup migration is conditional. - https://gitlab.com/gitlab-org/gitlab/-/issues/465805 tracks conversion of integer IDs in the primary cell. </details> ### Rollout in batches We batch the conversion **by column**, not by index. A column can only be swapped once all of its indexes exist on the `bigint` column, so every index that references a batch's columns must be prepared (and later dropped) within that batch. Each batch runs the full prepare → swap → drop cycle for its columns before the next batch starts. #### The `id` coupling and peak duplication `id` appears in composite indexes alongside `project_id`, `milestone_id`, and `author_id` (for example `idx_issues_on_project_id_and_created_at_and_id_and_state_id`), so these four columns are related through 19 shared indexes. They can still be swapped in separate batches: a shared composite is rebuilt in the right shape per phase (for example `(project_id, id_convert_to_bigint)` while `id` is swapped, then `(project_id_convert_to_bigint, id)` afterwards), and each batch's temporary indexes are dropped right after its swap. Because the temporary indexes are dropped per batch, the metric that matters is the **peak** number of duplicate indexes held at once, not the total. Keeping all four coupled columns in one batch peaks at all 19 duplicated together. Giving `id` its own batch lowers the peak to ~14: the `id` batch needs the 13 indexes that contain `id`, and the `project_id`/`milestone_id`/`author_id` batch needs 14 (8 shared composites rebuilt in their final shape, plus 6 project/author-only indexes) — but never more than ~14 at a time. The cost is rebuilding the 8 shared composites twice, which is an acceptable trade for the lower peak. The plan is therefore a **3-batch split**: free columns, then `id` on its own, then `project_id`/`milestone_id`/`author_id`. #### Batch 1 — free single-column FK columns (6 indexes) Columns: `closed_by_id`, `duplicated_to_id`, `last_edited_by_id`, `moved_to_id`, `promoted_to_epic_id`, `updated_by_id` - `index_issues_on_closed_by_id` - `index_issues_on_duplicated_to_id` - `index_issues_on_last_edited_by_id` - `index_issues_on_moved_to_id` - `index_issues_on_promoted_to_epic_id` - `index_issues_on_updated_by_id` #### Batch 2 — `id` (13 indexes) Column: `id` - `issues_pkey` - `index_issues_on_id_and_weight` - `index_issues_on_milestone_id_and_id` - `idx_issues_on_project_id_and_created_at_and_id_and_state_id` - `idx_issues_on_project_id_and_updated_at_and_id_and_state_id` - `idx_issues_on_project_id_and_rel_position_and_id_and_state_id` - `idx_issues_on_project_id_and_due_date_and_id_and_state_id` - `index_issues_on_project_id_closed_at_desc_state_id_and_id` - `index_issues_on_namespace_id_created_at_id_state_id` - `index_issues_on_namespace_id_updated_at_id_state_id` - `index_issues_on_namespace_id_relative_position_id_state_id` - `index_open_issues_on_namespace_id_confidential_author_id_id` - `idx_open_issues_on_project_and_confidential_and_author_and_id` The composite indexes above that also contain `project_id`, `milestone_id`, or `author_id` are prepared here in their interim shape (for example `(project_id, id_convert_to_bigint)`) and rebuilt in their final shape in Batch 3. The incoming foreign keys on `issues.id` are converted **within this batch**: every FK referencing `id` must exist and be validated before `id` can be swapped. Following the `merge_requests` precedent, the bigint FKs are added `validate: false` with asynchronous validation a milestone ahead of the swap, then validated and swapped in the swap milestone. #### Batch 3 — `project_id`, `milestone_id`, `author_id` (14 indexes) Columns: `project_id`, `milestone_id`, `author_id` Shared composites rebuilt in final shape (also swapped in Batch 2 in interim shape): - `index_issues_on_milestone_id_and_id` - `idx_issues_on_project_id_and_created_at_and_id_and_state_id` - `idx_issues_on_project_id_and_updated_at_and_id_and_state_id` - `idx_issues_on_project_id_and_rel_position_and_id_and_state_id` - `idx_issues_on_project_id_and_due_date_and_id_and_state_id` - `index_issues_on_project_id_closed_at_desc_state_id_and_id` - `index_open_issues_on_namespace_id_confidential_author_id_id` - `idx_open_issues_on_project_and_confidential_and_author_and_id` Project/author-only indexes (no `id`): - `index_issues_on_author_id` - `idx_issues_on_project_work_item_type_closed_at_where_closed` - `index_issue_on_project_id_state_id_and_blocking_issues_count` - `index_issues_on_project_id_and_iid` - `index_issues_on_project_id_and_upvotes_count` - `index_issues_on_work_item_type_id_project_id_created_at_state` Outgoing foreign keys for these columns (to `users`, `projects`, `milestones`) are converted within this batch. #### Remaining `integer` referencing columns A subset of the columns that reference `issues.id` are still `integer` on staging and production: `epic_issues.issue_id`, `epics.issue_id`, `issue_links.source_id`/`target_id`, `issue_assignees.issue_id`, `issue_user_mentions.issue_id`, `merge_requests_closing_issues.issue_id`, `timelogs.issue_id`, `resource_label_events.issue_id`, `description_versions.issue_id`, `design_management_designs.issue_id`, plus the self-references `issues.duplicated_to_id`/`moved_to_id`. Because `integer` → `bigint` foreign keys are allowed, these do not block the `id` swap. They remain `integer` afterwards and are tracked separately alongside their own tables.