일치하지 않는 B-tree 색인 찾기 및 수정

데이터베이스 색인 불일치는 소프트웨어 결함, 하드웨어 문제 또는 정렬 순서 변경과 같은 동작의 근본적인 변경 등 다양한 이유로 발생할 수 있습니다.

PostgreSQL 커뮤니티는 이러한 문제를 식별하고 해결하는 도구를 빌드했습니다. 여기에는 이전 버전의 PostgreSQL 14에서 나타난 문제를 비롯해 일관성 문제를 식별하기 위해 PostgreSQL 커뮤니티에서 권장하는 amcheck 등의 도구가 포함됩니다.

이 플레이북은 이러한 문제를 경험하는 PostgreSQL용 Cloud SQL 사용자를 위한 참조 자료로 작성되었습니다. 이 페이지가 다른 PostgreSQL 사용자가 일관되지 않은 b-tree 색인을 식별하고 문제를 해결하는 데 도움이 될 수 있기를 바랍니다. Google의 목표는 더 광범위한 오픈소스 커뮤니티의 리소스로 이 문서를 지속적으로 개선하는 것입니다. 의견이 있는 경우 이 페이지의 상단과 하단에 있는 의견 보내기 버튼을 사용하세요.

색인 불일치를 해결하려면 다음 단계를 수행합니다.

1. 시작하기 전에

   색인을 재생성하기 전에 데이터베이스를 백업하고 올바른 권한을 설정하고 psql 클라이언트 버전을 확인하며 amcheck 확장 프로그램을 다운로드해야 합니다.

2. 일치하지 않는 B-tree 색인을 확인합니다.

   불일치를 수정해야 하는 색인을 식별하려면 일치하지 않는 모든 B-tree 색인과 모든 고유 및 기본 키 위반사항을 식별해야 합니다.

3. 색인 불일치를 수정합니다.

   색인을 재생성하면 색인의 모든 불일치가 해결됩니다. 성능이 향상되도록 인스턴스의 메모리 설정을 조정해야 할 수도 있습니다.

4. 색인 재생성 작업을 모니터링합니다.

   색인 재생성 작업의 진행 상황을 모니터링하여 작업이 진행 중이며 차단되지 않았는지 확인하는 것이 좋습니다.

5. 색인이 일치하는지 확인합니다.

   색인이 성공적으로 재생성되면 색인에 불일치가 포함되어 있지 않은지 확인하는 것이 좋습니다.

시작하기 전에

데이터베이스 백업

색인 재생성 중에 데이터가 손실되지 않도록 데이터베이스를 백업하는 것이 좋습니다. 자세한 내용은 주문형 백업 만들기를 참조하세요.

주의: 이미 색인 불일치가 있는 데이터베이스에서 수행된 백업은 복원될 때 계속 불일치가 있습니다.

cloudsqlsuperuser 권한 설정

이 페이지의 단계를 완료하려면 cloudsqlsuperuser 권한이 있어야 합니다. 자세한 내용은 session_replication_role을 참조하세요.

psql 클라이언트 버전이 9.6 이상인지 확인

이 페이지의 단계를 완료하려면 psql 클라이언트 버전이 9.6 이상인지 확인해야 합니다. psql --version 명령어를 실행하여 현재 psql 클라이언트 버전을 확인합니다.

amcheck 확장 프로그램 설치

색인 불일치를 확인하려면 amcheck 확장 프로그램을 사용 설정해야 합니다.

PostgreSQL 9.6

참고: amcheck_next를 설치하기 전에 POSTGRES_9_6_24.R20220710.01_12가 설치되어 있는지 확인해야 합니다. 새로운 대상 유지보수 버전 설치 방법에 대한 자세한 내용은 셀프서비스 유지보수를 참조하세요.

PostgreSQL 9.6용 amcheck를 설치하려면 다음 문을 실행합니다.

  CREATE EXTENSION amcheck_next;
  

`확장 프로그램 제어 파일을 열 수 없음`이라는 오류가 표시되면 올바른 대상 유지보수 버전(POSTGRES_9_6_24.R20220710.01_12)이 실행 중인지 확인합니다.

PostgreSQL 10 이상

PostgreSQL 10 이상용 amcheck를 설치하려면 다음 문을 실행합니다.

  CREATE EXTENSION amcheck;
  

일치하지 않는 B-tree 색인 확인

다음 섹션에서는 색인의 불일치와 고유 및 기본 키 위반사항을 확인하여 B-tree 색인 불일치를 확인하는 방법을 설명합니다.

