You need to sign in or sign up before continuing.
Create partitioned CI component usage table
2 unresolved threads
2 unresolved threads
What does this MR do and why?
This MR adds a migration to create p_catalog_resource_component_usages. This table is used to record unique projects that include a component in their pipeline(s) for each day.
This data will later enable users to sort components (or component projects) by popularity. We require a daily metric because we will need to query the number of unique projects for the last 30-days (rolling window).
Note: Our existing analytics instrumentation services don't readily support our requirements for both GitLab.com and self-managed instances. Thus we determined that a custom data table would be the simplest to implement. (Ref: !144932 (comment 1783721915))
Table/model notes:
- Unique on
component_id-used_by_project_id-used_date.-
used_dateis the formatYYYY-mm-dd. We do not need the time parameters as we're only interested in the number of unique projects per day. - The uniqueness validation ensures that redundant data is not inserted into the table.
-
- The table is partitioned for efficiency.
- For now we will not truncate the partitions until after 1 year as we may need to keep historical data. We are planning to shorten this duration to 2-3 months as part of a follow up issue: #443681.
- We'll likely be aggregating historical data by month, so it was determined that
monthlywould be the most appropriate partitioning strategy.
- The sharding key is set to
project_idinstead ofused_by_project_idbecause the "owner" of the data should be the project that owns the component rather than the one using it. (See !145881 (comment 1798736129).) -
used_by_project_iddoes not have an FK constraint because we'll want to keep historical usage data on a component even if the associated used_by_project is deleted. - We will add further indexes later as needed to better support the queries for batch processing the data in #443381 (closed).
Resolves Part 1 of 2 of Task Create component usage data table and tracking ... (#443380 - closed). Parent issue: #440382 (closed).
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Query plan
Up
Down
Related to #440382 (closed)
Edited by Leaminn Ma
Merge request reports
Activity
changed milestone to %16.10
assigned to @lma-git
mentioned in epic &7462
added 605 commits
-
804e4873...53bebfba - 604 commits from branch
master - 6eca4fef - Add table to track CI component usage
-
804e4873...53bebfba - 604 commits from branch
- A deleted user
added database databasereview pending labels
2 Warnings 
This MR changes code in ee/, but its Changelog commit is missing theEE: truetrailer. Consider adding it to your Changelog commits.featureaddition and featureenhancement merge requests normally have a documentation change. Consider adding a documentation update or confirming the documentation plan with the Technical Writer counterpart.
For more information, see:
- The Handbook page on merge request types.
- The definition of done documentation.
Reviewer roulette
Category Reviewer Maintainer backend @eduardobonet
(UTC+1, 9 hours ahead of author)
@alejandro (UTC-5, 3 hours ahead of author)
database @jdrpereira (UTC+0, 8 hours ahead of author)
@euko (UTC+9, 17 hours ahead of author)
~"Verify" Reviewer review is optional for ~"Verify" @drew (UTC+0, 8 hours ahead of author)
Please check reviewer's status!
-
Reviewer is available!
-
Reviewer is unavailable!
Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.
If needed, you can retry the
danger-reviewjob that generated this comment.Generated by
Database migrations (on the main database)
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 20240301210341 - CreateCatalogResourceComponentUsagesTable Regular 2.5 s 
+8.00 KiB [note] 20240301210400 - AddComponentFkToCatalogResourceComponentUsages Regular 2.4 s +0.00 B 20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages Regular 2.4 s +0.00 B 20240301210440 - AddProjectFkToCatalogResourceComponentUsages Regular 2.2 s +0.00 B Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 34 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: 20240301210341 - CreateCatalogResourceComponentUsagesTable
- Type: Regular
- Duration: 2.5 s
- Database size change: +8.00 KiB [note]
Calls Total Time Max Time Mean Time Rows Query 1 36.3 ms 36.3 ms 36.3 ms 0 CREATE TABLE IF NOT EXISTS "p_catalog_resource_component_usages" ("id" bigserial NOT NULL, "component_id" bigint NOT NULL, "catalog_resource_id" bigint NOT NULL, "project_id" bigint NOT NULL, "used_by_project_id" bigint NOT NULL, "used_date" date NOT NULL, PRIMARY KEY ("id", "used_date")) PARTITION BY RANGE (used_date)1 1.6 ms 1.6 ms 1.6 ms 0 CREATE INDEX IF NOT EXISTS "idx_p_catalog_resource_component_usages_on_catalog_resource_id" ON "p_catalog_resource_component_usages" ("catalog_resource_id")1 1.5 ms 1.5 ms 1.5 ms 0 CREATE UNIQUE INDEX IF NOT EXISTS "idx_component_usages_on_component_used_by_project_and_used_date" ON "p_catalog_resource_component_usages" ("component_id", "used_by_project_id", "used_date")1 1.4 ms 1.4 ms 1.4 ms 0 CREATE INDEX IF NOT EXISTS "index_p_catalog_resource_component_usages_on_project_id" ON "p_catalog_resource_component_usages" ("project_id")1 0.6 ms 0.6 ms 0.6 ms 0 CREATE INDEX IF NOT EXISTS "index_p_catalog_resource_component_usages_on_component_id" ON "p_catalog_resource_component_usages" ("component_id")1 0.1 ms 0.1 ms 0.1 ms 1 SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for CreateCatalogResourceComponentUsagesTable
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 8 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: 20240301210400 - AddComponentFkToCatalogResourceComponentUsages
- Type: Regular
- Duration: 2.4 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 15.5 ms 15.5 ms 15.5 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_e1ba64b7ee FOREIGN KEY (component_id) REFERENCES catalog_resource_components (id) ON DELETE CASCADE
1 3.2 ms 3.2 ms 3.2 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC2 0.6 ms 0.3 ms 0.3 ms 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_e1ba64b7ee
2 0.1 ms 0.1 ms 0.1 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 AddComponentFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 9 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: 20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages
- Type: Regular
- Duration: 2.4 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 63.4 ms 63.4 ms 63.4 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC1 19.7 ms 19.7 ms 19.7 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_9430673479 FOREIGN KEY (catalog_resource_id) REFERENCES catalog_resources (id) ON DELETE CASCADE
2 0.6 ms 0.3 ms 0.3 ms 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_9430673479
2 0.1 ms 0.1 ms 0.1 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 AddCatalogResourceFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 8 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: 20240301210440 - AddProjectFkToCatalogResourceComponentUsages
- Type: Regular
- Duration: 2.2 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 47.3 ms 47.3 ms 47.3 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_0e15a4677f FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
1 3.8 ms 3.8 ms 3.8 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC2 1.3 ms 1.2 ms 0.6 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.5 ms 0.2 ms 0.2 ms 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_0e15a4677f
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for AddProjectFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 9 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Background Migration: BackupAndRemoveNotesWithNullNoteableType
Sampled 1 batches. Estimated Time to complete: 7 months, 4 days, 5 hours, and 18 seconds
- Interval: 120s
- Total tuple count: 1564329500
- Max batch size: 0
- Estimated seconds to complete: 18771840s
- Estimated number of batches: 156432
- Average batch time: 15.01s
- Batch size: 10000
- N. of batches sampled: 1
- 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 33 1165.9 ms 904.4 ms 35.3 ms 1650 DELETE
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL AND notes.id >= $3 AND notes.id < $433 2580.5 ms 213.5 ms 78.2 ms 1650 INSERT INTO temp_notes_backup
SELECT notes.*
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL AND notes.id >= $3 AND notes.id < $41 34.8 ms 34.8 ms 34.8 ms 8 INSERT INTO temp_notes_backup
SELECT notes.*
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL AND notes.id >= $31 2.3 ms 2.3 ms 2.3 ms 8 DELETE
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL AND notes.id >= $32 0.3 ms 0.2 ms 0.2 ms 2 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
34 2.2 ms 0.1 ms 0.1 ms 33 SELECT notes.id
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL AND notes.id >= $3
ORDER BY notes.id ASC
LIMIT $4
OFFSET $51 0.1 ms 0.1 ms 0.1 ms 1 UPDATE batched_background_migration_jobs
SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
WHERE batched_background_migration_jobs.id = $51 0.1 ms 0.1 ms 0.1 ms 1 UPDATE batched_background_migration_jobs
SET updated_at = $1, started_at = $2, status = $3, attempts = $4
WHERE batched_background_migration_jobs.id = $52 0.1 ms 0.1 ms 0.0 ms 2 SELECT batched_background_migration_jobs.*
FROM batched_background_migration_jobs
WHERE batched_background_migration_jobs.id = $1
LIMIT $21 0.1 ms 0.1 ms 0.1 ms 1 SELECT batched_background_migrations.*
FROM batched_background_migrations
WHERE batched_background_migrations.id = $1
LIMIT $21 0.0 ms 0.0 ms 0.0 ms 1 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)1 0.0 ms 0.0 ms 0.0 ms 1 SELECT notes.id
FROM notes
WHERE notes.id BETWEEN $1 AND $2 AND notes.noteable_type IS NULL
ORDER BY notes.id ASC
LIMIT $3Histogram of batch runtimes for BackupAndRemoveNotesWithNullNoteableType
Batch Runtime Count 0 seconds - 10 seconds 0 10 seconds - 1 minute 1 1 minute - 2 minutes 0 2 minutes - 3 minutes 0 3 minutes - 5 minutes 0 5 minutes + 0 Histogram across all sampled batches of BackupAndRemoveNotesWithNullNoteableType
Query Runtime Count 0 seconds - 0.1 seconds 90 0.1 seconds - 0.5 seconds 20 0.5 seconds - 1 second 1 1 second - 2 seconds 0 2 seconds - 5 seconds 0 5 seconds + 0 Background Migration: BackfillArchivedAndTraversalIdsToVulnerabilityReads
Sampled 13 batches. Estimated Time to complete: 1 week, 5 days, and 16 hours
- Interval: 120s
- Total tuple count: 91207960
- Max batch size: 0
- Estimated seconds to complete: 1094400s
- Estimated number of batches: 9120
- Average batch time: 69.53s
- Batch size: 10000
- N. of batches sampled: 13
- 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 1282 625778.5 ms 17298.4 ms 488.1 ms 128200 UPDATE vulnerability_reads
SET traversal_ids = namespaces.traversal_ids, archived = projects.archived
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE vulnerability_reads.id IN (
SELECT vulnerability_reads.id
FROM vulnerability_reads
WHERE vulnerability_reads.id BETWEEN $1 AND $2 AND vulnerability_reads.id >= $3 AND vulnerability_reads.id < $4
) AND vulnerability_reads.project_id = projects.id13 4952.8 ms 600.8 ms 381.0 ms 1266 UPDATE vulnerability_reads
SET traversal_ids = namespaces.traversal_ids, archived = projects.archived
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE vulnerability_reads.id IN (
SELECT vulnerability_reads.id
FROM vulnerability_reads
WHERE vulnerability_reads.id BETWEEN $1 AND $2 AND vulnerability_reads.id >= $3
) AND vulnerability_reads.project_id = projects.id1295 222.9 ms 18.3 ms 0.2 ms 1282 SELECT vulnerability_reads.id
FROM vulnerability_reads
WHERE vulnerability_reads.id BETWEEN $1 AND $2 AND vulnerability_reads.id >= $3
ORDER BY vulnerability_reads.id ASC
LIMIT $4
OFFSET $526 13.9 ms 8.1 ms 0.5 ms 26 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
13 2.8 ms 1.6 ms 0.2 ms 13 UPDATE batched_background_migration_jobs
SET updated_at = $1, started_at = $2, status = $3, attempts = $4
WHERE batched_background_migration_jobs.id = $513 6.2 ms 1.4 ms 0.5 ms 13 UPDATE batched_background_migration_jobs
SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
WHERE batched_background_migration_jobs.id = $526 2.1 ms 0.3 ms 0.1 ms 26 SELECT batched_background_migration_jobs.*
FROM batched_background_migration_jobs
WHERE batched_background_migration_jobs.id = $1
LIMIT $213 0.6 ms 0.1 ms 0.0 ms 13 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)13 0.6 ms 0.1 ms 0.0 ms 13 SELECT vulnerability_reads.id
FROM vulnerability_reads
WHERE vulnerability_reads.id BETWEEN $1 AND $2
ORDER BY vulnerability_reads.id ASC
LIMIT $313 0.5 ms 0.0 ms 0.0 ms 13 SELECT batched_background_migrations.*
FROM batched_background_migrations
WHERE batched_background_migrations.id = $1
LIMIT $2Histogram of batch runtimes for BackfillArchivedAndTraversalIdsToVulnerabilityReads
Batch Runtime Count 0 seconds - 10 seconds 0 10 seconds - 1 minute 2 1 minute - 2 minutes 11 2 minutes - 3 minutes 0 3 minutes - 5 minutes 0 5 minutes + 0 Histogram across all sampled batches of BackfillArchivedAndTraversalIdsToVulnerabilityReads
Query Runtime Count 0 seconds - 0.1 seconds 1409 0.1 seconds - 0.5 seconds 792 0.5 seconds - 1 second 428 1 second - 2 seconds 63 2 seconds - 5 seconds 9 5 seconds + 6 Other information
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change 20240206225046 - IndexOrganizationIdOnDependencyListExports Post deploy 2.3 s +16.00 KiB 20240208235322 - AddForeignKeyToOrganizationIdOnDependencyListExports Post deploy 2.1 s +0.00 B 20240210104125 - EnsureMemberRolesNamesUniq Post deploy 1.9 s +8.00 KiB [note] 20240214163238 - QueueBackfillArchivedAndTraversalIdsToVulnerabilityReads Post deploy 2.2 s +8.00 KiB [note] 20240214204800 - DropInvalidVulnerabilitiesGdk Post deploy 1.9 s +0.00 B 20240219040351 - SwapColumnsForAutoCanceledByIdBetweenCiBuildsAndCiPipelines Post deploy 13.6 s -16.00 KiB 20240221134504 - AddNameUniqueIndexToMemberRoles Post deploy 2.5 s +64.00 KiB 20240226142658 - AddTempNotesBackupTable Post deploy 1.7 s +16.00 KiB 20240226143323 - QueueBackupAndRemoveNotesWithNullNoteableType Post deploy 2.0 s +0.00 B 20240227131801 - UnscheduleOpenAiClearConvosCron Post deploy 1.6 s +0.00 B Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2941366-13052130-main2024-03-04T20:13:32Z 2024-03-02T04:15:06Z 2024-03-05 08:48:07 +0000 database-testing-2941366-13052130-ci2024-03-04T20:13:32Z 2024-03-04T16:44:58Z 2024-03-05 08:48:07 +0000 Database migrations (on the ci database)
1 Warnings 20240301210440 - AddProjectFkToCatalogResourceComponentUsages had a query that exceeded
timing guidelines. Run time should not exceed 100ms, but it was 407.85ms. Please consider possible
options to improve the query performance.ALTER TABLE p_catalog_resource_component_usages
ADD CONSTRAINT fk_rails_0e15a4677f FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE
CASCADEMigrations 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 20240301210341 - CreateCatalogResourceComponentUsagesTable Regular 3.1 s +8.00 KiB [note] 20240301210400 - AddComponentFkToCatalogResourceComponentUsages Regular 3.1 s +0.00 B 20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages Regular 2.9 s +0.00 B 20240301210440 - AddProjectFkToCatalogResourceComponentUsages Regular 3.3 s +0.00 B Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 34 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: 20240301210341 - CreateCatalogResourceComponentUsagesTable
- Type: Regular
- Duration: 3.1 s
- Database size change: +8.00 KiB [note]
Calls Total Time Max Time Mean Time Rows Query 1 98.7 ms 98.7 ms 98.7 ms 0 CREATE TABLE IF NOT EXISTS "p_catalog_resource_component_usages" ("id" bigserial NOT NULL, "component_id" bigint NOT NULL, "catalog_resource_id" bigint NOT NULL, "project_id" bigint NOT NULL, "used_by_project_id" bigint NOT NULL, "used_date" date NOT NULL, PRIMARY KEY ("id", "used_date")) PARTITION BY RANGE (used_date)1 12.0 ms 12.0 ms 12.0 ms 0 CREATE TRIGGER gitlab_schema_write_trigger_for_p_catalog_resource_component_usages BEFORE INSERT OR
UPDATE OR DELETE OR TRUNCATE ON p_catalog_resource_component_usages FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()1 1.5 ms 1.5 ms 1.5 ms 0 CREATE UNIQUE INDEX IF NOT EXISTS "idx_component_usages_on_component_used_by_project_and_used_date" ON "p_catalog_resource_component_usages" ("component_id", "used_by_project_id", "used_date")1 0.7 ms 0.7 ms 0.7 ms 0 CREATE INDEX IF NOT EXISTS "index_p_catalog_resource_component_usages_on_project_id" ON "p_catalog_resource_component_usages" ("project_id")1 0.7 ms 0.7 ms 0.7 ms 0 CREATE INDEX IF NOT EXISTS "index_p_catalog_resource_component_usages_on_component_id" ON "p_catalog_resource_component_usages" ("component_id")1 0.6 ms 0.6 ms 0.6 ms 0 CREATE INDEX IF NOT EXISTS "idx_p_catalog_resource_component_usages_on_catalog_resource_id" ON "p_catalog_resource_component_usages" ("catalog_resource_id")1 0.1 ms 0.1 ms 0.1 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 CreateCatalogResourceComponentUsagesTable
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 8 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: 20240301210400 - AddComponentFkToCatalogResourceComponentUsages
- Type: Regular
- Duration: 3.1 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 56.1 ms 56.1 ms 56.1 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_e1ba64b7ee FOREIGN KEY (component_id) REFERENCES catalog_resource_components (id) ON DELETE CASCADE
1 4.0 ms 4.0 ms 4.0 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC2 1.0 ms 1.0 ms 0.5 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.7 ms 0.4 ms 0.3 ms 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_e1ba64b7ee
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for AddComponentFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 9 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: 20240301210420 - AddCatalogResourceFkToCatalogResourceComponentUsages
- Type: Regular
- Duration: 2.9 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 22.5 ms 22.5 ms 22.5 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_9430673479 FOREIGN KEY (catalog_resource_id) REFERENCES catalog_resources (id) ON DELETE CASCADE
1 4.6 ms 4.6 ms 4.6 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC2 1.5 ms 1.5 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 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_9430673479
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for AddCatalogResourceFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 9 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 - Type: Regular
- Duration: 3.3 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 407.9 ms 407.9 ms 407.9 ms 0 ALTER TABLE p_catalog_resource_component_usages ADD CONSTRAINT fk_rails_0e15a4677f FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
1 4.4 ms 4.4 ms 4.4 ms 0 SELECT "postgres_partitions".*
FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
ORDER BY "postgres_partitions"."name" ASC2 3.9 ms 3.8 ms 1.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.4 ms 0.2 ms 0.2 ms 2 SELECT "postgres_partitioned_tables".*
FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 0 ALTER TABLE p_catalog_resource_component_usages VALIDATE CONSTRAINT fk_rails_0e15a4677f
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for AddProjectFkToCatalogResourceComponentUsages
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 8 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 20240206225046 - IndexOrganizationIdOnDependencyListExports Post deploy 2.8 s +8.00 KiB [note] 20240208235322 - AddForeignKeyToOrganizationIdOnDependencyListExports Post deploy 2.9 s +0.00 B 20240210104125 - EnsureMemberRolesNamesUniq Post deploy 2.3 s +0.00 B 20240214163238 - QueueBackfillArchivedAndTraversalIdsToVulnerabilityReads Post deploy 2.3 s +0.00 B 20240219040351 - SwapColumnsForAutoCanceledByIdBetweenCiBuildsAndCiPipelines Post deploy 19.9 s -43.05 GiB 20240221134504 - AddNameUniqueIndexToMemberRoles Post deploy 3.2 s +16.00 KiB 20240227131801 - UnscheduleOpenAiClearConvosCron Post deploy 2.3 s +0.00 B Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2941366-13052130-main2024-03-04T20:13:32Z 2024-03-02T04:15:06Z 2024-03-05 08:48:07 +0000 database-testing-2941366-13052130-ci2024-03-04T20:13:32Z 2024-03-04T16:44:58Z 2024-03-05 08:48:07 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
- A deleted user
added database-testing-automation label
added 283 commits
-
434613b8...375c5edc - 282 commits from branch
master - 0fc56a67 - Add table to track CI component usage
-
434613b8...375c5edc - 282 commits from branch
- Resolved by Leaminn Ma
Hi @DylanGriffith!
A couple comments/questions:
-
Unfortunately we're not dropping old data partitions just yet as we may need to keep historical data for aggregation later. I've opened a follow up issue to address this: #443681.
-
You mentioned that sliding list partition would serve us well here, but I'm wondering if the monthly partitioning strategy might benefit us in the long run. Currently, we're aggregating the data for a 30-day rolling window, so I understand that monthly partitions wouldn't benefit us in that case. However, later on we're likely going to aggregate the data for long term storage (as part of #443681), which would be on a monthly basis.
So since neither the sliding list nor monthly strategies really help in the rolling window aggregation (afaik), but the monthly partitioning might help us later. Wdyt?
-
- Last reply by Leaminn Ma
requested review from @DylanGriffith
removed review request for @DylanGriffith
added 214 commits
-
9abb152c...3153d265 - 213 commits from branch
master - 9322b07e - Add table to track CI component usage
-
9abb152c...3153d265 - 213 commits from branch
- Resolved by Leaminn Ma
Hi @furkanayhan! Sorry for assigning you this right after you come back from PTO
(Feel free to re-assign if you're at capacity though!)
Thank you!
- Last reply by Furkan Ayhan
requested review from @furkanayhan
mentioned in issue #440382 (closed)
mentioned in issue #247718
removed review request for @furkanayhan
- Resolved by Leaminn Ma
- Last reply by Max Orefice
requested review from @morefice
- Resolved by Laura Montemayor
Hi @Kasia_Misirli! Could you please do the first backend / ~"Verify" review here? It's mostly a database MR, but it would be good to get some PA eyes on it first before moving it forward. Please send it to a ~"Verify" maintainer if it looks okay. Thanks!
Edited by Leaminn Ma - Last reply by Laura Montemayor
requested review from @Kasia_Misirli
added 1 commit
- ee02fb61 - Add clarifying comment to ci_component_usages relationship in Project
mentioned in task #443380 (closed)
added pipeline:mr-approved label
Resolved by Leaminn Ma@morefice, thanks for approving this merge request.This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break
master, a new pipeline will be started shortly.Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.
added databasereviewed label and removed databasereview pending label
requested review from @DylanGriffith and removed review request for @morefice
E2E Test Result Summary
allure-report-publishergenerated test report!e2e-test-on-gdk:
expand test summary
+------------------------------------------------------------------+ | suites summary | +-------------+--------+--------+---------+-------+-------+--------+ | | passed | failed | skipped | flaky | total | result | +-------------+--------+--------+---------+-------+-------+--------+ | Verify | 35 | 0 | 1 | 0 | 36 | ✅ | | Govern | 3 | 0 | 0 | 0 | 3 | ✅ | | Plan | 4 | 0 | 0 | 0 | 4 | ✅ | | Package | 0 | 0 | 1 | 0 | 1 | ➖ | | Create | 8 | 0 | 3 | 0 | 11 | ✅ | | Data Stores | 2 | 0 | 0 | 0 | 2 | ✅ | | Monitor | 4 | 0 | 0 | 0 | 4 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+ | Total | 56 | 0 | 5 | 0 | 61 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+e2e-package-and-test:
expand test summary
+------------------------------------------------------------------+ | suites summary | +-------------+--------+--------+---------+-------+-------+--------+ | | passed | failed | skipped | flaky | total | result | +-------------+--------+--------+---------+-------+-------+--------+ | Verify | 147 | 0 | 30 | 3 | 177 | ✅ | | Create | 153 | 0 | 20 | 3 | 173 | ✅ | | Plan | 8 | 0 | 0 | 0 | 8 | ✅ | | Monitor | 8 | 0 | 0 | 0 | 8 | ✅ | | Govern | 6 | 0 | 0 | 0 | 6 | ✅ | | Package | 0 | 0 | 2 | 0 | 2 | ➖ | | Data Stores | 4 | 0 | 0 | 0 | 4 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+ | Total | 326 | 0 | 52 | 6 | 378 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+- Resolved by Leaminn Ma
Hi @lma-git I left a couple of comments
- Last reply by Leaminn Ma
requested review from @lauraXD
added databaseapproved label and removed databasereviewed label
removed review request for @DylanGriffith
enabled an automatic merge when the pipeline for 20eb5f91 succeeds
-
@lma-git we've got a broken master
mentioned in issue #443681
-
@lauraXD, did you forget to run a pipeline before you merged this work? Based on our code review process, if the latest pipeline was created more than 4 hours ago, you should:
- Ensure the merge request is not in Draft status.
- Start a pipeline (especially important for Community contribution merge requests).
- Set the merge request to auto-merge.
This is a guideline, not a rule. Please consider replying to this comment for transparency.
This message was generated automatically. You're welcome to improve it.
-
Hello @lma-git
The database team is looking for ways to improve the database review process and we would love your help!
If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:
@gitlab-org/database-teamAnd someone will be by shortly!
Thanks for your help!
This message was generated automatically. You're welcome to improve it.
mentioned in commit dacbb52a
added workflowstaging-canary label and removed workflowin review label
mentioned in merge request !146555 (merged)
12174 12174 12175 12175 ALTER SEQUENCE p_batched_git_ref_updates_deletions_id_seq OWNED BY p_batched_git_ref_updates_deletions.id; 12176 12176 12177 CREATE TABLE p_catalog_resource_component_usages ( 12178 id bigint NOT NULL, 12179 component_id bigint NOT NULL, 12180 catalog_resource_id bigint NOT NULL, 12181 project_id bigint NOT NULL, 12182 used_by_project_id bigint NOT NULL, 12183 used_date date NOT NULL 12184 ) -
This is the position of the table definition when there are no partitions yet. Once partition are created it's moved up, which leads to diff like this after
gdk updateordb:migrate:diff --git a/db/structure.sql b/db/structure.sql index 7eb704c64599..12b4e27a03ae 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -1035,6 +1035,16 @@ CREATE TABLE p_batched_git_ref_updates_deletions ( ) PARTITION BY LIST (partition_id); +CREATE TABLE p_catalog_resource_component_usages ( + id bigint NOT NULL, + component_id bigint NOT NULL, + catalog_resource_id bigint NOT NULL, + project_id bigint NOT NULL, + used_by_project_id bigint NOT NULL, + used_date date NOT NULL +) +PARTITION BY RANGE (used_date); + CREATE TABLE p_catalog_resource_sync_events ( id bigint NOT NULL, catalog_resource_id bigint NOT NULL, @@ -12251,16 +12261,6 @@ CREATE SEQUENCE p_batched_git_ref_updates_deletions_id_seq ALTER SEQUENCE p_batched_git_ref_updates_deletions_id_seq OWNED BY p_batched_git_ref_updates_deletions.id; -CREATE TABLE p_catalog_resource_component_usages ( - id bigint NOT NULL, - component_id bigint NOT NULL, - catalog_resource_id bigint NOT NULL, - project_id bigint NOT NULL, - used_by_project_id bigint NOT NULL, - used_date date NOT NULL -) -PARTITION BY RANGE (used_date); - CREATE SEQUENCE p_catalog_resource_component_usages_id_seq START WITH 1 INCREMENT BY 1Edited by Krasimir Angelov -
mentioned in commit 1e13f72b
mentioned in merge request !147242 (merged)
mentioned in merge request !147293 (merged)
mentioned in merge request kubitus-project/kubitus-installer!2869 (merged)
mentioned in merge request !148216 (merged)
added releasedcandidate label
added releasedpublished label and removed releasedcandidate label
added pipelinetier-3 label
mentioned in epic gitlab-org#7462
mentioned in merge request !168512 (merged)
Please register or sign in to reply