REINDEX

REINDEX (REINDEX)

인덱스를 재구축(rebuild)하는 명령이에요. 인덱스가 손상됐거나 "비대해져서(bloated)" 공간을 과도하게 차지할 때, 저장 파라미터를 바꿨는데 그 효과를 확실히 적용하고 싶을 때 등에 사용합니다.

출처: PostgreSQL 문서

본문

개요 (Synopsis)

REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]

여기서 option은 다음 중 하나예요:

    CONCURRENTLY [ boolean ]
    TABLESPACE new_tablespace
    VERBOSE [ boolean ]

설명 (Description)

REINDEX는 인덱스 테이블에 저장된 데이터를 사용해 인덱스를 재구축하고, 인덱스의 기존 복사본을 교체해요. REINDEX를 사용해야 하는 여러 시나리오가 있어요:

  • 인덱스가 손상되어 더 이상 유효한 데이터를 포함하지 않음. 이론상 이런 일은 없어야 하지만, 실제로는 소프트웨어 버그나 하드웨어 실패로 인덱스가 손상될 수 있어요. REINDEX는 복구 방법을 제공해요.
  • 인덱스가 "비대해져서(bloated)", 즉 많고 텅 빈 또는 거의 빈 페이지를 포함하게 됨. PostgreSQL의 B-tree 인덱스에서 특정 드문 접근 패턴 아래에서 발생할 수 있어요. REINDEX는 죽은 페이지 없이 인덱스의 새 버전을 써서 인덱스의 공간 소비를 줄이는 방법을 제공해요. 자세한 내용은 Section 24.2를 참고하세요.
  • 인덱스에 대해 저장 파라미터(예: fillfactor)를 변경했고, 그 변경이 완전히 적용됐는지 확인하고 싶음.
  • CONCURRENTLY 옵션으로 인덱스 빌드가 실패하면, 그 인덱스는 "invalid" 상태로 남아요. 그런 인덱스는 쓸모없지만 REINDEX로 재구축하기 편리해요. REINDEX INDEX만이 invalid 인덱스에 대해 동시 빌드를 수행할 수 있다는 점에 유의하세요.

파라미터 (Parameters)

  • INDEX — 지정된 인덱스를 다시 만들어요. 이 형태의 REINDEX는 파티션 인덱스와 함께 쓸 때 트랜잭션 블록 안에서 실행할 수 없어요.
  • TABLE — 지정된 테이블의 모든 인덱스를 다시 만들어요. 테이블에 보조 "TOAST" 테이블이 있으면 그것도 재인덱싱돼요. 이 형태의 REINDEX는 파티션 테이블과 함께 쓸 때 트랜잭션 블록 안에서 실행할 수 없어요.
  • SCHEMA — 지정된 스키마의 모든 인덱스를 다시 만들어요. 이 스키마의 테이블에 보조 "TOAST" 테이블이 있으면 그것도 재인덱싱돼요. 공유 시스템 카탈로그의 인덱스도 처리돼요. 이 형태의 REINDEX는 트랜잭션 블록 안에서 실행할 수 없어요.
  • DATABASE — 시스템 카탈로그를 제외한 현재 데이터베이스 안의 모든 인덱스를 다시 만들어요. 시스템 카탈로그의 인덱스는 처리되지 않아요. 이 형태의 REINDEX는 트랜잭션 블록 안에서 실행할 수 없어요.
  • SYSTEM — 현재 데이터베이스 안의 시스템 카탈로그에 있는 모든 인덱스를 다시 만들어요. 공유 시스템 카탈로그의 인덱스는 포함돼요. 사용자 테이블의 인덱스는 처리되지 않아요. 이 형태의 REINDEX는 트랜잭션 블록 안에서 실행할 수 없어요.
  • name — 재인덱싱할 특정 인덱스, 테이블, 또는 데이터베이스의 이름이에요. 인덱스와 테이블 이름은 스키마 한정으로 쓸 수 있어요. 현재 REINDEX DATABASE와 REINDEX SYSTEM은 현재 데이터베이스만 재인덱싱할 수 있어요. 그 파라미터는 선택사항이며, 현재 데이터베이스의 이름과 일치해야 해요.
  • CONCURRENTLY — 이 옵션을 사용하면 PostgreSQL은 테이블에 대한 동시 insert, update, delete를 방해하는 잠금 없이 인덱스를 재구축해요. 반면 표준 인덱스 재구축은 끝날 때까지 테이블에 대한 쓰기(읽기는 아님)를 잠가요. 이 옵션을 쓸 때 알아둬야 할 몇 가지 주의 사항이 있어요 — 아래 동시 인덱스 재구축을 참고하세요. 임시 테이블의 경우 REINDEX는 다른 세션이 접근할 수 없고 비동시 재인덱싱이 더 저렴하므로 항상 비동시로 수행돼요.
  • TABLESPACE — 인덱스가 새 테이블스페이스에 재구축되도록 지정해요.
  • VERBOSE — 각 인덱스가 재인덱싱될 때 INFO 레벨로 진행 보고서를 출력해요.
  • boolean — 선택된 옵션을 켤지 끌지 지정해요. 옵션을 활성화하려면 TRUE, ON, 1을, 비활성화하려면 FALSE, OFF, 0을 쓸 수 있어요. boolean 값을 생략할 수도 있는데, 그 경우 TRUE가 가정돼요.
  • new_tablespace — 인덱스가 재구축될 테이블스페이스예요.

