깨진 인덱스 문제 해결: PostgreSQL 인덱스 재구축 및 복구 방법

인덱스는 일반적으로 PostgreSQL이 전체 테이블을 읽는 대신 밀리초 단위로 쿼리에 응답할 수 있게 해주는 이유입니다. 또한 인덱스는 비대해지거나, 유효하지 않게 되거나, 손상이 의심될 때까지 잊혀지기 쉽습니다. 그러면 처음에는 일반적인 성능 문제처럼 증상이 나타납니다. 특정 쿼리가 느려지고, 디스크 읽기가 증가하며, 한때 조용했던 테이블이 비용이 많이 들게 되거나, 쿼리 계획이 이해되지 않게 됩니다.

인덱스를 재구축하는 것은 어렵지 않습니다. 언제 재구축해야 하는지 아는 것이 더 어려운 부분입니다. 비대해진 인덱스는 REINDEX로 해결될 수 있지만, 근본 원인은 약한 오토배큠 설정이나 하루 종일 동일한 행을 업데이트하는 워크로드일 수 있습니다. 손상된 인덱스는 긴급 복구가 필요할 수 있지만, 왜 손상이 발생했는지도 물어봐야 합니다: 스토리지, 메모리, 커널 오류, 안전하지 않은 하드웨어 설정 또는 드문 소프트웨어 버그.

이 가이드는 실용적인 PostgreSQL 명령어에 초점을 맞춥니다: 의심스러운 인덱스를 식별하는 방법, 다운타임 유무에 관계없이 재구축하는 방법, 프로덕션 데이터베이스에서 유지 관리를 실행하기 전에 확인해야 할 사항.

PostgreSQL 인덱스 및 일반적인 문제 이해

PostgreSQL 인덱스, 가장 일반적으로 B-트리 인덱스는 플래너가 모든 행을 스캔하는 것을 피하도록 돕는 조회 구조입니다. 인덱스가 건강하고 선택적일 때 PostgreSQL은 필요한 테이블의 작은 부분으로 바로 이동할 수 있습니다. 인덱스가 비대하거나 유효하지 않으면 플래너가 여전히 사용할 수 있지만, 데이터베이스는 동일한 결과를 얻기 위해 추가 작업을 수행합니다.

인덱스는 주로 두 가지 이유로 문제가 될 수 있습니다: 블로트와 손상.

인덱스 블로트

인덱스 블로트는 인덱스 구조 내에 "데드 튜플"(오래된 데이터 버전)이 축적되는 것을 말합니다. PostgreSQL에서 행이 업데이트되거나 삭제되면 이전 버전의 데이터(및 해당 인덱스 항목)가 즉시 제거되지 않습니다. 대신 "데드"로 표시되고 결국 VACUUM 프로세스에 의해 회수됩니다. VACUUM이 충분히 자주 또는 효과적으로 실행되지 않거나 업데이트/삭제 비율이 높으면 이러한 데드 튜플이 축적되어 인덱스가 필요 이상으로 커질 수 있습니다. 비대해진 인덱스는 더 많은 디스크 공간을 차지하고, 스캔에 더 많은 I/O 작업이 필요하며, 쿼리 속도를 높이는 데 덜 효과적일 수 있습니다.

인덱스 손상

인덱스 손상은 인덱스의 내부 구조가 논리적으로 일관성이 없거나 물리적으로 손상되는 더 심각한 문제입니다. 이는 다양한 요인으로 인해 발생할 수 있습니다:

• 하드웨어 오류: 디스크 오류, 메모리 문제 또는 정전.
• 소프트웨어 버그: PostgreSQL 자체 또는 기본 운영 체제 구성 요소의 드물지만 가능한 결함.
• 갑작스러운 시스템 충돌: 적절한 종료 절차 없이 PostgreSQL 서버가 갑자기 종료됨.

손상된 인덱스는 잘못된 쿼리 결과, "인덱스에 예기치 않은 데이터가 포함되어 있습니다"와 같은 오류 또는 쿼리 완료를 방해할 수 있습니다. 손상을 식별하고 수정하는 것은 데이터 무결성과 데이터베이스 안정성에 중요합니다.

