Convert ci_builds_metadata.id to bigint - Step 1: Add new columns and sync data
The ci_builds_metadata
table is one of the largest tables in GitLab's database still uses an integer (int4
) Primary Key. It has a high Primary Key Overflow risk as can be seen on its tracking issue.
The first step to address the problem is to create a new column of type bigint
, load all the data by using background jobs from the current id
column to it and keep them in sync with a trigger.
In addition, the ci_builds_metadata
table references (has a Foreign Key towards) the ci_builds
table, which is also one of the tables with a high Primary Key Overflow risk and we'll have to convert its id
column to bigint
, so it would be optimal to do so in one pass while converting its Primary Key.
So the overall process will be as follows:
- Create new column
ci_builds_metadata.new_id
for the Primary Key, withbigint NOT NULL DEFAULT 0
- Create new column
ci_builds_metadata.new_build_id
for the Foreign Key that referencesci_builds
, withbigint NOT NULL DEFAULT 0
- Install sync triggers for both columns to keep them updated while new records are inserted or existing ones are updated or deleted.
- Start background jobs that will batch through the whole table and copy the
id
andbuild_id
values to the new columns.
We'll follow with a cleanup migration in the next milestone than the one that the aforementioned migrations are deployed, which will add the necessary indexes, swap the PK (and its sequence) and the FK and finally drop the old columns.
Related Tracking Issue: #276020 (closed)