[PostgreSQL] 운영 중인 테이블에 인덱스 생성하기

 

안녕하세요. 황진성입니다.

오늘은 운영 중인 데이터베이스에 인덱스를 생성하는 방법에 대해 알아보겠습니다.

 

그냥 만들면 되는 거 아냐?

테이블에 인덱스를 생성하면, 인덱스를 구성하는 동안 테이블에 Lock이 걸리게 됩니다.

운영 중인 테이블에 인덱스를 생성하면 인덱스가 생성되는 동안 서비스가 중지될 수도 있습니다.

따라서 인덱스를 생성한다면 신중하게 판단해야 합니다.

 

일반적으로 운영 중인 테이블에 인덱스를 생성한다면 아래와 같이 생성합니다.

이해를 위해 Pseudo code로 작성했으며, DBMS Vender에 따라 문법이 조금씩 다를 수 있습니다.

-- 1. 기존 테이블과 동일한 구조, 데이터를 가진 새로운 테이블을 생성한다.
CREATE TABLE new_table_name AS (
	SELECT * FROM table_name
);

-- 2. 새로운 테이블에 인덱스를 생성한다.
CREATE INDEX new_index_name ON new_table_name(column_name);

-- 3. 인덱스 생성이 끝나면 이름을 변경한다.
RENAME TABLE table_name TO old_table_name,
	new_table_name TO table_name;

 

실시간으로 인덱스 생성하기

하지만 운영 중인 테이블에서도 실시간으로 중단 없이 인덱스를 생성하는 것이 가능합니다.

보통 `Online index creation` 이라는 키워드로 검색하면 자료를 찾아보실 수 있습니다.

 

PostgreSQL 에서도 Online index creation 기능을 제공합니다.

인덱스 생성 시 `CONCURRENTLY` 옵션을 주면 됩니다.

-- Example
CREATE INDEX CONCURRENTLY new_index_name ON table_name (column_name);

 

동작 방식을 알아봅시다. (마땅한 자료를 찾지 못해서 제가 이해한 바 작성했습니다. 😢)

 

1. 임시로 사용할, 유효하지 않은(invalid), 고유한 이름을 가진 인덱스 하나가 생성됩니다.

2. 첫 번째 트랜잭션이 시작됩니다. [Tx1]

2-1. 테이블 전체를 스캔하고 인덱스를 위한 구조를 생성합니다. 초기에는 테이블에 대한 새로운 데이터나 변경 사항을 추적하지 않습니다.

3. 초기 인덱스 빌드가 끝나면, 두 번째 트랜잭션이 시작됩니다. [Tx2]

3-1. 데이터베이스의 스냅샷을 찍어둡니다. 이 스냅샷은 초기 인덱스 빌드 이후 테이블에 적용된 모든 새로운 데이터나 변경 사항을 추적하는 데 사용됩니다.

3-2. 스냅샷 이후 테이블에 적용된 모든 변경 사항으로 인덱스를 점진적으로 업데이트를 진행합니다. 이 업데이트 단계를 catch-up 단계라고도 부릅니다. PostgreSQL은 미리 write-ahead log(WAL)에서 변경 사항을 스캔하고 인덱스에 적용하여 업데이트를 수행합니다.

4. catch-up 단계가 끝나고 인덱스가 모든 변경 사항으로 최신 상태가 되면, PostgreSQL은 임시 인덱스를 유효한(valid) 인덱스로 변경하고, 임시 이름에서 사용자가 지정한 이름으로 변경하게 됩니다.

 

기억해야 할 점은, 테이블 스캔은 2개의 트랜잭션에 의해 2번 실행되며 각 테이블 스캔이 진행되기 전 인덱스를 사용할 것으로 예상되는 모든 트랜잭션이 종료된 후 실행됩니다.

 

인덱스 생성에 실패했다면,

인덱스 생성이 비정상적으로 종료될 수도 있습니다. 아래 2가지 케이스가 있습니다.

1. 테이블 스캔 과정에서 데드락(교착상태)이 발생함.

2. Unique 인덱스 제약 조건 위반된 건이 발견됨.

 

이 경우 `CREATE INDEX` 쿼리는 실패하지만, 유효하지 않은 인덱스(invalid index)가 남아있게 됩니다.

불완전한 인덱스이기 때문에 쿼리 목적으로는 무시되고 실행됩니다. 하지만 인덱스 업데이트 오버헤드는 여전히 남아있게 됩니다.

따라서 psql `\d` 명령어를 이용해서 INVALID 인덱스가 있는지 확인해야 하며, 꼭 삭제해 줘야 불필요한 오버헤드를 줄일 수 있습니다.

 

이 경우에는 인덱스 삭제 후 다시 생성해줄 수도 있지만, `REINDEX INDEX CONCURRENTLY` 를 사용할 수도 있습니다.

 

Online 방식으로 인덱스 생성 시 주의사항

하지만 이 방식으로 인덱스를 생성한다면 주의해야 할 사항이 있습니다.

 

첫 번째로, 인덱스 생성 속도가 느립니다. 인덱스를 Online으로 생성하면 해당 테이블이 다른 작업을 계속 진행할 수 있어야 합니다. 따라서 Offline으로 중단 후 인덱스를 생성하는 방식보다 느립니다.

 

두 번째로, Unique 인덱스를 Online으로 생성하는 경우, 인덱스 생성이 종료될 때까지 Unique 제약 조건이 적용되지 않습니다. Unique 제약 조건을 위배하는 중복 레코드가 존재하는 경우에는 인덱스 생성에 실패합니다. 이 경우에는 반드시 공식 문서를 꼼꼼히 읽어보고 실행하는 것을 권장합니다.

 

세 번째로, I/O 및 CPU 사용량이 증가합니다. Online으로 인덱스를 생성하면 테이블 단위의 Lock은 발생하지 않지만, 인덱스 생성 과정에서 Disk I/O와 생성을 위한 연산 증가로 CPU 사용량이 증가합니다. 따라서 인덱스를 생성하는 중에는 시스템 리소스 모니터링을 진행해야 합니다. 이러한 이유로 데이터베이스 사용량이 적은 시간대에 작업을 진행하는 것을 추천합니다.

 

네 번째로, Online 인덱스는 동일한 테이블에 대해서 한 번에 하나만 수행할 수 있습니다. 또한 명시적 트랜잭션 블록 내에서 인덱스를 Online으로 생성할 수 없습니다. START TRANSACTION, COMMIT, ROLLBACK 따위를 사용할 수 없음을 의미합니다. 따라서 CREATE INDEX CONCURRENTLY 구문은 트랜잭션 외부에서 실행해야 합니다.

 

 

정말 인덱스가 최선일까?

지금까지 인덱스 생성에만 초점을 맞춰서 글을 작성해 봤는데, 인덱스는 DML 성능에 악영향을 미치기 때문에 무조건 생성해서는 안됩니다.

인덱스를 생성하기 전에 아래의 것들을 미리 고려해보면 더 좋겠습니다.

 

1. Read 연산(select)의 비율이 높은 테이블인지 확인합니다.

2. 테이블에서 실행되는 쿼리를 수집해서, 어떤 컬럼에 인덱스를 거는 것이 유리할지 판단합니다.

3. 인덱스 생성 전에 쿼리 최적화가 가능한지 판단합니다.

 

 

References

• https://www.postgresql.org/docs/current/sql-createindex.html
• https://www.2ndquadrant.com/en/blog/create-index-concurrently/
• https://mozi.tistory.com/334
• Real MySQL 8.0 [1] / 백은빈, 이성욱 저 / 위키북스 / 2021