문제가 있는 인덱스의 증상에는 특정 쿼리의 갑작스러운 속도 저하, 명백한 이유 없는 I/O 활동 증가 또는 인덱스 스캔과 관련된 오류 메시지가 포함되는 경우가 많습니다.

문제가 있는 인덱스 식별

인덱스를 복구하기 전에 어떤 인덱스가 문제를 일으키는지 식별해야 합니다. PostgreSQL은 이를 위한 여러 방법을 제공합니다.

사용되지 않거나 비효율적인 인덱스 확인

pg_stat_user_indexes 뷰는 인덱스 사용에 대한 통계를 제공합니다. 이를 쿼리하여 거의 또는 전혀 사용되지 않는 인덱스를 찾을 수 있으며, 이는 제거 또는 재평가 대상이 될 수 있습니다.

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- 스캔된 적이 없는 인덱스
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

idx_scan이 0이면 사용되지 않는 인덱스를 나타낼 수 있지만, 일부 인덱스는 제약 조건(예: UNIQUE, PRIMARY KEY) 또는 자주 액세스하지 않는 보고서에 사용된다는 점을 고려하는 것이 중요합니다. 삭제하기 전에 항상 조사하십시오.

인덱스 블로트 감지

블로트는 직접 감지하기 어렵지만, 테이블에 비해 불균형적으로 큰 인덱스 크기 또는 해당 데이터 증가 없이 과도하게 커지는 인덱스는 블로트를 나타낼 수 있습니다. 테이블과 해당 인덱스의 크기를 비교할 수 있습니다:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

더 고급 블로트 감지를 위해 커뮤니티 기여 스크립트나 pg_repack 또는 pgstattuple(튜플 밀도를 보고 블로트를 추정할 수 있음)과 같은 확장 기능 사용을 고려할 수 있습니다.

EXPLAIN ANALYZE로 느린 쿼리 식별

특정 쿼리가 느려지면 EXPLAIN ANALYZE가 가장 좋은 도구입니다. 쿼리 실행 계획과 실제 런타임 통계를 보여주며, 인덱스가 어떻게 사용되는지(또는 사용되지 않는지)를 포함합니다.

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE your_column = 'some_value';

계획이 인덱스 스캔이 예상된 곳에서 순차 스캔을 보여주거나 인덱스 스캔이 비정상적으로 오래 걸리는 경우 비효율적이거나 문제가 있는 인덱스를 가리킬 수 있습니다.

인덱스 손상 확인

인덱스 손상은 종종 PostgreSQL 로그의 오류 또는 쿼리가 예기치 않게 실패할 때 나타납니다. corruption, unexpected data, invalid page 또는 bad block과 같은 문구가 포함된 메시지를 찾으십시오. 데이터베이스의 모든 인덱스가 정상임을 증명하는 단일 내장 SQL 명령은 없습니다. 더 깊은 확인을 위해 팀은 유지 관리 기간 동안 PostgreSQL의 amcheck 확장, 특히 B-트리 인덱스의 경우 bt_index_check 및 bt_index_parent_check를 사용하는 경우가 많습니다.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_products_name'::regclass);

amcheck는 진단 도구이지 복구 도구가 아닙니다. 문제를 보고하면 최신 백업이 없는 경우 백업을 수행하고 PostgreSQL 및 시스템 로그를 검사한 후 재구축을 계획하십시오.

팁: 오류 메시지에 대해 PostgreSQL 로그를 정기적으로 모니터링하십시오. 손상을 조기에 발견하면 더 큰 문제를 방지할 수 있습니다.

REINDEX 명령: 기본 도구

REINDEX 명령은 PostgreSQL 인덱스를 재구축하는 기본 도구입니다. 테이블의 현재 데이터를 기반으로 인덱스를 처음부터 다시 구성하여 데드 튜플을 제거하여 블로트를 효과적으로 수정하고 새롭고 유효한 구조를 구축하여 손상을 복구합니다.