참고 (Notes)

사용자 테이블의 인덱스 손상을 의심하면, REINDEX INDEX나 REINDEX TABLE로 그 인덱스 또는 테이블의 모든 인덱스를 간단히 재구축할 수 있어요.

시스템 테이블의 인덱스 손상으로부터 복구해야 한다면 상황은 더 어려워져요. 이 경우 시스템이 의심되는 인덱스 중 어느 것도 스스로 사용하지 않는 것이 중요해요. (실제로 이런 시나리오에서는 손상된 인덱스에 의존해 서버 프로세스가 시작 시 즉시 크래시하는 것을 발견할 수 있어요.) 안전하게 복구하려면 서버가 시스템 카탈로그 조회에 인덱스를 사용하지 못하게 하는 -P 옵션으로 시작해야 해요.

이를 수행하는 한 가지 방법은 서버를 종료하고 명령줄에 -P 옵션이 포함된 단일 사용자 PostgreSQL 서버를 시작하는 거예요. 그런 다음 얼마나 많이 재구성하고 싶은지에 따라 REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, 또는 REINDEX INDEX를 실행할 수 있어요. 확신이 없으면 REINDEX SYSTEM을 사용해 데이터베이스의 모든 시스템 인덱스 재구성을 선택해요. 그런 다음 단일 사용자 서버 세션을 종료하고 일반 서버를 다시 시작해요. 단일 사용자 서버 인터페이스와 상호작용하는 방법에 대한 자세한 내용은 postgres 참조 페이지를 참고하세요.

대안으로, 명령줄 옵션에 -P가 포함된 일반 서버 세션을 시작할 수도 있어요. 이 방법은 클라이언트마다 다르지만, 모든 libpq 기반 클라이언트에서는 -P를 설정한 PGOPTIONS 환경 변수를 클라이언트 시작 전에 설정할 수 있어요. 이 방법은 다른 클라이언트를 잠글 필요가 없지만, 손상된 데이터베이스에 다른 사용자가 연결하지 못하게 하는 것이 복구가 끝날 때까지 여전히 현명할 수 있다는 점에 유의하세요.

REINDEX는 인덱스 내용이 처음부터 다시 구축된다는 점에서 인덱스의 drop 및 recreate와 비슷해요. 다만 잠금 고려 사항은 상당히 달라요. REINDEX는 인덱스의 부모 테이블에 대한 쓰기를 잠그지만 읽기는 잠그지 않아요. 또한 처리 중인 특정 인덱스에 ACCESS EXCLUSIVE 잠금을 걸어, 그 인덱스를 사용하려는 읽기를 차단해요. 특히 쿼리 플래너는 쿼리와 무관하게 테이블의 모든 인덱스에 ACCESS SHARE 잠금을 걸려고 하므로, REINDEX는 이 인덱스를 사용하지 않는 계획이 캐시된 일부 prepared 쿼리를 제외한 사실상 모든 쿼리를 차단해요. 대조적으로 DROP INDEX는 잠시 부모 테이블에 ACCESS EXCLUSIVE 잠금을 걸어 쓰기와 읽기를 모두 차단해요. 그 후 CREATE INDEX는 쓰기를 잠그지만 읽기는 잠그지 않아요. 인덱스가 거기 없으므로 어떤 읽기도 그것을 사용하려 하지 않아, 차단은 없지만 읽기가 값비싼 순차 스캔(sequential scan)으로 강제될 수 있어요.

REINDEX가 실행되는 동안 search_path는 일시적으로 pg_catalog, pg_temp로 변경돼요.

단일 인덱스나 테이블을 재인덱싱하려면 테이블에 대한 MAINTAIN 권한이 필요해요. 파티션 인덱스나 파티션 테이블의 REINDEX는 파티션 테이블에 대한 MAINTAIN 권한을 요구하지만, 그러한 명령은 개별 파티션을 처리할 때 권한 검사를 건너뛴다는 점에 유의하세요. 스키마나 데이터베이스를 재인덱싱하려면 그 스키마나 데이터베이스의 소유자이거나 pg_maintain 역할의 권한을 가져야 해요. 따라서 비수퍼유저가 다른 사용자가 소유한 테이블의 인덱스를 재구축하는 것이 가능함을 특히 유의하세요. 다만 특별한 예외로, REINDEX DATABASE, REINDEX SCHEMA, REINDEX SYSTEM은 사용자가 카탈로그에 대한 MAINTAIN 권한이 없으면 공유 카탈로그의 인덱스를 건너뛰어요.

