PostgreSQL에서 인덱스 상태 유지하기: REINDEX와 VACUUM FULL 중 선택 시기

PostgreSQL 데이터베이스에서 최적의 성능을 유지하는 것은 종종 인덱스의 효율성에 달려 있습니다. 시간이 지남에 따라 빈번한 업데이트 및 삭제로 인해 인덱스가 과도하게 팽창하거나 단편화되어 쿼리 실행 속도가 느려지고 저장 공간 사용량이 증가할 수 있습니다. 이러한 성능 저하는 사소한 문제가 아니라 애플리케이션 응답성과 전반적인 시스템 상태에 심각한 영향을 미칠 수 있습니다. 이를 해결하기 위해 사용 가능한 도구, 특히 REINDEX와 VACUUM FULL을 이해하고 언제, 어떻게 적용해야 하는지를 아는 것은 모든 데이터베이스 관리자나 개발자에게 매우 중요합니다. 이 문서는 이 두 가지 강력한 명령어를 자세히 살펴보고, 포괄적인 기본 메커니즘, 사용 사례 및 PostgreSQL 인덱스를 최상의 상태로 유지하기 위한 실용적인 시사점을 탐구합니다.

기본 사항 이해하기: 팽창, 인덱스 및 MVCC

REINDEX와 VACUUM FULL에 대해 자세히 알아보기 전에 몇 가지 핵심 개념을 간략하게 살펴보겠습니다.

인덱스 팽창 (Index Bloat): PostgreSQL에서 행이 업데이트되거나 삭제되면 해당 행의 이전 버전 (및 해당 인덱스 항목)이 즉시 제거되지 않습니다. 대신 "데드 튜플 (dead tuples)"로 표시됩니다. 자동 VACUUM이 결국 이러한 데드 튜플을 정리하지만, 높은 변경률은 데드 튜플이 상당히 축적되어 불필요하게 인덱스가 커지는 원인이 될 수 있습니다. 이러한 불필요한 공간 소비와 더 많은 데이터 페이지를 스캔해야 하는 필요성은 "인덱스 팽창"을 구성합니다. 팽창은 디스크에서 더 많은 데이터를 읽어야 하고 캐시 활용이 덜 효과적이므로 성능에 직접적인 영향을 미칩니다.

PostgreSQL 인덱스: 인덱스는 데이터베이스 검색 엔진이 데이터 검색 속도를 높이는 데 사용할 수 있는 특수 조회 테이블입니다. 본질적으로 인덱싱된 열의 값 목록과 실제 테이블의 행에 대한 포인터입니다. 쿼리가 인덱싱된 열을 사용하면 PostgreSQL은 전체 테이블을 스캔하지 않고도 관련 행을 빠르게 찾을 수 있습니다.

MVCC (Multi-Version Concurrency Control): PostgreSQL은 전체 테이블을 잠그지 않고 동시 트랜잭션을 처리하기 위해 MVCC를 구현합니다. 행이 업데이트되면 행의 새 버전이 생성되고, 업데이트가 시작되기 전에 시작된 트랜잭션에는 이전 버전이 계속 표시됩니다. 이 메커니즘은 동시성에는 훌륭하지만, 데드 튜플과 그 결과인 팽창의 근본 원인입니다.

REINDEX: 인덱스 점진적으로 재구성하기

REINDEX는 하나 이상의 기존 인덱스를 재구성하는 데 사용되는 명령입니다. REINDEX를 실행하면 PostgreSQL은 이전 인덱스를 삭제하고 (또는 해당 항목을 무효로 표시하고) 최신 라이브 데이터를 사용하여 처음부터 다시 생성합니다. 이 프로세스는 오래된 인덱스 구조에 존재하는 모든 팽창을 제거합니다. 모든 데드 튜플이 삭제되고 새 인덱스가 활성 포인터만으로 구축되기 때문입니다.

REINDEX 작동 방식

높은 수준에서 REINDEX는 다음을 포함합니다:

1. 테이블 스캔: 라이브 행을 식별하기 위해 전체 테이블을 읽습니다.
2. 새 인덱스 구축: 라이브 행을 기반으로 완전히 새로운 인덱스 구조를 구성합니다.
3. 기존 인덱스 교체: 새 인덱스가 구축되면 원자적으로 이전 인덱스를 교체합니다.

REINDEX 사용 시기

REINDEX는 인덱스 팽창을 해결하기 위한 주요 도구입니다. 다음 시나리오에서 사용을 고려해야 합니다.