REINDEX 작동 방식

REINDEX가 실행되면 PostgreSQL은 현재 테이블 데이터에서 인덱스를 재구축합니다. 결과는 새롭고 컴팩트한 인덱스 구조입니다. 블로트의 경우 인덱스 내부의 데드 공간이 제거됩니다. 많은 인덱스 수준 손상 사례의 경우 테이블에서 구축된 새로운 구조를 PostgreSQL에 제공합니다.

REINDEX 구문 및 사용법

REINDEX는 다양한 세분성으로 적용될 수 있습니다:

1. 특정 인덱스 재인덱싱:

   REINDEX INDEX index_name;
   

   이것은 가장 일반적인 사용 사례로, 단일 문제 인덱스를 대상으로 합니다.

2. 테이블의 모든 인덱스 재인덱싱:

   REINDEX TABLE table_name;
   

   테이블에 여러 개의 비대하거나 손상된 인덱스가 있을 때 유용합니다.

3. 데이터베이스의 모든 인덱스 재인덱싱:

   REINDEX DATABASE database_name;
   

   이것은 더 과감한 조치로, 일반적으로 광범위한 손상이나 블로트가 의심되는 상황에서 사용됩니다. 상당한 다운타임을 유발할 수 있습니다.

4. 데이터베이스의 시스템 카탈로그 재인덱싱:

   REINDEX SYSTEM database_name;
   

   이것은 지정된 데이터베이스 내의 시스템 카탈로그 테이블에 대한 모든 인덱스를 재구축합니다. 시스템 카탈로그 인덱스에 문제가 있다고 의심되는 경우에만 극도의 주의를 기울여 사용해야 하며, 전체 데이터베이스 기능에 영향을 미치고 독점 액세스가 필요할 수 있습니다.

경고: CONCURRENTLY 없이 REINDEX를 실행하면 더 강력한 잠금을 획득하고 영향을 받는 객체에 대한 일반 애플리케이션 트래픽을 차단할 수 있습니다. PostgreSQL 버전 및 객체 유형에 대한 정확한 명령 및 잠금 동작을 테스트하지 않는 한 다운타임 작업으로 취급하십시오.

REINDEX CONCURRENTLY로 다운타임 최소화

다운타임이 허용되지 않는 프로덕션 시스템의 경우 REINDEX CONCURRENTLY는 매우 귀중한 옵션입니다. 테이블에 대한 동시 읽기 및 쓰기 작업을 차단하지 않고 인덱스를 재구축할 수 있습니다.

REINDEX CONCURRENTLY 작동 방식:

1. 정상 작업과 동시에 새 인덱스 정의를 구축합니다.
2. 테이블에 대해 짧은 SHARE UPDATE EXCLUSIVE 잠금을 획득하여 DDL(예: ALTER TABLE)은 차단하지만 DML(INSERT, UPDATE, DELETE) 및 SELECT 문은 허용합니다.
3. 그런 다음 테이블을 스캔하여 새 인덱스를 구축합니다.
4. 초기 구축 후 구축 프로세스 중에 발생한 변경 사항을 적용하기 위해 또 다른 매우 짧은 SHARE UPDATE EXCLUSIVE 잠금을 획득합니다.
5. 마지막으로 이전 인덱스를 새 인덱스로 교체하고 이전 인덱스를 삭제합니다.

구문:

REINDEX INDEX CONCURRENTLY index_name;

REINDEX CONCURRENTLY에 대한 중요 고려 사항:

• 느린 실행: 동시 변경 사항을 처리해야 하기 때문에 REINDEX CONCURRENTLY는 일반적으로 비동시 REINDEX보다 느립니다.
• 디스크 공간: 일시적으로 이전 및 새 인덱스 구조 모두에 대한 디스크 공간이 필요합니다.
• 트랜잭션 지원 없음: REINDEX CONCURRENTLY는 트랜잭션 블록 내에서 실행할 수 없습니다.
• 오류 처리: REINDEX CONCURRENTLY가 실패하면(예: 고유 인덱스의 고유 제약 조건 위반으로 인해) 유효하지 않은 인덱스가 남습니다. 이 유효하지 않은 인덱스를 DROP한 다음 REINDEX CONCURRENTLY 명령을 다시 실행해야 합니다.

