Skip to content

Increase the performance of the PersonalAccessTokensFinder for the Credentials Inventory

Hakeem Abdul-Razakrequested to merge
524159-pat_fix_timeouts into master

What?

  • Increases the performance of the PatFinder by about 97.5-99.2% using the efficient InOperatorOptimization
    • Uses a gitlab.com top-level-group with 30,523 users for database testing
  • Creates a new ::CredentialsInventoryPersonalAccessTokensFinder in /ee directory
  • Adds 4 composite indexes for the 4 sorting orders we use on group_credentials_inventory (expires_at, created_at, last_used at & name)
  • Made to be compatible with the upcoming credentials inventory management API, current /admin and /group level credentials inventory
  • Flagged behind the credentials_inventory_pat_finder feature flag

Follow-ups

Index & limit for the name column
  • Due to the lack of a limit on the PAT name column, the following MRs will be created before adding an index for the account for the 4th sorting order:
    • Add the limit constraint
    • Add the 4th sorting index CREATE INDEX index_personal_access_tokens_on_user_id_and_name_and_pat_id ON personal_access_tokens USING btree(user_id, name, id DESC) WHERE impersonation = false
Credentials Inventory API
  • Make the /manage/personal_access_tokens GET endpoint use the credentials_inventory_pat_finder (!194865 (merged))

Why?

Database Review

Details
  • The query will be focused on the gitlab.com credentials inventory query
    • The only difference is that the array_scope is User.human for self-managed (no current issues from customers), but group.enterprise_user_details for gitlab.com (our focus)
  • Test enterprise_group_id = 4249178 (>~7k billable users, >~30k total users)
  • 15 seconds database timeout constraint
Old Query 
SELECT
    personal_access_tokens.id AS t0_r0,
    personal_access_tokens.user_id AS t0_r1,
    personal_access_tokens.name AS t0_r2,
    personal_access_tokens.revoked AS t0_r3,
    personal_access_tokens.expires_at AS t0_r4,
    personal_access_tokens.created_at AS t0_r5,
    personal_access_tokens.updated_at AS t0_r6,
    personal_access_tokens.scopes AS t0_r7,
    personal_access_tokens.impersonation AS t0_r8,
    personal_access_tokens.token_digest AS t0_r9,
    personal_access_tokens.expire_notification_delivered AS t0_r10,
    personal_access_tokens.last_used_at AS t0_r11,
    personal_access_tokens.after_expiry_notification_delivered AS t0_r12,
    personal_access_tokens.previous_personal_access_token_id AS t0_r13,
    personal_access_tokens.organization_id AS t0_r14,
    personal_access_tokens.seven_days_notification_sent_at AS t0_r15,
    personal_access_tokens.thirty_days_notification_sent_at AS t0_r16,
    personal_access_tokens.sixty_days_notification_sent_at AS t0_r17,
    personal_access_tokens.description AS t0_r18,
    users.id AS t1_r0,
    users.email AS t1_r1,
    users.encrypted_password AS t1_r2,
    users.reset_password_token AS t1_r3,
    users.reset_password_sent_at AS t1_r4,
    users.remember_created_at AS t1_r5,
    users.sign_in_count AS t1_r6,
    users.current_sign_in_at AS t1_r7,
    users.last_sign_in_at AS t1_r8,
    users.current_sign_in_ip AS t1_r9,
    users.last_sign_in_ip AS t1_r10,
    users.created_at AS t1_r11,
    users.updated_at AS t1_r12,
    users.name AS t1_r13,
    users.admin AS t1_r14,
    users.projects_limit AS t1_r15,
    users.failed_attempts AS t1_r16,
    users.locked_at AS t1_r17,
    users.username AS t1_r18,
    users.can_create_group AS t1_r19,
    users.can_create_team AS t1_r20,
    users.state AS t1_r21,
    users.color_scheme_id AS t1_r22,
    users.password_expires_at AS t1_r23,
    users.created_by_id AS t1_r24,
    users.last_credential_check_at AS t1_r25,
    users.avatar AS t1_r26,
    users.confirmation_token AS t1_r27,
    users.confirmed_at AS t1_r28,
    users.confirmation_sent_at AS t1_r29,
    users.unconfirmed_email AS t1_r30,
    users.hide_no_ssh_key AS t1_r31,
    users.admin_email_unsubscribed_at AS t1_r32,
    users.notification_email AS t1_r33,
    users.hide_no_password AS t1_r34,
    users.password_automatically_set AS t1_r35,
    users.encrypted_otp_secret AS t1_r36,
    users.encrypted_otp_secret_iv AS t1_r37,
    users.encrypted_otp_secret_salt AS t1_r38,
    users.otp_required_for_login AS t1_r39,
    users.otp_backup_codes AS t1_r40,
    users.public_email AS t1_r41,
    users.dashboard AS t1_r42,
    users.project_view AS t1_r43,
    users.consumed_timestep AS t1_r44,
    users.layout AS t1_r45,
    users.hide_project_limit AS t1_r46,
    users.note AS t1_r47,
    users.unlock_token AS t1_r48,
    users.otp_grace_period_started_at AS t1_r49,
    users.external AS t1_r50,
    users.incoming_email_token AS t1_r51,
    users.auditor AS t1_r52,
    users.require_two_factor_authentication_from_group AS t1_r53,
    users.two_factor_grace_period AS t1_r54,
    users.last_activity_on AS t1_r55,
    users.notified_of_own_activity AS t1_r56,
    users.preferred_language AS t1_r57,
    users.theme_id AS t1_r58,
    users.accepted_term_id AS t1_r59,
    users.feed_token AS t1_r60,
    users.private_profile AS t1_r61,
    users.roadmap_layout AS t1_r62,
    users.include_private_contributions AS t1_r63,
    users.commit_email AS t1_r64,
    users.group_view AS t1_r65,
    users.managing_group_id AS t1_r66,
    users.first_name AS t1_r67,
    users.last_name AS t1_r68,
    users.static_object_token AS t1_r69,
    users.role AS t1_r70,
    users.user_type AS t1_r71,
    users.static_object_token_encrypted AS t1_r72,
    users.otp_secret_expires_at AS t1_r73,
    users.onboarding_in_progress AS t1_r74,
    users.color_mode_id AS t1_r75,
    users.composite_identity_enforced AS t1_r76
