[PostgreSQL] 운영 중인 DB 에 인덱스를 추가할 때 고려해야 할 것들 - Lock을 중심으로

운영 중인 PostgreSQL에 인덱스를 추가하며 배운 것들

 

상황 

DB 에서 데이터를 특정 조건으로 검색하거나 조회할 때 인덱싱이 중요하다는 건 익히 알고 있었다.

최초의 요구사항이나 기획단에서 염두해서 신중하게 중복값과 의미가 있는지 여부를 고민하여 유저(학생) 이름에 Index를 넣어두었다.

 

문제

예상과 달리 새로운 요구사항 (사용자 통합 검색 기능)의 들어와 개발 작업에 착수했다. 이름뿐 아니라 학년, 연락처, 아이디 등 다양한 조건으로 쿼리를 사용해야 했다. 이로 인해 기존에는 없던 인덱스를 새로 추가하는 것이 불가피했다.

문제는 이미 서비스가 릴리즈되어 운영 중인 상태였다는 점이다.

 

대응

처음에 그냥 무작정 migration 을 짜면서 자연스레 CREATE INDEX를 작성했다.

그런데, 다른 동료가 독점적 락이 걸려 기존 읽기/쓰기가 중단될 수 있다는 점을 언급했다. 

 

놓치고 있던 부분이라 자세히 찾아보았다.

 

 

 

PostgreSQL에서 CREATE INDEX 로직의 구체적인 동작 과정

PostgreSQL에서 CREATE INDEX 명령에서 인덱스 생성 과정

 

1. 테이블 락 획득
AccessExclusiveLock 획득: 해당 테이블에 대한 모든 읽기/쓰기 작업을 막는 락으로 서비스 중단을 유발할 수 있다.

2. 인덱스 메타데이터 등록
PostgreSQL에서는 pg_class, pg_index, pg_attribute 등의 시스템 카탈로그에 인덱스 정보를 먼저 등록한다.
인덱스 자체는 아직 비어 있지만, 인덱스 정보가 존재하는 것으로 간주한다.

3. 기존 테이블 스캔 및 인덱스 빌드
PostgreSQL은 대상 테이블을 스캔하여 각 레코드에 대해 인덱스 키를 계산하고, B-Tree 등의 자료구조로 인덱스를 구성한다.
이 과정이 가장 시간이 많이 걸리며, 테이블이 클수록 시간과 비용이 많이 든다.

4. 인덱스 정합성 검증
트랜잭션 도중 삽입된 레코드를 추적하여 인덱스에 반영하는 과정을 수행한다.
MVCC 원칙을 따르기 때문에 이 과정은 복잡하고 느릴 수 있으며, 도중에 실패할 경우 임시 인덱스 오브젝트가 남는다.

5. 인덱스 유효화 및 커밋
인덱스가 완전히 생성되면 시스템 카탈로그에서 indisvalid 필드가 true로 설정되고, 실제 쿼리에서 사용 가능해진다.
마지막으로 트랜잭션이 커밋되면서 인덱스가 정식 등록된다.

 

요약

단계	설명	기본 CREATE INDEX
1	테이블 락	AccessExclusive
2	메타데이터 등록	즉시
3	인덱스 빌드	단일 스캔
4	정합성 검증	빠름
5	커밋	빠름

 

 

 

대응

 무작정 CREATE INDEX를 사용하면 해당 테이블에 독점적 락이 걸리면서 읽기/쓰기 모두 중단된다. 이건 곧 서비스 중단으로 이어지기 때문에 현실적으로 선택할 수 없었다.

 

엄청난 대용량의 데이터가 있진 않았지만, 서비스 중단이 발생할 확률이 0은 아니었기에 신중해야 했다.

여기서 PostgreSQL의 CREATE INDEX CONCURRENTLY 옵션이 유용했다. 이 옵션을 사용하면 인덱스를 백그라운드에서 생성할 수 있어 서비스는 정상적으로 운영된다. MongoDB에서 @Index({ background: true })와 비슷한 개념이다. 하지만 단점도 분명히 존재했는데 작업이 느리고, 중간에 실패할 경우 롤백되지 않기 때문에 수동 정리가 필요하다.

또 하나 기억할 점은, 기존 인덱스를 재구성해야 할 경우 REINDEX CONCURRENTLY를 사용할 수 있다는 것이다.

PostgreSQL 12 이상에서 지원되며, 기존 REINDEX가 락을 걸었던 것과 달리 무중단으로 작업이 가능하다. 물론 이 역시 속도가 느리고 실패 시 수동 처리가 필요하다.

정리
운영 DB에는 절대로 기본 CREATE INDEX를 쓰지 말 것
새 인덱스는 CREATE INDEX CONCURRENTLY, 기존 인덱스는 REINDEX CONCURRENTLY
실패 시 대응 계획을 세우고, 쿼리 이름, 인덱스 이름 등을 명확히 지정할 것
인덱스가 실제 쿼리에서 사용되는지 EXPLAIN으로 꼭 확인할 것

 

서비스 무중단을 지키면서 성능 개선을 이루려면 단순한 명령 하나에도 고민이 필요하다.

이번 경험을 통해 PostgreSQL의 인덱싱 메커니즘과 운영상의 위험 요소들을 실감하며 배울 수 있었다.