Migration Style Guide

graph LR
    A{Schema<br/>changed?}
    A -->|Yes| C{Critical to<br/>speed or<br/>behavior?}
    A -->|No| D{Is it fast?}

    C -->|Yes| H{Is it fast?}
    C -->|No| F[Post-deploy migration]

    H -->|Yes| E[Regular migration]
    H -->|No| I[Post-deploy migration<br/>+ feature flag]

    D -->|Yes| F[Post-deploy migration]
    D -->|No| G[Background migration]

bundle exec rails g migration migration_name_here

bundle exec rails g model model_name_here

# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema

# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main

$ echo -n "20241021120146" | sha256sum
7a3e382a6e5564bfa7004bca1a357a910b151e7399c6466113daf01526d97470  -

def down
  # no-op

  # comment explaining why changes performed by `up` cannot be reversed.
end

scripts/refresh-migrations-timestamps

class TestMigration < Gitlab::Database::Migration[2.1]
  def change
  end
end

def change
  remove_column :users, :full_name, :string
end

bundle exec rails g migration migration_name_here
```0

#### Changing default value for a column

Changing column defaults can cause application downtime if a multi-release process is not followed.
See [avoiding downtime in migrations for changing column defaults](database/avoiding_downtime_in_migrations.md#changing-column-defaults) for details.

```shell
bundle exec rails g migration migration_name_here
```1

#### Creating a new table when we have two foreign keys

Only one foreign key should be created per transaction. This is because [the addition of a foreign key constraint requires a `SHARE ROW EXCLUSIVE` lock on the referenced table](https://www.postgresql.org/docs/12/sql-createtable.html#:~:text=The%20addition%20of%20a%20foreign%20key%20constraint%20requires%20a%20SHARE%20ROW%20EXCLUSIVE%20lock%20on%20the%20referenced%20table), and locking multiple tables in the same transaction should be avoided.

For this, we need three migrations:

1. Creating the table without foreign keys (with the indices).
1. Add foreign key to the first table.
1. Add foreign key to the second table.

Creating the table:

```shell
bundle exec rails g migration migration_name_here
```2

Adding foreign key to `projects`:

We can use the `add_concurrent_foreign_key` method in this case, as this helper method
has the lock retries built into it.

```shell
bundle exec rails g migration migration_name_here
```3

Adding foreign key to `users`:

```shell
bundle exec rails g migration migration_name_here
```4

### Usage with non-transactional migrations

Only when we disable transactional migrations using `disable_ddl_transaction!`, we can use
the `with_lock_retries` helper to guard an individual sequence of steps. It opens a transaction
to execute the given block.

A custom RuboCop rule ensures that only allowed methods can be placed within the lock retries block.

```shell
bundle exec rails g migration migration_name_here
```5

The RuboCop rule generally allows standard Rails migration methods, listed below. This example causes a RuboCop offense:

```shell
bundle exec rails g migration migration_name_here
```6

### When to use the helper method

You can **only** use the `with_lock_retries` helper method when the execution is not already inside
an open transaction (using PostgreSQL subtransactions is discouraged). It can be used with
standard Rails migration helper methods. Calling more than one migration
helper is not a problem if they're executed on the same table.

Using the `with_lock_retries` helper method is advised when a database
migration involves one of the [high-traffic tables](#high-traffic-tables).

Example changes:

- `add_foreign_key` / `remove_foreign_key`
- `add_column` / `remove_column`
- `change_column_default`
- `create_table` / `drop_table`

The `with_lock_retries` method **cannot** be used within the `change` method, you must manually define the `up` and `down` methods to make the migration reversible.

### How the helper method works

1. Iterate 50 times.
1. For each iteration, set a pre-configured `lock_timeout`.
1. Try to execute the given block. (`remove_column`).
1. If `LockWaitTimeout` error is raised, sleep for the pre-configured `sleep_time`
   and retry the block.
1. If no error is raised, the current iteration has successfully executed the block.

For more information check the [`Gitlab::Database::WithLockRetries`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/with_lock_retries.rb) class. The `with_lock_retries` helper method is implemented in the [`Gitlab::Database::MigrationHelpers`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rb) module.

In a worst-case scenario, the method:

- Executes the block for a maximum of 50 times over 40 minutes.
  - Most of the time is spent in a pre-configured sleep period after each iteration.
- After the 50th retry, the block is executed without `lock_timeout`, just
  like a standard migration invocation.
- If a lock cannot be acquired, the migration fails with `statement timeout` error.

The migration might fail if there is a very long running transaction (40+ minutes)
accessing the `users` table.

#### Lock-retry methodology at the SQL level

In this section, we provide a simplified SQL example that demonstrates the use of `lock_timeout`.
You can follow along by running the given snippets in multiple `psql` sessions.

When altering a table to add a column,
`AccessExclusiveLock`, which conflicts with most lock types, is required on the table.
If the target table is a very busy one, the transaction adding the column
may fail to acquire `AccessExclusiveLock` in a timely fashion.

Suppose a transaction is attempting to insert a row into a table:

```shell
bundle exec rails g migration migration_name_here
```7

At this point Transaction 1 acquired `RowExclusiveLock` on `my_notes`.
Transaction 1 could still execute more statements prior to committing or aborting.
There could be other similar, concurrent transactions that touch `my_notes`.

Suppose a transactional migration is attempting to add a column to the table
without using any lock retry helper:

```shell
bundle exec rails g migration migration_name_here
```8

Transaction 2 is now blocked because it cannot acquire
`AccessExclusiveLock` on `my_notes` table
as Transaction 1 is still executing and holding the `RowExclusiveLock`
on `my_notes`.

A more pernicious effect is blocking the transactions that would
normally not conflict with Transaction 1 because Transaction 2
is queueing to acquire `AccessExclusiveLock`.
In a normal situation, if another transaction attempted to read from and write
to the same table `my_notes` at the same time as Transaction 1,
the transaction would go through
since the locks needed for reading and writing would not
conflict with `RowExclusiveLock` held by Transaction 1.
However, when the request to acquire `AccessExclusiveLock` is queued,
the subsequent requests for conflicting locks on the table would block although
they could be executed concurrently alongside Transaction 1.

If we used `with_lock_retries`, Transaction 2 would instead quickly
timeout after failing to acquire the lock within the specified time period
and allow other transactions to proceed:

```shell
bundle exec rails g migration migration_name_here
```9

The lock retry helper would repeatedly try the same transaction
at different time intervals until it succeeded.

`SET LOCAL` scopes the parameter (`lock_timeout`) change to
the transaction.

## Removing indexes

If the table is not empty when removing an index, make sure to use the method
`remove_concurrent_index` instead of the regular `remove_index` method.
The `remove_concurrent_index` method drops indexes concurrently, so no locking is required,
and there is no need for downtime. To use this method, you must disable single-transaction mode
by calling the method `disable_ddl_transaction!` in the body of your migration
class like so:

```shell
bundle exec rails g model model_name_here
```0

You can verify that the index is not being used with [Grafana](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22pum%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22sum%20by%20%28type%29%28rate%28pg_stat_user_indexes_idx_scan%7Benv%3D%5C%22gprd%5C%22,%20indexrelname%3D%5C%22INSERT%20INDEX%20NAME%20HERE%5C%22%7D%5B30d%5D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-1h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1):

```shell
bundle exec rails g model model_name_here
```1

It is not necessary to check if the index exists prior to
removing it, however it is required to specify the name of the
index that is being removed. This can be done either by passing the name
as an option to the appropriate form of `remove_index` or `remove_concurrent_index`,
or by using the `remove_concurrent_index_by_name` method. Explicitly
specifying the name is important to ensure the correct index is removed.

For a small table (such as an empty one or one with less than `1,000` records),
it is recommended to use `remove_index` in a single-transaction migration,
combining it with other operations that don't require `disable_ddl_transaction!`.

### Disabling an index

[Disabling an index is not a safe operation](database/maintenance_operations.md#disabling-an-index-is-not-safe).

## Adding indexes

Before adding an index, consider if one is necessary. The [Adding Database indexes](database/adding_database_indexes.md) guide contains more details to help you decide if an index is necessary and provides best practices for adding indexes.

## Testing for existence of indexes

If a migration requires conditional logic based on the absence or presence of an index, you must test for existence of that index using its name. This helps avoids problems with how Rails compares index definitions, which can lead to unexpected results.

For more details, review the [Adding Database Indexes](database/adding_database_indexes.md#testing-for-existence-of-indexes)
guide.

## Adding foreign-key constraints

When adding a foreign-key constraint to either an existing or a new column also
remember to add an index on the column.

This is **required** for all foreign-keys, for example, to support efficient cascading
deleting: when a lot of rows in a table get deleted, the referenced records need
to be deleted too. The database has to look for corresponding records in the
referenced table. Without an index, this results in a sequential scan on the
table, which can take a long time.

Here's an example where we add a new column with a foreign key
constraint. Note it includes `index: true` to create an index for it.

```shell
bundle exec rails g model model_name_here
```2

When adding a foreign-key constraint to an existing column in a non-empty table,
we have to employ `add_concurrent_foreign_key` and `add_concurrent_index`
instead of `add_reference`.

If you have a new or empty table that doesn't reference a
[high-traffic table](#high-traffic-tables),
we recommend that you use `add_reference` in a single-transaction migration. You can
combine it with other operations that don't require `disable_ddl_transaction!`.

You can read more about adding [foreign key constraints to an existing column](database/add_foreign_key_to_existing_column.md).

## `NOT NULL` constraints

See the style guide on [`NOT NULL` constraints](database/not_null_constraints.md) for more information.

## Adding Columns With Default Values

With PostgreSQL 11 being the minimum version in GitLab, adding columns with default values has become much easier and
the standard `add_column` helper should be used in all cases.

Before PostgreSQL 11, adding a column with a default was problematic as it would
have caused a full table rewrite.

## Removing the column default for non-nullable columns

If you have added a non-nullable column, and used the default value to populate
existing data, you need to keep that default value around until at least after
the application code is updated. You cannot remove the default value in the
same migration, as the migrations run before the model code is updated and
models will have an old schema cache, meaning they won't know about this column
and won't be able to set it. In this case it's recommended to:

1. Add the column with default value in a standard migration.
1. Remove the default in a post-deployment migration.

The post-deployment migration happens after the application restarts,
ensuring the new column has been discovered.

## Changing the column default

One might think that changing a default column with `change_column_default` is an
expensive and disruptive operation for larger tables, but in reality it's not.

Take the following migration as an example:

```shell
bundle exec rails g model model_name_here
```3

Migration above changes the default column value of one of our largest
tables: `namespaces`. This can be translated to:

```shell
bundle exec rails g model model_name_here
```4

In this particular case, the default value exists and we're just changing the metadata for
`request_access_enabled` column, which does not imply a rewrite of all the existing records
in the `namespaces` table. Only when creating a new column with a default, all the records are going be rewritten.

NOTE:
A faster [ALTER TABLE ADD COLUMN with a non-null default](https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/)
was introduced on PostgreSQL 11.0, removing the need of rewriting the table when a new column with a default value is added.

For the reasons mentioned above, it's safe to use `change_column_default` in a single-transaction migration
without requiring `disable_ddl_transaction!`.

## Updating an existing column

To update an existing column to a particular value, you can use
`update_column_in_batches`. This splits the updates into batches, so we
don't update too many rows at in a single statement.

This updates the column `foo` in the `projects` table to 10, where `some_column`
is `'hello'`:

```shell
bundle exec rails g model model_name_here
```5

If a computed update is needed, the value can be wrapped in `Arel.sql`, so Arel
treats it as an SQL literal. It's also a required deprecation for [Rails 6](https://gitlab.com/gitlab-org/gitlab/-/issues/28497).

The below example is the same as the one above, but
the value is set to the product of the `bar` and `baz` columns:

```shell
bundle exec rails g model model_name_here
```6

In the case of `update_column_in_batches`, it may be acceptable
to run on a large table, as long as it is only updating a small subset of the
rows in the table, but do not ignore that without validating on the GitLab.com
staging environment - or asking someone else to do so for you - beforehand.

## Removing a foreign key constraint

When removing a foreign key constraint, we need to acquire a lock on both tables
that are related to the foreign key. For tables with heavy write patterns, it's a good
idea to use `with_lock_retries`, otherwise you might fail to acquire a lock in time.
You might also run into deadlocks when acquiring a lock, because ordinarily
the application writes in `parent,child` order. However, removing a foreign
key acquires the lock in `child,parent` order. To resolve this, you can
explicitly acquire the lock in `parent,child`, for example:

```shell
bundle exec rails g model model_name_here
```7

## Dropping a database table

NOTE:
After a table has been dropped, it should be added to the database dictionary, following the
steps in the [database dictionary guide](database/database_dictionary.md#dropping-tables).

Dropping a database table is uncommon, and the `drop_table` method
provided by Rails is generally considered safe. Before dropping the table,
consider the following:

If your table has foreign keys on a [high-traffic table](#high-traffic-tables) (like `projects`), then
the `DROP TABLE` statement is likely to stall concurrent traffic until it fails with **statement timeout** error.

Table **has no records** (feature was never in use) and **no foreign
keys**:

- Use the `drop_table` method in your migration.

```shell
bundle exec rails g model model_name_here
```8

Table **has records** but **no foreign keys**:

- Remove the application code related to the table, such as models,
  controllers and services.
- In a post-deployment migration, use `drop_table`.

This can all be in a single migration if you're sure the code is not used.
If you want to reduce risk slightly, consider putting the migrations into a
second merge request after the application changes are merged. This approach
provides an opportunity to roll back.

```shell
bundle exec rails g model model_name_here
```9

Table **has foreign keys**:

- Remove the application code related to the table, such as models,
  controllers, and services.
- In a post-deployment migration, remove the foreign keys using the
  `with_lock_retries` helper method. In another subsequent post-deployment
  migration, use `drop_table`.

This can all be in a single migration if you're sure the code is not used.
If you want to reduce risk slightly, consider putting the migrations into a
second merge request after the application changes are merged. This approach
provides an opportunity to roll back.

Removing the foreign key on the `projects` table using a non-transactional migration:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```0

