Add index for partitioned PK on ci_pipelines
Technical Proposal
Prepare Ci::Pipeline
for partitioning, similar to what we did to partition Ci::BuildMetadata.
It creates 2 asynchronous indexes required to partition this table:
- Primary key:
ci_pipelines: (id, partition_id)
. - Unique constraint
ci_pipelines: (project_id, iid, partition_id)
.
Indexes:
"ci_pipelines_pkey" PRIMARY KEY, btree (id)
"index_ci_pipelines_on_project_id_and_iid" UNIQUE, btree (project_id, iid) WHERE iid IS NOT NULL
Use swap_primary_key to make sure the primary key include the partition key.
This will create the following schema:
Indexes:
"ci_pipelines_pkey" PRIMARY KEY, btree (id, partition_id)
"index_ci_pipelines_on_project_id_and_iid_and_partition_id" UNIQUE, btree (project_id, iid, partition_id) WHERE iid IS NOT NULL
Additionally create a synchronous index which will act as a no-op
on gitlab.com
.
You can follow the documentation to create those indexes locally.
Why are we doing this?
Those indexes will be used to partition ci_pipelines
as explained in this MR introducing zero partition helper.
Edited by Caroline Simpson