Prepare DevOps Adoption DB structure for migration (!60733) · Merge requests · GitLab.org / GitLab

Pavel Shutsin requested to merge 329521-store-segment-target-groups into master Apr 30, 2021

What does this MR do?

As part of #329521 (closed) we prepare DB structure to separate "target" namespace from "display_namespace" concepts for Devops Adoption.

This particular MR introduces new fields and double-write them.

Migration details

Migration output

== 20210430135954 CopyAdoptionSegmentsNamespace: reverting ====================
-- execute("UPDATE analytics_devops_adoption_segments SET display_namespace_id = NULL")
   -> 0.0073s
== 20210430135954 CopyAdoptionSegmentsNamespace: reverted (0.0073s) ===========

== 20210430134202 CopyAdoptionSnapshotNamespace: reverting ====================
-- execute("UPDATE analytics_devops_adoption_snapshots SET namespace_id = NULL")
   -> 0.0054s
== 20210430134202 CopyAdoptionSnapshotNamespace: reverted (0.0055s) ===========

== 20210430130259 RemoveObsoleteSegmentsField: reverting ======================
-- add_column(:analytics_devops_adoption_segments, :name, :text)
   -> 0.0043s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0016s
-- execute("ALTER TABLE analytics_devops_adoption_segments\nADD CONSTRAINT check_4be7a006fd\nCHECK ( char_length(name) <= 255 )\nNOT VALID;\n")
   -> 0.0023s
-- current_schema()
   -> 0.0012s
-- execute("ALTER TABLE analytics_devops_adoption_segments VALIDATE CONSTRAINT check_4be7a006fd;")
   -> 0.0023s
== 20210430130259 RemoveObsoleteSegmentsField: reverted (0.0362s) =============

== 20210430124630 AddDevopsAdoptionIndexes: reverting =========================
-- transaction_open?()
   -> 0.0000s
-- indexes(:analytics_devops_adoption_segments)
   -> 0.0054s
-- remove_index(:analytics_devops_adoption_segments, {:algorithm=>:concurrently, :name=>"idx_devops_adoption_segments_namespaces_pair"})
   -> 0.0051s
-- transaction_open?()
   -> 0.0000s
-- indexes(:analytics_devops_adoption_snapshots)
   -> 0.0081s
-- remove_index(:analytics_devops_adoption_snapshots, {:algorithm=>:concurrently, :name=>"idx_devops_adoption_segments_namespace_end_time"})
   -> 0.0041s
-- transaction_open?()
   -> 0.0000s
-- indexes(:analytics_devops_adoption_snapshots)
   -> 0.0088s
-- remove_index(:analytics_devops_adoption_snapshots, {:algorithm=>:concurrently, :name=>"idx_devops_adoption_segments_namespace_recorded_at"})
   -> 0.0026s
-- remove_foreign_key(:analytics_devops_adoption_snapshots, :namespaces, {:column=>:namespace_id})
   -> 0.0082s
-- remove_foreign_key(:analytics_devops_adoption_segments, :namespaces, {:column=>:display_namespace_id})
   -> 0.0078s
== 20210430124630 AddDevopsAdoptionIndexes: reverted (0.0568s) ================

== 20210430124212 AddDisplayNamespaceIdToSegments: reverting ==================
-- remove_column(:analytics_devops_adoption_segments, :display_namespace_id, :integer)
   -> 0.0059s
== 20210430124212 AddDisplayNamespaceIdToSegments: reverted (0.0082s) =========

== 20210430122951 AddSnapshotNamespaceId: reverting ===========================
-- remove_column(:analytics_devops_adoption_snapshots, :namespace_id, :integer)
   -> 0.0066s
== 20210430122951 AddSnapshotNamespaceId: reverted (0.0067s) ==================

== 20210430122951 AddSnapshotNamespaceId: migrating ===========================
-- add_column(:analytics_devops_adoption_snapshots, :namespace_id, :integer)
   -> 0.0065s
== 20210430122951 AddSnapshotNamespaceId: migrated (0.0066s) ==================

== 20210430124212 AddDisplayNamespaceIdToSegments: migrating ==================
-- add_column(:analytics_devops_adoption_segments, :display_namespace_id, :integer)
   -> 0.0058s
== 20210430124212 AddDisplayNamespaceIdToSegments: migrated (0.0058s) =========

== 20210430124630 AddDevopsAdoptionIndexes: migrating =========================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:analytics_devops_adoption_snapshots, [:namespace_id, :end_time], {:name=>"idx_devops_adoption_segments_namespace_end_time", :algorithm=>:concurrently})
   -> 0.0072s
-- add_index(:analytics_devops_adoption_snapshots, [:namespace_id, :end_time], {:name=>"idx_devops_adoption_segments_namespace_end_time", :algorithm=>:concurrently})
   -> 0.0127s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:analytics_devops_adoption_snapshots, [:namespace_id, :recorded_at], {:name=>"idx_devops_adoption_segments_namespace_recorded_at", :algorithm=>:concurrently})
   -> 0.0078s