Dropping the table:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```1

## Dropping a sequence

> - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/88387) in GitLab 15.1.

Dropping a sequence is uncommon, but you can use the `drop_sequence` method provided by the database team.

Under the hood, it works like this:

Remove a sequence:

- Remove the default value if the sequence is actually used.
- Execute `DROP SEQUENCE`.

Re-add a sequence:

- Create the sequence, with the possibility of specifying the current value.
- Change the default value of the column.

A Rails migration example:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```2

NOTE:
`add_sequence` should be avoided for columns with foreign keys.
Adding sequence to these columns is **only allowed** in the down method (restore previous schema state).

## Truncate a table

> - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/117373) in GitLab 15.11.

Truncating a table is uncommon, but you can use the `truncate_tables!` method provided by the database team.

Under the hood, it works like this:

- Finds the `gitlab_schema` for the tables to be truncated.
- If the `gitlab_schema` for the tables is included in the connection's `gitlab_schema`s,
  it then executes the `TRUNCATE` statement.
- If the `gitlab_schema` for the tables is not included in the connection's
  `gitlab_schema`s, it does nothing.

## Swapping primary key

> - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/98645) in GitLab 15.5.

Swapping the primary key is required to partition a table as the **partition key must be included in the primary key**.

You can use the `swap_primary_key` method provided by the database team.

