Ensure safety of having a composite primary key for `ci_builds`
Description
In the recent status update https://gitlab.com/gitlab-org/verify-stage/-/issues/352#note_1301715648 @mbobin mentioned that we soon plan to swap a primary key for ci_builds, to replace the normally used single :id primary key with a composite one (:id, :partition_id). This will allow us to soon partition ci_builds
Before we do that however, I wonder if it would be a good idea to double check and make sure that this is a safe thing to do. If we swap the primary key, then the uniqueness of ci_builds.id will not be guaranteed after we create a second parition.
Before we do that (either swap the primary key or create the second partition) should we answer following questions?
- What happens if there are two builds with the same
idin two different partitions? - How can we better guarantee consistency on the database or an application level?
- Does it potentially open a private data leak avenue when we mix up the two builds?
- Do we still want to use
:idas a reliable build identifier in the traces / general API / UI? - What can we do to reduce the risk of things going wrong?