FROM
    personal_access_tokens
    LEFT JOIN users ON users.id = personal_access_tokens.user_id
WHERE
    personal_access_tokens.user_id IN (
        SELECT
            users.id
        FROM
            users
            JOIN user_details ON users.id = user_details.user_id
        WHERE
            user_details.enterprise_group_id = 4249178
    ) AND
    personal_access_tokens.impersonation = false AND
    users.user_type = 0
ORDER BY
    personal_access_tokens.expires_at ASC,
    personal_access_tokens.id DESC
LIMIT 21
OFFSET 0
Optimized Query 
SELECT
    *
FROM
    (
        WITH RECURSIVE array_cte AS MATERIALIZED (
            SELECT
                user_details.user_id
            FROM
                user_details
            WHERE
                user_details.enterprise_group_id = 4249178
        ), recursive_keyset_cte AS (
            (
                SELECT
                    NULL::personal_access_tokens AS records,
                    array_cte_user_id_array,
                    personal_access_tokens_expires_at_array,
                    personal_access_tokens_id_array,
                    0::pg_catalog.int8 AS count
                FROM
                    (
                        SELECT
                            array_agg( array_cte.user_id ) AS array_cte_user_id_array,
                            array_agg( personal_access_tokens.expires_at ) AS personal_access_tokens_expires_at_array,
                            array_agg( personal_access_tokens.id ) AS personal_access_tokens_id_array
                        FROM
                            (
                                SELECT
                                    array_cte.user_id
                                FROM
                                    array_cte
                            ) AS array_cte
                            LEFT JOIN LATERAL (
                                SELECT
                                    personal_access_tokens.expires_at AS expires_at,
                                    personal_access_tokens.id AS id
                                FROM
                                    personal_access_tokens
                                WHERE
                                    personal_access_tokens.user_id = array_cte.user_id AND
                                    personal_access_tokens.impersonation = false
                                ORDER BY
                                    personal_access_tokens.expires_at ASC,
                                    personal_access_tokens.id DESC
                                LIMIT 1
                            ) AS personal_access_tokens ON true
                        WHERE
                            personal_access_tokens.id IS NOT NULL
                    ) AS array_scope_lateral_query
                LIMIT 1
            )
            UNION ALL
            (
                SELECT
                    (
                        SELECT
                            personal_access_tokens
                        FROM
                            personal_access_tokens
                        WHERE
                            personal_access_tokens.id = recursive_keyset_cte.personal_access_tokens_id_array[position]
                        LIMIT 1
                    ),
                    array_cte_user_id_array,
                    recursive_keyset_cte.personal_access_tokens_expires_at_array[:position_query.position - 1] || next_cursor_values.expires_at || recursive_keyset_cte.personal_access_tokens_expires_at_array[position_query.position + 1:],
                    recursive_keyset_cte.personal_access_tokens_id_array[:position_query.position - 1] || next_cursor_values.id || recursive_keyset_cte.personal_access_tokens_id_array[position_query.position + 1:],
                    recursive_keyset_cte.count + 1
                FROM
                    recursive_keyset_cte,
                    LATERAL (
                        SELECT
                            expires_at,
                            id,
                            position
                        FROM
                            unnest( personal_access_tokens_expires_at_array, personal_access_tokens_id_array ) WITH ORDINALITY AS u ( expires_at, id, position )
                        WHERE
                            id IS NOT NULL
                        ORDER BY
                            1 ASC NULLS LAST,
                            2 DESC
                        LIMIT 1
                    ) AS position_query,
                    LATERAL (
                        SELECT
                            record.expires_at,
                            record.id
                        FROM
                            (
                                VALUES
                                    ( NULL, NULL )
                            ) AS nulls
                            LEFT JOIN (
                                SELECT
                                    personal_access_tokens.expires_at AS expires_at,
                                    personal_access_tokens.id AS id
                                FROM
                                    (
                                        (
                                            SELECT
                                                personal_access_tokens.expires_at AS expires_at,
                                                personal_access_tokens.id AS id
                                            FROM
                                                personal_access_tokens
                                            WHERE
                                                personal_access_tokens.user_id = recursive_keyset_cte.array_cte_user_id_array[position] AND
                                                personal_access_tokens.impersonation = false AND
                                                recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NULL AND
                                                personal_access_tokens.expires_at IS NULL AND
                                                personal_access_tokens.id < recursive_keyset_cte.personal_access_tokens_id_array[position]
                                            ORDER BY
                                                personal_access_tokens.expires_at ASC,
                                                personal_access_tokens.id DESC
                                        )
                                        UNION ALL
                                        (
                                            SELECT
                                                personal_access_tokens.expires_at AS expires_at,
                                                personal_access_tokens.id AS id
                                            FROM
                                                personal_access_tokens
                                            WHERE
                                                personal_access_tokens.user_id = recursive_keyset_cte.array_cte_user_id_array[position] AND
                                                personal_access_tokens.impersonation = false AND
                                                recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND
                                                personal_access_tokens.expires_at IS NULL
                                            ORDER BY
                                                personal_access_tokens.expires_at ASC,
                                                personal_access_tokens.id DESC
                                        )
                                        UNION ALL
                                        (
                                            SELECT
                                                personal_access_tokens.expires_at AS expires_at,
                                                personal_access_tokens.id AS id
                                            FROM
                                                personal_access_tokens
                                            WHERE
                                                personal_access_tokens.user_id = recursive_keyset_cte.array_cte_user_id_array[position] AND
                                                personal_access_tokens.impersonation = false AND
                                                recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND
                                                personal_access_tokens.expires_at > recursive_keyset_cte.personal_access_tokens_expires_at_array[position]
                                            ORDER BY
                                                personal_access_tokens.expires_at ASC,
                                                personal_access_tokens.id DESC
                                        )
                                        UNION ALL
                                        (
                                            SELECT
                                                personal_access_tokens.expires_at AS expires_at,
                                                personal_access_tokens.id AS id
                                            FROM
                                                personal_access_tokens
                                            WHERE
                                                personal_access_tokens.user_id = recursive_keyset_cte.array_cte_user_id_array[position] AND
                                                personal_access_tokens.impersonation = false AND
                                                recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND
                                                personal_access_tokens.expires_at = recursive_keyset_cte.personal_access_tokens_expires_at_array[position] AND
                                                personal_access_tokens.id < recursive_keyset_cte.personal_access_tokens_id_array[position]
                                            ORDER BY
                                                personal_access_tokens.expires_at ASC,
                                                personal_access_tokens.id DESC
                                        )
                                    ) AS personal_access_tokens
                                ORDER BY
                                    personal_access_tokens.expires_at ASC,
                                    personal_access_tokens.id DESC
                                LIMIT 1
                            ) AS record ON true
                        LIMIT 1
                    ) AS next_cursor_values
            )
        )
        SELECT
            records.*
        FROM
            recursive_keyset_cte AS personal_access_tokens
        WHERE
            count <> 0
    ) AS personal_access_tokens
