쿼리 재사용
데이터 크기
유지보수 오버헤드
사용하지 않는 인덱스 찾기
인덱스 이름 정의 요구사항
- 인덱스 이름에 대한 고려 사항
- 명시적인 이름이 필요한 이유
인덱스 존재 여부 테스트
비동기적으로 인덱스 생성하기
로컬에서 데이터베이스 인덱스 변경 사항 테스트
- 비동기적으로 생성된 인덱스 확인
비동기적으로 인덱스 제거하기

데이터베이스 인덱스 추가

인덱스는 데이터베이스 쿼리의 속도를 높이는 데 사용될 수 있지만, 언제 새로운 인덱스를 추가해야 할까요? 이 질문에 대한 전통적인 대답은 데이터 필터링이나 조인에 사용되는 각 열에 대해 인덱스를 추가하는 것입니다. 예를 들어, 다음 쿼리를 고려해보세요:

SELECT *
FROM projects
WHERE user_id = 2;

여기서 우리는 user_id 열로 필터링하고, 따라서 개발자는 이 열에 대해 인덱스를 추가하기로 결정할지도 모릅니다.

일반적으로 위의 방식으로 열을 인덱싱하는 것이 합리적인 경우도 있지만, 실제로는 부정적인 영향을 줄 수도 있습니다. 테이블에 데이터를 작성할 때마다 기존의 모든 인덱스를 업데이트해야 합니다. 인덱스가 많을수록 이 작업은 느려질 수 있습니다. 또한 인덱스는 데이터 양과 인덱스 유형에 따라 상당한 디스크 공간을 차지할 수 있습니다. 예를 들어, PostgreSQL은 일반적인 B-트리 인덱스로는 인덱싱할 수 없는 일부 데이터 유형을 인덱싱할 수 있는 GIN 인덱스를 제공합니다. 그러나 이러한 인덱스는 일반적으로 더 많은 데이터를 차지하며 B-트리 인덱스에 비해 업데이트 속도가 느립니다.

이 모든 이유로 새로운 인덱스를 추가할 때 다음과 같은 고려 사항을 중요시해야 합니다:

새로운 쿼리가 가능한 한 많은 기존 인덱스를 재사용하는가?
인덱스를 사용하는 것이 테이블의 행을 반복하는 것보다 빠른가?
인덱스를 유지하는 오버헤드가 쿼리 실행 시간을 줄이는 것만큼 가치 있는가?

어떤 상황에서는 인덱스가 필요하지 않을 수도 있습니다:

테이블이 작고(레코드 수가 1,000 미만) 크게 성장하지 않을 것으로 예상될 때.
기존의 인덱스가 충분한 행을 필터링한다.
인덱스가 추가된 후 쿼리 실행 시간이 크게 줄어들지 않는 경우.

또한, 넓은 인덱스는 쿼리의 모든 필터 조건과 일치할 필요가 없습니다. 단지 인덱스 조회가 충분히 선택적일 정도로 충분한 열을 포함하면 됩니다.

쿼리 재사용

첫 번째 단계는 쿼리가 가능한 한 많은 기존 인덱스를 재사용하는지 확인하는 것입니다. 예를 들어, 다음 쿼리를 고려해보세요:

SELECT *
FROM todos
WHERE user_id = 123
AND state = 'open';

이제 user_id 열에 이미 인덱스가 있지만 state 열에는 없다고 상상해봅시다. state이 인덱싱되지 않아서 이 쿼리가 성능이 좋지 않을 것으로 생각할 수 있습니다. 하지만 실제로는 user_id에 대한 인덱스가 충분히 많은 행을 필터링할 수 있는지에 따라 쿼리가 아주 잘 수행될 수 있습니다.

인덱스가 재사용되는지 확인하는 가장 좋은 방법은 쿼리를 EXPLAIN ANALYZE를 사용하여 실행하는 것입니다. 조인된 테이블과 필터링에 사용되는 열에 따라, 추가된 인덱스가 큰 영향을 주지 않을 수도 있습니다.

간단히 말해서:

가능한 한 많은 기존 인덱스를 재사용할 수 있도록 쿼리를 작성해보세요.
EXPLAIN ANALYZE를 사용하여 쿼리를 실행하고, 가장 이상적인 쿼리를 찾기 위해 결과를 분석해보세요.