파티션 인덱스나 파티션 테이블의 재인덱싱은 각각 REINDEX INDEX 또는 REINDEX TABLE로 지원돼요. 지정된 파티션 관계의 각 파티션은 별도의 트랜잭션으로 재인덱싱돼요. 이러한 명령은 파티션 테이블이나 인덱스에서 작업할 때 트랜잭션 블록 안에서 사용할 수 없어요.

파티션 인덱스나 테이블의 REINDEX에서 TABLESPACE 절을 사용하면, 리프 파티션의 테이블스페이스 참조만 갱신돼요. 파티션 인덱스는 갱신되지 않으므로, 새 파티션이 첨부될 때 새 테이블스페이스를 상속하도록 그 위에 ALTER TABLE ONLY를 별도로 사용하는 것이 권장돼요. 실패 시 모든 인덱스를 새 테이블스페이스로 옮기지 못했을 수 있어요. 명령을 다시 실행하면 모든 리프 파티션을 재구축하고 이전에 처리되지 않은 인덱스를 새 테이블스페이스로 옮겨요.

SCHEMA, DATABASE, SYSTEM을 TABLESPACE와 함께 사용하면 시스템 관계는 건너뛰고 단일 WARNING이 생성돼요. TOAST 테이블의 인덱스는 재구축되지만 새 테이블스페이스로 옮겨지지는 않아요.

동시 인덱스 재구축 (Rebuilding Indexes Concurrently)

인덱스 재구축은 데이터베이스의 정상 운영을 방해할 수 있어요. 보통 PostgreSQL은 인덱스가 재구축되는 테이블을 쓰기에 대해 잠그고 단일 테이블 스캔으로 전체 인덱스 빌드를 수행해요. 다른 트랜잭션은 여전히 테이블을 읽을 수 있지만, 테이블에 행을 삽입, 갱신, 삭제하려 하면 인덱스 재구축이 끝날 때까지 차단돼요. 시스템이 활성 운영 데이터베이스라면 이는 심각한 영향을 줄 수 있어요. 매우 큰 테이블은 인덱싱하는 데 몇 시간이 걸릴 수 있고, 더 작은 테이블에서도 인덱스 재구축은 운영 시스템에 허용할 수 없을 정도로 긴 기간 동안 작성자를 잠글 수 있어요.

PostgreSQL은 쓰기에 대한 최소 잠금으로 인덱스 재구축을 지원해요. 이 방법은 REINDEX의 CONCURRENTLY 옵션을 지정해 호출돼요. 이 옵션을 사용하면 PostgreSQL은 재구축해야 하는 각 인덱스에 대해 테이블을 두 번 스캔하고, 잠재적으로 그 인덱스를 사용할 수 있는 모든 기존 트랜잭션의 종료를 기다려야 해요. 이 방법은 표준 인덱스 재구축보다 더 많은 총 작업을 요구하고, 인덱스를 수정할 수 있는 미완료 트랜잭션을 기다려야 하므로 완료에 훨씬 오래 걸려요. 다만 인덱스가 재구축되는 동안 정상 운영을 계속할 수 있으므로, 이 방법은 운영 환경에서 인덱스를 재구축하는 데 유용해요. 물론 인덱스 재구축이 부과하는 추가 CPU, 메모리, I/O 부하는 다른 운영을 느리게 할 수 있어요.

