Add database structure for add-ons
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.
-
I have evaluated the MR acceptance checklist for this MR.
Merge request reports
Activity
changed milestone to %16.1
assigned to @cwiesner
added database label
- A deleted user
added databasereview pending documentation labels
2 Messages This merge request adds or changes files that require a review from the Database team. This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge. This merge request requires a database review. To make sure these changes are reviewed, take the following steps:
-
Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
-
Prepare your MR for database review according to the docs.
-
Assign and mention the database reviewer suggested by Reviewer Roulette.
-
Kick off the
db:gitlabcom-database-testing
manual job. This job can also be used before requesting review to test your migrations against production data.
The following files require a review from the Database team:
db/migrate/20230531134916_create_subscription_add_ons.rb
db/migrate/20230531135001_create_subscription_add_on_purchases.rb
db/migrate/20230531142032_add_foreign_key_subscription_add_on_id_on_subscription_add_on_purchases.rb
db/migrate/20230531142053_add_foreign_key_namespace_id_on_subscription_add_on_purchases.rb
db/schema_migrations/20230531134916
db/schema_migrations/20230531135001
db/schema_migrations/20230531142032
db/schema_migrations/20230531142053
db/structure.sql
Documentation review
The following files require a review from a technical writer:
-
db/docs/subscription_add_on_purchases.yml
(Link to current live version) -
db/docs/subscription_add_ons.yml
(Link to current live version)
The review does not need to block merging this merge request. See the:
-
Metadata for the
*.md
files that you've changed. The first few lines of each*.md
file identify the stage and group most closely associated with your docs change. - The Technical Writer assigned for that stage and group.
- Documentation workflows for information on when to assign a merge request for review.
Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer backend Sylvester Chin (
@schin1
) (UTC+8, 6 hours ahead of@cwiesner
)Sincheol (David) Kim (
@dskim_gitlab
) (UTC+9.5, 7.5 hours ahead of@cwiesner
)database Dmytro Biryukov (
@dbiryukov
) (UTC+2, same timezone as@cwiesner
)Dylan Griffith (
@DylanGriffith
) (UTC+10, 8 hours ahead of@cwiesner
)~"migration" No reviewer available No maintainer available To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
Danger-
- A deleted user
added Data WarehouseImpact Check label
- Resolved by Michał Zając
- Resolved by Mohamed Hamda
- Resolved by Peter Leitzen
@bhrai Given you are familiar with this change, do you mind taking the initial review? I would like to see if we're aligned with everything especially since there are some differences in the column types in regards to your documentation addition. I'll hold off with my review on that because of that.
added workflowin review label and removed workflowin dev label
requested review from @bhrai
Allure report
allure-report-publisher
generated test report!e2e-test-on-gdk:
test report for c6ca6a8aexpand 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 | ❗ | +------------------+--------+--------+---------+-------+-------+--------+
- Resolved by Corinna Gogolok
- Resolved by Bishwa Hang Rai
added priority1 label
- Resolved by Michał Zając
@mhamda Do you mind reviewing this as the initial database reviewer?
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.
requested review from @mhamda
marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed
Database migrations (on the main database)
2 Warnings 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)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_c873b07531Migrations 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 +32.00 KiB 20230531134953 - AddTextLimitToDescriptionOnAddOns Regular 2.0 s +0.00 B 20230531135001 - CreatePurchasedAddOns Regular 1.6 s +40.00 KiB 20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns Regular 1.8 s +0.00 B 20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns Regular 2.0 s +0.00 B 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns Regular 2.5 s +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 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 $82 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 $32 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 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 $31 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 $82 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 = $18504 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" < $416 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" >= $3576 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" = $3661 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" = $332 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" = $516 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" = $516 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" = $54 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" = $343 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" = $4151 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 $51 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" = $432 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 $216 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 $216 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 $3Histogram 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 +0.00 B 20230530114845 - CleanupOrganizationsWithNullPath Post deploy 1.5 s +0.00 B 20230530115830 - RemoveDefaultOnOrganizationPath Post deploy 1.4 s +0.00 B 20230602063059 - RemoveBroadcastMessagesNamespaceIdColumn Post deploy 2.0 s -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 Database migrations (on the ci database)
2 Warnings 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)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_c873b07531Migrations 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 +32.00 KiB 20230531134953 - AddTextLimitToDescriptionOnAddOns Regular 2.5 s +0.00 B 20230531135001 - CreatePurchasedAddOns Regular 2.3 s +40.00 KiB 20230531135033 - AddTextLimitToPurchaseXidOnPurchasedAddOns Regular 2.4 s +0.00 B 20230531142032 - AddForeignKeyAddOnIdOnPurchasedAddOns Regular 2.5 s +0.00 B 20230531142053 - AddForeignKeyNamespaceIdOnPurchasedAddOns Regular 2.9 s +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 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 = $22 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 = $22 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 $82 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 $32 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 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 $31 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 $82 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 +0.00 B 20230530114845 - CleanupOrganizationsWithNullPath Post deploy 2.1 s +0.00 B 20230530115830 - RemoveDefaultOnOrganizationPath Post deploy 2.1 s +0.00 B 20230602063059 - RemoveBroadcastMessagesNamespaceIdColumn Post deploy 2.1 s -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
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
requested review from @splattael and removed review request for @bhrai
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- A deleted user
added database-testing-automation label
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
- Resolved by Corinna Gogolok
@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 pipeline:mr-approved label
added databasereviewed label and removed databasereview pending label