Schedule backfill of suggestions.namespace_id

What does this MR do and why?

Needed as part of Define a sharding key for `suggestions` table (#550696)

This MR adds a background migration that backfills suggestions.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).

⚠️ Note that the model validation for the column was already added in !210092 (merged)

🔗 References

Related to #572849

Add `suggestions` sharding key (!207836 - merged)

Ensure `suggestions` sharding key is set at the... (!210092 - merged)

DB Review

Migration output

UP

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17601
main: == 20251106100834 QueueBackfillSuggestionsNamespaceId: migrating ==============
main: == 20251106100834 QueueBackfillSuggestionsNamespaceId: migrated (0.0780s) =====

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17601
ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17642
ci: == 20251106100834 QueueBackfillSuggestionsNamespaceId: 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: == 20251106100834 QueueBackfillSuggestionsNamespaceId: migrated (0.0214s) =====

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

DOWN

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17369
main: == 20251106100834 QueueBackfillSuggestionsNamespaceId: reverting ==============
main: == 20251106100834 QueueBackfillSuggestionsNamespaceId: reverted (0.0610s) =====

main: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17369
ci: == [advisory_lock_connection] object_id: 130460, pg_backend_pid: 17428
ci: == 20251106100834 QueueBackfillSuggestionsNamespaceId: 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: == 20251106100834 QueueBackfillSuggestionsNamespaceId: reverted (0.0305s) =====

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

Query plans

UPDATE namespace_id batch

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

WITH relation AS MATERIALIZED (
  SELECT "suggestions"."id", "suggestions"."note_id" FROM "suggestions" WHERE "suggestions"."id" BETWEEN 100000 AND 100301 AND "suggestions"."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 "suggestions"
SET "namespace_id" = "relation_with_namespace_id"."namespace_id"
FROM "relation_with_namespace_id"
WHERE "suggestions"."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.