Skip to content

Replacement partial index for ci_builds.token

Andreas Brandl requested to merge ab/ci-builds-token-partial-index into master

What does this MR do?

This relates to #333812 (closed): We aim to replace the existing non-partial index on ci_builds.token with a partial index on ci_builds (token) WHERE token IS NOT NULL.

This is the first step to create the replacement index. Second step will be to drop the existing one. Doing that separately allows us to check if the new index is going to be picked up.

Per the database testing !64221 (comment 603117368), the new index is going to be < 5 GB in size. The existing index is 121 GB currently.


Up

== 20210616145254 AddPartialIndexForCiBuildsToken: migrating ==================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, :token, {:unique=>true, :where=>"token IS NOT NULL", :name=>"index_ci_builds_on_token_partial", :algorithm=>:concurrently})
   -> 0.0163s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:ci_builds, :token, {:unique=>true, :where=>"token IS NOT NULL", :name=>"index_ci_builds_on_token_partial", :algorithm=>:concurrently})
   -> 0.0059s
-- execute("RESET ALL")
   -> 0.0005s
== 20210616145254 AddPartialIndexForCiBuildsToken: migrated (0.0241s) =========

Down

== 20210616145254 AddPartialIndexForCiBuildsToken: reverting ==================
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0080s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_token_partial"})
   -> 0.0120s
-- execute("RESET ALL")
   -> 0.0006s
== 20210616145254 AddPartialIndexForCiBuildsToken: reverted (0.0216s) =========
Edited by Andreas Brandl

Merge request reports