Add BBM to migrate legacy step_url values with prefix match

Roy Liurequested to merge
593994-migrate-legacy-step-urls-prefix-match into master

What does this MR do and why?

This MR adds a new batched background migration (BBM) called UpdateLegacyStepUrlsToWelcomePath. It rewrites user_details.onboarding_status ->> 'step_url' to /users/sign_up/welcome?migrating=true for every row whose step_url starts with /users/sign_up/company or /users/sign_up/groups/new. It uses a SQL LIKE prefix match, not an exact match.

This is a follow-up to an earlier BBM, UpdateStepUrlToWelcomePath, from !238748 (merged).

The earlier BBM matched the two legacy paths with exact string equality: IN ('/users/sign_up/company', '/users/sign_up/groups/new'). The legacy welcome controller wrote the company step through the new_users_sign_up_company_path(passed_through_params) route helper, so the stored values actually look like /users/sign_up/company/new?glm_source=...&glm_content=..., with a varying query string. None of those values equal the bare /users/sign_up/company, so the earlier migration skipped all of them. The groups path was written without params, so the exact match worked for it. This new BBM still uses a prefix match on the groups path too, for consistency and safety.

On GitLab.com, as of 2026-09-04:

  • About 99,749 rows have a step_url starting with /users/sign_up/company, across 4,590 distinct values.
  • Of those, about 82,959 rows belong to users with onboarding_in_progress = true.
  • About 105 rows have a step_url exactly equal to /users/sign_up/groups/new, of which about 18 are in progress. These come from live traffic still hitting the legacy company form, which writes the groups path on submit.

This matters because while onboarding_in_progress is true, the Onboarding::Redirect controller concern redirects the user to their stored step_url on every GET request. !253445 removes CompanyController. If that MR merges before these rows are rewritten, about 83,000 mid-onboarding users would be redirected to a dead route on every page load. This BBM must finish running on GitLab.com before that MR merges.

Design choices:

  • The target value matches the first BBM, so the existing migrating=true handling in the welcome flow picks these users up.
  • onboarding_in_progress is left unchanged, matching the first BBM.
  • Batching config matches the first BBM: batch size 3,000, sub-batch size 250, max batch size 10,000, iterating user_details by user_id, gitlab_schema gitlab_main_user.
  • This is a BBM and not a regular post-deploy migration because there is no index on the step_url expression. The filter scans all roughly 25 million user_details rows, which took 85 seconds cold on postgres.ai. That exceeds the post-deploy migration time limits.
  • The first BBM took about six days to complete on GitLab.com. Expect a similar duration for this one.

References

Sub-batch update query

Each sub-batch runs one update_all over a window of 250 user_details rows ordered by user_id. This is the exact SQL, with a real GitLab.com user_id window that contains matching rows:

UPDATE "user_details"
  SET "onboarding_status" = jsonb_set(
    onboarding_status,
    '{step_url}',
    '"/users/sign_up/welcome?migrating=true"'::jsonb
  )
  WHERE "user_details"."user_id" >= 40010000
    AND "user_details"."user_id" < 40010250
    AND (
      onboarding_status ->> 'step_url' LIKE '/users/sign_up/company%'
      OR onboarding_status ->> 'step_url' LIKE '/users/sign_up/groups/new%'
    );

Query plan, measured on postgres.ai on 2026-09-04 (query ID 3841679127240556998). The window above holds one matching row and 103 non-matching rows. The sub-batch is served by an index range scan on index_user_details_on_user_id, with the LIKE filter applied to the 104 rows in the window.

EXPLAIN (ANALYZE, BUFFERS) output 
 Update on public.user_details  (cost=0.44..168.09 rows=0 width=0) (actual time=50.511..50.512 rows=0 loops=1)
   Buffers: shared hit=108 read=55 dirtied=11
   WAL: records=10 fpi=10 bytes=57344
   I/O Timings: read=48.605 write=0.000
   ->  Index Scan using index_user_details_on_user_id on public.user_details  (cost=0.44..168.09 rows=1 width=38) (actual time=0.550..14.248 rows=1 loops=1)
         Index Cond: ((user_details.user_id >= 40010000) AND (user_details.user_id < 40010250))
         Filter: (((user_details.onboarding_status ->> 'step_url'::text) ~~ '/users/sign_up/company%'::text) OR ((user_details.onboarding_status ->> 'step_url'::text) ~~ '/users/sign_up/groups/new%'::text))
         Rows Removed by Filter: 103
         Buffers: shared hit=86 read=22
         I/O Timings: read=13.300 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'
Query ID: 3841679127240556998

Time: 57.489 ms
  - planning: 6.827 ms
  - execution: 50.662 ms
    - I/O read: 48.605 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 108 (~864.00 KiB) from the buffer pool
  - reads: 55 (~440.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 11 (~88.00 KiB)
  - writes: 0

Query plans

Measured on postgres.ai on 2026-09-04:

  • Rows with step_url starting with /users/sign_up/company: 99,749 rows, 4,590 distinct values (query ID -2703671383442774799).
  • Query ID -7785994466994924412: counted, of those rows, how many belong to users with onboarding_in_progress = true (about 82,959 rows).
  • Query ID 4451000014018545946: counted rows with step_url exactly equal to /users/sign_up/groups/new and how many of those are in progress (about 105 rows, about 18 in progress).

Note for a follow-up verification query once the BBM finishes: use LIKE '/users/sign_up/company%', not an equality check, since the stored values include query strings.

Screenshots or screen recordings

Not applicable. This is a database data migration with no UI.

How to set up and validate locally

  1. In a rails console, set a user's step_url to a legacy value with a query string: 
    user = User.last
user.update!(onboarding_status_step_url: '/users/sign_up/company/new?glm_source=about.gitlab.com')
  2. Run the migration job directly: 
    Gitlab::BackgroundMigration::UpdateLegacyStepUrlsToWelcomePath.new(
  start_id: user.user_detail.user_id,
  end_id: user.user_detail.user_id,
  batch_table: :user_details,
  batch_column: :user_id,
  sub_batch_size: 100,
  pause_ms: 0,
  connection: ApplicationRecord.connection
).perform
  3. Verify the result: 
    user.reload.onboarding_status_step_url
# => "/users/sign_up/welcome?migrating=true"

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 Roy Liu

Merge request reports