Ensure uniqueness p_ci_job_artifacts

Ref: #429905 (closed)

What does this MR do and why?

This MR ensures uniqueness of p_ci_job_artifacts across other partitions now that we successfully created its partition table.

https://docs.gitlab.com/ee/development/database/partitioning/list.html#step-8---ensure-id-uniqueness-across-partitions

assigned to @morefice

added database databasereview pending labels

	1 Warning
	You've made some app changes, but didn't add any tests. That's OK as long as you're refactoring existing code, but please consider adding any of the maintenancepipelines, maintenancerefactor, maintenanceworkflow, documentation, QA labels.

Reviewer roulette

Category	Reviewer	Maintainer
database	@bauerdominic (UTC+1, same timezone as author)	@praba.m7n (UTC+5.5, 4.5 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-review job that generated this comment.

Generated by Danger

added Data WarehouseImpact Check label

Proper labels assigned to this merge request. Please ignore me.

@morefice - please see the following guidance and update this merge request.

	1 Error
	Please add typebug typefeature, or typemaintenance label to this merge request.

changed the description

changed milestone to %16.10

added CI data partitioning Category:Continuous Integration backend ciscaling cicdactive devopsverify grouppipeline execution maintenancescalability needs weight sectionci typemaintenance workflowplanning breakdown labels

resolved all threads

removed needs weight label

added workflowin review label and removed workflowplanning breakdown label

Database migrations (on the main database)

	1 Warnings
	20240212092520 - EnsureIdUniquenessForPCiJobArtifacts had a query that exceeded timing guidelines. Run time should not exceed 100ms, but it was 278.98ms. Please consider possible options to improve the query performance. SELECT "postgres_sequences".* FROM "postgres_sequences" WHERE "postgres_sequences"."table_name" = $1 ORDER BY "postgres_sequences"."seq_name" ASC LIMIT $2

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
20240212092520 - EnsureIdUniquenessForPCiJobArtifacts	Post deploy	2.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	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: 20240212092520 - EnsureIdUniquenessForPCiJobArtifacts

• Type: Post deploy
• Duration: 2.3 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	279.0 ms	279.0 ms	279.0 ms	1	SELECT "postgres_sequences".* FROM "postgres_sequences" WHERE "postgres_sequences"."table_name" = $1 ORDER BY "postgres_sequences"."seq_name" ASC LIMIT $2
1	29.8 ms	29.8 ms	29.8 ms	0	ALTER TABLE "p_ci_job_artifacts" ALTER COLUMN "id" DROP DEFAULT
1	21.0 ms	21.0 ms	21.0 ms	0	CREATE OR REPLACE FUNCTION assign_p_ci_job_artifacts_id_value() RETURNS TRIGGER AS $1 LANGUAGE PLPGSQL
1	6.5 ms	6.5 ms	6.5 ms	0	CREATE TRIGGER assign_p_ci_job_artifacts_id_trigger BEFORE INSERT ON p_ci_job_artifacts FOR EACH ROW EXECUTE FUNCTION assign_p_ci_job_artifacts_id_value()
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for EnsureIdUniquenessForPCiJobArtifacts

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

Background Migration: PopulateTopicsSlugColumn

Sampled 11 batches. Estimated Time to complete: 13 hours, 22 minutes, and 52 seconds

• Interval: 120s
• Total tuple count: 282742
• Max batch size: 0
• Estimated seconds to complete: 48172s
• Estimated number of batches: 282
• Average batch time: 170.82s
• Batch size: 1000
• N. of batches sampled: 11
• 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
11000	15039.0 ms	4786.6 ms	1.4 ms	11000	UPDATE topics SET updated_at = $1, slug = $2 WHERE topics.id = $3
11573	675.5 ms	22.4 ms	0.1 ms	573	SELECT topics.* FROM topics WHERE topics.slug = $1 LIMIT $2
22	24.0 ms	20.5 ms	1.1 ms	22	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
99	65.9 ms	9.4 ms	0.7 ms	9900	SELECT topics.* FROM topics WHERE topics.id BETWEEN $1 AND $2 AND topics.slug IS NULL AND topics.id >= $3 AND topics.id < $4
11	7.4 ms	3.8 ms	0.7 ms	11	UPDATE batched_background_migration_jobs SET updated_at = $1, finished_at = $2, status = $3, metrics = $4 WHERE batched_background_migration_jobs.id = $5
11	2.2 ms	1.1 ms	0.2 ms	11	UPDATE batched_background_migration_jobs SET updated_at = $1, started_at = $2, status = $3, attempts = $4 WHERE batched_background_migration_jobs.id = $5
110	32.8 ms	0.5 ms	0.3 ms	99	SELECT topics.id FROM topics WHERE topics.id BETWEEN $1 AND $2 AND topics.slug IS NULL AND topics.id >= $3 ORDER BY topics.id ASC LIMIT $4 OFFSET $5
11	4.2 ms	0.5 ms	0.4 ms	1100	SELECT topics.* FROM topics WHERE topics.id BETWEEN $1 AND $2 AND topics.slug IS NULL AND topics.id >= $3
22	1.4 ms	0.3 ms	0.1 ms	22	SELECT batched_background_migration_jobs.* FROM batched_background_migration_jobs WHERE batched_background_migration_jobs.id = $1 LIMIT $2
11	0.5 ms	0.1 ms	0.0 ms	11	SELECT batched_background_migrations.* FROM batched_background_migrations WHERE batched_background_migrations.id = $1 LIMIT $2
11	0.5 ms	0.1 ms	0.0 ms	11	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)
11	0.4 ms	0.1 ms	0.0 ms	11	SELECT topics.id FROM topics WHERE topics.id BETWEEN $1 AND $2 AND topics.slug IS NULL ORDER BY topics.id ASC LIMIT $3

