4.2A Look at Indexes for Performance

앞선 포스팅에서 포스트그레스는 8kb의 블록 단위로 데이터를 저장한다는 것을 배웠다. 그런데 그게 왜 중요할까? 쿼리의 성능 최적화를 하려다보면 low-level단의 이해가 필요하기 때문이다.

Full Table Scans (전체 테이블 검색)

아래의 쿼리를 실행하는 상황을 가정하자.

sql
SELECT * FROM users
WHERE username = 'Riann';

이 쿼리를 실행하면, PostgreSQL은 Heap File에 있는 데이터들을 메모리(RAM)에 로드한 후, 한 줄씩 username = 'Riann 조건을 확인한다. 이렇게 테이블 전체를 훑는 방식을 Full Table Scan이라고 한다.

하지만 데이터가 수백만, 수천만 건이라면 이렇게 모든 데이터를 로드하는 것은 비효율적일 것이다. 이럴 때 사용할 수 있는 보다 효율적인 검색을 위한 도구가 인덱스다.

Index

인덱스란, 특정 컬럼의 데이터를 기준으로, 해당 데이터가 어느 블록에 위치해 있는지 알려주는 데이터 구조다. 책의 목차(인덱스)처럼, 해당 데이터가 있는 위치를 빠르게 찾기 위한 또 하나의 데이터라고 생각할 수 있다.

인덱스의 작동 방식

인덱스를 만들려면 우선 인덱스를 만들 컬럼을 지정한다. 우리 예시의 경우, username 컬럼이다.

이렇게 인덱스를 만들 컬럼을 정하면, PostgreSQL은 해당 컬럼의 값을 정렬하고, 각 값이 저장된 데이터 위치(포인터)를 저장한다. 예를 들어 텍스트형 데이터는 알파벳 순서대로, 숫자형 데이터는 오름차순 등이다.

그리고 PostgreSQL은 이 데이터를 트리 형태(B-Tree 등)로 정리한다. 가장 위에 있는 노드인 Root Node에는 조건문을 넣어 빠르게 다음 노드를 찾을 수 있게 만든다. 예를 들어, Root Node에 조건문을 'Alf' <= username <= 'Jia'로 넣어두면, Riann은 false를 반환하므로 해당 조건문을 만족했을 때의 노드가 아닌 다른 Leaf Node에서 검색을 하게 된다.

이렇게 검색하면, 원하는 데이터의 위치를 얻게 되고 해당 데이터의 위치를 바로 Heap에서 접근해 데이터를 얻는다.

인덱스 만들기

인덱스는 CREATE 키워드로 만들 수 있다.

sql
CREATE INDEX ON users (username);

이렇게 인덱스를 만들면, PGAdmin4에선 해당 테이블 아래에 Indexes 메뉴에서 볼 수 있으며, 해당 인덱스의 이름은 자동으로 테이블명_컬럼명_idx로 생성된다. 우리 예시의 경우엔 users_username_idx로 생성되었다.

Index를 제거하려면 DROP 키워드를 사용한다.

sql
DROP INDEX users_username_idx;

쿼리 성능 확인하기

인덱싱으로 개선된 쿼리의 성능을 확인하기 위해, 먼저 쿼리의 성능을 확인하는 방법부터 알아보자. PGAdmin4에서 쿼리를 실행하면 늘 몇ms만에 결과가 나왔다는 메시지가 나오지만, 이 시간은 실제 쿼리 성능이 아니라 네트워크 지연이나 기타 요소가 포함되어 있다. 실제 쿼리 성능을 보기 위해선 EXPLAIN ANALYZE 키워드를 사용한다.

sql
EXPLAIN ANALYZE SELECT * FROM users
WHERE username = 'Emil30'

인덱스를 드랍하고 다시 쿼리를 실행해보면, 인덱싱이 큰 성능 개선을 가져온다는 것을 확인할 수 있다.

우리의 예시에서는 인덱싱이 되었을 때 0.045ms정도가, 인덱싱을 없앴을 때 1.1ms정도가 걸렸다.

인덱스를 사용할 때 주의해야 할 점들

인덱싱은 성능을 개선하는 대신, 별도의 공간을 차지한다. users 테이블이 차지하는 용량을 확인하기 위해 아래의 쿼리를 실행해보자.

sql
SELECT pg_size_pretty(pg_relation_size('users'))

우리의 예시에선 인덱싱이 안 되어 있을 때는 110KB였던 것이, 인덱싱을 하면 872KB가 된다. 요즘 시대 기준으로 절대적으로 큰 용량이 늘어난 것은 아니지만, 보통 스토리지 사용량에 비례해 비용이 늘어나기 때문에 큰 프로젝트에서 인덱싱은 유의미한 비용을 들여야할 수도 있다.

또, 인덱싱은 INSERT, UPDATE, DELETE 속도에 손해를 가져온다. 데이터 변경을 할 때 인덱스도 함께 수정되기 때문이다.

마지막으로, 모든 컬럼에 인덱스를 만드는 것은 좋지 않다. 예를 들어, Primary Key 컬럼과 unique 제한이 있는 컬럼은 포스트그레스에서 자동으로 인덱싱을 하므로 수동으로 추가할 필요가 없다. PGAdmin4의 GUI에선 보이지 않아도, 쿼리로 조회하면 볼 수 있다.

sql
SELECT relname, relkind
FROM pg_class
WHERE relkind = 'i';

---

🤠 개인 탐구

Index Types