재인덱싱의 실제 예

products 테이블에 idx_products_name 인덱스가 있다고 가정해 보겠습니다.

단일 인덱스 재구축(다운타임 포함)

영향을 받는 인덱스에 대해 짧은 중단을 감당할 수 있는 경우:

REINDEX INDEX idx_products_name;

단일 인덱스 재구축(동시, 최소 다운타임)

products 테이블에 액세스 가능해야 하는 프로덕션 시스템의 경우:

-- B-트리 인덱스의 경우:
REINDEX INDEX CONCURRENTLY idx_products_name;

-- 기본 키 또는 고유 제약 조건 인덱스의 경우(종종 특별한 처리가 필요하지만 REINDEX CONCURRENTLY가 처리함):
-- 기본 키 또는 고유 제약 조건 인덱스를 재구축해야 하는 경우 일반적으로 기본 인덱스를 재구축합니다.
-- 예를 들어 'products_pkey'가 기본 키 인덱스인 경우:
REINDEX INDEX CONCURRENTLY products_pkey;

테이블의 모든 인덱스 재구축

products 테이블의 여러 인덱스에 문제가 있다고 의심되는 경우:

-- 이렇게 하면 'products' 테이블에 ACCESS EXCLUSIVE 잠금이 획득됩니다.
REINDEX TABLE products;

최신 PostgreSQL 버전은 동시 테이블 재인덱싱을 지원합니다:

REINDEX TABLE CONCURRENTLY products;

이는 일반적으로 각 인덱스를 수동으로 재구축하는 것보다 쉽지만 여전히 I/O, CPU 및 임시 디스크 공간을 소비합니다. 이 구문을 지원하지 않는 이전 PostgreSQL 릴리스에서는 테이블의 인덱스를 식별하고 각각을 REINDEX INDEX CONCURRENTLY로 재구축하십시오.

먼저 테이블의 모든 인덱스를 식별합니다:

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

수동 제어를 위해 먼저 인덱스를 나열합니다:

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'products'
ORDER BY indexname;

데이터베이스의 모든 인덱스 재구축

이것은 최후의 수단이며 상당한 다운타임이 필요합니다. 예약된 유지 관리 기간 동안에만 수행해야 합니다.

REINDEX DATABASE your_database_name;

또는 지원되는 PostgreSQL 버전에서 REINDEX DATABASE CONCURRENTLY your_database_name;을 사용할 수 있습니다. 최악의 차단 동작을 피하지만 여전히 주요 유지 관리 작업이며 트랜잭션 블록 내에서 실행할 수 없습니다.

관련 유지 관리 명령 및 모범 사례

재인덱싱은 종종 광범위한 유지 관리 전략의 일부입니다. 다른 명령은 인덱스 문제를 방지하는 데 중요한 역할을 합니다.

VACUUM 및 VACUUM FULL

• VACUUM: 데드 튜플이 차지하는 공간을 회수하여 재사용할 수 있도록 합니다. 디스크의 테이블이나 인덱스 파일을 축소하지는 않지만 블로트를 방지하는 데 중요합니다. autovacuum 데몬이 일반적으로 이를 자동으로 처리합니다.

  VACUUM your_table;
  

• VACUUM FULL: 전체 테이블과 관련 인덱스를 새 디스크 파일에 다시 작성하여 최대 공간을 회수하고 블로트를 제거합니다. 그러나 테이블에 ACCESS EXCLUSIVE 잠금을 획득하여 모든 작업을 차단하므로 극도의 주의를 기울여 사용해야 합니다. 인덱스 블로트의 경우 REINDEX가 선호되는 경우가 많습니다.

  VACUUM FULL your_table;
  

