Skip to content

Re-add missing routes index on some instances

Adam Hegyi requested to merge 446112-readd-routes-index-which-might-be-missing into master

What does this MR do and why?

This MR re-adds an index in the redirect_routes table which is missing on several self-managed instances. Adding the index on most instances will be a no-op.

Minor risk: we add a unique index on LOWER(path) which might cause issues when there are duplicates in the table. I think the risk is minor, because:

  • We also have a unique index on path.
  • We have a case insensitive uniqueness validation on the model level.
  • Not a high traffic table.

Output

Up:

main: == [advisory_lock_connection] object_id: 124440, pg_backend_pid: 115001
main: == 20240508085441 ReAddRedirectRoutesPathIndex: migrating =====================
main: -- indexes(:redirect_routes)
main:    -> 0.0064s
main: == 20240508085441 ReAddRedirectRoutesPathIndex: migrated (0.0096s) ============

main: == [advisory_lock_connection] object_id: 124440, pg_backend_pid: 115001
ci: == [advisory_lock_connection] object_id: 124640, pg_backend_pid: 115003
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: migrating =====================
ci: -- indexes(:redirect_routes)
ci:    -> 0.0031s
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: migrated (0.0098s) ============

Up 2, when index is missing:

main: == [advisory_lock_connection] object_id: 124440, pg_backend_pid: 110070
main: == 20240508085441 ReAddRedirectRoutesPathIndex: migrating =====================
main: -- indexes(:redirect_routes)
main:    -> 0.0050s
main: -- current_schema(nil)
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_exists?(:redirect_routes, "LOWER(path) varchar_pattern_ops", {:unique=>true, :name=>"index_redirect_routes_on_path_unique_text_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0016s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:redirect_routes, "LOWER(path) varchar_pattern_ops", {:unique=>true, :name=>"index_redirect_routes_on_path_unique_text_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0111s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240508085441 ReAddRedirectRoutesPathIndex: migrated (0.0302s) ============

main: == [advisory_lock_connection] object_id: 124440, pg_backend_pid: 110070
ci: == [advisory_lock_connection] object_id: 124680, pg_backend_pid: 110072
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: migrating =====================
ci: -- indexes(:redirect_routes)
ci:    -> 0.0023s
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: migrated (0.0091s) ============

Down:

ci: == [advisory_lock_connection] object_id: 124000, pg_backend_pid: 114039
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: reverting =====================
ci: == 20240508085441 ReAddRedirectRoutesPathIndex: reverted (0.0094s) ============

main: == [advisory_lock_connection] object_id: 124000, pg_backend_pid: 114461
main: == 20240508085441 ReAddRedirectRoutesPathIndex: reverting =====================
main: == 20240508085441 ReAddRedirectRoutesPathIndex: reverted (0.0026s) ============

MR acceptance checklist

Please 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 #446112 (closed)

Merge request reports