• 상당한 인덱스 팽창: pg_stats_reports 또는 pg_bloat_check 도구가 인덱스의 높은 팽창 비율을 나타내는 경우.
• 성능 저하: 특정 인덱스를 사용하는 쿼리가 눈에 띄게 느려지고 인덱스 팽창이 원인이라고 의심되는 경우.
• 손상: 드물지만 인덱스가 손상된 경우, REINDEX는 테이블 데이터를 기반으로 다시 구축하여 효과적으로 복구할 수 있습니다.
• 인덱스 매개변수 변경: 기존 인덱스에 새 저장 매개변수 또는 fillfactor 설정을 적용하기 위한 경우.

REINDEX를 사용한 실용적인 예제

예를 들어 설명해 보겠습니다. products 테이블과 idx_product_name 인덱스가 있다고 가정합니다.

-- 샘플 테이블 및 인덱스 생성
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2)
);

CREATE INDEX idx_product_name ON products (name);

-- 데이터 삽입
INSERT INTO products (name, price) SELECT 'Product ' || generate_series(1, 10000), random() * 100;

-- 많은 행 업데이트를 통해 팽창 시뮬레이션
UPDATE products SET name = 'Updated Product ' || id WHERE id > 5000;
DELETE FROM products WHERE id BETWEEN 100 AND 200;

-- 팽창 여부를 확인할 수 있습니다 (특정 확장 또는 쿼리 필요).
-- 데모를 위해 팽창이 발생했다고 가정합니다.

-- 특정 인덱스 재색인
REINDEX INDEX idx_product_name;

-- 테이블의 모든 인덱스 재색인
REINDEX TABLE products;

-- 데이터베이스의 모든 인덱스 재색인 (매우 리소스 집약적일 수 있습니다)
REINDEX DATABASE my_database;

REINDEX에 대한 중요 고려 사항:

• 잠금 (Locking): REINDEX는 실행 중에 해당 개체에 대한 읽기 및 쓰기를 방지하는 ACCESS EXCLUSIVE 잠금을 인덱스 (또는 테이블, 또는 데이터베이스)에 대해 획득할 수 있습니다. 이 서비스 중단은 고가용성 시스템에서 용납할 수 없을 수 있습니다.
• CONCURRENTLY 옵션: 테이블의 인덱스에 대해 PostgreSQL은 REINDEX INDEX CONCURRENTLY 및 REINDEX TABLE CONCURRENTLY 옵션을 제공합니다. 이 옵션은 ACCESS EXCLUSIVE 잠금을 유지하지 않고 재색인을 수행할 수 있으므로 프로덕션 환경에 적합합니다. 두 번의 테이블 스캔을 수행하며 더 많은 시간과 임시 디스크 공간이 필요합니다.

  REINDEX INDEX CONCURRENTLY idx_product_name;

  이렇게 동시적으로 구축된 인덱스는 동시 DML 작업을 차단하지 않고 원본을 대체합니다. CONCURRENTLY 옵션은 REINDEX DATABASE 또는 REINDEX SYSTEM에는 사용할 수 없습니다.

VACUUM FULL: 테이블 및 인덱스에서 공간 회수

VACUUM FULL은 VACUUM보다 훨씬 강력한 명령입니다. 일반 VACUUM은 데드 튜플을 사용하도록 표시하는 반면, VACUUM FULL은 완전히 새로운 테이블 버전을 작성하여 테이블 전체와 관련 인덱스를 처음부터 재구축합니다. 이렇게 하면 테이블 데이터와 인덱스 내의 모든 데드 튜플 공간이 효과적으로 회수되고 테이블이 디스크에서 압축됩니다.

VACUUM FULL 작동 방식

VACUUM FULL은 다음을 수행합니다:

1. 새 테이블 파일 생성: 라이브 행만 포함하는 테이블의 새 버전을 디스크의 새 위치에 씁니다.
2. 인덱스 재구축: 이 프로세스의 일부로 모든 관련 인덱스도 새 테이블 구조를 가리키도록 재구축됩니다.
3. 기존 테이블 교체: 새 테이블과 인덱스가 완성되면 이전 테이블과 관련 인덱스 파일이 제거됩니다.

VACUUM FULL 사용 시기

VACUUM FULL은 무딘 도구이며 심각한 잠금 영향으로 인해 신중하게 사용해야 합니다. 주요 사용 사례는 다음과 같습니다.

