Background migration to backfill environment tiers

What does this MR do and why?

This MR backfills the environments.tier by using Environment#guess_tier logic, which is a default behavior to assign a tier value.

Environments created after 13.10 already have a value, however, environments created before 13.10 don't. See #300741 (closed) for more information.

Fixes gitlab-org/cluster-integration/gitlab-agent#332 (closed)

Estimation on gitlab.com

• Row Count: 2,224,441 (30.2% of entire rows)
• Batch size: 1000 (sub-batch size: 100)
• Job count: 2225 jobs
• Job interval: 2 min
• Total Duration: 4450 min (3.09 days)

Query example per batch

SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(dev|review|trunk)') ORDER BY "environments"."id" ASC LIMIT 1
SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(dev|review|trunk)') AND "environments"."id" >= 171 ORDER BY "environments"."id" ASC LIMIT 1 OFFSET 10
UPDATE "environments" SET "tier" = 3 WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(dev|review|trunk)') AND "environments"."id" >= 171

SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(test|tst|int|ac(ce|)pt|qa|qc|control|quality)') ORDER BY "environments"."id" ASC LIMIT 1
SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(test|tst|int|ac(ce|)pt|qa|qc|control|quality)') AND "environments"."id" >= 170 ORDER BY "environments"."id" ASC LIMIT 1 OFFSET 10
UPDATE "environments" SET "tier" = 2 WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(test|tst|int|ac(ce|)pt|qa|qc|control|quality)') AND "environments"."id" >= 170

SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(st(a|)g|mod(e|)l|pre|demo|non)') ORDER BY "environments"."id" ASC LIMIT 1
SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(st(a|)g|mod(e|)l|pre|demo|non)') AND "environments"."id" >= 169 ORDER BY "environments"."id" ASC LIMIT 1 OFFSET 10
UPDATE "environments" SET "tier" = 1 WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(st(a|)g|mod(e|)l|pre|demo|non)') AND "environments"."id" >= 169

SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(pr(o|)d|live)') ORDER BY "environments"."id" ASC LIMIT 1
SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(pr(o|)d|live)') AND "environments"."id" >= 168 ORDER BY "environments"."id" ASC LIMIT 1 OFFSET 10
UPDATE "environments" SET "tier" = 0 WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND (name ~* '(pr(o|)d|live)') AND "environments"."id" >= 168

SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL ORDER BY "environments"."id" ASC LIMIT 1
SELECT "environments"."id" FROM "environments" WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND "environments"."id" >= 172 ORDER BY "environments"."id" ASC LIMIT 1 OFFSET 10
UPDATE "environments" SET "tier" = 4 WHERE "environments"."id" BETWEEN 1 AND 1000 AND "environments"."tier" IS NULL AND "environments"."id" >= 172

Plans:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13786/commands/48359 https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13786/commands/48360 https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13786/commands/48361

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.