This change introduces a new "not null" check constraint for the
`traversal_ids` column of the `vulnerability_reads` table.

Changelog: added

Adds a new partitioned table p_catalog_resource_component_usages.
This table will track when a CI component is included in a project's
pipeline. Only unique records of component-used_by_project-used_date
are inserted.

This data will be used to display component usage popularity
aggregated by component or catalog_resource per 30 day rolling window.

This table is partitioned by month. For now, the
table will drop partitions with data older than 12 months. This
duration will likely be reduced in a follow up issue.

Changelog: added

!144882 adds couple
of indexes, but at the wrong position in `structure.sql`.

This restores the expected order (alphabetical by index name).

See also
!145294 (comment 1805667098).

[skip-db-structure-check]

- Added user_id, custom_role_id column to member_approvals
- Removed not null constraint from member_id and old_access_level
- Changed unique index
- Added loose foreign key for user_id
- Added index for new columns

Changelog: changed
EE: true

Changelog: added
EE: true

This is the first step of partitioning the merge_request_diff_commits
table by int range.

Epic: &11271

Changelog: added

Changelog: changed

Expiring worker refactor done

Niko Belokolodov authored 11 months ago and Dylan Griffith committed 11 months ago

Backfill RawUsageData records with Default organization

Michael Becker authored 11 months ago and Adam Hegyi committed 11 months ago

We want to drop the `vulnerability_occurrence_pipelines` table. In
order to do this, we still need the first and latest pipeline ID
stored somewhere to support the existing feature-set

The columns and asynchronous index creation were added in a
[previous MR][0]

This change adds the synchronous index migration to complete the index
creation [process][1]

Follow-up Work
---------------------------

We will need to add loose foreign keys and model association. Both of
these are blocked on this index creation

That work is tracked in the following tasks:

- #443283
- #443284

---

Related to #422382
Resolves #443282

[0]:!144697
[1]:https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#create-indexes-asynchronously

Changelog: added

This commit makes sure the reference FK for ci_stages
are now defined a the routing table level so all other new
partitions will inherit from it.

Changelog: other

Max Orefice authored 11 months ago and Marius Bobin committed 11 months ago

Changelog: other

Pam Artiaga authored 11 months ago and euko committed 11 months ago

To be used for this change: !146154

Sam Word authored 11 months ago and Tiger Watson committed 11 months ago

In PruneExpiredExportJobsService, EachBatch won't scale for large
volumes of data because we're trying to batch by pkey and a timestamp.
This introduces a new index on project_export_jobs to use loop-based
deletion and keyset pagination to more efficiently prune old uploads.

Jarka Košanová authored 11 months ago and Rajendra Kadam committed 11 months ago

Changelog: other

Tianwen Chen authored 11 months ago and Marius Bobin committed 11 months ago

See https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#swap-the-columns-release-n--1

Changelog: changed

!143868 introduced
new FK, but not at the right place in `strcuture.sql` (constraints
should be ordered alphabetically by name).

[skip-db-structure-check]

euko authored 1 year ago and Michał Zając committed 1 year ago

The `notes` records with NULL noteable_type are orphaned notes
(the cause(s) are unknown.)
They should be removed from the `notes` table.

To insure against inadvertent data loss,
`temp_notes_backup` table is created to store the removed records.

`temp_notes_backup` should be removed later.

Changelog: other

Michael Becker authored 1 year ago and Leonardo da Rosa committed 1 year ago

We want to drop the `vulnerability_occurrence_pipelines` table. In
order to do this, we still need the first and latest pipeline ID
stored somewhere to support the existing feature-set

Follow-up Work
---------------------------

We will need to add loose foreign keys and model association. Both of
these are blocked on index creation, which has to be done via the
[async process][0]

That work is tracked in the following tasks:

- #443282
- #443283
- #443284

---

Related to #422382
Resolves #443281
Resolves #443280

[0]:https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#create-indexes-asynchronously

Changelog: added

Alex Buijs authored 1 year ago and Jarka Košanová committed 1 year ago

