Backfill organization_id on CI mirror tables

Rutger Wesselsrequested to merge
627588-backfill-mirrors-organization_id into master

What does this MR do and why?

This MR adds two batched background migrations that backfill the new organization_id column on ci_project_mirrors and ci_namespace_mirrors for existing rows.

The source of truth (projects.organization_id and namespaces.organization_id) lives on the main database, while the mirror tables live on the ci database. A SQL join is not possible, so each sub-batch looks up the mapping in Ruby and injects it as a VALUES list into a single UPDATE on the ci database. Rows that already have an organization_id (written by the sync since the previous MR) are skipped, so the jobs are idempotent.

This MR is part 3 of a series:

  1. !253978 (merged) - add the organization_id columns
  2. !254205 (merged) - sync organization_id on changes
  3. This MR - backfill organization_id for existing rows
  4. !254210 - use the mirror tables in the data isolation logic

References

How to set up and validate locally

  • Fresh database based on development seeds
  • Make sure background jobs are running
  • Ensure tables are fully synced:
    • Namespace.update_all(updated_at: Time.now)
    • Project.update_all(updated_at: Time.now)
  • Make sure the sync is done:
    • gdk psql -d gitlabhq_development_ci -c "select project_id, organization_id from ci_project_mirrors union select namespace_id, organization_id from ci_namespace_mirrors;"
  • Update organization_id to NULL:
    • gdk psql -d gitlabhq_development_ci -c "update ci_project_mirrors set organization_id = null;update ci_namespace_mirrors set organization_id = null;"
  • Check that organization_id is now NULL:
    • gdk psql -d gitlabhq_development_ci -c "select project_id, organization_id from ci_project_mirrors union select namespace_id, organization_id from ci_namespace_mirrors;"

Now run the database migration:

  • gdk rails db:migrate
  • Wait for the background migration to finish
  • gdk psql -d gitlabhq_development_ci -c "select project_id, organization_id from ci_project_mirrors union select namespace_id, organization_id from ci_namespace_mirrors;"

Organization ID should now no longer be NULL

Query Plans

Select a ci_project_mirrors batch

SELECT "ci_project_mirrors".* FROM "ci_project_mirrors" WHERE ("ci_project_mirrors"."id") <= (18) AND ("ci_project_
mirrors"."id") >= (1) ORDER BY "ci_project_mirrors"."id" ASC LIMIT 2 OFFSET 99
Time: 1.062 ms  
  - planning: 1.011 ms  
  - execution: 0.051 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 7 (~56.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/57759/commands/162368

Select records that need to be updated

SELECT "ci_project_mirrors"."id", "ci_project_mirrors"."project_id" FROM "ci_project_mirrors" WHERE ("ci_project_mirrors"."id") <= (18) AND ("ci_project_mirrors"."id") >= (1) AND "ci_project_mirrors"."organization_id" IS NULL ORDER BY "ci_project_mirrors"."id" ASC LIMIT 100
Time: 1.336 ms  
  - planning: 1.269 ms  
  - execution: 0.067 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 7 (~56.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/58277/commands/162945

Look up the mapping on the main database

SELECT "projects"."id", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND "projects"."organization_id" IS NOT NULL
Time: 5.105 ms  
  - planning: 5.051 ms  
  - execution: 0.054 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 8 (~64.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58279/commands/162955

Update statement

UPDATE ci_project_mirrors
SET organization_id = mapping.organization_id
FROM (VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1)) AS mapping(id, organization_id)
WHERE ci_project_mirrors.id = mapping.id
  AND ci_project_mirrors.organization_id IS NULL
Time: 1.261 ms  
  - planning: 1.082 ms  
  - execution: 0.179 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 39 (~312.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/58277/commands/162947

Select a ci_namespace_mirrors batch

SELECT "ci_namespace_mirrors".* FROM "ci_namespace_mirrors" WHERE ("ci_namespace_mirrors"."id") <= (18) AND ("ci_namespace_mirrors"."id") >= (1) ORDER BY "ci_namespace_mirrors"."id" ASC LIMIT 2 OFFSET 99
Time: 1.149 ms  
  - planning: 1.101 ms  
  - execution: 0.048 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 7 (~56.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/57759/commands/162379

Select records that need to be updated

SELECT "ci_namespace_mirrors"."id", "ci_namespace_mirrors"."namespace_id" FROM "ci_namespace_mirrors" WHERE ("ci_namespace_mirrors"."id") <= (18) AND ("ci_namespace_mirrors"."id") >= (1) AND "ci_namespace_mirrors"."organization_id" IS NULL ORDER BY "ci_namespace_mirrors"."id" ASC LIMIT 100
Time: 1.317 ms  
  - planning: 1.259 ms  
  - execution: 0.058 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 7 (~56.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/58277/commands/162949

Look up the mapping on the main database

SELECT "namespaces"."id", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND "namespaces"."organization_id" IS NOT NULL
Time: 1.806 ms  
  - planning: 1.760 ms  
  - execution: 0.046 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 12 (~96.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58279/commands/162953

Update statement

UPDATE ci_namespace_mirrors
SET organization_id = mapping.organization_id
FROM (VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1)) AS mapping(id, organization_id)
WHERE ci_namespace_mirrors.id = mapping.id
  AND ci_namespace_mirrors.organization_id IS NULL
Time: 1.582 ms  
  - planning: 1.443 ms  
  - execution: 0.139 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 39 (~312.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/58277/commands/162950

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 Rutger Wessels

Merge request reports