Skip to content

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, with bigint 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