Skip to content
Snippets Groups Projects

Add database structure for add-ons

Merged Corinna Gogolok requested to merge 408494_add_add_on_database_tables into master

What does this MR do and why?

Part of https://gitlab.com/gitlab-org/gitlab/-/issues/408494+

This change adds the models and their database tables for add-ons.

Database migrations

Up migrations

main: == [advisory_lock_connection] object_id: 227960, pg_backend_pid: 29328
main: == 20230531135001 CreateSubscriptionAddOnPurchases: migrating =================
main: -- create_table(:subscription_add_on_purchases, {:if_not_exists=>true})
main: -- quote_column_name(:purchase_xid)
main:    -> 0.0000s
main:    -> 0.0064s
main: == 20230531135001 CreateSubscriptionAddOnPurchases: migrated (0.0983s) ========

main: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE subscription_add_on_purchases ADD CONSTRAINT fk_410004d68b FOREIGN KEY (subscription_add_on_id) REFERENCES subscription_add_ons (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0015s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE subscription_add_on_purchases VALIDATE CONSTRAINT fk_410004d68b;")
main:    -> 0.0015s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: migrated (0.0380s) 

main: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE subscription_add_on_purchases ADD CONSTRAINT fk_a1db288990 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("ALTER TABLE subscription_add_on_purchases VALIDATE CONSTRAINT fk_a1db288990;")
main:    -> 0.0036s
main: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: migrated (0.0213s) 

main: == [advisory_lock_connection] object_id: 227960, pg_backend_pid: 29328
ci: == [advisory_lock_connection] object_id: 228480, pg_backend_pid: 29339
ci: == 20230531135001 CreateSubscriptionAddOnPurchases: migrating =================
ci: -- create_table(:subscription_add_on_purchases, {:if_not_exists=>true})
ci: -- quote_column_name(:purchase_xid)
ci:    -> 0.0001s
ci:    -> 0.0086s
I, [2023-06-07T11:09:14.903688 #28830]  INFO -- : Database: 'ci', Table: 'subscription_add_on_purchases': Lock Writes
ci: == 20230531135001 CreateSubscriptionAddOnPurchases: migrated (0.0194s) ========

ci: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: migrating 
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE subscription_add_on_purchases ADD CONSTRAINT fk_410004d68b FOREIGN KEY (subscription_add_on_id) REFERENCES subscription_add_ons (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0013s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE subscription_add_on_purchases VALIDATE CONSTRAINT fk_410004d68b;")
ci:    -> 0.0014s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: migrated (0.0254s) 

ci: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: migrating 
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE subscription_add_on_purchases ADD CONSTRAINT fk_a1db288990 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0013s
ci: -- execute("ALTER TABLE subscription_add_on_purchases VALIDATE CONSTRAINT fk_a1db288990;")
ci:    -> 0.0023s
ci: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: migrated (0.0217s) 

ci: == [advisory_lock_connection] object_id: 228480, pg_backend_pid: 29339

Down migrations

ci: == [advisory_lock_connection] object_id: 227720, pg_backend_pid: 30109
ci: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: reverting 
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:subscription_add_on_purchases, {:column=>:namespace_id})
ci:    -> 0.0042s
ci: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: reverted (0.0387s) 

ci: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: reverting 
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:subscription_add_on_purchases, {:column=>:subscription_add_on_id})
ci:    -> 0.0030s
ci: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: reverted (0.0167s) 

ci: == 20230531135001 CreateSubscriptionAddOnPurchases: reverting =================
ci: -- drop_table(:subscription_add_on_purchases, {:if_not_exists=>true})
ci:    -> 0.0013s
ci: == 20230531135001 CreateSubscriptionAddOnPurchases: reverted (0.0128s) ========

ci: == [advisory_lock_connection] object_id: 227720, pg_backend_pid: 30109

main: == [advisory_lock_connection] object_id: 227720, pg_backend_pid: 30527
main: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:subscription_add_on_purchases, {:column=>:namespace_id})
main:    -> 0.0041s
main: == 20230531142053 AddForeignKeyNamespaceIdOnSubscriptionAddOnPurchases: reverted (0.0271s) 

main: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:subscription_add_on_purchases, {:column=>:subscription_add_on_id})
main:    -> 0.0027s
main: == 20230531142032 AddForeignKeySubscriptionAddOnIdOnSubscriptionAddOnPurchases: reverted (0.0081s) 

