Avoiding downtime in migrations

class User < ApplicationRecord
  ignore_column :updated_at, remove_with: '12.7', remove_after: '2019-12-22'
end

ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'

CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
SELECT id, username, updated_at
FROM users
WHERE updated_at > now() - interval '30 day'

class RecentlyUpdatedUsersView < ApplicationRecord
  self.table_name = 'recently_updated_users_view'

  ignore_columns :updated_at
end

bundle exec rails g post_deployment_migration remove_users_updated_at_column

class RemoveUsersUpdatedAtColumn < Gitlab::Database::Migration[2.1]
  def up
    remove_column :users, :updated_at
  end

  def down
    add_column :users, :updated_at, :datetime
  end
end

class RemoveUsersUpdatedAtColumn < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    remove_column :users, :updated_at
  end

  def down
    add_column(:users, :updated_at, :datetime, if_not_exists: true)

    # Make sure to add back any indexes or constraints,
    # that were dropped in the `up` method. For example:
    add_concurrent_index(:users, :updated_at)
  end
end

class RemoveUsersUpdatedAtColumn < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    execute <<-SQL
      DROP VIEW IF EXISTS recently_updated_users_view;

      CREATE VIEW recently_updated_users_view(id, username) AS
        SELECT id, username
        FROM users;
    SQL

    remove_column :users, :updated_at
  end

  def down
    add_column :users, :updated_at, :datetime

    execute <<-SQL
      DROP VIEW IF EXISTS recently_updated_users_view;

      CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
        SELECT id, username, updated_at
        FROM users
        WHERE updated_at > now() - interval '30 day'
    SQL
  end
end

# A regular migration in db/migrate
class RenameUsersUpdatedAtToUpdatedAtTimestamp < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    rename_column_concurrently :users, :updated_at, :updated_at_timestamp
  end

  def down
    undo_rename_column_concurrently :users, :updated_at, :updated_at_timestamp
  end
end

# A regular migration in db/migrate including database view recreation
class RenameUsersUpdatedAtToUpdatedAtTimestamp < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    rename_column_concurrently :users, :updated_at, :updated_at_timestamp

    execute <<-SQL
      DROP VIEW IF EXISTS recently_updated_users_view;

      CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
        SELECT id, username, updated_at_timestamp
        FROM users
        WHERE updated_at > now() - interval '30 day'
    SQL
  end

  def down
    execute <<-SQL
      DROP VIEW IF EXISTS recently_updated_users_view;

      CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
        SELECT id, username, updated_at
        FROM users
        WHERE updated_at > now() - interval '30 day'
    SQL

    undo_rename_column_concurrently :users, :updated_at, :updated_at_timestamp
  end
end

ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```0

### Add a post-deployment migration (release M)

The renaming procedure requires some cleaning up in a post-deployment migration.
We can perform this cleanup using
`Gitlab::Database::MigrationHelpers#cleanup_concurrent_column_rename`:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```1