데이터 크기

특히 작은 테이블에 대해서는 데이터베이스가 일반적으로 평행하게 스캔(모든 행을 반복함)하는 것이 더 빠를 수 있으므로, 데이터베이스는 인덱스를 사용할 수 없을 수도 있습니다.

테이블의 크기가 예상됨에 따라 인덱스를 추가하고, 쿼리에서 많은 행을 필터링해야 하는 경우에는 인덱스를 추가하는 것을 고려해보세요. 테이블의 크기가 작을 경우(< 1,000 레코드)나 기존의 인덱스가 이미 충분한 행을 필터링하는 경우에는 인덱스를 추가하지 않을 수도 있습니다.

유지보수 오버헤드

인덱스는 모든 테이블 쓰기에 업데이트되어야 합니다. PostgreSQL의 경우 테이블에 데이터를 기록할 때 모든 기존 인덱스가 업데이트됩니다. 결과적으로, 동일한 테이블에 많은 인덱스가 있으면 쓰기 속도가 느려집니다. 따라서 추가된 인덱스의 유지보수 오버헤드를 쿼리 성능과 균형을 이루는 것이 중요합니다.

예를 들어, 인덱스 추가로 SELECT 실행 시간이 5밀리초 감소하지만 INSERT/UPDATE/DELETE 실행 시간이 10밀리초 증가한다고 가정해봅시다. 이러한 경우 추가된 인덱스는 가치가 없을 수 있습니다. SELECT 실행 시간이 줄어들고 INSERT/UPDATE/DELETE 실행 시간이 영향을 받지 않을 때 인덱스가 더 유용합니다.

사용하지 않는 인덱스 찾기

사용하지 않는 인덱스를 확인하려면 다음 쿼리를 실행할 수 있습니다:

SELECT relname as table_name, indexrelname as index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) desc;

이 쿼리는 사용되지 않는 모든 인덱스를 나열하고 크기의 내림차순으로 정렬하여 출력합니다. 이 쿼리를 사용하면 기존의 인덱스가 여전히 필요한지 결정하는 데 도움이 됩니다. 각 열의 의미에 대한 자세한 정보는 다음 링크에서 찾을 수 있습니다: https://www.postgresql.org/docs/current/monitoring-stats.html.

프로덕션에서 인덱스가 여전히 사용되고 있는지 확인하려면 Thanos를 사용하세요:

sum by (type)(rate(pg_stat_user_indexes_idx_scan{env="gprd", indexrelname="INSERT INDEX NAME HERE"}[30d]))

이 쿼리 출력은 실제 데이터베이스의 사용과 관련이 있기 때문에 다음과 같은 요소에 영향을 받을 수 있습니다:

특정 쿼리가 실행되지 않아 특정 인덱스를 사용할 수 없음.
일부 테이블에 데이터가 적어 PostgreSQL이 인덱스 검색 대신 시퀀스 스캔을 사용함.

이 데이터는 자주 사용되는 데이터베이스에 대해서만 신뢰할 수 있으며, 가능한 많은 GitLab 기능을 사용하는 것이 좋습니다.

인덱스 이름 정의 요구사항

복잡한 정의를 가진 인덱스는 이동 메서드의 암묵적인 이름 지정 동작에 의존하는 대신 명시적으로 이름을 제공해야 합니다. 간단히 말해서, 다음 옵션 중 하나 이상을 사용하여 생성된 인덱스에 대해 명시적인 이름 인자를 제공해야 합니다:

where
using
order
length
type
opclass

인덱스 이름에 대한 고려 사항

제약 조건 네이밍 규칙 페이지를 확인하세요.

명시적인 이름이 필요한 이유

Rails는 데이터베이스에 중립적이므로 필수 옵션에 따라 인덱스 이름을 생성합니다. 테이블 이름과 열 이름만으로 인덱스 이름을 생성합니다. 예를 들어, 마이그레이션에서 다음 두 인덱스가 생성된다고 가정해 보세요.

def up
  add_index :my_table, :my_column
  
  add_index :my_table, :my_column, where: 'my_column IS NOT NULL'
end

