Convert ci_build_trace_sections.build_id to bigint - Step 1: Add new columns and sync data
The ci_build_trace_sections
table is the largest CI table that references ci_builds, with ~5.4 Billion records. As the Primary Key of ci_builds
is at risk of overflowing, we have to convert both to bigint
.
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 build_id
column to it and keep them in sync with a trigger.
The overall process for the ci_build_trace_sections
table will be as follows:
- Create a new column
ci_build_trace_sections.build_id_convert_to_bigint
for the Foreign Key that referencesci_builds
(also part of the Primary Key), withbigint NOT NULL DEFAULT 0
- Install sync triggers to keep the new columns 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
build_id
values to the new column.
Care should be taken on how we are going to batch through ci_build_trace_sections
as it has a composite key defined:
"ci_build_trace_sections_pkey" PRIMARY KEY, btree (build_id, section_name_id)
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 FKs and finally drop the old columns.