Deduplicate DevOps Adoption snapshots and add a unique index per namespace and month
Part of Project Bulkhead. Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/636118
analytics_devops_adoption_snapshots has no unique index on (namespace_id, end_time). Analytics::DevopsAdoption::Snapshots::CalculateAndSaveService looks the month up with .first and inserts when it finds nothing, so two CreateSnapshotWorker jobs for the same namespace that both look up before either saves write two snapshots for the same month. The bulk enable path produces exactly that whenever a namespace is enabled under two display namespaces, because each enabled row gets its own job. Once two rows exist, EnabledNamespace#latest_snapshot and Snapshot.for_month(...).first pick one of them arbitrarily, and the nightly worker keeps recalculating whichever non-finalised copy it happens to see.
This MR closes the hole in three steps:
- A post-deployment migration removes the existing duplicates, keeping the newest row per
(namespace_id, end_time)(highestrecorded_at, then highestid). The table is documented astable_size: small, so this is a regulareach_batchloop in the migration rather than a batched background migration. - A post-deployment migration adds a unique index on
(namespace_id, end_time), and a third one dropsidx_devops_adoption_segments_namespace_end_time, the non-unique index on the same two columns that the unique index makes redundant. CalculateAndSaveServiceusesfirst_or_initializeon the month scope and always saves throughUpdateService, so a second run for the same month updates the existing row. If two jobs still race past the lookup, the loser now getsActiveRecord::RecordNotUniquefrom the index; the service rescues that once, reloads the row the other job inserted, and updates it instead of failing the job.
The 1,000-row cap on enabled namespaces per display namespace from the same issue is a separate MR, !267369 (merged), and is not touched here.
Business impact
DevOps Adoption keeps one snapshot per namespace per month, but the table has no unique index on (namespace_id, end_time) and the writer does "look up the month, insert if missing". Two snapshot jobs for the same namespace that both look up before either saves write two rows for the same month, which happens whenever a namespace is enabled under two display groups. The report and the nightly recalculation then pick one of the two arbitrarily, so a customer can see a month's adoption flags change between page loads and the nightly job keeps recalculating whichever copy it finds. The production run of the dedupe migration confirmed that such duplicates exist on GitLab.com.
This MR deletes the older duplicate rows (keeping the newest row per namespace and month), adds the unique index, drops the non-unique index it replaces, and makes the service update the existing row instead of inserting a second one, falling back to an update if two jobs still race. Nothing changes on the happy path and there is no new limit or error; every namespace ends up with exactly one row per month, the report is stable and the nightly job stops redoing work. Not behind a feature flag: this is a data fix with reversible index migrations and a no-op down for the dedupe, since every deleted row has a newer row for the same month that survives, and any month can be recalculated from the source data (see Database review).
Database review
Production run of the migrations
The db:gitlabcom-database-testing job on pipeline 3025582948 ran all three migrations against a GitLab.com clone (report: !267456 (comment 4156732944)), so the duplicate check the issue asks for is answered by the dedupe itself:
| Migration | Runtime | Result |
|---|---|---|
RemoveDuplicateAnalyticsDevopsAdoptionSnapshots |
111.6 s, 505 batches | 713 older duplicate rows deleted; per-batch DELETE mean 7.8 ms, max 347 ms |
AddUniqueIndexDevopsAdoptionSnapshotsNamespaceIdEndTime |
3.7 s | +15.18 MiB |
RemoveIdxDevopsAdoptionSegmentsNamespaceEndTime |
4.4 s | -20.60 MiB |
Duplicates do exist in production, so the unique index cannot be added without the dedupe. One batch exceeded the 100 ms single-query guideline (347 ms); the delete is a post-deployment migration bounded to 1,000 ids per statement with a 7.8 ms mean, and no batch came near the migration timing limits.
The standalone count, for anyone who wants to re-check on Database Lab before the migration runs:
SELECT count(*) AS duplicate_groups,
sum(rows_per_group - 1) AS surplus_rows,
max(rows_per_group) AS max_rows_per_group
FROM (
SELECT namespace_id, end_time, count(*) AS rows_per_group
FROM analytics_devops_adoption_snapshots
GROUP BY namespace_id, end_time
HAVING count(*) > 1
) duplicates;RemoveDuplicateAnalyticsDevopsAdoptionSnapshots (post-deployment, gitlab_main_org)
Iterates the table by id in batches of 1,000 and issues one DELETE per batch. A row is deleted when a sibling for the same namespace and month was recorded later, or at the same time with a higher id, so exactly one row per group survives and it is the most recently calculated one. The EXISTS probe is served by the existing (namespace_id, end_time) index.
DELETE FROM analytics_devops_adoption_snapshots
WHERE analytics_devops_adoption_snapshots.id >= $batch_start
AND analytics_devops_adoption_snapshots.id < $batch_end
AND EXISTS (
SELECT 1
FROM analytics_devops_adoption_snapshots newer
WHERE newer.namespace_id = analytics_devops_adoption_snapshots.namespace_id
AND newer.end_time = analytics_devops_adoption_snapshots.end_time
AND (newer.recorded_at, newer.id) > (analytics_devops_adoption_snapshots.recorded_at, analytics_devops_adoption_snapshots.id)
);Every deleted row is an older calculation of a month whose newest row survives, so the report keeps showing the latest calculation for each month.
Recovery: snapshots are derived data. If a month's adoption flags look wrong after the dedupe, Snapshots::CalculateAndSaveService.new(enabled_namespace:, range_end:).execute recalculates that month from the source tables and updates the surviving row. The nightly CreateAllSnapshotsWorker does this on its own for every month that is not yet finalised (recorded_at < end_time); a finalised month is recalculated by running the service for that enabled namespace and month end from a Rails console. down is a no-op because there is nothing to restore that a recalculation does not rebuild.
AddUniqueIndexDevopsAdoptionSnapshotsNamespaceIdEndTime (post-deployment)
CREATE UNIQUE INDEX CONCURRENTLY index_devops_adoption_snapshots_on_namespace_id_and_end_time
ON analytics_devops_adoption_snapshots USING btree (namespace_id, end_time);The full-table-name form of the index name exceeds 63 characters, so the analytics_ prefix is dropped, matching the table's existing idx_devops_adoption_segments_* indexes.
RemoveIdxDevopsAdoptionSegmentsNamespaceEndTime (post-deployment)
DROP INDEX CONCURRENTLY idx_devops_adoption_segments_namespace_end_time;Same columns and order as the new unique index, so every query it served (including the namespace_id foreign key lookups) is served by the unique index. idx_analytics_devops_adoption_snapshots_finalized (partial, recorded_at >= end_time) and idx_devops_adoption_segments_namespace_recorded_at stay.
Application queries
The month lookup in CalculateAndSaveService is unchanged (WHERE namespace_id = $1 AND end_time = $2 ORDER BY id LIMIT 1); first_or_initialize only changes what happens when it returns nothing. No new query is introduced.
Captured from log/test.log of ee/spec/services/analytics/devops_adoption/snapshots/calculate_and_save_service_spec.rb (literal values replaced):
SELECT "analytics_devops_adoption_snapshots".* FROM "analytics_devops_adoption_snapshots" WHERE "analytics_devops_adoption_snapshots"."namespace_id" = <namespace_id> AND "analytics_devops_adoption_snapshots"."end_time" = '<month_end>' ORDER BY "analytics_devops_adoption_snapshots"."id" ASC LIMIT 1
INSERT INTO "analytics_devops_adoption_snapshots" ("recorded_at", "issue_opened", "merge_request_opened", "merge_request_approved", "runner_configured", "pipeline_succeeded", "deploy_succeeded", "end_time", "total_projects_count", "namespace_id") VALUES (<calculated values>) RETURNING "id"
UPDATE "analytics_devops_adoption_snapshots" SET <changed metric columns>, "updated_at" = '<now>' WHERE "analytics_devops_adoption_snapshots"."id" = <snapshot_id>The lookup is served by the new unique index; the retry after RecordNotUnique re-runs the same lookup and takes the UPDATE path.
Verification
RAILS_ENV=test bin/rails db:migrate, thendb:rollback:main STEP=3anddb:migrateagain on a local test database: all three migrations apply and revert; the regenerateddb/structure.sqlwas byte-identical across both runs.bundle exec rspec spec/migrations/20261103101119_remove_duplicate_analytics_devops_adoption_snapshots_spec.rb ee/spec/services/analytics/devops_adoption/snapshots/calculate_and_save_service_spec.rb: 6 examples, 0 failures.- RuboCop on the six changed files: no offenses.
- Not run here: the Database Lab duplicate count (see above) and
db:gitlabcom-database-testing, which I will trigger from the pipeline.
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.