Functional index `index_on_routes_lower_path` missing

In context of gitlab-com/gl-infra/production#2885 (closed), we ran into an issue with recreating a functional index which led to said incident and outage.

The index in question is: index_on_routes_lower_path ON routes USING btree (lower(path::text))

This index is not present in the codebase in db/structure.sql. It is however present in our staging and production environments.

Cause

The index has been added in 2016 through a migration with 211f0193. However, the index definition wasn't present in db/schema.rb at the time. A guess is that functional indexes had not been supported at that time, but I'm not sure.

Impact

We've seen that a large installation heavily relies on this index. During the outage tracked by gitlab-com/gl-infra/production#2885 (closed), high frequency queries on the routes table had query plans with execution times up to 30s. With the index (and statistics for it) present, timings are down to only a few milliseconds.

The insight from this is two-fold:

  1. For self-hosted installations that were installed since >= 2017, this index is not present. For large installations, this may have significant impact on database load and request latencies.
  2. For GitLab.com, we already know there are significant inconsistencies with respect to the database structure. This is being tracked in &3928, with index differences being discussed in #271165 (closed).

Fix

Ship a database migration that creates the index unless it is present already. Make sure the change is included in db/structure.sql as usual.