두 번째 인덱스의 생성이 실패합니다. 왜냐하면 Rails가 두 인덱스에 대해 동일한 이름을 생성하기 때문입니다.

이 이름 지정 문제는 index_exists? 메서드의 동작으로 더욱 복잡해집니다. 이 메서드는 테이블 이름, 열 이름 및 고유성 명세만을 고려하여 비교할 때, 일치하는 인덱스만을 고려합니다. 예를 들어,

def up
  unless index_exists?(:my_table, :my_column, where: 'my_column IS NOT NULL')
    add_index :my_table, :my_column, where: 'my_column IS NOT NULL'
  end
end

index_exists? 호출은 :my_table 및 :my_column에 어떠한 인덱스가 존재한다면 true를 반환하고 인덱스 생성이 우회됩니다.

add_concurrent_index 도우미는 존재하는 테이블에 인덱스를 생성하기 위한 요구사항입니다. 트랜잭션 마이그레이션 내부에서 사용할 수 없으므로 이미 존재하는 인덱스를 검색합니다. 일치하는 값이 발견되면 인덱스 생성이 건너뜁니다. 특정 유형의 인덱스에 항상 이름을 요구함으로써, 오류 가능성이 크게 줄어듭니다.

인덱스 존재 여부 테스트

인덱스 이름에 의해 인덱스의 존재 여부를 테스트하는 가장 쉬운 방법은 index_name_exists? 메서드를 사용하는 것입니다. 그러나 index_exists? 메서드도 이름 옵션을 사용할 수 있습니다. 예를 들어:

class MyMigration < Gitlab::Database::Migration[2.1]
  INDEX_NAME = 'index_name'
  
  def up
    # 스키마 불일치로 인해 조건부로 인덱스를 생성해야 합니다
    unless index_exists?(:table_name, :column_name, name: INDEX_NAME)
      add_index :table_name, :column_name, name: INDEX_NAME
    end
  end
  
  def down
    # 아무 작업 필요 없음
  end
end

그러나 add_concurrent_index, remove_concurrent_index, remove_concurrent_index_by_name과 같은 동시 인덱스 도우미는 이미 내부적으로 존재 여부를 검사합니다.

… (중략) …

비동기적으로 인덱스 생성하기

매우 큰 테이블의 경우, 인덱스 생성은 관리하기 어려울 수 있습니다. add_concurrent_index는 보통 트래픽을 차단하지 않고 인덱스를 생성하지만, 여러 시간 동안 인덱스 생성이 실행되는 경우에는 여전히 문제가 될 수 있습니다. autovacuum과 같은 필수 데이터베이스 작업은 실행되지 않고 GitLab.com에서는 인덱스 생성이 완료될 때까지 배포 프로세스가 차단될 수 있습니다.

GitLab.com에 영향을 최소화하기 위해 주말 동안 비동기적으로 인덱스를 생성하는 프로세스가 있습니다. 보통 트래픽이 적고 배포도 적은 주말에 인덱스 생성이 진행되어 더 낮은 수준의 리스크로 진행될 수 있습니다.

인덱스 생성 일정 예약하기

비동기적으로 인덱스를 생성하는 사후 배포 마이그레이션을 포함하는 Merge Request를 생성하세요. 이로써 비동기적으로 인덱스를 생성할 준비가 됩니다.
추후 이슈 생성하여 인덱스를 동기적으로 생성하는 마이그레이션을 추가하세요.
비동기적으로 인덱스를 준비하는 MR에 추후 이슈에 대한 코멘트를 추가하세요.

아래는 비동기적으로 인덱스를 생성하기 위한 도움말을 사용하여 인덱스를 생성하는 예시입니다. 이 마이그레이션은 postgres_async_indexes 테이블에 인덱스 이름과 정의를 입력합니다. 주말에 실행되는 프로세스는 이 테이블에서 인덱스를 가져와 생성을 시도합니다.

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

# TODO: https://gitlab.com/gitlab-org/gitlab/-/issues/XXXXX에서 동기적으로 생성할 인덱스
def up
  prepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

def down
  unprepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

MR 배포 상태 확인 및 인덱스가 프로덕션에 존재하는지 확인

