Schedule backfill of diff_note_positions.namespace_id

What does this MR do and why?

Needed as part of Define a sharding key for `diff_note_positions`... (#550693)

This MR adds a background migration that backfills diff_note_positions.namespace_id from the associated note's namespace ID. Although the notes table has a namespace_id column as a sharding key, we cannot rely on it being present yet, so we fetch the value from the note's project instead (project is always present for this type of note).

🔗 References

Related to #572835

Add `diff_note_positions` sharding key (!207822 - merged)

DB Review

Migration output

UP

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 40402
main: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: migrating ========
main: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: migrated (0.0777s)

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 40402
ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 40677
ci: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: migrating ========
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main_org].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_ci_cell_local, :gitlab_internal, :gitlab_shared, :gitlab_shared_cell_local, :gitlab_shared_org].
ci: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: migrated (0.0278s)

ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 40677

DOWN

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 39771
main: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: reverting ========
main: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: reverted (0.1691s)

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 39771
ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 39890
ci: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: reverting ========
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main_org].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_ci_cell_local, :gitlab_internal, :gitlab_shared, :gitlab_shared_cell_local, :gitlab_shared_org].
ci: == 20251106115656 QueueBackfillDiffNotePositionsNamespaceId: reverted (0.0299s)

ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 39890

Query plans

UPDATE namespace_id batch

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45263/commands/138822

WITH relation AS MATERIALIZED (
  SELECT "diff_note_positions"."id", "diff_note_positions"."note_id" FROM "diff_note_positions" WHERE "diff_note_positions"."id" BETWEEN 100000 AND 100301 AND "diff_note_positions"."id" >= 100101 LIMIT 100
), relation_with_namespace_id AS MATERIALIZED (
  SELECT "relation".*, "projects"."project_namespace_id" AS namespace_id
FROM "relation"
INNER JOIN "notes" ON "notes"."id" = "relation"."note_id"
INNER JOIN "projects" ON "projects"."id" = "notes"."project_id"
LIMIT 100

)
UPDATE "diff_note_positions"
SET "namespace_id" = "relation_with_namespace_id"."namespace_id"
FROM "relation_with_namespace_id"
WHERE "diff_note_positions"."id" = "relation_with_namespace_id"."id"

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.