Reintroduce composite index_ssh_signatures_on_project_id_and_commit_sha for ssh_signatures table

Javiera Tapiarequested to merge
jtapiab/583644-reintroduce-composite-index-ssh-signatures into master

What does this MR do and why?

There was an original MR that attempted to:

  • Drop index_ssh_signatures_on_commit_sha and redundant index_ssh_signatures_on_project_id indexes
  • Add a new composite index index_ssh_signatures_on_project_id_and_commit_sha

This change is needed for Cells to ensure that the unique index by commit_sha is still unique for the ssh_signatures table. This MR was reverted: Revert "Cells: Add composite index index_ssh_si... (!215953 - merged)

References

Database

Before migration 

                                             Table "public.ssh_signatures"
         Column         |           Type           | Collation | Nullable |                  Default
------------------------+--------------------------+-----------+----------+--------------------------------------------
 id                     | bigint                   |           | not null | nextval('ssh_signatures_id_seq'::regclass)
 created_at             | timestamp with time zone |           | not null |
 updated_at             | timestamp with time zone |           | not null |
 project_id             | bigint                   |           | not null |
 key_id                 | bigint                   |           |          |
 verification_status    | smallint                 |           | not null | 0
 commit_sha             | bytea                    |           | not null |
 user_id                | bigint                   |           |          |
 key_fingerprint_sha256 | bytea                    |           |          |
 committer_email        | text                     |           |          |
Indexes:
    "ssh_signatures_pkey" PRIMARY KEY, btree (id)
    "index_ssh_signatures_on_commit_sha" UNIQUE, btree (commit_sha)
    "index_ssh_signatures_on_key_id" btree (key_id)
    "index_ssh_signatures_on_project_id" btree (project_id)
    "index_ssh_signatures_on_user_id" btree (user_id)
Check constraints:
    "check_73776e38f9" CHECK (char_length(committer_email) <= 255)
Foreign-key constraints:
    "fk_0c83baaa5f" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    "fk_7d2f93996c" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_aa1efbe865" FOREIGN KEY (key_id) REFERENCES keys(id) ON DELETE SET NULL

After migration 

                                             Table "public.ssh_signatures"
         Column         |           Type           | Collation | Nullable |                  Default
------------------------+--------------------------+-----------+----------+--------------------------------------------
 id                     | bigint                   |           | not null | nextval('ssh_signatures_id_seq'::regclass)
 created_at             | timestamp with time zone |           | not null |
 updated_at             | timestamp with time zone |           | not null |
 project_id             | bigint                   |           | not null |
 key_id                 | bigint                   |           |          |
 verification_status    | smallint                 |           | not null | 0
 commit_sha             | bytea                    |           | not null |
 user_id                | bigint                   |           |          |
 key_fingerprint_sha256 | bytea                    |           |          |
 committer_email        | text                     |           |          |
Indexes:
    "ssh_signatures_pkey" PRIMARY KEY, btree (id)
    "index_ssh_signatures_on_key_id" btree (key_id)
    "index_ssh_signatures_on_project_id_and_commit_sha" UNIQUE, btree (project_id, commit_sha)
    "index_ssh_signatures_on_user_id" btree (user_id)
Check constraints:
    "check_73776e38f9" CHECK (char_length(committer_email) <= 255)
Foreign-key constraints:
    "fk_0c83baaa5f" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    "fk_7d2f93996c" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_aa1efbe865" FOREIGN KEY (key_id) REFERENCES keys(id) ON DELETE SET NULL

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #583644 (closed)

Edited by Javiera Tapia

Merge request reports