Under the hood, it works like this:

- Drop the primary key constraint.
- Add the primary key using the index defined beforehand.

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```3

NOTE:
Make sure to introduce the new index beforehand in a separate migration in order
to swap the primary key.

## Integer column type

By default, an integer column can hold up to a 4-byte (32-bit) number. That is
a max value of 2,147,483,647. Be aware of this when creating a column that
holds file sizes in byte units. If you are tracking file size in bytes, this
restricts the maximum file size to just over 2GB.

To allow an integer column to hold up to an 8-byte (64-bit) number, explicitly
set the limit to 8-bytes. This allows the column to hold a value up to
`9,223,372,036,854,775,807`.

Rails migration example:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```4

## Strings and the Text data type

See the [text data type](database/strings_and_the_text_data_type.md) style guide for more information.

## Timestamp column type

By default, Rails uses the `timestamp` data type that stores timestamp data
without time zone information. The `timestamp` data type is used by calling
either the `add_timestamps` or the `timestamps` method.

Also, Rails converts the `:datetime` data type to the `timestamp` one.

Example:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```5

Instead of using these methods, one should use the following methods to store
timestamps with time zones:

- `add_timestamps_with_timezone`
- `timestamps_with_timezone`
- `datetime_with_timezone`

This ensures all timestamps have a time zone specified. This, in turn, means
existing timestamps don't suddenly use a different time zone when the system's
time zone changes. It also makes it very clear which time zone was used in the
first place.

## Storing JSON in database

The Rails 5 natively supports `JSONB` (binary JSON) column type.
Example migration adding this column:

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```6