• 심각한 테이블 팽창: 일반 VACUUM (자동 VACUUM 포함)이 효율적으로 회수할 수 없는 엄청난 양의 데드 공간이 테이블 자체에 축적된 경우. 이는 특정 인덱스 팽창보다는 전체 테이블 저장 공간에 관한 것입니다.
• 특정 저장 공간 시나리오: 디스크 공간을 즉시 복구해야 하고 긴 서비스 중단 시간도 허용 가능하다는 확신이 있는 매우 드문 경우.

VACUUM FULL을 사용한 실용적인 예제

products 테이블 예제를 사용합니다:

-- 심각한 테이블 및 인덱스 팽창 시뮬레이션
-- (예: 자동 VACUUM이 실행되지 않은 상태에서 대규모 행 삭제)
DELETE FROM products WHERE id > 7000;

-- 이제 VACUUM FULL 실행
VACUUM FULL products;

VACUUM FULL에 대한 중요 고려 사항:

• 잠금 (Locking): VACUUM FULL은 처리 중인 테이블에 대해 ACCESS EXCLUSIVE 잠금을 획득합니다. 이는 VACUUM FULL이 완료될 때까지 해당 테이블 (및 암묵적으로 해당 인덱스)에서 어떤 읽기 또는 쓰기도 발생할 수 없음을 의미합니다. 대규모 테이블의 경우 이 서비스 중단은 몇 시간 동안 지속될 수 있으므로 일반적으로 프로덕션 시스템에는 적합하지 않습니다.
• 디스크 공간: 처리 중인 테이블의 크기와 거의 같은 양의 임시 디스크 공간이 필요하며, 테이블의 새 복사본을 생성합니다.
• 인덱스 영향: VACUUM FULL은 테이블 재구축의 부작용으로 인덱스 팽창을 제거하지만, 주로 테이블 팽창에 중점을 둡니다. 인덱스 팽창만 문제가 된다면 REINDEX (특히 CONCURRENTLY)가 거의 항상 선호되는 해결책입니다.
• 대안: 테이블 팽창의 경우, pg_repack (타사 유틸리티)와 같은 도구를 고려해 보세요. 이 도구는 장기간 ACCESS EXCLUSIVE 잠금을 유지하지 않고도 테이블과 인덱스를 온라인으로 재구축할 수 있습니다.

REINDEX와 VACUUM FULL 중 선택하기

선택은 해결하려는 특정 문제와 서비스 중단에 대한 허용 범위에 따라 달라집니다.

REINDEX를 선호하는 경우:

• 주로 테이블 자체보다는 인덱스에서 팽창이 관찰되는 경우.
• 손상된 인덱스를 복구해야 하는 경우.
• 인덱스 저장 매개변수를 수정하려는 경우.
• 최소한의 서비스 중단 시간이 필요한 경우 (CONCURRENTLY 사용).

VACUUM FULL을 고려해야 하는 경우 (극도의 주의와 함께):

• 테이블 자체가 심하게 팽창하고 VACUUM (자동 VACUUM 포함)이 충분하지 않은 경우.
• 장기간의 ACCESS EXCLUSIVE 잠금이 허용되는 예약된 유지 보수 기간이 있는 경우.
• 테이블에 대한 완전한 서비스 중단을 이해하고 수락하는 경우.

대부분의 최신 PostgreSQL 배포판에서 REINDEX INDEX CONCURRENTLY는 인덱스 유지 관리를 위한 우선적인 해결책입니다. 테이블 팽창이 반복되는 문제인 경우, VACUUM FULL을 사용하기 전에 자동 VACUUM 설정을 조정하거나 pg_repack과 같은 외부 도구를 탐색하는 것을 고려해 보세요.

결론

최적의 PostgreSQL 성능을 달성하려면 인덱스 상태를 유지하는 것이 가장 중요합니다. REINDEX와 VACUUM FULL 모두 팽창을 제거하고 공간을 회수할 수 있지만, 범위, 잠금 동작 및 시스템 가용성에 미치는 영향에서 중요한 차이가 있습니다. REINDEX는 인덱스 전용으로 설계되었으며, CONCURRENTLY 옵션은 온라인 인덱스 최적화에서 이를 우선적인 방법으로 만들고, 반면 VACUUM FULL은 전체 테이블에 작용하며 서비스 중단 시간이 긴 경우에만 사용해야 합니다. 올바른 도구를 선택하면 서비스 중단 없이 데이터베이스를 빠르고 효율적으로 유지 관리할 수 있습니다.