동시 재인덱싱에서 다음 단계들이 발생해요. 각 단계는 별도의 트랜잭션으로 실행돼요. 재구축할 인덱스가 여러 개면 각 단계가 다음 단계로 넘어가기 전에 모든 인덱스를 순환해요.

  • 새 임시 인덱스 정의가 카탈로그 pg_index에 추가돼요. 이 정의는 기존 인덱스를 교체하는 데 사용돼요. 처리가 진행되는 동안 스키마 수정을 방지하기 위해 재인덱싱되는 인덱스와 관련 테이블에 세션 레벨의 SHARE UPDATE EXCLUSIVE 잠금이 걸려요.
  • 각 새 인덱스에 대해 인덱스를 구축하는 첫 번째 패스가 수행돼요. 인덱스가 구축되면 그 flag pg_index.indisready가 "true"로 전환되어 삽입 준비가 되고, 빌드를 수행한 트랜잭션이 끝나면 다른 세션에 보이게 돼요. 이 단계는 각 인덱스에 대해 별도의 트랜잭션으로 수행돼요.
  • 그런 다음 첫 번째 패스가 실행되는 동안 추가된 튜플을 추가하는 두 번째 패스가 수행돼요. 이 단계도 각 인덱스에 대해 별도의 트랜잭션으로 수행돼요.
  • 인덱스를 참조하는 모든 제약 조건이 새 인덱스 정의를 참조하도록 변경되고, 인덱스의 이름이 변경돼요. 이 시점에서 pg_index.indisvalid가 새 인덱스에 대해 "true"로, 기존 인덱스에 대해 "false"로 전환되고, 기존 인덱스를 참조한 모든 세션을 무효화하는 캐시 무효화가 수행돼요.
  • 기존 인덱스는 새 튜플 삽입을 방지하기 위해 pg_index.indisready가 "false"로 전환되는데, 이는 기존 인덱스를 참조할 수 있는 실행 중인 쿼리가 완료되기를 기다린 후에 오는 일이에요.
  • 기존 인덱스가 삭제돼요. 인덱스와 테이블에 대한 SHARE UPDATE EXCLUSIVE 세션 잠금이 해제돼요.

인덱스 재구축 중 문제(예: 고유 인덱스의 고유성 위반)가 발생하면 REINDEX 명령은 실패하지만, 기존 인덱스 외에 "invalid" 새 인덱스를 남겨요. 이 인덱스는 불완전할 수 있으므로 쿼리 목적에는 무시되지만, 갱신 오버헤드는 계속 소비해요. psql의 \d 명령은 그런 인덱스를 INVALID로 보고해요:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Modifiers
--------+---------+-----------
 col    | integer |
Indexes:
    "idx" btree (col)
    "idx_ccnew" btree (col) INVALID

INVALID로 표시된 인덱스가 _ccnew 접미사로 끝나면, 그것은 동시 작업 중 생성된 임시 인덱스에 해당하며 권장 복구 방법은 DROP INDEX로 삭제한 후 REINDEX CONCURRENTLY를 다시 시도하는 거예요. invalid 인덱스가 대신 _ccold 접미사로 끝나면, 그것은 삭제할 수 없었던 원래 인덱스에 해당하며 권장 복구 방법은 그냥 그 인덱스를 삭제하는 거예요. 재구축 자체는 성공했기 때문이에요. invalid 인덱스 이름의 접미사에는 고유성을 유지하기 위해 0이 아닌 숫자가 추가될 수 있어요. 예: _ccnew1, _ccold2 등.

일반 인덱스 빌드는 같은 테이블에서 다른 일반 인덱스 빌드가 동시에 발생하는 것을 허용하지만, 동시 인덱스 빌드는 한 번에 테이블 하나에서만 발생할 수 있어요. 두 경우 모두 그 동안 테이블에 대한 다른 유형의 스키마 수정은 허용되지 않아요. 또 다른 차이는 일반 REINDEX TABLE이나 REINDEX INDEX 명령은 트랜잭션 블록 안에서 수행될 수 있지만, REINDEX CONCURRENTLY는 그럴 수 없다는 점이에요.

다른 오래 실행되는 트랜잭션처럼, 테이블의 REINDEX는 어떤 다른 테이블에서 동시 VACUUM이 제거할 수 있는 튜플에 영향을 줄 수 있어요.

REINDEX SYSTEM은 시스템 카탈로그를 동시에 재인덱싱할 수 없으므로 CONCURRENTLY를 지원하지 않아요.

또한 배제 제약 조건(exclusion constraint)의 인덱스는 동시에 재인덱싱할 수 없어요. 그런 인덱스가 이 명령에 직접 이름이 지정되면 오류가 발생해요. 배제 제약 조건 인덱스가 있는 테이블이나 데이터베이스가 동시에 재인덱싱되면 그런 인덱스는 건너뛰어져요. (CONCURRENTLY 옵션 없이는 그런 인덱스를 재인덱싱할 수 있어요.)

REINDEX를 실행하는 각 백엔드는 pg_stat_progress_create_index 뷰에서 진행 상황을 보고해요. 자세한 내용은 Section 27.4.4를 참고하세요.

예시 (Examples)

단일 인덱스를 재구축해요:

REINDEX INDEX my_index;

테이블 my_table의 모든 인덱스를 재구축해요:

REINDEX TABLE my_table;

특정 데이터베이스의 모든 인덱스를, 시스템 인덱스가 이미 유효하다고 믿지 않고 재구축해요:

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

관련 관계에 대한 읽기·쓰기 작업을 차단하지 않고 테이블의 인덱스를 재구축해요:

REINDEX TABLE CONCURRENTLY my_broken_table;

호환성 (Compatibility)

SQL 표준에는 REINDEX 명령이 없어요.

더 알아보기 (Learn more)