챗옵스를 사용하여 GitLab.com에서 사후 배포 마이그레이션이 실행되었는지 확인하세요. 명령어는 /chatops run auto_deploy status <merge_sha>입니다. 출력이 db/gprd로 나오면 프로덕션 데이터베이스에서 사후 배포 마이그레이션이 실행된 것입니다. 자세한 정보는 GitLab.com에서 사후 배포 마이그레이션이 실행되었는지 확인하는 방법을 확인하세요.
비동기적으로 생성된 인덱스의 경우, 다음 주에 인덱스가 주말에 생성될 수 있도록 기다려주세요.
Database Lab을 사용하여 생성이 성공했는지 확인하세요. 출력에서 인덱스가 유효하지 않음을 나타내면 안 됩니다.

인덱스를 동기적으로 생성하는 마이그레이션 추가

프로덕션 데이터베이스에 인덱스가 존재하는 것이 확인되면, 인덱스를 동기적으로 생성하는 두 번째 Merge Request를 생성하세요. 스키마 변경 사항은 두 번째 Merge Request의 structure.sql에 업데이트되어 커밋되어야 합니다. 동기적 마이그레이션은 GitLab.com에서는 No-op이지만 다른 설치에서는 예상대로 마이그레이션을 추가해야 합니다. 아래 블록은 이전 비동기적 예시에 두 번째 마이그레이션을 생성하는 방법을 보여줍니다.

경고: add_concurrent_index와 함께 두 번째 마이그레이션을 Merge하기 전에 프로덕션에 인덱스가 존재하는지 확인하세요. 두 번째 마이그레이션이 인덱스가 생성되기 전에 배포되면, 두 번째 마이그레이션 실행 시에 인덱스가 동기적으로 생성됩니다.

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

disable_ddl_transaction!

def up
  add_concurrent_index :ci_builds, :some_column, name: INDEX_NAME
end

def down
  remove_concurrent_index_by_name :ci_builds, INDEX_NAME
end

로컬에서 데이터베이스 인덱스 변경 사항 테스트

Merge Request를 생성하기 전에 로컬에서 데이터베이스 인덱스 변경 사항을 테스트해야 합니다.

비동기적으로 생성된 인덱스 확인

인덱스를 생성하기 위해 자신의 로컬 환경에서 비동기적 인덱스 도우미를 사용하여 변경 사항을 테스트하세요.

레일스 콘솔에서 Feature.enable(:database_async_index_creation)와 Feature.enable(:database_reindexing)을 실행하여 피처 플래그를 활성화하세요.
bundle exec rails db:migrate를 실행하여 postgres_async_indexes 테이블에 항목을 작성하세요.
bundle exec rails gitlab:db:execute_async_index_operations:all를 실행하여 모든 데이터베이스에서 인덱스가 비동기적으로 생성되도록 합니다.
인덱스를 확인하기 위해 GDK 명령어 gdk psql를 사용하여 PostgreSQL 콘솔을 열고 명령어 \d <index_name>를 실행하여 새로 생성된 인덱스가 있는지 확인하세요.

비동기적으로 인덱스 제거하기

매우 큰 테이블의 경우, 인덱스 제거는 관리하기 어려울 수 있습니다. remove_concurrent_index는 보통 트래픽을 차단하지 않고 인덱스를 제거하지만, 여러 시간 동안 인덱스 제거가 실행되는 경우에는 여전히 문제가 될 수 있습니다. autovacuum과 같은 필수 데이터베이스 작업은 실행되지 않고 GitLab.com의 배포 프로세스는 인덱스 제거가 완료될 때까지 차단됩니다.

GitLab.com에 영향을 최소화하기 위해 주말 동안 비동기적으로 인덱스를 제거하는 프로세스를 사용하세요. 보통 트래픽이 적고 배포도 적은 주말에 인덱스 제거가 진행되어 더 낮은 수준의 리스크로 진행될 수 있습니다.

인덱스 제거 일정 예약하기.
MR이 배포되고 인덱스가 프로덕션에 더 이상 존재하지 않는지 확인.
인덱스를 동기적으로 제거하는 마이그레이션 추가.

인덱스 제거 일정 예약하기