If you're renaming a [large table](https://gitlab.com/gitlab-org/gitlab/-/blob/master/rubocop/rubocop-migrations.yml#L3), carefully consider the state when the first migration has run but the second cleanup migration hasn't been run yet.
With [Canary](https://gitlab.com/gitlab-com/gl-infra/readiness/-/tree/master/library/canary/) it is possible that the system runs in this state for a significant amount of time.

### Remove the ignore rule (release M+1)

Same as when column is dropped, after the rename is completed, we need to [remove the ignore rule](#removing-the-ignore-rule-release-m2) in a subsequent release.

## Changing column constraints

Adding or removing a `NOT NULL` clause (or another constraint) can typically be
done without requiring downtime. Adding a `NOT NULL` constraint requires that any application
changes are deployed _first_, so it should happen in a post-deployment migration.
In contrary removing a `NOT NULL` constraint should be done in a regular migration.
This way any code which inserts `NULL` values can safely run for the column.

Avoid using `change_column` as it produces an inefficient query because it re-defines
the whole column type.

You can check the following guides for each specific use case:

- [Adding foreign-key constraints](../migration_style_guide.md#adding-foreign-key-constraints)
- [Adding `NOT NULL` constraints](not_null_constraints.md)
- [Adding limits to text columns](strings_and_the_text_data_type.md)

## Changing column types

Changing the type of a column can be done using
`Gitlab::Database::MigrationHelpers#change_column_type_concurrently`. This
method works similarly to `rename_column_concurrently`. For example, let's say
we want to change the type of `users.username` from `string` to `text`:

1. [Create a regular migration](#create-a-regular-migration)
1. [Create a post-deployment migration](#create-a-post-deployment-migration)
1. [Casting data to a new type](#casting-data-to-a-new-type)

When changing columns type that are referenced by a database view the view needs to be recreated as part of the process.

### Create a regular migration

A regular migration is used to create a new column with a temporary name along
with setting up some triggers to keep data in sync. Such a migration would look
as follows:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```2

When the column is referenced by a database view, the view needs to be recreated
and pointed to the new temporary column.

When in the later step the temporary column is renamed back to the original name, the view updates
itself internally and doesn't require any other change:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```3

### Create a post-deployment migration

Next we need to clean up our changes using a post-deployment migration:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```4

And that's it, we're done!

### Casting data to a new type

Some type changes require casting data to a new type. For example when changing from `text` to `jsonb`.
In this case, use the `type_cast_function` option.
Make sure there is no bad data and the cast always succeeds. You can also provide a custom function that handles
casting errors.

Example migration:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```5

## Changing column defaults

Changing column defaults is difficult because of how Rails handles values
that are equal to the default.

Rails ignores sending the default values to PostgreSQL when inserting records, if the [partial_inserts](https://gitlab.com/gitlab-org/gitlab/-/blob/55ac06c9083434e6c18e0a2aaf8be5f189ef34eb/config/application.rb#L40) config has been enabled. It leaves this task to
the database. When migrations change the default values of the columns, the running application is unaware
of this change due to the schema cache. The application is then under the risk of accidentally writing
wrong data to the database, especially when deploying the new version of the code
long after we run database migrations.

If running code ever explicitly writes the old default value of a column, you must follow a multi-step
process to prevent Rails replacing the old default with the new default in INSERT queries that explicitly
specify the old default.

Doing this requires steps in two minor releases:

1. [Add the `SafelyChangeColumnDefault` concern to the model](#add-the-safelychangecolumndefault-concern-to-the-model-and-change-the-default-in-a-post-migration) and change the default in a post-migration.
1. [Clean up the `SafelyChangeColumnDefault` concern](#clean-up-the-safelychangecolumndefault-concern-in-the-next-minor-release) in the next minor release.

We must wait a minor release before cleaning up the `SafelyChangeColumnDefault` because self-managed
releases bundle an entire minor release into a single zero-downtime deployment.

### Add the `SafelyChangeColumnDefault` concern to the model and change the default in a post-migration

The first step is to mark the column as safe to change in application code.

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```6

Then create a **post-deployment migration** to change the default:

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```7

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```8

### Clean up the `SafelyChangeColumnDefault` concern in the next minor release

In the next minor release, create a new merge request to remove the `columns_changing_default` call. Also remove the `SafelyChangeColumnDefault` include
if it is not needed for a different column.

## Changing the schema for large tables

While `change_column_type_concurrently` and `rename_column_concurrently` can be
used for changing the schema of a table without downtime, it doesn't work very
well for large tables. Because all of the work happens in sequence the migration
can take a very long time to complete, preventing a deployment from proceeding.
They can also produce a lot of pressure on the database due to it rapidly
updating many rows in sequence.

To reduce database pressure you should instead use a background migration
when migrating a column in a large table (for example, `issues`). Background
migrations spread the work / load over a longer time period, without slowing
down deployments.

For more information, see [the documentation on cleaning up batched background migrations](batched_background_migrations.md#cleaning-up-a-batched-background-migration).

## Adding indexes

Adding indexes does not require downtime when `add_concurrent_index`
is used.

See also [Migration Style Guide](../migration_style_guide.md#adding-indexes)
for more information.

## Dropping indexes

Dropping an index does not require downtime.

## Adding tables

This operation is safe as there's no code using the table just yet.

## Dropping tables

Dropping tables can be done safely using a post-deployment migration, but only
if the application no longer uses the table.

Add the table to [`db/docs/deleted_tables`](https://gitlab.com/gitlab-org/gitlab/-/tree/master/db/docs/deleted_tables) using the process described in [database dictionary](database_dictionary.md#dropping-tables).
Even though the table is deleted, it is still referenced in database migrations.

## Renaming tables

Renaming tables requires downtime as an application may continue
using the old table name during/after a database migration.

If the table and the ActiveRecord model is not in use yet, removing the old
table and creating a new one is the preferred way to "rename" the table.

Renaming a table is possible without downtime by following our multi-release
[rename table process](rename_database_tables.md).

## Adding foreign keys

Adding foreign keys usually works in 3 steps:

1. Start a transaction
1. Run `ALTER TABLE` to add the constraints
1. Check all existing data

Because `ALTER TABLE` typically acquires an exclusive lock until the end of a
transaction this means this approach would require downtime.

GitLab allows you to work around this by using
`Gitlab::Database::MigrationHelpers#add_concurrent_foreign_key`. This method
ensures that no downtime is needed.

## Removing foreign keys

This operation does not require downtime.

## Migrating `integer` primary keys to `bigint`

To [prevent the overflow risk](https://gitlab.com/groups/gitlab-org/-/epics/4785) for some tables
with `integer` primary key (PK), we have to migrate their PK to `bigint`. The process to do this
without downtime and causing too much load on the database is described below.

### Initialize the conversion and start migrating existing data (release N)

To start the process, add a regular migration to create the new `bigint` columns. Use the provided
`initialize_conversion_of_integer_to_bigint` helper. The helper also creates a database trigger
to keep in sync both columns for any new records ([code](https://gitlab.com/gitlab-org/gitlab/-/blob/97aee76c4bfc2043dc0a1ef9ffbb71c58e0e2857/db/migrate/20230127093353_initialize_conversion_of_merge_request_metrics_to_bigint.rb)):

```ruby
ignore_columns %i[updated_at created_at], remove_with: '12.7', remove_after: '2019-12-22'
```9

Ignore the new `bigint` columns:

```sql
CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
SELECT id, username, updated_at
FROM users
WHERE updated_at > now() - interval '30 day'
```0

Enqueue batched background migration ([code](https://gitlab.com/gitlab-org/gitlab/-/blob/97aee76c4bfc2043dc0a1ef9ffbb71c58e0e2857/db/post_migrate/20230127101834_backfill_merge_request_metrics_for_bigint_conversion.rb))
to migrate the existing data:

```sql
CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
SELECT id, username, updated_at
FROM users
WHERE updated_at > now() - interval '30 day'
```1

- With [Issue#438124](https://gitlab.com/gitlab-org/gitlab/-/issues/438124) new instances have all ID columns in bigint.
  The list of IDs yet to be converted to bigint in old instances (includes `Gitlab.com` SaaS) is maintained in `db/integer_ids_not_yet_initialized_to_bigint.yml`.
- Since the schema file already has all IDs in `bigint`, don't push any changes to `db/structure.sql`.

### Monitor the background migration

Check how the migration is performing while it's running. Multiple ways to do this are described below.

#### High-level status of batched background migrations

See how to [check the status of batched background migrations](../../update/background_migrations.md).

#### Query the database

We can query the related database tables directly. Requires access to read-only replica.
Example queries:

```sql
CREATE VIEW recently_updated_users_view(id, username, updated_at) AS
SELECT id, username, updated_at
FROM users
WHERE updated_at > now() - interval '30 day'
```2

#### Sidekiq logs

We can also use the Sidekiq logs to monitor the worker that executes the batched background
migrations:

1. Sign in to [Kibana](https://log.gprd.gitlab.net) with a `@gitlab.com` email address.
1. Change the index pattern to `pubsub-sidekiq-inf-gprd*`.
1. Add filter for `json.queue: cronjob:database_batched_background_migration`.

#### PostgreSQL slow queries log

Slow queries log keeps track of low queries that took above 1 second to execute. To see them
for batched background migration:

1. Sign in to [Kibana](https://log.gprd.gitlab.net) with a `@gitlab.com` email address.
1. Change the index pattern to `pubsub-postgres-inf-gprd*`.
1. Add filter for `json.endpoint_id.keyword: Database::BatchedBackgroundMigrationWorker`.
1. Optional. To see only updates, add a filter for `json.command_tag.keyword: UPDATE`.
1. Optional. To see only failed statements, add a filter for `json.error_severity.keyword: ERROR`.
1. Optional. Add a filter by table name.

#### Grafana dashboards

To monitor the health of the database, use these additional metrics:

- [PostgreSQL Tuple Statistics](https://dashboards.gitlab.net/d/000000167/postgresql-tuple-statistics?orgId=1&refresh=1m): if you see high rate of updates for the tables being actively converted, or increasing percentage of dead tuples for this table, it might mean that `autovacuum` cannot keep up.
- [PostgreSQL Overview](https://dashboards.gitlab.net/d/000000144/postgresql-overview?orgId=1): if you see high system usage or transactions per second (TPS) on the primary database server, it might mean that the migration is causing problems.

### Prometheus metrics

Number of [metrics](https://gitlab.com/gitlab-org/gitlab/-/blob/294a92484ce4611f660439aa48eee4dfec2230b5/lib/gitlab/database/background_migration/batched_migration_wrapper.rb#L90-128)
for each batched background migration are published to Prometheus. These metrics can be searched for and
visualized in Grafana ([see an example](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:%22sum%20%28rate%28batched_migration_job_updated_tuples_total%7Benv%3D%5C%22gprd%5C%22%7D%5B5m%5D%29%29%20by%20%28migration_id%29%20%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-3d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1)).

### Swap the columns (release N + 1)

After the background migration is complete and the new `bigint` columns are populated for all records, we can
swap the columns. Swapping is done with post-deployment migration. The exact process depends on the
table being converted, but in general it's done in the following steps:

1. Using the provided `ensure_backfill_conversion_of_integer_to_bigint_is_finished` helper, make sure the batched
   migration has finished.
   If the migration has not completed, the subsequent steps fail anyway. By checking in advance we
   aim to have more helpful error message.

   ```ruby
   disable_ddl_transaction!

   restrict_gitlab_migration gitlab_schema: :gitlab_ci

   def up
     ensure_backfill_conversion_of_integer_to_bigint_is_finished(
       :ci_builds,
       %i[
         project_id
         runner_id
         user_id
       ],
       # optional. Only needed when there is no primary key e.g. like schema_migrations
       primary_key: :id
     )
   end

   def down; end