Skip to content

Create index on security_findings asynchronously

What does this MR do and why?

This MR schedules index creation on security_findings table required for !88472 (merged) (tl;dr low query performance, explained in !88472 (comment 1015403873))

Related to Create `Mutations::SecurityFinding::CreateIssue` (#361948 - closed)

How to set up and validate locally

  1. https://docs.gitlab.com/ee/development/adding_database_indexes.html#verify-indexes-created-asynchronously

Database review

Index creation

gitlabhq_development=# SELECT * FROM postgres_async_indexes WHERE name = 'index_security_findings_uuid_id_order_desc';
 id |          created_at           |          updated_at           |                    name                    |                                                    definition                                                     |    table_name
----+-------------------------------+-------------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------+-------------------
 11 | 2022-07-06 11:57:47.537541+00 | 2022-07-06 11:57:47.537541+00 | index_security_findings_uuid_id_order_desc | CREATE INDEX CONCURRENTLY "index_security_findings_uuid_id_order_desc" ON "security_findings" ("uuid", "id" DESC) | security_findings
(1 row)

After gitlab:db:reindex

gitlabhq_development=# \d index_security_findings_uuid_id_order_desc
Index "public.index_security_findings_uuid_id_order_desc"
 Column |  Type  | Key? | Definition
--------+--------+------+------------
 uuid   | uuid   | yes  | uuid
 id     | bigint | yes  | id
btree, for table "public.security_findings"

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports

Loading