불일치 확인

다음 문을 실행하여 각 데이터베이스에서 모든 B-tree 색인의 불일치를 확인합니다.

코드 샘플

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started amcheck on database: %', current_database();
    SHOW server_version into version;
    SELECT split_part(version, '.', 1) into version;
    FOR r IN
      SELECT c.oid, c.oid::regclass relname, i.indisunique
        FROM pg_index i
        JOIN pg_opclass op ON i.indclass[0] = op.oid
        JOIN pg_am am ON op.opcmethod = am.oid
        JOIN pg_class c ON i.indexrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
      WHERE am.amname = 'btree'
        AND c.relpersistence != 't'
        AND c.relkind = 'i'
        AND i.indisready AND i.indisvalid LOOP
      BEGIN
        RAISE NOTICE 'Checking index %:', r.relname;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      EXCEPTION
        WHEN undefined_function THEN
          RAISE EXCEPTION 'Failed to find the amcheck extension';
        WHEN OTHERS THEN
          RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
          RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
      END;
    END LOOP;
    RAISE NOTICE 'Finished amcheck on database: %', current_database();
  END $$;
  

다음과 비슷한 출력이 표시됩니다.

출력

  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"
  

경고: 출력에 WARNING 알림이 포함되어 있지 않더라도 여전히 고유 또는 기본 키 위반이 존재할 수 있습니다. 위반을 확인하려면 고유 및 기본 키 위반을 확인하고 수정합니다.

PostgreSQL 로그 보기에 대한 자세한 내용은 인스턴스 로그 보기를 참조하세요.

고유 키와 기본 키의 위반사항 식별 및 수정

이 섹션에서는 색인에서 고유 키 및 기본 키 위반사항을 확인하는 방법과 위반사항이 있는 경우 이를 해결하는 방법을 설명합니다.

고유 키 위반 식별

색인을 재생성하기 전에 고유 키 위반사항을 해결해야 합니다. 모든 고유 키 위반사항을 확인하려면 각 데이터베이스에서 다음 명령어를 실행합니다.

