Use tag_list from job definition, if available
What does this MR do and why?
This MR implements the first step of migrating tag_list usage from legacy tables (ci_build_tags join table and tags table) to the new job definitions model, making job definitions the single source of truth (SSoT) for job tags, while falling back to legacy tables if the specified job's immutable details haven't yet been migrated to a job definition.
Background
As part of the CI/CD data sharding initiative, we are moving CI job metadata from legacy tables into the ci_job_definitions table. In Phase 2 (#565405), we started storing tag_list in job definitions. This MR switches the application to read tags from job definitions instead of the legacy ci_tags and ci_build_tags tables, whenever the job definition is available.
Changes
1. Override tag_list method in Ci::Build:
- Reads
tag_listfromjob_definition.config[:tag_list]when available - Falls back to the legacy
tagsassociation whenjob_definitionis nil - Memoizes the result for performance
- This ensures backward compatibility during the migration period
2. Update eager loading:
- Modified
eager_load_tagsscope to include:job_definitionassociation - Ensures efficient queries when accessing tags through job definitions
3. Adapt Ci::Build.build_matchers:
- Updated to use job definition tags when available via new
tag_names_array_querymethod - Uses
COALESCEto try job definition first, then fall back to legacy tags - Maintains backward compatibility with feature flag control
4. Update Ci::PendingBuild.build_tags_ids:
- New method that reads tags from job definition when available
- Uses
Ci::Tag.named(job_definition.config[:tag_list]).idsto get tag IDs - Falls back to
build.tags_idswhen job definition is nil or feature flag is disabled - NEW: Ensures pending builds use the correct tag source
-
NEW: Upserts tags from job definition when
upsert_tags_from_ci_pending_buildfeature flag is enabled -
NEW: Calls
build.save_tagsto ensure tags exist in thetagstable before creating pending builds -
NEW: This ensures
ci_pending_builds.tag_idscan be populated even when tags don't yet exist in the database
5. Feature flags:
-
NEW:
ci_build_uses_job_definition_tag_list- Controls reading tags from job definitions -
NEW:
upsert_tags_from_ci_pending_build- Controls upserting tags from job definitions to thetagstable - Allows gradual migration and easy rollback if needed
6. Comprehensive test coverage:
- Tests for reading tags from job definition
- Tests for fallback to legacy tags when job definition is absent
- Tests for empty tag lists in job definition
- Tests for eager loading behavior
-
NEW: Tests for
build_matcherswith job definition tags -
NEW: Tests for
Ci::PendingBuildtag handling -
NEW: Tests for upserting tags when they don't exist in the
tagstable -
NEW: Tests for feature flag interactions between
ci_build_uses_job_definition_tag_listandupsert_tags_from_ci_pending_build
Migration Path
This MR is part of a multi-phase migration:
-
✅ Phase 1: Addtag_listcolumn to job definitions table -
✅ Phase 2: Start storing tags in job definitions for new jobs -
🔄 Phase 3 (this MR): Switch application to read from job definitions -
📋 Phase 4: Backfill existing jobs with tags from legacy tables (!208674) -
📋 Phase 5: Drop legacyci_build_tagstable
Benefits
- Single source of truth: Tags are now consistently read from job definitions
- Sharding ready: Job definitions are designed for horizontal sharding
- Performance: Reduces joins to legacy tables once migration is complete
- Data consistency: Eliminates potential discrepancies between legacy tables and job definitions
-
NEW: Automatic tag creation: Tags are automatically created from job definitions when needed, ensuring
ci_pending_buildscan always reference valid tag IDs
References
Related to:
- Epic: &11837 (closed) (CI/CD Data Sharding)
- Phase 2: #565405 (closed)
Screenshots or screen recordings
N/A - Backend code change
How to set up and validate locally
-
Run the updated tests:
bundle exec rspec spec/models/ci/build_spec.rb -e "tag_list" bundle exec rspec spec/models/ci/pending_build_spec.rb -
Test eager loading:
# Verify no N+1 queries when accessing tags builds = Ci::Build.eager_load_tags.limit(10) # This should trigger a single query on p_ci_job_definition_instances/p_ci_job_definitions builds.each { |b| puts b.tag_list } -
Test with job_definitions:
# Make sure we save the current development database state gdk sandbox disable; gdk sandbox enable # Get migrations from https://gitlab.com/gitlab-org/gitlab/-/merge_requests/208674 curl https://gitlab.com/gitlab-org/gitlab/-/merge_requests/208674.diff | git apply # Clean migration MR code changes git reset && git clean -f -d . && git checkout . --force# Ensure there are some job descriptions now Ci::JobDefinition.count # Enable the feature flag Feature.enable(:ci_build_uses_job_definition_tag_list) # Verify tags are read from job definition build = Ci::BuildTag.first.build build.tag_list # Should load tags from job_definition.config[:tag_list] (`Ci::JobDefinition Load` and not hit `Ci::Tag Load`) pipeline = build.pipeline # Test return the builds from pipeline grouped by protected status and tags (coming from `p_ci_job_definitions`) pipeline.builds.latest.build_matchers(pipeline.project) # Verify tags are upserted when creating pending builds Feature.enable(:upsert_tags_from_ci_pending_build) Ci::PendingBuild.upsert_from_build!(build)# Rollback db state gdk sandbox reset
Database query plans
Ci::Build.build_matchers (FF disabled)
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45265/commands/138815
SELECT array_agg(id), "p_ci_builds"."protected", (
SELECT COALESCE(array_agg(tags.name ORDER BY tags.name), ARRAY[]::text[])
FROM "p_ci_build_tags"
INNER JOIN "tags" ON "tags"."id" = "p_ci_build_tags"."tag_id"
WHERE "p_ci_build_tags"."build_id" = "p_ci_builds"."id"
AND "p_ci_build_tags"."partition_id" = "p_ci_builds"."partition_id")
FROM "p_ci_builds"
WHERE "p_ci_builds"."type" = 'Ci::Build'
AND "p_ci_builds"."commit_id" = 2143349430
AND "p_ci_builds"."partition_id" = 107
AND ("p_ci_builds"."retried" = FALSE
OR "p_ci_builds"."retried" IS NULL)
GROUP BY "p_ci_builds"."protected", (
SELECT COALESCE(array_agg(tags.name ORDER BY tags.name), ARRAY[]::text[])
FROM "p_ci_build_tags"
INNER JOIN "tags" ON "tags"."id" = "p_ci_build_tags"."tag_id"
WHERE "p_ci_build_tags"."build_id" = "p_ci_builds"."id"
AND "p_ci_build_tags"."partition_id" = "p_ci_builds"."partition_id") Aggregate (cost=27006.51..104781.49 rows=92 width=65) (actual time=7.916..8.378 rows=1 loops=1)
Group Key: p_ci_builds.protected, ((SubPlan 1))
Buffers: shared hit=55 read=12 dirtied=5
WAL: records=9 fpi=5 bytes=40491
-> Incremental Sort (cost=27006.51..52936.02 rows=92 width=41) (actual time=7.903..8.365 rows=10 loops=1)
Sort Key: p_ci_builds.protected, ((SubPlan 1))
Buffers: shared hit=55 read=12 dirtied=5
WAL: records=9 fpi=5 bytes=40491
-> Gather Merge (cost=1078.18..52932.29 rows=92 width=41) (actual time=7.820..8.339 rows=10 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=50 read=12 dirtied=5
WAL: records=9 fpi=5 bytes=40491
-> Sort (cost=78.17..78.31 rows=54 width=17) (actual time=1.886..1.887 rows=5 loops=2)
Sort Key: p_ci_builds.protected
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=11 read=8 dirtied=5
WAL: records=9 fpi=5 bytes=40491
-> Parallel Index Scan using ci_builds_107_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds (cost=0.57..76.62 rows=54 width=17) (actual time=1.485..1.857 rows=5 loops=2)
Index Cond: ((p_ci_builds.commit_id = 2143349430) AND ((p_ci_builds.type)::text = 'Ci::Build'::text))
Filter: (((NOT p_ci_builds.retried) OR (p_ci_builds.retried IS NULL)) AND (p_ci_builds.partition_id = 107))
Rows Removed by Filter: 0
Buffers: shared hit=1 read=8 dirtied=5
WAL: records=9 fpi=5 bytes=40491
SubPlan 1
-> Aggregate (cost=563.51..563.52 rows=1 width=32) (actual time=0.137..0.137 rows=1 loops=10)
Buffers: shared hit=39 read=4
-> Sort (cost=562.87..563.19 rows=128 width=34) (actual time=0.136..0.136 rows=0 loops=10)
Sort Key: tags.name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=39 read=4
-> Nested Loop (cost=1.01..558.39 rows=128 width=34) (actual time=0.130..0.130 rows=0 loops=10)
Buffers: shared hit=36 read=4
-> Append (cost=0.58..117.11 rows=128 width=8) (actual time=0.128..0.129 rows=0 loops=10)
Buffers: shared hit=36 read=4
-> Index Scan using ci_build_tags_100_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_100 p_ci_build_tags_1 (cost=0.58..39.10 rows=53 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_1.build_id = p_ci_builds.id) AND (p_ci_build_tags_1.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_101_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_101 p_ci_build_tags_2 (cost=0.57..14.84 rows=17 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_2.build_id = p_ci_builds.id) AND (p_ci_build_tags_2.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_102_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_102 p_ci_build_tags_3 (cost=0.58..28.56 rows=29 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_3.build_id = p_ci_builds.id) AND (p_ci_build_tags_3.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_103_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_103 p_ci_build_tags_4 (cost=0.57..6.36 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_4.build_id = p_ci_builds.id) AND (p_ci_build_tags_4.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_104_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_104 p_ci_build_tags_5 (cost=0.57..7.07 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_5.build_id = p_ci_builds.id) AND (p_ci_build_tags_5.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_105_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_105 p_ci_build_tags_6 (cost=0.57..7.07 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_6.build_id = p_ci_builds.id) AND (p_ci_build_tags_6.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_106_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_106 p_ci_build_tags_7 (cost=0.57..7.15 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_7.build_id = p_ci_builds.id) AND (p_ci_build_tags_7.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_107_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_107 p_ci_build_tags_8 (cost=0.57..6.33 rows=5 width=8) (actual time=0.127..0.127 rows=0 loops=10)
Index Cond: ((p_ci_build_tags_8.build_id = p_ci_builds.id) AND (p_ci_build_tags_8.partition_id = p_ci_builds.partition_id))
Buffers: shared hit=36 read=4
-> Seq Scan on gitlab_partitions_dynamic.ci_build_tags_108 p_ci_build_tags_9 (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((p_ci_build_tags_9.build_id = p_ci_builds.id) AND (p_ci_build_tags_9.partition_id = p_ci_builds.partition_id))
Rows Removed by Filter: 0
-> Index Scan using tags_pkey on public.tags (cost=0.43..3.45 rows=1 width=38) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (tags.id = p_ci_build_tags.tag_id)
Settings: jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5'Ci::Build.build_matchers (FF enabled)
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45265/commands/138817
SELECT array_agg(id), "p_ci_builds"."protected", COALESCE((
SELECT ARRAY (
SELECT jsonb_array_elements_text(COALESCE("p_ci_job_definitions".config -> 'tag_list',
'[]'::jsonb)) AS tag_list ORDER BY tag_list)
FROM "p_ci_job_definition_instances"
INNER JOIN "p_ci_job_definitions" ON "p_ci_job_definitions"."partition_id" IS NOT NULL
AND "p_ci_job_definitions"."id" = "p_ci_job_definition_instances"."job_definition_id"
AND "p_ci_job_definitions"."partition_id" = "p_ci_job_definition_instances"."partition_id"
WHERE "p_ci_job_definition_instances"."job_id" = "p_ci_builds"."id"
AND "p_ci_job_definition_instances"."partition_id" = "p_ci_builds"."partition_id" LIMIT 1), (
SELECT COALESCE(array_agg(tags.name ORDER BY tags.name), ARRAY[]::text[])
FROM "p_ci_build_tags"
INNER JOIN "tags" ON "tags"."id" = "p_ci_build_tags"."tag_id"
WHERE "p_ci_build_tags"."build_id" = "p_ci_builds"."id"
AND "p_ci_build_tags"."partition_id" = "p_ci_builds"."partition_id"))
FROM "p_ci_builds"
WHERE "p_ci_builds"."type" = 'Ci::Build'
AND "p_ci_builds"."commit_id" = 2143349430
AND "p_ci_builds"."partition_id" = 107
AND ("p_ci_builds"."retried" = FALSE
OR "p_ci_builds"."retried" IS NULL)
GROUP BY "p_ci_builds"."protected", COALESCE((
SELECT ARRAY (
SELECT jsonb_array_elements_text(COALESCE("p_ci_job_definitions".config -> 'tag_list',
'[]'::jsonb)) AS tag_list ORDER BY tag_list)
FROM "p_ci_job_definition_instances"
INNER JOIN "p_ci_job_definitions" ON "p_ci_job_definitions"."partition_id" IS NOT NULL
AND "p_ci_job_definitions"."id" = "p_ci_job_definition_instances"."job_definition_id"
AND "p_ci_job_definitions"."partition_id" = "p_ci_job_definition_instances"."partition_id"
WHERE "p_ci_job_definition_instances"."job_id" = "p_ci_builds"."id"
AND "p_ci_job_definition_instances"."partition_id" = "p_ci_builds"."partition_id" LIMIT 1), (
SELECT COALESCE(array_agg(tags.name ORDER BY tags.name), ARRAY[]::text[])
FROM "p_ci_build_tags"
INNER JOIN "tags" ON "tags"."id" = "p_ci_build_tags"."tag_id"
WHERE "p_ci_build_tags"."build_id" = "p_ci_builds"."id"
AND "p_ci_build_tags"."partition_id" = "p_ci_builds"."partition_id")) Aggregate (cost=28529.94..110875.21 rows=92 width=65) (actual time=5.628..6.600 rows=1 loops=1)
Group Key: p_ci_builds.protected, (COALESCE((SubPlan 2), ((SubPlan 3))::text[]))
Buffers: shared hit=116
-> Incremental Sort (cost=28529.94..55982.88 rows=92 width=41) (actual time=5.614..6.585 rows=10 loops=1)
Sort Key: p_ci_builds.protected, (COALESCE((SubPlan 2), ((SubPlan 3))::text[]))
Buffers: shared hit=116
-> Gather Merge (cost=1078.18..55979.15 rows=92 width=41) (actual time=5.437..6.554 rows=10 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=111
-> Sort (cost=78.17..78.31 rows=54 width=17) (actual time=0.046..0.047 rows=5 loops=2)
Sort Key: p_ci_builds.protected
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=18
-> Parallel Index Scan using ci_builds_107_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds (cost=0.57..76.62 rows=54 width=17) (actual time=0.011..0.021 rows=5 loops=2)
Index Cond: ((p_ci_builds.commit_id = 2143349430) AND ((p_ci_builds.type)::text = 'Ci::Build'::text))
Filter: (((NOT p_ci_builds.retried) OR (p_ci_builds.retried IS NULL)) AND (p_ci_builds.partition_id = 107))
Rows Removed by Filter: 0
Buffers: shared hit=8
SubPlan 2
-> Limit (cost=0.56..33.12 rows=1 width=32) (actual time=0.071..0.072 rows=1 loops=10)
Buffers: shared hit=93
-> Nested Loop (cost=0.56..293.60 rows=9 width=32) (actual time=0.071..0.071 rows=1 loops=10)
Buffers: shared hit=93
-> Append (cost=0.28..27.06 rows=9 width=16) (actual time=0.010..0.010 rows=1 loops=10)
Buffers: shared hit=50
-> Index Scan using ci_job_definition_instances_100_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_100 p_ci_job_definition_instances_1 (cost=0.28..3.30 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_1.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_1.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_101_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_101 p_ci_job_definition_instances_2 (cost=0.28..3.30 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_2.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_2.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_102_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_102 p_ci_job_definition_instances_3 (cost=0.29..3.31 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_3.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_3.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_103_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_103 p_ci_job_definition_instances_4 (cost=0.28..3.30 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_4.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_4.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_104_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_104 p_ci_job_definition_instances_5 (cost=0.29..3.31 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_5.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_5.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_105_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_105 p_ci_job_definition_instances_6 (cost=0.29..3.31 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_6.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_6.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_106_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_106 p_ci_job_definition_instances_7 (cost=0.57..3.59 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definition_instances_7.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_7.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definition_instances_107_pkey on gitlab_partitions_dynamic.ci_job_definition_instances_107 p_ci_job_definition_instances_8 (cost=0.57..3.59 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=10)
Index Cond: ((p_ci_job_definition_instances_8.job_id = p_ci_builds.id) AND (p_ci_job_definition_instances_8.partition_id = p_ci_builds.partition_id))
Buffers: shared hit=50
-> Seq Scan on gitlab_partitions_dynamic.ci_job_definition_instances_108 p_ci_job_definition_instances_9 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((p_ci_job_definition_instances_9.partition_id = p_ci_builds.partition_id) AND (p_ci_job_definition_instances_9.job_id = p_ci_builds.id))
Rows Removed by Filter: 0
-> Append (cost=0.28..25.43 rows=9 width=675) (actual time=0.007..0.007 rows=1 loops=10)
Buffers: shared hit=40
-> Index Scan using ci_job_definitions_100_pkey on gitlab_partitions_dynamic.ci_job_definitions_100 p_ci_job_definitions_1 (cost=0.28..2.80 rows=1 width=886) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_1.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_1.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_101_pkey on gitlab_partitions_dynamic.ci_job_definitions_101 p_ci_job_definitions_2 (cost=0.28..2.79 rows=1 width=765) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_2.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_2.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_102_pkey on gitlab_partitions_dynamic.ci_job_definitions_102 p_ci_job_definitions_3 (cost=0.28..3.14 rows=1 width=820) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_3.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_3.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_103_pkey on gitlab_partitions_dynamic.ci_job_definitions_103 p_ci_job_definitions_4 (cost=0.28..3.14 rows=1 width=689) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_4.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_4.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_104_pkey on gitlab_partitions_dynamic.ci_job_definitions_104 p_ci_job_definitions_5 (cost=0.29..3.30 rows=1 width=741) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_5.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_5.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_105_pkey on gitlab_partitions_dynamic.ci_job_definitions_105 p_ci_job_definitions_6 (cost=0.29..3.31 rows=1 width=643) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_6.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_6.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_106_pkey on gitlab_partitions_dynamic.ci_job_definitions_106 p_ci_job_definitions_7 (cost=0.43..3.45 rows=1 width=671) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_job_definitions_7.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_7.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_job_definitions_107_pkey on gitlab_partitions_dynamic.ci_job_definitions_107 p_ci_job_definitions_8 (cost=0.44..3.46 rows=1 width=677) (actual time=0.006..0.006 rows=1 loops=10)
Index Cond: ((p_ci_job_definitions_8.id = p_ci_job_definition_instances.job_definition_id) AND (p_ci_job_definitions_8.partition_id = p_ci_builds.partition_id))
Buffers: shared hit=40
-> Seq Scan on gitlab_partitions_dynamic.ci_job_definitions_108 p_ci_job_definitions_9 (cost=0.00..0.00 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((p_ci_job_definitions_9.partition_id = p_ci_builds.partition_id) AND (p_ci_job_definitions_9.id = p_ci_job_definition_instances.job_definition_id))
Rows Removed by Filter: 0
SubPlan 1
-> Sort (cost=3.84..4.09 rows=100 width=32) (actual time=0.051..0.051 rows=0 loops=10)
Sort Key: (jsonb_array_elements_text(COALESCE((p_ci_job_definitions.config -> 'tag_list'::text), '[]'::jsonb)))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3
-> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.046..0.046 rows=0 loops=10)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=10)
SubPlan 3
-> Aggregate (cost=563.51..563.52 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
-> Sort (cost=562.87..563.19 rows=128 width=34) (actual time=0.000..0.000 rows=0 loops=0)
Sort Key: tags.name
-> Nested Loop (cost=1.01..558.39 rows=128 width=34) (actual time=0.000..0.000 rows=0 loops=0)
-> Append (cost=0.58..117.11 rows=128 width=8) (actual time=0.000..0.000 rows=0 loops=0)
-> Index Scan using ci_build_tags_100_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_100 p_ci_build_tags_1 (cost=0.58..39.10 rows=53 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_1.build_id = p_ci_builds.id) AND (p_ci_build_tags_1.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_101_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_101 p_ci_build_tags_2 (cost=0.57..14.84 rows=17 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_2.build_id = p_ci_builds.id) AND (p_ci_build_tags_2.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_102_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_102 p_ci_build_tags_3 (cost=0.58..28.56 rows=29 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_3.build_id = p_ci_builds.id) AND (p_ci_build_tags_3.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_103_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_103 p_ci_build_tags_4 (cost=0.57..6.36 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_4.build_id = p_ci_builds.id) AND (p_ci_build_tags_4.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_104_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_104 p_ci_build_tags_5 (cost=0.57..7.07 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_5.build_id = p_ci_builds.id) AND (p_ci_build_tags_5.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_105_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_105 p_ci_build_tags_6 (cost=0.57..7.07 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_6.build_id = p_ci_builds.id) AND (p_ci_build_tags_6.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_106_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_106 p_ci_build_tags_7 (cost=0.57..7.15 rows=6 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_7.build_id = p_ci_builds.id) AND (p_ci_build_tags_7.partition_id = p_ci_builds.partition_id))
-> Index Scan using ci_build_tags_107_build_id_partition_id_idx on gitlab_partitions_dynamic.ci_build_tags_107 p_ci_build_tags_8 (cost=0.57..6.33 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((p_ci_build_tags_8.build_id = p_ci_builds.id) AND (p_ci_build_tags_8.partition_id = p_ci_builds.partition_id))
-> Seq Scan on gitlab_partitions_dynamic.ci_build_tags_108 p_ci_build_tags_9 (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((p_ci_build_tags_9.build_id = p_ci_builds.id) AND (p_ci_build_tags_9.partition_id = p_ci_builds.partition_id))
Rows Removed by Filter: 0
-> Index Scan using tags_pkey on public.tags (cost=0.43..3.45 rows=1 width=38) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (tags.id = p_ci_build_tags.tag_id)
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'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.
-
Performance: Eager loading updated to include job_definition; memoization added for tag_list -
Reliability: Maintains backward compatibility with fallback to legacy tags; feature flag allows safe rollout -
Security: No security implications -
Maintainability: Simplifies data model by establishing job definitions as SSoT -
Testing: Comprehensive test coverage for all scenarios including edge cases -
Database: Part of planned migration to drop legacy tables; no immediate database changes
Edited by Pedro Pombeiro