ANALYZE

ANALYZE 명령은 데이터베이스 테이블의 내용에 대한 통계를 수집하여 pg_statistic에 저장합니다. PostgreSQL 쿼리 플래너는 이러한 통계를 사용하여 인덱스 사용 여부를 포함하여 쿼리 실행 방법에 대한 지능적인 결정을 내립니다. 중요한 데이터 변경 후(또는 재인덱싱 후) ANALYZE를 실행하면 플래너가 최신 정보를 갖게 됩니다.

ANALYZE your_table;
-- 또는 전체 데이터베이스 분석:
ANALYZE;

오토배큠 모니터링

autovacuum 데몬이 실행 중이고 올바르게 구성되었는지 확인하십시오. 블로트를 방지하고 통계를 최신 상태로 유지하는 데 중요한 VACUUM 및 ANALYZE 작업을 자동으로 수행합니다. 잘못 구성된 autovacuum은 성능 저하의 일반적인 원인입니다.

정기적인 유지 관리 일정

사전 예방적 인덱스 유지 관리는 대응적 문제 해결보다 낫습니다. 다음에 대한 일정을 수립하십시오:

• 인덱스 사용 및 크기 모니터링: 잠재적인 블로트 또는 사용되지 않는 인덱스를 식별합니다.
• REINDEX CONCURRENTLY 실행: 자주 업데이트되거나 삭제되는 테이블의 경우 또는 중요한 데이터 마이그레이션 후.
• autovacuum 로그 및 설정 검토: 데이터베이스 활동을 따라잡고 있는지 확인합니다.

테스트 및 백업

• 항상 테스트: 프로덕션 데이터베이스에서 주요 유지 관리 작업을 수행하기 전에 프로덕션 설정을 미러링하는 스테이징 또는 개발 환경에서 철저히 테스트하십시오.
• 항상 백업: 특히 비동시 작업이나 전체 테이블/데이터베이스를 대상으로 하는 REINDEX 작업을 시작하기 전에 데이터베이스의 최근 신뢰할 수 있는 백업이 있는지 확인하십시오. REINDEX는 일반적으로 안전하지만 손상된 데이터베이스 백업은 쓸모가 없습니다.

문제 해결 팁 및 경고

• 디스크 공간: REINDEX 작업(특히 CONCURRENTLY)은 재구축되는 인덱스 크기의 최대 두 배에 달하는 상당한 임시 디스크 공간이 필요합니다. 데이터베이스 서버에 충분한 여유 공간이 있는지 확인하십시오.
• 성능 영향: REINDEX CONCURRENTLY라도 실행 중에 CPU 및 I/O 리소스를 소비합니다. 실행 중 시스템 성능을 주의 깊게 모니터링하십시오.
• 근본 원인 식별: 인덱스가 비대해지거나 손상되는 이유를 이해하지 않고 반복적으로 재인덱싱하지 마십시오. 비효율적인 VACUUM 설정, 높은 트랜잭션 속도 또는 하드웨어 문제와 같은 근본적인 문제를 조사하십시오.
• 인덱스 생성 vs 재인덱싱: CREATE INDEX CONCURRENTLY는 차단 없이 새 인덱스를 생성하기 위한 REINDEX INDEX CONCURRENTLY와 동등합니다. 유사한 원칙을 따르며 유사한 제한 사항이 있습니다.

좋은 인덱스 유지 관리는 부분적으로 명령 지식이고 부분적으로 자제력입니다. REINDEX CONCURRENTLY는 유용한 복구 도구이지만, 워크로드를 이해하지 않고 반복적으로 재인덱싱하면 일반적으로 동일한 블로트가 다시 돌아옵니다. 위의 명령을 사용하여 문제를 확인하고, 가능한 가장 작은 영향을 받는 객체를 재구축한 다음, 오토배큠, 업데이트 패턴, 디스크 상태 및 쿼리 계획을 확인하여 다음 달에 동일한 긴급 복구를 수행하지 않도록 하십시오.