By default hash keys will be strings. Optionally you can add a custom data type to provide different access to keys.

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```7

When using a `JSONB` column, use the [JsonSchemaValidator](https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/validators/json_schema_validator.rb) to keep control of the data being inserted over time.

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```8

Additionally, you can expose the keys in a `JSONB` column as
ActiveRecord attributes. Do this when you need complex validations,
or ActiveRecord change tracking. This feature is provided by the
[`jsonb_accessor`](https://github.com/madeintandem/jsonb_accessor) gem,
and does not replace `JsonSchemaValidator`.

```shell
# Regenerate schema against `main`
scripts/regenerate-schema

# Regenerate schema against `12-9-stable-ee`
TARGET=12-9-stable-ee scripts/regenerate-schema
```9

You can now use `restricted_visibility_levels` as an ActiveRecord attribute:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```0

## Encrypted attributes

Do not store `attr_encrypted` attributes as `:text` in the database; use
`:binary` instead. This uses the `bytea` type in PostgreSQL and makes storage more
efficient:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```1

When storing encrypted attributes in a binary column, we need to provide the
`encode: false` and `encode_iv: false` options to `attr_encrypted`:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```2

## Testing

See the [Testing Rails migrations](testing_guide/testing_migrations_guide.md) style guide.

## Data migration

Prefer Arel and plain SQL over usual ActiveRecord syntax. In case of
using plain SQL, you need to quote all input manually with `quote_string` helper.

Example with Arel:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```3

Example with plain SQL and `quote_string` helper:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```4

If you need more complex logic, you can define and use models local to a
migration. For example:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```5

When doing so be sure to explicitly set the model's table name, so it's not
derived from the class name or namespace.

Be aware of the limitations [when using models in migrations](#using-application-code-in-migrations-discouraged).

### Modifying existing data

In most circumstances, prefer migrating data in **batches** when modifying data in the database.

We introduced a new helper [`each_batch_range`](https://gitlab.com/gitlab-org/gitlab/-/blob/cd3e0a5cddcb464cb9b8c6e3275839cf57dfa6e2/lib/gitlab/database/dynamic_model_helpers.rb#L28-32) which facilitates the process of iterating over a collection in a performant way. The default size of the batch is defined in the `BATCH_SIZE` constant.

See the following example to get an idea.

**Purging data in batch:**

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```6

- The first argument is the table being modified: `'ci_pending_builds'`.
- The second argument calls a lambda which fetches the relevant dataset selected (the default is set to `.all`): `scope: ->(table) { table.ref_protected }`.
- The third argument is the batch size (the default is set in the `BATCH_SIZE` constant): `of: BATCH_SIZE`.

Here is an [example MR](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/62195) illustrating how to use our new helper.

## Using application code in migrations (discouraged)

The use of application code (including models) in migrations is generally
discouraged. This is because the migrations stick around for a long time and
the application code it depends on may change and break the migration in
future. In the past some background migrations needed to use
application code in order to avoid copying hundreds of lines of code spread
across multiple files into the migration. In these rare cases it's critical to
ensure the migration has good tests so that anyone refactoring the code in
future will learn if they break the migration. Using application code is also
[discouraged for batched background migrations](database/batched_background_migrations.md#isolation)
, the model needs to be declared in the migration.

Usually you can avoid using application code (specifically models) in a
migration by defining a class that inherits from `MigrationRecord` (see
examples below).

If using are using a model (including defined in the migration), you should
first
[clear the column cache](https://api.rubyonrails.org/classes/ActiveRecord/ModelSchema/ClassMethods.html#method-i-reset_column_information)
using `reset_column_information`.

If using a model that leverages single table inheritance (STI), there are
[special considerations](database/single_table_inheritance.md#in-migrations).

This avoids problems where a column that you are using was altered and cached
in a previous migration.

### Example: Add a column `my_column` to the users table

It is important not to leave out the `User.reset_column_information` command, to ensure that the old schema is dropped from the cache and ActiveRecord loads the updated schema information.

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```7

The underlying table is modified and then accessed by using ActiveRecord.

This also needs to be used if the table is modified in a previous, different migration,
if both migrations are run in the same `db:migrate` process.

This results in the following. Note the inclusion of `my_column`:

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```8

If you skip clearing the schema cache (`User.reset_column_information`), the column is not
used by ActiveRecord and the intended changes are not made, leading to the result below,
where `my_column` is missing from the query.

```shell
# Rebase against master
git rebase master

# Rollback changes
VERSION=<migration ID> bundle exec rails db:migrate:down:main

# Checkout db/structure.sql from master
git checkout origin/master db/structure.sql

# Migrate changes
VERSION=<migration ID> bundle exec rails db:migrate:main
```9

## High traffic tables

Here's a list of current [high-traffic tables](https://gitlab.com/gitlab-org/gitlab/-/blob/master/rubocop/rubocop-migrations.yml).

Determining what tables are high-traffic can be difficult. Self-managed instances might use
different features of GitLab with different usage patterns, thus making assumptions based
on GitLab.com not enough.

To identify a high-traffic table for GitLab.com the following measures are considered.
The metrics linked here are GitLab-internal only:

- [Read operations](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22m95%22:%7B%22datasource%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22topk%28500,%20sum%20by%20%28relname%29%20%28rate%28pg_stat_user_tables_seq_tup_read%7Benvironment%3D%5C%22gprd%5C%22%7D%5B12h%5D%29%20%2B%20rate%28pg_stat_user_tables_idx_scan%7Benvironment%3D%5C%22gprd%5C%22%7D%5B12h%5D%29%20%2B%20rate%28pg_stat_user_tables_idx_tup_fetch%7Benvironment%3D%5C%22gprd%5C%22%7D%5B12h%5D%29%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-12h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1)
- [Number of records](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22m95%22:%7B%22datasource%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22topk%28500,%20max%20by%20%28relname%29%20%28pg_stat_user_tables_n_live_tup%7Benvironment%3D%5C%22gprd%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-6h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1)
- [Size](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22m95%22:%7B%22datasource%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22topk%28500,%20max%20by%20%28relname%29%20%28pg_total_relation_size_bytes%7Benvironment%3D%5C%22gprd%5C%22%7D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22e58c2f51-20f8-4f4b-ad48-2968782ca7d6%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-6h%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) is greater than 10 GB

Any table which has some high read operation compared to current [high-traffic tables](https://gitlab.com/gitlab-org/gitlab/-/blob/master/rubocop/rubocop-migrations.yml#L4) might be a good candidate.

As a general rule, we discourage adding columns to high-traffic tables that are purely for
analytics or reporting of GitLab.com. This can have negative performance impacts for all
self-managed instances without providing direct feature value to them.

## Milestone

Beginning in GitLab 16.6, all new migrations must specify a milestone, using the following syntax:

```shell
$ echo -n "20241021120146" | sha256sum
7a3e382a6e5564bfa7004bca1a357a910b151e7399c6466113daf01526d97470  -
```0

Adding the correct milestone to a migration enables us to logically partition migrations into
their corresponding GitLab minor versions. This:

- Simplifies the upgrade process.
- Alleviates potential migration ordering issues that arise when we rely solely on the migration's timestamp for ordering.

## Autovacuum wraparound protection

This is a [special autovacuum](https://www.cybertec-postgresql.com/en/autovacuum-wraparound-protection-in-postgresql/)
run mode for PostgreSQL and it requires a `ShareUpdateExclusiveLock` on the
table that it is vacuuming. For [larger tables](https://gitlab.com/gitlab-org/release-tools/-/blob/master/lib/release_tools/prometheus/wraparound_vacuum_checks.rb#L11)
this could take hours and the lock can conflict with most DDL migrations that
try to modify the table at the same time. Because the migrations will not be
able to acquire the lock in time, they will fail and block the deployments.

The [post-deploy migration (PDM) pipeline](https://gitlab.com/gitlab-org/release/docs/-/tree/master/general/post_deploy_migration) can check and halt its execution if it
detects a wraparound prevention vacuum process on one of the tables. For this to
happen we need to use the complete table name in the migration name. For example
`add_foreign_key_between_ci_builds_and_ci_job_artifacts` will check for vacuum
on `ci_builds` and `ci_job_artifacts` before executing the migrations.

If the migration doesn't have conflicting locks, the vacuum check can be skipped
by not using the complete table name, for example `create_async_index_on_job_artifacts`.