This adds "Remove Group" as a custom role ability,
so that it can be added onto any base role.

Changelog: added
EE: true

Changelog: other

We want to include approval_rule_id in the unique index so that users
are able to approve more than once if they are in more than one group.

Changelog: added

- Set it to false by default
- Add a before_save hook to the MemberRole model
to set occupies_seat to true, if a role is created
with any of the elevated_permissions

Changelog: added
EE: true

Changelog: added

- needed to add the visibility functionality to organizations
  that mirrors namespace and project setup.
- see #442147

Changelog: other

- Was ignored in 16.9: !142849
- Next step is to drop in 16.10
- Will remove from ignore list in 16.11 per
  https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#ignoring-the-column-release-m
- #439402

Changelog: removed

Add background database migration to migrate dark `color_mode_id` (`2`)
where users have selected dark mode `theme_id` (`11`)
Add ColorMode model

Changelog: changed

Changelog: removed

Changelog: other

Enforce a NON-NULL constraint on the uuid column
of the vs_code_settings table

Changelog: other

See https://gitlab.slack.com/archives/C06LDF0H8HL/p1708941520316659.

## What does this MR do and why?

Add and backfill project_id for cluster_agent_tokens.

This table has a
[desired sharding key](https://docs.gitlab.com/ee/development/database/multiple_databases.html#defining-a-desired_sharding_key-for-automatically-backfilling-a-sharding_key)
configured ([view configuration](https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/docs/cluster_agent_tokens.yml)).

This merge request is the first step towards transforming the desired sharding key into a
[sharding key](https://docs.gitlab.com/ee/development/database/multiple_databases.html#defining-a-sharding-key-for-all-cell-local-tables).

This involves three changes:

- Adding a new column that will serve as the sharding key (along with the relevant index, foreign key and not null constraint).
- Populating the sharding key when new records are created, by adding a `belongs_to` association on the model with a `default` value.
- Scheduling a [batched background migration](https://docs.gitlab.com/ee/development/database/batched_background_migrations.html)
  to set the sharding key for existing records.

Once the background migration has completed, a second merge request will be created to finalize the background
migration and validate the not null constraint.

## How to verify

We have assigned a random backend engineer from ~"group::environments" to review these changes. Please review this merge
request from a ~backend perspective. The main thing we are looking to verify is that the added column and association
match the values specified by the [desired sharding key](https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/docs/cluster_agent_tokens.yml)
configuration and that backfilling the column from this other table makes sense in the context of this feature.

When you are finished, please:

1. Trigger the [database testing pipeline](https://docs.gitlab.com/ee/development/database/database_migration_pipeline.html)
   as instructed by Danger.
1. Request a review from the ~backend maintainer and ~database reviewer suggested by Danger.

If you have any questions or concerns, reach out to @tigerwnz, @DylanGriffith or @manojmj.

This merge request was generated by a once off keep implemented in
!143774

This change was generated by
[gitlab-housekeeper](https://gitlab.com/gitlab-org/gitlab/-/tree/master/gems/gitlab-housekeeper)
using the Keeps::BackfillDesiredShardingKeySmallTable keep.

To provide feedback on your experience with `gitlab-housekeeper` please comment in
<#442003>.

Changelog: other

Changelog: fixed

In !139630\#note_1781181642
we introduced migrations to remove the relevant foreign keys and drop the
user_interacted_projects table. This caused a minor incident
(gitlab-com/gl-infra/production#17622)
due to the foreign keys referencing to two different high-traffic tables
(users, projects) which caused a deadlock. This MR addresses the
incident and re-introduces the migrations with added with_lock_retries
for each foreign key.

Changelog: fixed

This commit creates the routing table for ci_stages.

Changelog: other

Contributes to #428548

Remove duplicates indexes:

* Remove "index_protected_tags_on_project_id" duplicated by
"index_protected_tags_on_project_id_and_name"
* Remove duplicated "index_project_repositories_on_shard_id" index
* Remove duplicated "index_approvals_on_merge_request_id" index
* Remove index_approval_project_rules_users_on_approval_project_rule_id

Changelog: performance