비동기적으로 인덱스 제거를 준비하는 사후 배포 마이그레이션을 포함하는 Merge Request를 생성하세요. 이로써 비동기적으로 인덱스를 제거할 준비가 됩니다.
추후 이슈 생성하여 인덱스를 동기적으로 제거하는 마이그레이션을 추가하세요.
비동기적으로 인덱스 제거를 준비하는 MR에 추후 이슈에 대한 코멘트를 추가하세요.

예를 들어, 비동기적으로 인덱스를 제거하기 위한 도움말은 다음과 같습니다.

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

# TODO: https://gitlab.com/gitlab-org/gitlab/-/issues/XXXXX에서 동기적으로 제거할 인덱스
def up
  prepare_async_index_removal :ci_builds, :some_column, name: INDEX_NAME
end

def down
  unprepare_async_index :ci_builds, :some_column, name: INDEX_NAME
end

이 마이그레이션은 postgres_async_indexes 테이블에 인덱스 이름과 정의를 입력합니다. 주말에 실행되는 프로세스는 이 테이블에서 인덱스를 가져와 제거를 시도합니다.

Merge Request를 생성하기 전에는 반드시 로컬에서 데이터베이스 인덱스 변경 사항을 테스트해야 합니다. 테스트의 결과를 Merge Request 설명에 포함하세요.

MR가 배포되었고 인덱스가 프로덕션에서 더는 존재하지 않는지 확인

/chatops run auto_deploy status <merge_sha>를 사용하여 GitLab.com에서 포스트-배포 마이그레이션이 실행되었는지 확인하세요. 출력이 db/gprd로 반환되면, 포스트-배포 마이그레이션이 프로덕션 데이터베이스에서 실행되었습니다. 자세한 정보는 GitLab.com에서 포스트-배포 마이그레이션이 실행되었는지 확인하는 방법을 참조하세요.
비동기적으로 삭제된 인덱스의 경우, 주말에 인덱스를 삭제할 수 있도록 다음 주까지 기다리세요.
Database Lab을 사용하여 삭제가 성공적으로 이루어졌는지 확인하세요. Database Lab은 삭제된 인덱스를 찾으려고 시도할 때 오류를 보고해야 합니다. 그렇지 않으면 인덱스가 아직 존재할 수 있습니다.

인덱스를 동기적으로 제거하는 마이그레이션 추가

프로덕션 데이터베이스에서 인덱스가 더는 존재하지 않음을 확인한 후, 인덱스를 동기적으로 제거하는 두 번째 Merge Request을 작성하세요. 스키마 변경 사항은 두 번째 Merge Request의 structure.sql에 업데이트되고 커밋되어야 합니다. 동기적인 마이그레이션은 GitLab.com에서는 무효이지만, 다른 설치 사례에 대비하여 여전히 마이그레이션을 추가해야 합니다. 예를 들어, 이전의 비동기적인 예제에 대한 두 번째 마이그레이션을 작성하는 방법은 다음과 같습니다:

경고: remove_concurrent_index_by_name과 함께 두 번째 마이그레이션을 Merge하기 전에 프로덕션에서 인덱스가 더는 존재하지 않는지 확인하세요. 두 번째 마이그레이션이 인덱스가 파괴되기 전에 배포된 경우, 두 번째 마이그레이션이 실행될 때 인덱스가 동기적으로 파괴됩니다.

# in db/post_migrate/

INDEX_NAME = 'index_ci_builds_on_some_column'

disable_ddl_transaction!

def up
  remove_concurrent_index_by_name :ci_builds, name: INDEX_NAME
end

def down
  add_concurrent_index :ci_builds, :some_column, name: INDEX_NAME
end

비동기적으로 제거된 인덱스 확인

인덱스를 제거하는 변경 사항을 테스트하려면 로컬 환경에서 비동기식 인덱스 도우미를 사용하세요:

Rails 콘솔에서 Feature.enable(:database_reindexing)를 실행하여 피처 플래그를 활성화하세요.
bundle exec rails db:migrate를 실행하여 postgres_async_indexes 테이블에 항목을 생성하세요.
bundle exec rails gitlab:db:reindex를 실행하여 인덱스를 비동기적으로 제거하세요.
인덱스를 확인하기 위해 PostgreSQL 콘솔을 열고 GDK 명령어 gdk psql을 사용하여 \d <index_name>을 실행하여 제거된 인덱스가 더는 존재하지 않는지 확인하세요.