코드 샘플

  WITH q AS (
      /* this gets info for all UNIQUE indexes */
      SELECT indexrelid::regclass as idxname,
            indrelid::regclass as tblname,
            indcollation,
            pg_get_indexdef(indexrelid),
            format('(%s)',(select string_agg(quote_ident(attname), ', ')
                from pg_attribute a
                join unnest(indkey) ia(nr) on ia.nr = a.attnum
              where attrelid = indrelid)) as idxfields,
            COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
        FROM pg_index
      WHERE indisunique
      /* next line excludes indexes not affected by collation changes */
        AND trim(replace(indcollation::text, '0', '')) != ''
  )
  SELECT
  /* the format constructs the query to execute for each index */
  format(
  $sql$
  DO $$ BEGIN RAISE NOTICE 'checking index=%3$I    on   table=%1$I      key_columns=%2$I '; END;$$;
  SELECT this,
        prev,
        /* we detect both reversed ordering or just not unique */
        (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
    FROM (SELECT %2$s AS this,
                lag(%2$s) OVER (ORDER BY %2$s) AS prev
            FROM %1$s %4$s
          ) s
  WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */
  $sql$, tblname, idxfields, idxname, whereclause
  )
    FROM q
  -- LIMIT 20 /* may use limit for testing */
  -- the next line tells psql to executes this query and then execute each returned line separately
  \gexec
  

이 스크립트 출력은 다음과 비슷합니다.

출력

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE
  

이 출력의 테이블 헤더 NOTICE에는 아래에 표시된 값의 색인, 열, 테이블이 표시됩니다. 출력에 DUPLICATE 또는 BACKWARDS가 표시된 행이 있는 경우 이는 손상되어 수정해야 할 수도 있는 색인이 있음을 나타냅니다. BACKWARDS가 있는 행은 숨겨진 중복 값을 나타낼 수 있습니다. 테이블에 이러한 항목이 표시되면 중복 키 위반 해결을 참조하세요.

중복 키 위반사항 해결

중복 고유 색인을 식별했거나 중복 키 위반 오류로 인해 색인 재생성 작업이 실패한 경우 다음 단계를 완료하여 중복 키를 찾고 삭제합니다.

참고: 주요 위반사항이 있는 각 색인에 대해 다음 단계를 반복해야 합니다.

1. 앞의 샘플 출력과 같이 NOTICE 테이블 헤더에서 key_columns를 추출합니다. 다음 예시에서 키 열은 email입니다.

   코드 샘플

     NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

   3단계의 쿼리에서 KEY_COLUMNS에 이러한 값을 사용합니다.

2. 테이블의 스키마를 찾습니다. psql을 사용하여 데이터베이스에 연결하고 다음 명령어를 실행합니다.

   코드 샘플

   \dt TABLE_NAME

   schema 열의 값은 3단계의 쿼리에서 SCHEMA_NAME에 대해 사용한 값입니다.

   예를 들면 다음 쿼리의 경우 다음과 같습니다.

    \dt games
    

   출력은 다음과 비슷합니다.

    List of relations
    Schema  | Name  | Type  | Owner
    --------+-------+-------+----------
    public  | games | table | postgres
    (1 row)
    

3. 다음 문을 실행하여 전체 테이블 스캔을 적용하고 중복 키를 가져옵니다.

   코드 샘플

   SET enable_indexscan = off;
   SET enable_bitmapscan = off;
   SET enable_indexonlyscan = off;
   
   SELECT KEY_COLUMNS, count(*)
     FROM SCHEMA_NAME.TABLE_NAME
   GROUP BY KEY_COLUMNS
   HAVING count(*) > 1;

   참고: 데이터베이스가 큰 경우 이 문을 완료하는 데 시간이 오래 걸릴 수 있습니다.

   위 문에서 KEY_COLUMNS는 확인하는 테이블의 고유 색인이나 기본 키에서 적용하는 하나 이상의 열입니다. 이러한 열은 고유 키 위반사항을 확인할 때 식별되었습니다. 이 문은 중복 키와 각 키의 중복 수를 반환합니다.

   예를 들면 다음 쿼리의 경우 다음과 같습니다.

     SELECT name,count(*)
       FROM public.TEST_NAMES
     GROUP BY name
     HAVING count(*) > 1;
     

   출력은 다음과 비슷합니다.

     name                | count
     --------------------+-------
     Johnny              |     2
     Peter               |     2
     (2 rows)
     

   이 경우 다음 단계로 계속 진행하여 중복 키를 삭제합니다.

   KEY_COLUMNS의 열 중 하나가 null이면 NULL 열에 고유한 제약조건이 적용되지 않으므로 무시해도 됩니다.

   중복 키가 없으면 일치하지 않는 색인 수정으로 이동할 수 있습니다.

4. 권장 선택사항: 중복 키가 포함된 레코드의 백업을 만듭니다. 다음 문을 실행하여 백업 레코드를 만듭니다.

   코드 샘플

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ((KEY_VALUES));
     

   이 문에서 KEY_VALUES는 이전 단계의 결과에서 복사된 값의 목록입니다. 예를 들면 다음과 같습니다.

   코드 샘플

     CREATE TABLE public.TEST_NAMES_bak
     AS SELECT * FROM public.TEST_NAMES
     WHERE (name) IN (('Johnny'),('Peter'))
     

   행 수가 많은 경우 IN 문의 ((KEY_VALUES)) 매개변수를 count 매개변수가 없는 2단계의 SELECT 문으로 간편하게 교체할 수 있습니다. 예를 들면 다음과 같습니다.

   코드 샘플

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ( SELECT (KEY_COLUMNS)
     FROM SCHEMA_NAME.TABLE_NAME
     GROUP BY (KEY_COLUMNS)
     HAVING count(*) > 1);
     

5. 사용자에게 복제 역할을 추가하여 트리거를 중지합니다.

   코드 샘플

     ALTER USER CURRENT_USER with REPLICATION;
     SET session_replication_role = replica;
     

6. 다음 문을 실행하여 중복 키를 삭제합니다.

   코드 샘플

     BEGIN;
   
     DELETE FROM  SCHEMA_NAME.TABLE_NAME a
         USING  (
                 SELECT   min(ctid) AS ctid,
                         KEY_COLUMNS
                 FROM     SCHEMA_NAME.TABLE_NAME
                 GROUP BY KEY_COLUMNS
                         HAVING count(*) > 1 ) b
         WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
         AND   a.ctid <> b.ctid;
     

   예를 들어 멀티 열 KEY_COLUMNS의 경우 다음과 같습니다.

   코드 샘플

     DELETE FROM public.test_random a
         USING (
                SELECT min(ctid) AS ctid,
                day, rnum
         FROM public.test_random
         GROUP BY day, rnum
                HAVING count(*) > 1 ) b
         WHERE a.day=b.day and a.rnum = b.rnum
         AND a.ctid <> b.ctid;
     

   여기서 day와 rnum은 KEY_COLUMNS입니다.

   이 문을 실행하면 중복 행 집합마다 행 허하나가 유지되고 다른 행은 삭제됩니다. 삭제할 행의 버전을 제어하려면 delete 문에서 다음 필터를 실행합니다.

   코드 샘플

     DELETE FROM  SCHEMA_NAME.TABLE_NAME
     WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
     

   경고: 이 트랜잭션을 커밋하면 데이터베이스가 변경되고 중복 키가 제거됩니다. 아직 필요한 데이터는 삭제하지 않도록 주의하세요. 백업에서 복원하면 일관되지 않은 색인이 복원될 수 있습니다.

7. 다음 단계를 완료하여 DELETE 명령어가 오류 없이 예상 행 수를 반환했는지 확인합니다.

   1. 다음 문을 실행하여 테이블이 변경된 행을 식별합니다.

      코드 샘플

        SELECT schemaname, relname, n_tup_del, n_tup_upd
          FROM pg_stat_xact_all_tables
        WHERE n_tup_del+n_tup_upd > 0;
        

   2. 모든 행이 올바르면 DELETE 트랜잭션을 커밋합니다.

      코드 샘플

        END;
        

   3. 오류가 발생한 경우 변경사항을 롤백하여 오류를 수정합니다.

      코드 샘플

        ROLLBACK;
        

8. 중복 키를 삭제한 후 색인을 다시 생성할 수 있습니다.

일치하지 않는 색인 수정

다음 섹션에서는 인스턴스에서 발견된 색인 불일치를 해결하는 방법을 설명합니다.

참고: 색인에 고유 및 기본 키 위반사항이 있으면 색인을 재생성하기 전에 이러한 위반사항을 수정해야 합니다.

데이터베이스 구성 방법에 따라 이전 단계에서 식별된 색인마다 다음을 수행해야 할 수 있습니다.

1. 색인 재생성을 준비합니다.

2. 색인을 재생성합니다.

3. 외래 키 위반사항으로 인해 색인 재생성 작업이 실패하면 이러한 위반사항을 찾아 수정해야 합니다.

4. 색인 재생성 작업을 다시 실행합니다.

색인 재생성 준비

색인 크기 찾기

대규모 데이터베이스의 색인을 생성하는 데 소규모 데이터베이스보다 많은 시간이 필요합니다. 대규모 데이터베이스의 색인 작업과 색인 재생성 작업 속도를 향상시키려면 이러한 작업에 더 많은 메모리와 CPU 성능을 할당하면 됩니다. 이 단계는 색인 재생성 작업을 계획할 때 중요한 단계입니다. 색인 크기를 알면 색인 재생성 작업에서 사용하는 메모리 크기를 설정하고 동시 작업자 수를 설정할 수 있습니다.

다음 문을 실행하여 수정하려는 색인의 색인 크기(KB)를 찾습니다.

코드 샘플

  SELECT i.relname                                      AS index_name,
        pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
  FROM   pg_index x
        JOIN pg_class i
          ON i.oid = x.indexrelid
  WHERE  i.relname = 'INDEX_NAME';
  

이 문의 출력은 다음과 비슷합니다.

출력

  index_name  | index_size
  ------------+------------
  my_index    | 16 kB
  (1 row)
  

색인 재생성에 사용할 메모리 크기 설정

이전 섹션에서 결정한 색인 크기에 따라 maintenance_work_mem에 적절한 값을 설정해야 합니다. 이 매개변수는 색인 재생성 작업에 사용할 메모리 양을 지정합니다. 예를 들어 색인 크기가 15GB보다 크면 유지보수 메모리를 조정하는 것이 좋습니다. 자세한 내용은 데이터베이스 플래그 설정을 참조하세요.

참고: maintenance_work_mem 값은 각 세션마다 설정됩니다. 값을 변경한 후 인스턴스를 다시 시작할 필요는 없습니다. 그러나 새 세션에서 데이터베이스에 다시 연결하면 이전 설정 값은 저장되지 않습니다.

대규모 데이터베이스의 색인을 생성하는 데 소규모 데이터베이스보다 많은 시간이 필요합니다. 색인 및 색인 재생성 작업의 속도를 개선하려면 maintenance_work_mem을 색인 재생성 작업 중 메모리가 4GB 이상인 인스턴스의 경우 인스턴스 메모리의 2% 이상으로 설정하는 것이 좋습니다.

동시 작업자 수 설정

PostgreSQL 11 이상을 사용하는 데이터베이스에서 max_parallel_maintenance_workers 매개변수를 설정하여 색인 재생성을 위한 동시 작업자 수를 늘릴 수 있습니다. 이 매개변수의 기본값은 2이지만 더 높은 값으로 설정하여 색인을 재생성하는 데 필요한 작업자 수를 늘릴 수 있습니다. vCPU 코어가 8개 이상인 인스턴스에서는 max_parallel_maintenance_workers 플래그 값을 4로 설정하는 것이 좋습니다.

자세한 내용은 데이터베이스 플래그 설정을 참조하세요.

색인 재생성

pg_repack 유틸리티를 사용하여 프로덕션 워크로드를 차단하지 않고 색인을 재생성할 수 있습니다. 이 유틸리티는 동시 색인 재생성 프로세스를 자동화하고 단순화하므로 다운타임 없이 특히 REINDEX CONCURRENTLY 작업이 없는 PostgreSQL 버전 11 이하에서 색인을 다시 생성할 수 있습니다. 이 절차에서는 pg_repack 버전 1.4.7을 사용합니다.

pg_repack을 사용하여 색인을 다시 생성하려면 다음 단계를 완료하세요.

1. pg_repack 페이지에서 pg_repack 유틸리티를 다운로드, 컴파일, 설치합니다.

   Debian GNU/Linux 11

   편의를 위해 Debian Linux 사용자는 이 Linux x86_64 플랫폼용으로 사전 빌드된 실행 가능한 바이너리를 다운로드하고 설치하는 것이 좋습니다.

   바이너리의 sha256 체크섬 해시는 다음과 같습니다.

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

   Linux 버전이 Debian GNU/Linux 11인지 확인하려면 hostnamectl 명령어를 실행합니다.

   자체 컴파일

   참고: 이 절차에서는 Linux 또는 Unix에서 작동하는 GNU 도구 모음과 함께 터미널에서 명령어를 실행해야 합니다.

   pg_repack 페이지에서 pg_repack 유틸리티를 다운로드, 컴파일, 설치합니다.

2. pg_repack 확장 프로그램을 만듭니다.

   코드 샘플

     CREATE EXTENSION pg_repack;
     

3. 다음 명령어를 실행하여 동시에 색인을 재생성합니다.

   코드 샘플

     pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
     

   참고: pg_repack을 사용하면 구체화된 뷰에서 색인을 다시 생성할 수 없습니다.

   이 명령어 출력은 다음과 비슷합니다.

   출력

     INFO: repacking index "public.t_i_key"
     

   pg_repack 실행 중에 오류가 발생하면 오류를 수정하고 다시 시도하면 됩니다. 모든 고유 키 색인과 기본 키 색인을 수정한 후에는 외래 키 위반사항을 확인하여 발견된 위반사항을 수정해야 합니다.

외래 키 위반사항 찾기 및 수정하기

외래 키 위반을 찾고 수정하는 방법에 대한 자세한 내용은 외래 키 위반 찾기 및 수정을 참조하세요.

색인 재생성 작업 모니터링

경우에 따라 색인 재생성 작업이 다른 세션에 의해 차단될 수도 있습니다. 4시간 간격으로 확인하는 것이 좋습니다. 색인 재생성 작업이 차단되면 색인 재생성 작업이 완료될 수 있도록 차단 세션을 취소할 수 있습니다.

다음 단계를 완료하여 차단 세션과 대기 세션을 식별하고 INDEX 작업에서 취소합니다.

1. 차단 세션을 식별하려면 다음 쿼리를 실행합니다.

   코드 샘플

     SELECT pid,
           usename,
           pg_blocking_pids(pid) AS blocked_by,
           query                 AS blocked_query
     FROM   pg_stat_activity
     WHERE  cardinality(pg_blocking_pids(pid)) > 0;
     

2. 세션을 취소하려면 이전 쿼리의 차단 세션 PID를 사용하여 다음 쿼리를 실행합니다.

   코드 샘플

     SELECT pg_cancel_backend(PID);
     

색인이 일치하는지 확인

불일치 색인마다 색인 불일치를 계속 확인해야 합니다. 모든 인스턴스의 일치하지 않는 색인과 키 위반사항을 수정한 후 이전 섹션의 단계를 수행하여 문제가 없는지 확인할 수 있습니다.

• 색인 불일치 확인

• 고유 키 위반사항 확인