Histogram of batch runtimes for PopulateTopicsSlugColumn

Batch Runtime	Count
0 seconds - 10 seconds	0
10 seconds - 1 minute	0
1 minute - 2 minutes	0
2 minutes - 3 minutes	10
3 minutes - 5 minutes	1
5 minutes +	0

Histogram across all sampled batches of PopulateTopicsSlugColumn

Query Runtime	Count
0 seconds - 0.1 seconds	22791
0.1 seconds - 0.5 seconds	99
0.5 seconds - 1 second	0
1 second - 2 seconds	1
2 seconds - 5 seconds	1
5 seconds +	0

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20240113071052 - QueuePopulateTopicsSlugColumn	Post deploy	2.7 s		+0.00 B
20240209183815 - FinalizeUpdateDelayedProjectRemovalToNull	Post deploy	1.7 s		+0.00 B
20240212023136 - PrepareAsyncIndexForBuildsPart4	Post deploy	11.5 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2860558-12831939-main	2024-02-13T10:42:12Z	2024-02-13T05:55:26Z	2024-02-13 23:20:20 +0000
database-testing-2860558-12831939-ci	2024-02-13T10:42:12Z	2024-02-13T08:46:14Z	2024-02-13 23:20:20 +0000

Job artifacts

Database migrations (on the ci database)

	2 Warnings
	20240212092520 - EnsureIdUniquenessForPCiJobArtifacts had a query that exceeded timing guidelines. Run time should not exceed 100ms, but it was 724.71ms. Please consider possible options to improve the query performance. SELECT "postgres_sequences".* FROM "postgres_sequences" WHERE "postgres_sequences"."table_name" = $1 ORDER BY "postgres_sequences"."seq_name" ASC LIMIT $2
	20240212092520 - EnsureIdUniquenessForPCiJobArtifacts had a query that exceeded timing guidelines. Run time should not exceed 100ms, but it was 101.28ms. Please consider possible options to improve the query performance. ALTER TABLE "p_ci_job_artifacts" ALTER COLUMN "id" DROP DEFAULT

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
20240212092520 - EnsureIdUniquenessForPCiJobArtifacts	Post deploy	3.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	4
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: 20240212092520 - EnsureIdUniquenessForPCiJobArtifacts

• Type: Post deploy
• Duration: 3.5 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	724.7 ms	724.7 ms	724.7 ms	1	SELECT "postgres_sequences".* FROM "postgres_sequences" WHERE "postgres_sequences"."table_name" = $1 ORDER BY "postgres_sequences"."seq_name" ASC LIMIT $2
1	101.3 ms	101.3 ms	101.3 ms	0	ALTER TABLE "p_ci_job_artifacts" ALTER COLUMN "id" DROP DEFAULT
1	18.7 ms	18.7 ms	18.7 ms	0	CREATE OR REPLACE FUNCTION assign_p_ci_job_artifacts_id_value() RETURNS TRIGGER AS $1 LANGUAGE PLPGSQL
1	9.4 ms	9.4 ms	9.4 ms	0	CREATE TRIGGER assign_p_ci_job_artifacts_id_trigger BEFORE INSERT ON p_ci_job_artifacts FOR EACH ROW EXECUTE FUNCTION assign_p_ci_job_artifacts_id_value()
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for EnsureIdUniquenessForPCiJobArtifacts

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	4
0.1 seconds - 1 second	2
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
20240113071052 - QueuePopulateTopicsSlugColumn	Post deploy	2.4 s		+0.00 B
20240209183815 - FinalizeUpdateDelayedProjectRemovalToNull	Post deploy	2.3 s		+0.00 B
20240212023136 - PrepareAsyncIndexForBuildsPart4	Post deploy	11.3 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2860558-12831939-main	2024-02-13T10:42:12Z	2024-02-13T05:55:26Z	2024-02-13 23:20:20 +0000
database-testing-2860558-12831939-ci	2024-02-13T10:42:12Z	2024-02-13T08:46:14Z	2024-02-13 23:20:20 +0000

Job artifacts

---

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

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 9dbb4c74

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 31     | 0      | 0       | 0     | 31    | ✅     |
| Create      | 8      | 0      | 3       | 0     | 11    | ✅     |
| Plan        | 4      | 0      | 0       | 0     | 4     | ✅     |
| Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
| Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
| Data Stores | 0      | 0      | 2       | 0     | 2     | ➖     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 50     | 0      | 6       | 0     | 56    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for 9dbb4c74

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 138    | 0      | 27      | 3     | 165   | ✅     |
| Create      | 148    | 0      | 24      | 4     | 172   | ✅     |
| Data Stores | 0      | 0      | 4       | 0     | 4     | ➖     |
| Plan        | 8      | 0      | 0       | 0     | 8     | ✅     |
| Govern      | 6      | 0      | 0       | 0     | 6     | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
| Package     | 0      | 0      | 2       | 0     | 2     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 308    | 0      | 57      | 7     | 365   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

added database-testing-automation label

marked this merge request as ready

requested review from @panoskanell

@panoskanell can you review this MR please?

requested review from @mbobin and removed review request for @panoskanell

approved this merge request

@panoskanell, 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 pipeline:mr-approved label

changed the description

added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

mentioned in epic &7522

approved this merge request

resolved all threads

added databaseapproved label and removed databasereview pending label

enabled an automatic merge when the pipeline for e87f323e succeeds

merged

mentioned in commit 806cfc13

Hello @morefice

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-team

And someone will be by shortly!

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

added workflowstaging-canary label and removed workflowin review label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added releasedcandidate label

mentioned in merge request kubitus-project/kubitus-installer!2869 (merged)

added releasedpublished label and removed releasedcandidate label

added pipelinetier-3 label