main: == 20230531135001 CreateSubscriptionAddOnPurchases: reverting =================
main: -- drop_table(:subscription_add_on_purchases, {:if_not_exists=>true})
main:    -> 0.0012s
main: == 20230531135001 CreateSubscriptionAddOnPurchases: reverted (0.0062s) ========

main: == [advisory_lock_connection] object_id: 227720, pg_backend_pid: 30527

MR acceptance checklist

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

Edited by Corinna Gogolok

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • added workflowin review label and removed workflowin dev label

  • Corinna Gogolok requested review from @bhrai

    requested review from @bhrai

  • Contributor

    Allure report

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :exclamation: test report for c6ca6a8a

    expand test summary
    +-----------------------------------------------------------------------+
    |                            suites summary                             |
    +------------------+--------+--------+---------+-------+-------+--------+
    |                  | passed | failed | skipped | flaky | total | result |
    +------------------+--------+--------+---------+-------+-------+--------+
    | Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
    | Create           | 8      | 0      | 1       | 0     | 9     | ✅     |
    | Plan             | 4      | 0      | 0       | 0     | 4     | ✅     |
    | Manage           | 1      | 0      | 0       | 0     | 1     | ✅     |
    | Data Stores      | 2      | 0      | 0       | 1     | 2     | ❗     |
    | Govern           | 2      | 0      | 0       | 0     | 2     | ✅     |
    | Monitor          | 4      | 0      | 0       | 0     | 4     | ✅     |
    +------------------+--------+--------+---------+-------+-------+--------+
    | Total            | 21     | 0      | 2       | 1     | 23    | ❗     |
    +------------------+--------+--------+---------+-------+-------+--------+
  • Corinna Gogolok changed the description

    changed the description

  • Corinna Gogolok added 1 commit

    added 1 commit

    • 0e238b83 - Add database structure for add-ons

    Compare with previous version

    • Author Developer
      Resolved by Michał Zając

      @mhamda Do you mind reviewing this as the initial database reviewer? :pray_tone1:

      For the database maintainer review later on, maybe we can ping @Quintasan if they are available since they were involved in the issue discussion for this. Any other maintainer, preferably in the EU to hopefully minimize delays, is fine too though.

  • Corinna Gogolok requested review from @mhamda

    requested review from @mhamda

  • Corinna Gogolok added 1 commit

    added 1 commit

    • 0d77c14a - Add database structure for add-ons

    Compare with previous version

  • Corinna Gogolok marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

    marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

  • Contributor

    Database migrations (on the main database)

    2 Warnings
    :warning: 20230531134916 - CreateAddOns had a query that exceeded timing guidelines. Run time should
    not exceed 100ms, but it was 147.87ms. Please consider possible options to improve the query
    performance.
    CREATE TABLE "add_ons" ("id" bigserial primary key, "created_at" timestamptz
    NOT NULL, "updated_at" timestamptz NOT NULL, "name" smallint NOT NULL, "description" text)
    :warning: 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns had a query that exceeded timing
    guidelines
    . Run time should not exceed 100ms, but it was 463.17ms. Please consider possible options
    to improve the query performance.
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT
    fk_c873b07531

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20230531134916 - CreateAddOns Regular 2.3 s :warning: +32.00 KiB
    20230531134953 - AddTextLimitToDescriptionOnAddOns Regular 2.0 s :white_check_mark: +0.00 B
    20230531135001 - CreatePurchasedAddOns Regular 1.6 s :white_check_mark: +40.00 KiB
    20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns Regular 1.8 s :white_check_mark: +0.00 B
    20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns Regular 2.0 s :white_check_mark: +0.00 B
    20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns Regular 2.5 s :warning: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 27
    0.1 seconds - 1 second 4
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20230531134916 - CreateAddOns

    • Type: Regular
    • Duration: 2.3 s
    • Database size change: +32.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 147.9 ms 147.9 ms 147.9 ms 0
    CREATE TABLE "add_ons" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "name" smallint NOT NULL, "description" text)
    1 1.5 ms 1.5 ms 1.5 ms 0
    CREATE UNIQUE INDEX "index_add_ons_on_name" ON "add_ons" ("name")
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531134953 - AddTextLimitToDescriptionOnAddOns

    • Type: Regular
    • Duration: 2.0 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 7.4 ms 7.4 ms 7.4 ms 0
    ALTER TABLE add_ons ADD CONSTRAINT check_c0a5af7c17 CHECK ( char_length(description) <= 512 ) NOT VALID
    1 6.2 ms 6.2 ms 6.2 ms 0
    ALTER TABLE add_ons VALIDATE CONSTRAINT check_c0a5af7c17
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddTextLimitToDescriptionOnAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531135001 - CreatePurchasedAddOns

    • Type: Regular
    • Duration: 1.6 s
    • Database size change: +40.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 20.2 ms 20.2 ms 20.2 ms 0
    CREATE TABLE IF NOT EXISTS "purchased_add_ons" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "add_on_id" bigint NOT NULL, "namespace_id" bigint NOT NULL, "quantity" integer NOT NULL, "expires_on" date NOT NULL, "purchase_xid" text NOT NULL)
    1 3.4 ms 3.4 ms 3.4 ms 0
    CREATE INDEX IF NOT EXISTS "index_purchased_add_ons_on_namespace_id" ON "purchased_add_ons" ("namespace_id")
    1 1.0 ms 1.0 ms 1.0 ms 0
    CREATE INDEX IF NOT EXISTS "index_purchased_add_ons_on_add_on_id" ON "purchased_add_ons" ("add_on_id")
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreatePurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 5
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns

    • Type: Regular
    • Duration: 1.8 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 0.5 ms 0.5 ms 0.5 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT check_cd64515556 CHECK ( char_length(purchase_xid) <= 255 ) NOT VALID
    1 0.3 ms 0.3 ms 0.3 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT check_cd64515556
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddTextLimitToPurchaseXidOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns

    • Type: Regular
    • Duration: 2.0 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 73.2 ms 73.2 ms 73.2 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT fk_de3fea9760 FOREIGN KEY (add_on_id) REFERENCES add_ons (id) ON DELETE CASCADE NOT VALID
    1 1.2 ms 1.2 ms 1.2 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT fk_de3fea9760
    1 0.8 ms 0.8 ms 0.8 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 0.4 ms 0.2 ms 0.2 ms 0
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddForeignKeyAddOnIdOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 6
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns

    • Type: Regular
    • Duration: 2.5 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 463.2 ms 463.2 ms 463.2 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT fk_c873b07531
    1 95.1 ms 95.1 ms 95.1 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT fk_c873b07531 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    2 0.4 ms 0.2 ms 0.2 ms 0
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.0 ms 0.0 ms 0.0 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddForeignKeyNamespaceIdOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 5
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Background Migration: BackfillRootStorageStatisticsForkStorageSizes

    Sampled 16 batches. Estimated Time to complete: 6 days, 17 hours, and 18 minutes
    • Interval: 120s
    • Total tuple count: 4839056
    • Max batch size:
    • Estimated seconds to complete: 580680s
    • Estimated number of batches: 4839
    • Average batch time: 117.27s
    • Batch size: 1000
    • N. of batches sampled: 16
    • N. of failed batches: 0

    Time estimation is conservative and based on sampling production data in a test environment. It represents the max time that migration could take. The actual time may differ from this estimation.

    _Consider changing max_batch_size and interval if this estimate is unacceptable._

    Calls Total Time Max Time Mean Time Rows Query
    14910 83614.8 ms 27627.9 ms 5.6 ms 14910
    SELECT type
    FROM namespaces
    WHERE id = $1
    8504 154763.5 ms 20506.4 ms 18.2 ms 1194
    SELECT SUM("project_statistics"."storage_size") AS sum_project_statistics_storage_size, "projects"."visibility_level" AS projects_visibility_level
    FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" INNER JOIN "fork_network_members" ON "fork_network_members"."project_id" = "projects"."id" INNER JOIN "fork_networks" ON "fork_networks"."id" = "fork_network_members"."fork_network_id" WHERE "projects"."namespace_id" = $1 AND (fork_networks.root_project_id != projects.id)
    GROUP BY "projects"."visibility_level"
    6406 115530.1 ms 13740.4 ms 18.0 ms 175
    SELECT SUM("project_statistics"."storage_size") AS sum_project_statistics_storage_size, "projects"."visibility_level" AS projects_visibility_level
    FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" INNER JOIN "fork_network_members" ON "fork_network_members"."project_id" = "projects"."id" INNER JOIN "fork_networks" ON "fork_networks"."id" = "fork_network_members"."fork_network_id" WHERE "projects"."namespace_id" IN (
    SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, $1)] AS id
    FROM "namespaces" WHERE "namespaces"."type" = $2 AND (traversal_ids @> ($3))
    ) AND (fork_networks.root_project_id != projects.id)
    GROUP BY "projects"."visibility_level"
    135 26252.2 ms 449.3 ms 194.5 ms 13500
    SELECT "namespace_root_storage_statistics".*
    FROM "namespace_root_storage_statistics" WHERE "namespace_root_storage_statistics"."namespace_id" BETWEEN $1 AND $2 AND "namespace_root_storage_statistics"."namespace_id" >= $3 AND "namespace_root_storage_statistics"."namespace_id" < $4
    16 3436.7 ms 423.1 ms 214.8 ms 1567
    SELECT "namespace_root_storage_statistics".*
    FROM "namespace_root_storage_statistics" WHERE "namespace_root_storage_statistics"."namespace_id" BETWEEN $1 AND $2 AND "namespace_root_storage_statistics"."namespace_id" >= $3
    576 1718.4 ms 218.0 ms 3.0 ms 576
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "public_forks_storage_size" = $2
    WHERE "namespace_root_storage_statistics"."namespace_id" = $3
    661 1779.9 ms 83.2 ms 2.7 ms 661
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "private_forks_storage_size" = $2
    WHERE "namespace_root_storage_statistics"."namespace_id" = $3
    32 74.9 ms 68.9 ms 2.3 ms 32
    INSERT INTO "batched_background_migration_job_transition_logs" ("batched_background_migration_job_id", "created_at", "updated_at", "previous_status", "next_status") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
    1 35.2 ms 35.2 ms 35.2 ms 1
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "public_forks_storage_size" = $2, "internal_forks_storage_size" = $3, "private_forks_storage_size" = $4
    WHERE "namespace_root_storage_statistics"."namespace_id" = $5
    16 47.5 ms 17.8 ms 3.0 ms 16
    UPDATE "batched_background_migration_jobs"  SET "updated_at" = $1, "finished_at" = $2, "status" = $3, "metrics" = $4
    WHERE "batched_background_migration_jobs"."id" = $5
    16 28.2 ms 17.0 ms 1.8 ms 16
    UPDATE "batched_background_migration_jobs"  SET "updated_at" = $1, "started_at" = $2, "status" = $3, "attempts" = $4
    WHERE "batched_background_migration_jobs"."id" = $5
    4 26.2 ms 10.1 ms 6.5 ms 4
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "internal_forks_storage_size" = $2
    WHERE "namespace_root_storage_statistics"."namespace_id" = $3
    43 63.9 ms 8.7 ms 1.5 ms 43
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "public_forks_storage_size" = $2, "private_forks_storage_size" = $3
    WHERE "namespace_root_storage_statistics"."namespace_id" = $4
    151 19.7 ms 2.2 ms 0.1 ms 135
    SELECT "namespace_root_storage_statistics"."namespace_id"  FROM "namespace_root_storage_statistics"  WHERE "namespace_root_storage_statistics"."namespace_id" BETWEEN $1 AND $2 AND "namespace_root_storage_statistics"."namespace_id" >= $3
    ORDER BY "namespace_root_storage_statistics"."namespace_id" ASC
    LIMIT $4
    OFFSET $5
    1 1.4 ms 1.4 ms 1.4 ms 1
    UPDATE "namespace_root_storage_statistics"  SET "updated_at" = $1, "public_forks_storage_size" = $2, "internal_forks_storage_size" = $3
    WHERE "namespace_root_storage_statistics"."namespace_id" = $4
    32 2.6 ms 1.1 ms 0.1 ms 32
    SELECT "batched_background_migration_jobs".*
    FROM "batched_background_migration_jobs" WHERE "batched_background_migration_jobs"."id" = $1
    LIMIT $2
    16 1.2 ms 0.1 ms 0.1 ms 16
    SELECT SUM("batched_background_migration_jobs"."batch_size")
    FROM "batched_background_migration_jobs" WHERE "batched_background_migration_jobs"."batched_background_migration_id" = $1 AND ("batched_background_migration_jobs"."status" IN ($2))
    16 0.6 ms 0.1 ms 0.0 ms 16
    SELECT "batched_background_migrations".*
    FROM "batched_background_migrations" WHERE "batched_background_migrations"."id" = $1
    LIMIT $2
    16 0.6 ms 0.1 ms 0.0 ms 16
    SELECT "namespace_root_storage_statistics"."namespace_id"  FROM "namespace_root_storage_statistics"  WHERE "namespace_root_storage_statistics"."namespace_id" BETWEEN $1 AND $2
    ORDER BY "namespace_root_storage_statistics"."namespace_id" ASC
    LIMIT $3
    Histogram of batch runtimes for BackfillRootStorageStatisticsForkStorageSizes
    Batch Runtime Count
    0 seconds - 10 seconds 1
    10 seconds - 1 minute 0
    1 minute - 2 minutes 10
    2 minutes - 3 minutes 4
    3 minutes - 5 minutes 1
    5 minutes + 0
    Histogram across all sampled batches of BackfillRootStorageStatisticsForkStorageSizes
    Query Runtime Count
    0 seconds - 0.1 seconds 30444
    0.1 seconds - 0.5 seconds 1072
    0.5 seconds - 1 second 26
    1 second - 2 seconds 5
    2 seconds - 5 seconds 2
    5 seconds + 3

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20230517163300 - QueueBackfillRootStorageStatisticsForkStorageSizes Post deploy 2.3 s :white_check_mark: +0.00 B
    20230530114845 - CleanupOrganizationsWithNullPath Post deploy 1.5 s :white_check_mark: +0.00 B
    20230530115830 - RemoveDefaultOnOrganizationPath Post deploy 1.4 s :white_check_mark: +0.00 B
    20230602063059 - RemoveBroadcastMessagesNamespaceIdColumn Post deploy 2.0 s :white_check_mark: -16.00 KiB
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-1998068-10251113-main 2023-06-06T09:37:29Z 2023-06-06T08:10:39Z 2023-06-06 22:13:55 +0000
    database-testing-1998068-10251113-ci 2023-06-06T09:37:29Z 2023-06-06T08:47:36Z 2023-06-06 22:13:55 +0000

    Job artifacts

    Database migrations (on the ci database)

    2 Warnings
    :warning: 20230531134916 - CreateAddOns had a query that exceeded timing guidelines. Run time should
    not exceed 100ms, but it was 147.14ms. Please consider possible options to improve the query
    performance.
    CREATE TABLE "add_ons" ("id" bigserial primary key, "created_at" timestamptz
    NOT NULL, "updated_at" timestamptz NOT NULL, "name" smallint NOT NULL, "description" text)
    :warning: 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns had a query that exceeded timing
    guidelines
    . Run time should not exceed 100ms, but it was 254.06ms. Please consider possible options
    to improve the query performance.
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT
    fk_c873b07531

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20230531134916 - CreateAddOns Regular 2.8 s :warning: +32.00 KiB
    20230531134953 - AddTextLimitToDescriptionOnAddOns Regular 2.5 s :white_check_mark: +0.00 B
    20230531135001 - CreatePurchasedAddOns Regular 2.3 s :white_check_mark: +40.00 KiB
    20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns Regular 2.4 s :white_check_mark: +0.00 B
    20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns Regular 2.5 s :white_check_mark: +0.00 B
    20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns Regular 2.9 s :warning: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 33
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20230531134916 - CreateAddOns

    • Type: Regular
    • Duration: 2.8 s
    • Database size change: +32.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 147.1 ms 147.1 ms 147.1 ms 0
    CREATE TABLE "add_ons" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "name" smallint NOT NULL, "description" text)
    1 20.0 ms 20.0 ms 20.0 ms 0
    CREATE TRIGGER gitlab_schema_write_trigger_for_add_ons BEFORE INSERT OR
    UPDATE OR DELETE OR TRUNCATE ON add_ons FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
    1 4.9 ms 4.9 ms 4.9 ms 0
    CREATE UNIQUE INDEX "index_add_ons_on_name" ON "add_ons" ("name")
    1 0.0 ms 0.0 ms 0.0 ms 1
    SELECT COUNT(*) from information_schema.triggers
    WHERE event_object_table = $1 AND trigger_name = $2
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 5
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531134953 - AddTextLimitToDescriptionOnAddOns

    • Type: Regular
    • Duration: 2.5 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 2.4 ms 2.4 ms 2.4 ms 0
    ALTER TABLE add_ons ADD CONSTRAINT check_c0a5af7c17 CHECK ( char_length(description) <= 512 ) NOT VALID
    1 0.3 ms 0.3 ms 0.3 ms 0
    ALTER TABLE add_ons VALIDATE CONSTRAINT check_c0a5af7c17
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddTextLimitToDescriptionOnAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531135001 - CreatePurchasedAddOns

    • Type: Regular
    • Duration: 2.3 s
    • Database size change: +40.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 6.6 ms 6.6 ms 6.6 ms 0
    CREATE TABLE IF NOT EXISTS "purchased_add_ons" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "add_on_id" bigint NOT NULL, "namespace_id" bigint NOT NULL, "quantity" integer NOT NULL, "expires_on" date NOT NULL, "purchase_xid" text NOT NULL)
    1 3.4 ms 3.4 ms 3.4 ms 0
    CREATE TRIGGER gitlab_schema_write_trigger_for_purchased_add_ons BEFORE INSERT OR
    UPDATE OR DELETE OR TRUNCATE ON purchased_add_ons FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
    1 1.1 ms 1.1 ms 1.1 ms 0
    CREATE INDEX IF NOT EXISTS "index_purchased_add_ons_on_add_on_id" ON "purchased_add_ons" ("add_on_id")
    1 0.9 ms 0.9 ms 0.9 ms 0
    CREATE INDEX IF NOT EXISTS "index_purchased_add_ons_on_namespace_id" ON "purchased_add_ons" ("namespace_id")
    1 0.0 ms 0.0 ms 0.0 ms 1
    SELECT COUNT(*) from information_schema.triggers
    WHERE event_object_table = $1 AND trigger_name = $2
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreatePurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 7
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns

    • Type: Regular
    • Duration: 2.4 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 0.5 ms 0.5 ms 0.5 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT check_cd64515556 CHECK ( char_length(purchase_xid) <= 255 ) NOT VALID
    1 0.4 ms 0.4 ms 0.4 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT check_cd64515556
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddTextLimitToPurchaseXidOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns

    • Type: Regular
    • Duration: 2.5 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 4.8 ms 4.8 ms 4.8 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT fk_de3fea9760 FOREIGN KEY (add_on_id) REFERENCES add_ons (id) ON DELETE CASCADE NOT VALID
    1 1.8 ms 1.8 ms 1.8 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT fk_de3fea9760
    1 0.9 ms 0.9 ms 0.9 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 0.6 ms 0.3 ms 0.3 ms 0
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddForeignKeyAddOnIdOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 7
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    :warning: Migration: 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns

    • Type: Regular
    • Duration: 2.9 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 254.1 ms 254.1 ms 254.1 ms 0
    ALTER TABLE purchased_add_ons VALIDATE CONSTRAINT fk_c873b07531
    1 14.3 ms 14.3 ms 14.3 ms 0
    ALTER TABLE purchased_add_ons ADD CONSTRAINT fk_c873b07531 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID
    2 0.6 ms 0.3 ms 0.3 ms 0
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.0 ms 0.0 ms 0.0 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddForeignKeyNamespaceIdOnPurchasedAddOns
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 6
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20230517163300 - QueueBackfillRootStorageStatisticsForkStorageSizes Post deploy 2.0 s :white_check_mark: +0.00 B
    20230530114845 - CleanupOrganizationsWithNullPath Post deploy 2.1 s :white_check_mark: +0.00 B
    20230530115830 - RemoveDefaultOnOrganizationPath Post deploy 2.1 s :white_check_mark: +0.00 B
    20230602063059 - RemoveBroadcastMessagesNamespaceIdColumn Post deploy 2.1 s :white_check_mark: -8.00 KiB
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-1998068-10251113-main 2023-06-06T09:37:29Z 2023-06-06T08:10:39Z 2023-06-06 22:13:55 +0000
    database-testing-1998068-10251113-ci 2023-06-06T09:37:29Z 2023-06-06T08:47:36Z 2023-06-06 22:13:55 +0000

    Job artifacts


    Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

  • Bishwa Hang Rai approved this merge request

    approved this merge request

  • Bishwa Hang Rai requested review from @splattael and removed review request for @bhrai

    requested review from @splattael and removed review request for @bhrai

  • Mohamed Hamda
  • Corinna Gogolok added 1 commit

    added 1 commit

    • ee3c1846 - Address database review feedback

    Compare with previous version

  • Corinna Gogolok changed the description

    changed the description

  • Mohamed Hamda approved this merge request

    approved this merge request

  • :wave: @mhamda, thanks for approving this merge request.

    This is the first time the merge request is approved. To ensure full test coverage, a new pipeline will be started shortly.

    For more info, please refer to the following links:

  • added databasereviewed label and removed databasereview pending label

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Please register or sign in to reply
    Loading