Convert ci_stages.id to bigint - Step 1: Add new columns and sync data
The ci_stages
table is one of the top 9 tables at risk of a Primary Key Overflow risk.
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:
- Create new column
ci_stages.new_id
for the Primary Key, withbigint NOT NULL DEFAULT 0
- Install a sync trigger to keep it 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
to the new column.
The same is required for the column ci_builds.stage_id
that references ci_stages.id
; we'll track that in the ci_builds
conversion issue (#325618 (closed)).
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 finally drop the old column.
Edited by Yannis Roussos