Add migration helpers for initializing the conversion of a primary key from int to bigint
In order to address the Primary Key overflow risk for tables with an integer PK, we need migration helpers that will allow us to convert a primary key and all the foreign keys that reference it from int to bigint.
The first step is to add a migration helper and a background migration for initializing the process:
- Given an integer column (e.g.
events.id
), create a new column (e.g.events.new_id
), withbigint NOT NULL DEFAULT 0
- Setup a trigger to keep the two columns in sync
- Backfill the new column with the values of the existing column by scheduling background jobs
- Support for tracking the scheduled jobs through the use of
Gitlab::Database::BackgroundMigrationJob
That means that we have to add:
- A new migration helper
initialize_convertion_of_int_column_to_bigint
that will handle all the aforementioned steps - A new Background Migration
CopyPrimaryKeyColumn
that will useBackgroundMigrationJob
to mark when it succeeds and will be usable with thequeue_background_migration_jobs_by_range_at_intervals
helper (which also supports job tracking)
This covers half the work required to support end-to-end the process of converting a primary key from int to bigint: In %13.8 we are also going to add support for finalizing the conversion, adding a unique index, adding any indexes that include the primary key and, finally, swapping the sequence and the primary key.
The migration helpers created in this issue will be used for initiating the events.id
conversion to bigint (#288004 (closed))