Loading
Adding bigint FK for deployments
Summary
This code adds two database migration files that work together to upgrade the data type of a foreign key relationship in GitLab's database. The first migration creates a new temporary foreign key connection between the "deployments" and "environments" tables using a larger integer format (bigint) that can handle bigger ID numbers. The second migration prepares this new connection for validation to ensure data integrity. Both migrations include safety checks to only run when the conversion is actually needed, and they can be reversed if necessary. This is part of a gradual process to modernize the database structure to handle larger scale data without disrupting the running system.
The indexes were added in !214749 (merged)
References
this is on local development (with fake environment_id_convert_to_bigint column)
gitlabhq_development=# \d+ deployments
Table "public.deployments"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------------------+-----------------------------+-----------+----------+-----------------------------------------+----------+-------------+--------------+-------------
id | bigint | | not null | nextval('deployments_id_seq'::regclass) | plain | | |
iid | integer | | not null | | plain | | |
project_id | bigint | | not null | | plain | | |
environment_id | bigint | | not null | | plain | | |
ref | character varying | | not null | | extended | | |
tag | boolean | | not null | | plain | | |
sha | character varying | | not null | | extended | | |
user_id | bigint | | | | plain | | |
deployable_type | character varying | | | | extended | | |
created_at | timestamp without time zone | | | | plain | | |
updated_at | timestamp without time zone | | | | plain | | |
on_stop | character varying | | | | extended | | |
status | smallint | | not null | | plain | | |
finished_at | timestamp with time zone | | | | plain | | |
deployable_id | bigint | | | | plain | | |
archived | boolean | | not null | false | plain | | |
environment_id_convert_to_bigint | bigint | | | | plain | | |
Indexes:
"deployments_pkey" PRIMARY KEY, btree (id)
"index_deployments_for_visible_scope" btree (environment_id, finished_at DESC) WHERE status = ANY (ARRAY[1, 2, 3, 4, 6])
"index_deployments_on_archived_project_id_iid" btree (archived, project_id, iid)
"index_deployments_on_created_at" btree (created_at)
"index_deployments_on_deployable_type_and_deployable_id" btree (deployable_type, deployable_id)
"index_deployments_on_environment_id_and_id" btree (environment_id, id)
"index_deployments_on_environment_id_and_ref" btree (environment_id, ref)
"index_deployments_on_environment_id_status_and_finished_at" btree (environment_id, status, finished_at)
"index_deployments_on_environment_id_status_and_id" btree (environment_id, status, id)
"index_deployments_on_environment_status_sha" btree (environment_id, status, sha)
"index_deployments_on_id_and_status_and_created_at" btree (id, status, created_at)
"index_deployments_on_project_and_environment_and_updated_at_id" btree (project_id, environment_id, updated_at, id)
"index_deployments_on_project_and_finished" btree (project_id, finished_at) WHERE status = 2
"index_deployments_on_project_id_and_id" btree (project_id, id DESC)
"index_deployments_on_project_id_and_iid" UNIQUE, btree (project_id, iid)
"index_deployments_on_project_id_and_status_and_created_at" btree (project_id, status, created_at)
"index_deployments_on_project_id_and_updated_at_and_id" btree (project_id, updated_at DESC, id DESC)
"index_deployments_on_user_id_and_status_and_created_at" btree (user_id, status, created_at)
Foreign-key constraints:
"fk_009fd21147" FOREIGN KEY (environment_id) REFERENCES environments(id) ON DELETE CASCADE
"fk_009fd21147_tmp" FOREIGN KEY (environment_id_convert_to_bigint) REFERENCES environments(id) ON DELETE CASCADE
"fk_b9a3851b82" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
TABLE "deployment_approvals" CONSTRAINT "fk_2d060dfc73" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
TABLE "job_environments" CONSTRAINT "fk_8729424205" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
TABLE "deployment_clusters" CONSTRAINT "fk_rails_6359a164df" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
TABLE "deployment_merge_requests" CONSTRAINT "fk_rails_dcbce9f4df" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
Access method: heapHow to test locally
https://docs.gitlab.com/development/database/foreign_keys/#test-database-fk-changes-locally
Edited by Max Fan