-- add_index(:analytics_devops_adoption_snapshots, [:namespace_id, :recorded_at], {:name=>"idx_devops_adoption_segments_namespace_recorded_at", :algorithm=>:concurrently})
   -> 0.0113s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:analytics_devops_adoption_segments, [:display_namespace_id, :namespace_id], {:unique=>true, :name=>"idx_devops_adoption_segments_namespaces_pair", :algorithm=>:concurrently})
   -> 0.0057s
-- add_index(:analytics_devops_adoption_segments, [:display_namespace_id, :namespace_id], {:unique=>true, :name=>"idx_devops_adoption_segments_namespaces_pair", :algorithm=>:concurrently})
   -> 0.0094s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:analytics_devops_adoption_snapshots)
   -> 0.0052s
-- execute("ALTER TABLE analytics_devops_adoption_snapshots\nADD CONSTRAINT fk_78c9eac821\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0044s
-- execute("ALTER TABLE analytics_devops_adoption_snapshots VALIDATE CONSTRAINT fk_78c9eac821;")
   -> 0.0057s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:analytics_devops_adoption_segments)
   -> 0.0055s
-- execute("ALTER TABLE analytics_devops_adoption_segments\nADD CONSTRAINT fk_190a24754d\nFOREIGN KEY (display_namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0061s
-- execute("ALTER TABLE analytics_devops_adoption_segments VALIDATE CONSTRAINT fk_190a24754d;")
   -> 0.0237s
== 20210430124630 AddDevopsAdoptionIndexes: migrated (0.1366s) ================

== 20210430130259 RemoveObsoleteSegmentsField: migrating ======================
-- remove_column(:analytics_devops_adoption_segments, :name)
   -> 0.0034s
== 20210430130259 RemoveObsoleteSegmentsField: migrated (0.0035s) =============

== 20210430134202 CopyAdoptionSnapshotNamespace: migrating ====================
-- execute("    UPDATE analytics_devops_adoption_snapshots snapshots\n      SET namespace_id = segments.namespace_id\n    FROM analytics_devops_adoption_segments segments\n    WHERE snapshots.namespace_id IS NULL AND segments.id = snapshots.segment_id\n")
   -> 0.0065s
== 20210430134202 CopyAdoptionSnapshotNamespace: migrated (0.0065s) ===========

== 20210430135954 CopyAdoptionSegmentsNamespace: migrating ====================
-- execute("    UPDATE analytics_devops_adoption_segments SET display_namespace_id = namespace_id\n    WHERE display_namespace_id IS NULL\n")
   -> 0.0056s
== 20210430135954 CopyAdoptionSegmentsNamespace: migrated (0.0057s) ===========

Data migrations explains

# explain UPDATE analytics_devops_adoption_segments SET display_namespace_id = namespace_id WHERE display_namespace_id IS NULL

ModifyTable on public.analytics_devops_adoption_segments  (cost=0.14..3.16 rows=1 width=78) (actual time=0.082..0.083 rows=0 loops=1)
   Buffers: shared read=1
   I/O Timings: read=0.046
   ->  Index Scan using tmp_indx on public.analytics_devops_adoption_segments  (cost=0.14..3.16 rows=1 width=78) (actual time=0.081..0.081 rows=0 loops=1)
         Index Cond: (analytics_devops_adoption_segments.display_namespace_id IS NULL)
         Buffers: shared read=1
         I/O Timings: read=0.046
# total number of rows in analytics_devops_adoption_segments - 80


# explain UPDATE analytics_devops_adoption_snapshots snapshots SET namespace_id = segments.namespace_id FROM analytics_devops_adoption_segments segments WHERE snapshots.namespace_id IS NULL AND segments.id = snapshots.segment_id

ModifyTable on public.analytics_devops_adoption_snapshots snapshots  (cost=0.29..6.39 rows=1 width=63) (actual time=6.343..6.344 rows=0 loops=1)
   Buffers: shared hit=783 read=7 dirtied=8
   I/O Timings: read=5.226
   ->  Nested Loop  (cost=0.29..6.39 rows=1 width=63) (actual time=0.122..0.310 rows=79 loops=1)
         Buffers: shared hit=159 read=1
         I/O Timings: read=0.057
         ->  Index Scan using tmp_index_snapshots on public.analytics_devops_adoption_snapshots snapshots  (cost=0.14..3.16 rows=1 width=53) (actual time=0.104..0.142 rows=79 loops=1)
               Index Cond: (snapshots.namespace_id IS NULL)
               Buffers: shared hit=1 read=1
               I/O Timings: read=0.057
         ->  Index Scan using analytics_devops_adoption_segments_pkey on public.analytics_devops_adoption_segments segments  (cost=0.14..3.16 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=79)
               Index Cond: (segments.id = snapshots.segment_id)
               Buffers: shared hit=158
# total number of rows in analytics_devops_adoption_snapshots - 79

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Related to #329521 (closed)

Edited May 13, 2021 by Pavel Shutsin

Prepare DevOps Adoption DB structure for migration