LIMIT 21
OFFSET 0
Base PAT Query Results
Cold Cache Warm Cache
1.130 min 549.268 ms
Indexes
  • expires_asc, id_desc is our most common tie-breaker, so we can add DESC to its index
  • the last 2 columns must be in the form (timestamp, tie_breaker)
  • PG's default order sort is ASC NULLS LAST 
exec CREATE INDEX index_personal_access_tokens_on_user_id_and_expires_at_and_pat_id ON personal_access_tokens USING btree(user_id, expires_at, id DESC) WHERE impersonation = false

exec CREATE INDEX index_personal_access_tokens_on_user_id_and_last_used_at_and_pat_id ON personal_access_tokens USING btree(user_id, last_used_at, id) WHERE impersonation = false

exec CREATE INDEX index_personal_access_tokens_on_user_id_and_created_at_and_pat_id ON personal_access_tokens USING btree(user_id, created_at, id) WHERE impersonation = false
Optimized PAT Query Results
Initial Query w/ Sorting Indexes Cold Cache Warm Cache
user_id, expires_at, id DESC (Default) 588.315 ms 230.366 ms
user_id, last_used_at, id 1.710 s 238.518 ms
user_id, created_at, id 585.158 ms 261.970 ms

Database Migrations

bin/rails db:migrate 
hakeem@habdul-razak--20240918-F45T4 gitlab % bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41935
main: == 20250612052552 AddIndexToPatsOnExpiresAt: migrating ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0083s
main: -- index_exists?(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0031s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0052s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612052552 AddIndexToPatsOnExpiresAt: migrated (0.0310s) ===============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41935
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41936
ci: == 20250612052552 AddIndexToPatsOnExpiresAt: migrating ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0086s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0038s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250612052552 AddIndexToPatsOnExpiresAt: migrated (0.0253s) ===============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41936
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41938
main: == 20250612065613 AddIndexToPatsOnLastUsedAt: migrating =======================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612065613 AddIndexToPatsOnLastUsedAt: migrated (0.0145s) ==============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41938
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41940
ci: == 20250612065613 AddIndexToPatsOnLastUsedAt: migrating =======================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0037s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0033s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250612065613 AddIndexToPatsOnLastUsedAt: migrated (0.0174s) ==============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41940
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41942
main: == 20250612065637 AddIndexToPatsOnCreatedAt: migrating ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0035s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612065637 AddIndexToPatsOnCreatedAt: migrated (0.0152s) ===============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41942
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41943
ci: == 20250612065637 AddIndexToPatsOnCreatedAt: migrating ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0039s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
ci:    -> 0.0055s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20250612065637 AddIndexToPatsOnCreatedAt: migrated (0.0212s) ===============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 41943

Database Rollback

index_pats_on_user_id_and_expires_at_and_pat_id

VERSION=20250612070642 bin/rails db:migrate:down:main && VERSION=20250612070642 bin/rails db:migrate:down:ci 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250612070642 bin/rails db:migrate:down:main && VERSION=20250612070642 bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43726
main: == 20250612070642 AddIndexToPatsOnExpiresAt: reverting ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0200s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0049s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_expires_at_and_pat_id"})
main:    -> 0.0016s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612070642 AddIndexToPatsOnExpiresAt: reverted (0.0408s) ===============

main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43726
ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43804
ci: == 20250612070642 AddIndexToPatsOnExpiresAt: reverting ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0213s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0062s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_expires_at_and_pat_id"})
ci:    -> 0.0020s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250612070642 AddIndexToPatsOnExpiresAt: reverted (0.0464s) ===============

ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43804

index_pats_on_user_id_and_last_used_at_and_pat_id

VERSION=20250612065613 bin/rails db:migrate:down:main && VERSION=20250612065613 bin/rails db:migrate:down:ci 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250612065613 bin/rails db:migrate:down:main && VERSION=20250612065613 bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43971
main: == 20250612065613 AddIndexToPatsOnLastUsedAt: reverting =======================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0199s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0046s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id"})
main:    -> 0.0021s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612065613 AddIndexToPatsOnLastUsedAt: reverted (0.0417s) ==============

main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 43971
ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44027
ci: == 20250612065613 AddIndexToPatsOnLastUsedAt: reverting =======================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0173s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0049s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id"})
ci:    -> 0.0019s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250612065613 AddIndexToPatsOnLastUsedAt: reverted (0.0412s) ==============

ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44027

index_pats_on_user_id_and_created_at_and_pat_id

VERSION=20250612065637 bin/rails db:migrate:down:main && VERSION=20250612065637 bin/rails db:migrate:down:ci 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250612065637 bin/rails db:migrate:down:main && VERSION=20250612065637 bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44147
main: == 20250612065637 AddIndexToPatsOnCreatedAt: reverting ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0165s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_created_at_and_pat_id"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250612065637 AddIndexToPatsOnCreatedAt: reverted (0.0342s) ===============

main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44147
ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44212
ci: == 20250612065637 AddIndexToPatsOnCreatedAt: reverting ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0180s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0042s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_created_at_and_pat_id"})
ci:    -> 0.0019s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250612065637 AddIndexToPatsOnCreatedAt: reverted (0.0637s) ===============

ci: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 44212

How to set up and validate locally

  • Navigate to the admin credentials inventory: https://gdk.test:3443/admin/credentials
    • Verify that filters & sorting work
  • Turn on SAAS Mode
  • Create a group with a premium/ultimate plan
  • As a top-level group owner:
    • Add multiple users to the group and make them enterprise users
gdk rails console
group = Group.find(group_id)
user = User.find_by(username: 'regular_user')
user.enterprise_group_id = group.id
user.save!

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Iterations

Draft 1

Related to #524159 (closed)

Blocked by this !193504 (merged) to remove 2 unused indexes on the PersonalAccessTokens table

What?

  • Increases the performance of the PatFinder by ~98.88% using the efficient InOperatorOptimization
  • Implements a scalable-optimization strategy that switches to an optimized PersonalAccessTokenFinder once a gitlab.com group surpasses a set amount of enterprise users
  • Adds 4 composite indexes for the 4 sorting orders we use on group_credentials_inventory (expires_at, created_at, updated_at & name)
  • Made to be compatible with the credentials inventory management API

Why?

Database

Details
  • Test enterprise_group_id = 4249178 (7k billable users)
  • 15 seconds database timeout constraint
Old Query for joe.ai 
explain SELECT "personal_access_tokens"."id" AS t0_r0, "personal_access_tokens"."user_id" AS t0_r1, "personal_access_tokens"."name" AS t0_r2, "personal_access_tokens"."revoked" AS t0_r3, "personal_access_tokens"."expires_at" AS t0_r4, "personal_access_tokens"."created_at" AS t0_r5, "personal_access_tokens"."updated_at" AS t0_r6, "personal_access_tokens"."scopes" AS t0_r7, "personal_access_tokens"."impersonation" AS t0_r8, "personal_access_tokens"."token_digest" AS t0_r9, "personal_access_tokens"."expire_notification_delivered" AS t0_r10, "personal_access_tokens"."last_used_at" AS t0_r11, "personal_access_tokens"."after_expiry_notification_delivered" AS t0_r12, "personal_access_tokens"."previous_personal_access_token_id" AS t0_r13, "personal_access_tokens"."organization_id" AS t0_r14, "personal_access_tokens"."seven_days_notification_sent_at" AS t0_r15, "personal_access_tokens"."thirty_days_notification_sent_at" AS t0_r16, "personal_access_tokens"."sixty_days_notification_sent_at" AS t0_r17, "personal_access_tokens"."description" AS t0_r18, "users"."id" AS t1_r0, "users"."email" AS t1_r1, "users"."encrypted_password" AS t1_r2, "users"."reset_password_token" AS t1_r3, "users"."reset_password_sent_at" AS t1_r4, "users"."remember_created_at" AS t1_r5, "users"."sign_in_count" AS t1_r6, "users"."current_sign_in_at" AS t1_r7, "users"."last_sign_in_at" AS t1_r8, "users"."current_sign_in_ip" AS t1_r9, "users"."last_sign_in_ip" AS t1_r10, "users"."created_at" AS t1_r11, "users"."updated_at" AS t1_r12, "users"."name" AS t1_r13, "users"."admin" AS t1_r14, "users"."projects_limit" AS t1_r15, "users"."failed_attempts" AS t1_r16, "users"."locked_at" AS t1_r17, "users"."username" AS t1_r18, "users"."can_create_group" AS t1_r19, "users"."can_create_team" AS t1_r20, "users"."state" AS t1_r21, "users"."color_scheme_id" AS t1_r22, "users"."password_expires_at" AS t1_r23, "users"."created_by_id" AS t1_r24, "users"."last_credential_check_at" AS t1_r25, "users"."avatar" AS t1_r26, "users"."confirmation_token" AS t1_r27, "users"."confirmed_at" AS t1_r28, "users"."confirmation_sent_at" AS t1_r29, "users"."unconfirmed_email" AS t1_r30, "users"."hide_no_ssh_key" AS t1_r31, "users"."admin_email_unsubscribed_at" AS t1_r32, "users"."notification_email" AS t1_r33, "users"."hide_no_password" AS t1_r34, "users"."password_automatically_set" AS t1_r35, "users"."encrypted_otp_secret" AS t1_r36, "users"."encrypted_otp_secret_iv" AS t1_r37, "users"."encrypted_otp_secret_salt" AS t1_r38, "users"."otp_required_for_login" AS t1_r39, "users"."otp_backup_codes" AS t1_r40, "users"."public_email" AS t1_r41, "users"."dashboard" AS t1_r42, "users"."project_view" AS t1_r43, "users"."consumed_timestep" AS t1_r44, "users"."layout" AS t1_r45, "users"."hide_project_limit" AS t1_r46, "users"."note" AS t1_r47, "users"."unlock_token" AS t1_r48, "users"."otp_grace_period_started_at" AS t1_r49, "users"."external" AS t1_r50, "users"."incoming_email_token" AS t1_r51, "users"."auditor" AS t1_r52, "users"."require_two_factor_authentication_from_group" AS t1_r53, "users"."two_factor_grace_period" AS t1_r54, "users"."last_activity_on" AS t1_r55, "users"."notified_of_own_activity" AS t1_r56, "users"."preferred_language" AS t1_r57, "users"."theme_id" AS t1_r58, "users"."accepted_term_id" AS t1_r59, "users"."feed_token" AS t1_r60, "users"."private_profile" AS t1_r61, "users"."roadmap_layout" AS t1_r62, "users"."include_private_contributions" AS t1_r63, "users"."commit_email" AS t1_r64, "users"."group_view" AS t1_r65, "users"."managing_group_id" AS t1_r66, "users"."first_name" AS t1_r67, "users"."last_name" AS t1_r68, "users"."static_object_token" AS t1_r69, "users"."role" AS t1_r70, "users"."user_type" AS t1_r71, "users"."static_object_token_encrypted" AS t1_r72, "users"."otp_secret_expires_at" AS t1_r73, "users"."onboarding_in_progress" AS t1_r74, "users"."color_mode_id" AS t1_r75, "users"."composite_identity_enforced" AS t1_r76 FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."user_id" IN (SELECT "users"."id" FROM "users" INNER JOIN "user_details" ON "users"."id" = "user_details"."user_id" WHERE "user_details"."enterprise_group_id" = 4249178) AND "personal_access_tokens"."impersonation" = FALSE AND "users"."user_type" = 0 ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC LIMIT 21 OFFSET 0
Sorting Indexes 
exec CREATE INDEX index_user_id_pat_id_and_expired_at ON personal_access_tokens USING btree(user_id, id, expires_at) WHERE impersonation = false

exec CREATE INDEX index_user_id_pat_id_and_updated_at ON personal_access_tokens USING btree(user_id, id, updated_at) WHERE impersonation = false

exec CREATE INDEX index_user_id_pat_id_and_created_at ON personal_access_tokens USING btree(user_id, id, created_at) WHERE impersonation = false

exec CREATE INDEX index_user_id_pat_id_and_name ON personal_access_tokens USING btree(user_id, id, name) WHERE impersonation = false
Optimized Query for joe.ai 
explain SELECT * FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT "users"."id" FROM "users" INNER JOIN "user_details" ON "users"."id" = "user_details"."user_id" WHERE "user_details"."enterprise_group_id" = 4249178), "recursive_keyset_cte" AS ((SELECT NULL::personal_access_tokens AS records, array_cte_id_array, personal_access_tokens_expires_at_array, personal_access_tokens_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array, ARRAY_AGG("personal_access_tokens"."expires_at") AS personal_access_tokens_expires_at_array, ARRAY_AGG("personal_access_tokens"."id") AS personal_access_tokens_id_array FROM (SELECT "array_cte"."id" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = "array_cte"."id" AND "personal_access_tokens"."impersonation" = FALSE ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC LIMIT 1) personal_access_tokens ON TRUE WHERE "personal_access_tokens"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(SELECT (SELECT personal_access_tokens FROM "personal_access_tokens" WHERE "personal_access_tokens"."id" = recursive_keyset_cte.personal_access_tokens_id_array[position] LIMIT 1), array_cte_id_array, recursive_keyset_cte.personal_access_tokens_expires_at_array[:position_query.position-1]||next_cursor_values.expires_at||recursive_keyset_cte.personal_access_tokens_expires_at_array[position_query.position+1:], recursive_keyset_cte.personal_access_tokens_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.personal_access_tokens_id_array[position_query.position+1:], recursive_keyset_cte.count + 1 FROM recursive_keyset_cte,
LATERAL (SELECT expires_at, id, position FROM UNNEST(personal_access_tokens_expires_at_array, personal_access_tokens_id_array) WITH ORDINALITY AS u(expires_at, id, position) WHERE id IS NOT NULL ORDER BY 1 ASC NULLS LAST, 2 DESC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."expires_at", "record"."id" FROM (VALUES (NULL, NULL)) AS nulls LEFT JOIN (SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM ((SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = recursive_keyset_cte.array_cte_id_array[position] AND "personal_access_tokens"."impersonation" = FALSE AND recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NULL AND "personal_access_tokens"."expires_at" IS NULL AND "personal_access_tokens"."id" < recursive_keyset_cte.personal_access_tokens_id_array[position] ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC)
UNION ALL
(SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = recursive_keyset_cte.array_cte_id_array[position] AND "personal_access_tokens"."impersonation" = FALSE AND recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND "personal_access_tokens"."expires_at" IS NULL ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC)
UNION ALL
(SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = recursive_keyset_cte.array_cte_id_array[position] AND "personal_access_tokens"."impersonation" = FALSE AND recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND "personal_access_tokens"."expires_at" > recursive_keyset_cte.personal_access_tokens_expires_at_array[position] ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC)
UNION ALL
(SELECT "personal_access_tokens"."expires_at" AS expires_at, "personal_access_tokens"."id" AS id FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = recursive_keyset_cte.array_cte_id_array[position] AND "personal_access_tokens"."impersonation" = FALSE AND recursive_keyset_cte.personal_access_tokens_expires_at_array[position] IS NOT NULL AND "personal_access_tokens"."expires_at" = recursive_keyset_cte.personal_access_tokens_expires_at_array[position] AND "personal_access_tokens"."id" < recursive_keyset_cte.personal_access_tokens_id_array[position] ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC)) personal_access_tokens ORDER BY "personal_access_tokens"."expires_at" ASC, "personal_access_tokens"."id" DESC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
)) SELECT (records).* FROM "recursive_keyset_cte" AS "personal_access_tokens" WHERE (count <> 0)) personal_access_tokens LIMIT 21 OFFSET 0
Scenario Base Query Optimized Query w/ Order Sort Indexes Improvement
Cold Cache 53.196 seconds 596.374 ms ~98.88% +

Inital query times of the base query varied from ~20->~50s

Database Migrations

bin/rails db:migrate 
hakeem@habdul-razak--20240918-F45T4 gitlab % bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90894
main: == 20250604001619 AddIndexToPatsOnExpiresAt: migrating ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0192s
main: -- index_exists?(:personal_access_tokens, [:user_id, :id, :expires_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_expires_at", :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:personal_access_tokens, [:user_id, :id, :expires_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_expires_at", :algorithm=>:concurrently})
main:    -> 0.0051s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604001619 AddIndexToPatsOnExpiresAt: migrated (0.0421s) ===============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90894
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90895
ci: == 20250604001619 AddIndexToPatsOnExpiresAt: migrating ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :id, :expires_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_expires_at", :algorithm=>:concurrently})
ci:    -> 0.0074s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :id, :expires_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_expires_at", :algorithm=>:concurrently})
ci:    -> 0.0037s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604001619 AddIndexToPatsOnExpiresAt: migrated (0.0228s) ===============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90895
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90897
main: == 20250604004156 AddIndexToPatsOnCreatedAt: migrating ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:personal_access_tokens, [:user_id, :id, :created_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_created_at", :algorithm=>:concurrently})
main:    -> 0.0042s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:personal_access_tokens, [:user_id, :id, :created_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_created_at", :algorithm=>:concurrently})
main:    -> 0.0046s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20250604004156 AddIndexToPatsOnCreatedAt: migrated (0.0189s) ===============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90897
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90898
ci: == 20250604004156 AddIndexToPatsOnCreatedAt: migrating ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :id, :created_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_created_at", :algorithm=>:concurrently})
ci:    -> 0.0032s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :id, :created_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_created_at", :algorithm=>:concurrently})
ci:    -> 0.0034s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604004156 AddIndexToPatsOnCreatedAt: migrated (0.0170s) ===============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90898
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90900
main: == 20250604004507 AddIndexToPatsOnUpdatedAt: migrating ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:personal_access_tokens, [:user_id, :id, :updated_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_updated_at", :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:personal_access_tokens, [:user_id, :id, :updated_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_updated_at", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604004507 AddIndexToPatsOnUpdatedAt: migrated (0.0141s) ===============

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90900
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90902
ci: == 20250604004507 AddIndexToPatsOnUpdatedAt: migrating ========================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :id, :updated_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_updated_at", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :id, :updated_at], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_updated_at", :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604004507 AddIndexToPatsOnUpdatedAt: migrated (0.0173s) ===============

ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90902
main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90904
main: == 20250604004844 AddIndexToPatsOnName: migrating =============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:personal_access_tokens, [:user_id, :id, :name], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_name", :algorithm=>:concurrently})
main:    -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:personal_access_tokens, [:user_id, :id, :name], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_name", :algorithm=>:concurrently})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604004844 AddIndexToPatsOnName: migrated (0.0143s) ====================

main: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90904
ci: == [advisory_lock_connection] object_id: 136500, pg_backend_pid: 90905
ci: == 20250604004844 AddIndexToPatsOnName: migrating =============================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:personal_access_tokens, [:user_id, :id, :name], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_name", :algorithm=>:concurrently})
ci:    -> 0.0036s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:personal_access_tokens, [:user_id, :id, :name], {:where=>"impersonation IS false", :name=>"index_personal_access_tokens_on_user_id_and_id_and_name", :algorithm=>:concurrently})
ci:    -> 0.0034s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604004844 AddIndexToPatsOnName: migrated (0.0172s) ====================

Database Rollback

index_personal_access_tokens_on_user_id_and_id_and_expires_at

VERSION=20250604001619 bin/rails db:migrate:down:main 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250604001619 bin/rails db:migrate:down:main && bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 93598
main: == 20250604001619 AddIndexToPatsOnExpiresAt: reverting ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0198s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0054s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_user_id_and_id_and_expires_at"})
main:    -> 0.0041s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20250604001619 AddIndexToPatsOnExpiresAt: reverted (0.0446s) ===============

index_personal_access_tokens_on_user_id_and_id_and_created_at

VERSION=20250604004156 bin/rails db:migrate:down:main 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250604004156 bin/rails db:migrate:down:main && bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 94557
main: == 20250604004156 AddIndexToPatsOnCreatedAt: reverting ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0239s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0058s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_user_id_and_id_and_created_at"})
main:    -> 0.0037s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20250604004156 AddIndexToPatsOnCreatedAt: reverted (0.0531s) ===============

index_personal_access_tokens_on_user_id_and_id_and_updated_at

VERSION=20250604004507 bin/rails db:migrate:down:main 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250604004507 bin/rails db:migrate:down:main
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 94843
main: == 20250604004507 AddIndexToPatsOnUpdatedAt: reverting ========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0190s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0040s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_user_id_and_id_and_updated_at"})
main:    -> 0.0028s
main: -- execute("RESET statement_timeout")
main:    -> 0.0260s
main: == 20250604004507 AddIndexToPatsOnUpdatedAt: reverted (0.0711s) ===============

main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 94843

index_personal_access_tokens_on_user_id_and_id_and_name

VERSION=20250604004844 bin/rails db:migrate:down:main 
hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250604004844 bin/rails db:migrate:down:main
main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 95048
main: == 20250604004844 AddIndexToPatsOnName: reverting =============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0199s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0044s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_personal_access_tokens_on_user_id_and_id_and_name"})
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604004844 AddIndexToPatsOnName: reverted (0.0410s) ====================

main: == [advisory_lock_connection] object_id: 136200, pg_backend_pid: 95048

How to set up and validate locally

  • Turn on SAAS Mode
  • Create a group with a premium/ultimate plan
  • As a top-level group owner:
    • Add multiple users to the group and make them enterprise users
gdk rails console
group = Group.find(group_id)
user = User.find_by(username: 'regular_user')
user.enterprise_group_id = group.id
user.save!

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Hakeem Abdul-Razak

Merge request reports