Skip to content

Drop tmp_index_oauth_access_tokens_on_id_where_expires_in_null

A temporary index was added in !107701 (merged) to facilitate backfilling expires_at values into each token. The backfill has been done, and the index is no longer needed. Considering:

                                           Table "public.oauth_access_tokens"
      Column       |            Type             | Collation | Nullable |                     Default                     
-------------------+-----------------------------+-----------+----------+-------------------------------------------------
 id                | integer                     |           | not null | nextval('oauth_access_tokens_id_seq'::regclass)
 resource_owner_id | integer                     |           |          | 
 application_id    | integer                     |           |          | 
 token             | character varying(255)      |           | not null | 
 refresh_token     | character varying(255)      |           |          | 
 expires_in        | integer                     |           |          | 7200
 revoked_at        | timestamp without time zone |           |          | 
 created_at        | timestamp without time zone |           | not null | 
 scopes            | character varying(255)      |           |          | 
Indexes:
    "oauth_access_tokens_pkey" PRIMARY KEY, btree (id)
    "index_oauth_access_tokens_on_application_id" btree (application_id)
    "index_oauth_access_tokens_on_refresh_token" UNIQUE, btree (refresh_token)
    "index_oauth_access_tokens_on_token" UNIQUE, btree (token)
    "partial_index_user_id_app_id_created_at_token_not_revoked" btree (resource_owner_id, application_id, created_at) WHERE revoked_at IS NULL
    "tmp_index_oauth_access_tokens_on_id_where_expires_in_null" btree (id) WHERE expires_in IS NULL
    "tmp_odx" btree (application_id, id) WHERE id > 22067338
Check constraints:
    "check_70f294ef54" CHECK (expires_in IS NOT NULL)

There is a check to ensure that expires in is not null, so the index has no reason to exist. Also this Thanos query shows us that the index is not being used.