성능 팁 (Performance Tips)

쿼리 성능은 아주 많은 것들에 영향을 받아요. 그중 일부는 사용자가 직접 제어할 수 있지만, 다른 일부는 시스템의 근본적인 설계에 묶여 있는 본질적인 요소예요. 이 장에서는 PostgreSQL 성능을 이해하고 조정하는 데 도움이 되는 힌트들을 정리해 볼게요.

성능 문제가 생기면 보통 "쿼리가 왜 이렇게 느리지?"라는 질문에서 출발하죠. 이 장은 그 질문에 답하는 순서대로 구성되어 있어요. 먼저 쿼리 계획을 어떻게 읽고 해석하는지(14.1), 플래너가 어떤 통계에 의존하는지(14.2), 플래너의 선택을 어떻게 제어할 수 있는지(14.3)를 다루고, 그다음으로 대량 데이터를 넣을 때 성능을 끌어올리는 방법(14.4), 마지막으로 내구성(durability)을 희생하고 속도를 얻는 설정(14.5)까지 이어져요.

14.1. EXPLAIN 사용하기 (Using EXPLAIN)

PostgreSQL은 받은 모든 쿼리에 대해 쿼리 계획(query plan)을 설계해요. 쿼리 구조와 데이터의 특성에 맞는 올바른 계획을 고르는 것이 좋은 성능의 핵심이기 때문에, 시스템에는 좋은 계획을 찾으려고 노력하는 복잡한 플래너(planner)가 들어 있어요. EXPLAIN 명령으로 플래너가 어떤 쿼리 계획을 만들었는지 직접 볼 수 있고, 그 계획을 읽는 것이 성능 튜닝의 첫걸음이에요.

계획 읽기는 경험이 필요한 일종의 기술(art)이지만, 이 절에서는 그 기초를 다뤄요.

14.1.1. EXPLAIN 기초 (EXPLAIN Basics)

쿼리 계획의 구조는 계획 노드(plan node)들의 트리예요. 트리의 맨 아래 계층에 있는 노드는 스캔 노드(scan node)로, 테이블에서 원시 행을 반환해요. 테이블 접근 방식에 따라 순차 스캔, 인덱스 스캔, 비트맵 인덱스 스캔 등 다양한 스캔 노드가 있으며, VALUES 절이나 FROM의 set-returning 함수처럼 테이블이 아닌 행 소스에 해당하는 스캔 노드 유형도 있어요.

쿼리가 조인, 집계, 정렬처럼 원시 행에 추가 처리를 요구하면, 그 작업을 수행하는 노드가 스캔 노드 위에 추가돼요. 이런 작업들도 보통 여러 방식이 가능하기 때문에 다양한 노드 유형이 등장할 수 있어요.

EXPLAIN의 출력은 계획 트리의 각 노드마다 한 줄씩 나와요. 기본 노드 유형과 함께, 플래너가 그 계획 노드를 실행할 때 예상한 비용(cost) 추정치가 표시돼요. 노드 요약 줄 아래에 들여쓰기된 추가 줄이 나와서 해당 노드의 추가 속성을 보여주기도 하고요. 맨 첫 줄(가장 위쪽 노드의 요약 줄)에는 계획 전체의 예상 총 실행 비용이 나오는데, 플래너가 최소화하려고 하는 것이 바로 이 숫자예요.

간단한 예시로 출력이 어떻게 생겼는지 볼게요:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

이 쿼리는 WHERE 절이 없으므로 테이블의 모든 행을 스캔해야 해요. 그래서 플래너는 단순한 순차 스캔 계획을 선택했어요. 괄호 안의 숫자는 왼쪽부터 차례로 다음과 같아요:

예상 시작 비용(estimated start-up cost): 출력을 만들기 시작하기 전에 소비되는 비용. 예를 들어 정렬이 있는 노드라면 정렬을 완료하는 비용이 여기 들어가요.
예상 총 비용(estimated total cost): 이 노드가 완전히 실행될 때까지의 총 비용. 이 값에는 보통 자식 노드들이 모두 처리됐다고 가정한 상태가 포함돼요.
예상 출력 행 수(estimated rows): 이 계획 노드가 출력하는 행 수.
예상 출력 폭(estimated width): 출력 행의 평균 바이트 수.

14.1.2. EXPLAIN ANALYZE

EXPLAIN은 계획만 보여주고 실제로 쿼리를 실행하지는 않아요. 실제 실행을 통해 추정치가 맞는지 확인하고 싶다면 EXPLAIN ANALYZE를 사용해요. 이 명령은 쿼리를 실제로 실행하고, 각 계획 노드에서 실제 소요 시간과 실제 반환된 행 수를 함께 보여줍니다. 예상치와 실제치를 비교하면 플래너의 추정이 얼마나 정확한지, 어디에서 성능 병목이 생기는지 파악하는 데 아주 유용해요.

EXPLAIN ANALYZE SELECT * FROM tenk1;

ANALYZE 옵션을 붙이면 쿼리가 실제로 실행되므로 SELECT가 아니라 INSERT/UPDATE/DELETE 같은 변경 쿼리라면 데이터가 실제로 바뀌니 주의해야 해요.

14.1.3. 주의사항 (Caveats)

이 절의 예시들은 v18 개발 소스(release)를 사용해 VACUUM ANALYZE를 수행한 회귀 테스트(regression test) 데이터베이스를 기준으로 해요. 직접 실행하면 비슷한 결과가 나오겠지만, ANALYZE의 통계는 정확한 값이 아니라 랜덤 샘플이고, 비용은 본질적으로 어느 정도 플랫폼 의존적이기 때문에 예상 비용과 행 수가 조금 달라질 수 있어요.
예시는 사람이 읽기 편하고 간결한 EXPLAIN의 기본 텍스트 출력 형식을 사용해요. EXPLAIN 출력을 프로그램에 넣어 추가 분석하려면 XML, JSON, YAML 같은 기계가 읽을 수 있는 출력 형식을 쓰는 게 좋아요.

14.2. 플래너가 사용하는 통계 (Statistics Used by the Planner)

앞 절에서 봤듯이 쿼리 플래너는 좋은 쿼리 계획을 고르기 위해 쿼리가 가져올 행 수를 추정해야 해요. 이 절에서는 그 추정에 시스템이 사용하는 통계를 간단히 살펴볼게요.

통계의 한 구성 요소는 각 테이블과 인덱스의 전체 항목 수, 그리고 각 테이블과 인덱스가 차지하는 디스크 블록 수예요. 이 정보는 pg_class 테이블의 reltuples와 relpages 칼럼에 저장돼 있어요. 다음과 같은 쿼리로 확인할 수 있어요:

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      345
 tenk1_hundred        | i       |     10000 |       11
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

여기서 tenk1이 인덱스들과 마찬가지로 10000개 행을 담고 있지만, 인덱스가 (당연하게도) 테이블보다 훨씬 작다는 걸 확인할 수 있어요.

효율성 때문에 reltuples와 relpages는 즉시 갱신되지 않아서 보통 다소 오래된 값을 담고 있어요. 이 값들은 VACUUM, ANALYZE, 그리고 CREATE INDEX 같은 일부 DDL 명령에 의해 갱신돼요. 테이블 전체를 스캔하지 않는(일반적인 경우) VACUUM이나 ANALYZE는 자신이 스캔한 부분을 기준으로 reltuples를 점진적으로 갱신해서 근사값을 만들어요. 어쨌든 플래너는 pg_class에서 찾은 값을 현재의 물리적 테이블 크기에 맞춰 스케일링해서 더 가까운 근사치를 얻어요.

대부분의 쿼리는 WHERE 절로 검사할 행을 제한하기 때문에 테이블 행의 일부만 가져와요. 그래서 플래너는 WHERE 절의 선택도(selectivity), 즉 WHERE 절의 각 조건과 일치하는 행의 비율을 추정해야 해요. 이 작업에 사용되는 정보는 pg_statistic 시스템 카탈로그에 저장되는데, pg_statistic의 항목은 ANALYZE와 VACUUM ANALYZE 명령에 의해 갱신되며, 방금 갱신됐어도 항상 근사값이에요.

pg_statistic을 직접 보기보다는, 통계를 직접 검사할 때는 그 뷰인 pg_stats를 보는 게 좋아요. pg_stats는 더 읽기 쉽게 설계됐고, 게다가 모든 사용자가 읽을 수 있어요. 반면 pg_statistic은 수퍼유저만 읽을 수 있어요. (이는 권한이 없는 사용자가 다른 사람 테이블의 내용을 통계를 통해 알게 되는 것을 막기 위함이에요. pg_stats 뷰는 현재 사용자가 접근 권한이 있는 테이블에 관한 행만 보여주도록 제한돼 있어요.)

14.2.1. 단일 칼럼 통계 (Single-Column Statistics)

[14.2의 앞부분에서 설명한 pg_stats / pg_statistic 내용을 참고]

14.2.2. 확장 통계 (Extended Statistics)

일반 통계는 각 칼럼을 독립적으로 다뤄요. 하지만 여러 칼럼이 서로 연관되어 있으면 단일 칼럼 통계만으로는 선택도를 정확히 추정하지 못해 잘못된 계획이 나올 수 있어요. 이때 사용하는 것이 확장 통계(extended statistics)예요. CREATE STATISTICS 명령으로 만들 수 있고, 이를 통해 칼럼 간 상관관계를 반영한 더 정확한 추정이 가능해져요.

14.3. 명시적 JOIN 절로 플래너 제어하기 (Controlling the Planner with Explicit JOIN Clauses)

명시적인 JOIN 구문을 사용하면 쿼리 플래너를 어느 정도 제어할 수 있어요. 왜 이것이 중요한지 이해하려면 먼저 배경지식이 필요해요.

아래와 같은 단순한 조인 쿼리를 보면:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

플래너는 주어진 테이블들을 어떤 순서로든 조인할 자유가 있어요. 예를 들어 WHERE 조건 a.id = b.id를 이용해 A를 B에 조인하고, 이어서 다른 WHERE 조건으로 C를 그 조인 결과에 조인하는 계획을 만들 수 있어요. 또는 B를 C에 조인한 뒤 A를 그 결과에 조인할 수도 있고요. A를 C에 조인한 뒤 B와 조인할 수도 있지만, 이 경우엔 WHERE 절에 그 조인을 최적화할 수 있는 조건이 없으므로 A와 C의 전체 카티전 곱(Cartesian product)을 만들어야 해서 비효율적일 거예요. (PostgreSQL 실행기의 모든 조인은 두 입력 테이블 사이에서 일어나므로, 이런 방식 중 하나로 결과를 쌓아 올리는 게 필요해요.) 중요한 점은 이렇게 다양한 조인 가능 방식들이 의미상으로는 동일한 결과를 내지만, 실행 비용은 크게 다를 수 있다는 거예요. 그래서 플래너는 가장 효율적인 계획을 찾기 위해 이 모든 가능성을 탐색해요.

쿼리가 두세 개의 테이블만 다루면 조인 순서가 얼마 없어요. 하지만 가능한 조인 순서의 수는 테이블 수가 늘어남에 따라 지수적으로 증가해요. 입력 테이블이 10개쯤 넘어가면 모든 가능성을 빠짐없이 탐색하는 것이 더 이상 현실적이지 않고, 6~7개 테이블에서도 플래닝이 꽤 오래 걸릴 수 있어요. 입력 테이블이 너무 많아지면 PostgreSQL 플래너는 완전 탐색(exhaustive search)에서 제한된 수의 가능성만 보는 유전적 확률 탐색(genetic probabilistic search)으로 전환해요. (전환 기준값은 geqo_threshold 런타임 파라미터로 정해져요.) 유전적 탐색은 시간이 덜 걸리지만 반드시 최상의 계획을 찾으리라는 보장은 없어요.

쿼리가 외부 조인(outer join)을 포함하면 플래너는 일반(내부) 조인보다 자유도가 줄어들어요. 예를 들어:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

이 쿼리의 제약은 겉보기에 앞의 예시와 비슷하지만, A의 모든 행 중 B와 C의 조인에서 매칭되는 행이 없는 것에 대해서도 행을 내보내야 하므로 의미가 달라져요. 따라서 플래너는 여기서 조인 순서를 선택할 수 없고, B를 C에 조인한 다음 A를 그 결과에 조인해야 해요. 그래서 이 쿼리는 앞의 쿼리보다 플래닝 시간이 덜 걸려요. 다른 경우에는 플래너가 여러 작업을 수행해야 할 수도 있어요.

명시적 JOIN 구문은 이런 플래너의 선택에 제약을 걸어주는 도구예요. 계획을 세우기 전에 쿼리를 미리 결합해두는 방식으로 플래너가 할 일을 줄일 수 있고, 복잡한 조인에서 의도한 조인 순서를 강제할 수 있어요.

14.4. 데이터베이스 채우기 (Populating a Database)

데이터베이스를 처음 채울 때는 대량의 데이터를 넣어야 할 수 있어요. 이 절에서는 이 과정을 가능한 한 효율적으로 만드는 몇 가지 제안을 다뤄요.

14.4.1. 자동 커밋 끄기 (Disable Autocommit)

여러 개의 INSERT를 사용할 때는 자동 커밋(autocommit)을 끄고 마지막에 한 번만 커밋해요. (일반 SQL에서는 시작에 BEGIN, 끝에 COMMIT을 붙이면 돼요. 일부 클라이언트 라이브러리는 사용자 모르게 이를 처리해 주는데, 그 경우에는 라이브러리가 원하는 시점에 정확히 그렇게 하도록 확인해야 해요.) 각 삽입을 개별적으로 커밋하게 두면 PostgreSQL은 추가되는 행마다 많은 작업을 하게 돼요. 모든 삽입을 하나의 트랜잭션으로 묶으면 얻는 추가 이점도 있는데, 한 행의 삽입이 실패하면 그 시점까지 삽입된 모든 행이 롤백되므로 부분적으로만 채워진 데이터에 갇히지 않는다는 점이에요.

14.4.2. COPY 사용하기 (Use COPY)

일련의 INSERT 명령 대신 COPY를 써서 모든 행을 한 명령으로 로드해요. COPY 명령은 많은 수의 행을 로드하도록 최적화되어 있고, INSERT보다는 덜 유연하지만 대량 데이터 로드에서는 오버헤드가 훨씬 적어요. COPY는 단일 명령이므로 이 방법으로 테이블을 채울 때는 자동 커밋을 끌 필요도 없어요.

COPY를 쓸 수 없다면, PREPARE로 준비된 INSERT 문(statement)을 만든 뒤 필요한 만큼 EXECUTE하는 것도 도움이 돼요. 이렇게 하면 INSERT를 반복해서 파싱하고 플래닝하는 오버헤드 일부를 피할 수 있어요. 인터페이스마다 이 기능을 제공하는 방식이 다르니, 인터페이스 문서에서 "prepared statements"를 찾아보세요.

대량 행 로드에는 PREPARE를 쓰고 여러 삽입을 하나의 트랜잭션으로 묶는다 해도, COPY를 쓰는 게 거의 항상 INSERT보다 빠르다는 점을 참고하세요.

COPY는 이전 CREATE TABLE이나 TRUNCATE 명령과 같은 트랜잭션 안에서 사용할 때 가장 빨라요. 이런 경우 오류가 나면 새로 로드된 데이터를 담은 파일이 어차피 제거되므로 WAL을 쓸 필요가 없기 때문이에요. 다만 이 고려 사항은 wal_level이 minimal일 때만 적용돼요. 그 외에는 모든 명령이 WAL을 써야 하니까요.

14.4.3. 인덱스 제거하기 (Remove Indexes)

새로 만든 테이블을 로드한다면 가장 빠른 방법은 테이블을 만들고, COPY로 데이터를 벌크 로드한 다음, 테이블에 필요한 인덱스를 만드는 거예요. 기존 데이터에 인덱스를 만드는 것은 각 행을 로드할 때마다 점진적으로 갱신하는 것보다 빠르기 때문이에요.

기존 테이블에 대량의 데이터를 추가한다면 인덱스를 제거하고 테이블을 로드한 뒤 인덱스를 다시 만드는 것이 이득일 수 있어요. 데이터가 많을수록 이 방식의 이득이 커져요. 다만 UNIQUE나 PRIMARY KEY 제약 조건이 있는 인덱스를 제거하면 데이터 무결성을 검사하지 못하게 되므로, 다시 만들 때 제약 조건을 위반하는 데이터가 있으면 실패할 수 있으니 주의해야 해요.

14.4.4. 외래 키 제약 조건 제거하기 (Remove Foreign Key Constraints)

외래 키 제약 조건은 삽입 중에 행마다 검사되어 오버헤드가 커요. 로드 중에는 외래 키 제약을 제거했다가, 로드가 끝난 뒤 다시 추가하는 것이 더 빠를 수 있어요. 외래 키 제약을 다시 추가할 때는 데이터가 실제로 제약을 만족하는지 확인하기 위해 테이블을 스캔한다는 점을 기억하세요.

14.4.5. maintenance_work_mem 늘리기 (Increase maintenance_work_mem)

CREATE INDEX나 VACUUM 같은 유지보수 작업 중에 임시적으로 사용할 수 있는 메모리 양을 늘리는 방법이에요. 이 파라미터는 각 유지보수 작업이 사용할 수 있는 메모리 상한을 제어해요. 인덱스를 만드는 동안 이 값을 높이면 정렬이 디스크로 넘어가는 것을 줄일 수 있어 성능이 좋아질 수 있어요. 다만 많이 사용하면 그만큼 메모리를 쓰므로 주의가 필요해요.

14.4.6. max_wal_size 늘리기 (Increase max_wal_size)

체크포인트(checkpoint)가 발생하는 빈도를 줄이려면 max_wal_size를 늘릴 수 있어요. 체크포인트가 드물어지면 체크포인트 동안의 디스크 쓰기 폭주가 줄어들 수 있지만, 그만큼 WAL 저장 공간 요구도 커져요.

14.4.7. WAL 보관과 스트리밍 복제 끄기 (Disable WAL Archival and Streaming Replication)

PITR(point-in-time recovery)을 위한 WAL 보관(archival)이나 스트리밍 복제를 쓰지 않는다면 이를 끄면 WAL 처리 오버헤드를 줄일 수 있어요. 대량 로드 중에는 WAL 보관과 스트리밍 복제를 잠시 끄는 것도 방법이에요. 다만 복제가 필요한 환경이라면 복구 지점과 일관성에 영향을 줄 수 있으니 신중하게 판단해야 해요.

14.4.8. 나중에 ANALYZE 실행하기 (Run ANALYZE Afterwards)

대량 데이터를 로드한 뒤에는 ANALYZE를 실행해서 통계를 갱신하는 것이 중요해요. 로드 직후에는 pg_statistic 등의 통계가 오래된 상태라, 플래너가 나쁜 계획을 선택할 수 있어요. 새 통계를 만들어야 쿼리 성능이 제대로 나와요.

14.4.9. pg_dump에 관한 몇 가지 참고 (Some Notes about pg_dump)

pg_dump로 데이터를 덤프했다가 다시 로드할 때도 위의 원칙들이 적용돼요. 덤프 파일을 로드할 때는 인덱스와 외래 키가 포함된 상태로 로드하는 것보다, 로드 과정을 더 빠르게 만들 수 있는 설정(예: 자동 커밋 끄기, 트랜잭션 단위 로드, 그다음 인덱스·제약 재생성)을 고려하세요.

14.5. 비내구성 설정 (Non-Durable Settings)

내구성(durability)은 서버가 충돌하거나 전원이 나가도 커밋된 트랜잭션이 기록됨을 보장하는 데이터베이스 기능이에요. 하지만 내구성은 상당한 데이터베이스 오버헤드를 추가해요. 그래서 이런 보장이 필요 없는 환경이라면 PostgreSQL을 훨씬 빠르게 실행되도록 구성할 수 있어요. 아래는 이런 경우에 성능을 높이기 위해 할 수 있는 설정 변경들이에요. 아래에 별도로 명시된 경우를 제외하면, 데이터베이스 소프트웨어 자체가 충돌해도 내구성은 여전히 보장돼요. 이 설정을 쓸 때 데이터 손실이나 손상 위험이 생기는 것은 오직 운영체제가 갑작스럽게 충돌할 때뿐이에요.

데이터베이스 클러스터의 데이터 디렉터리를 메모리 기반 파일 시스템(즉, RAM 디스크)에 두기. 모든 데이터베이스 디스크 I/O가 사라지지만, 데이터 저장 용량이 사용 가능한 메모리(그리고 어쩌면 스왑)로 제한돼요.
fsync 끄기. 데이터를 디스크에 플러시할 필요가 없어져요.
synchronous_commit 끄기. 매 커밋마다 WAL 쓰기를 디스크까지 강제할 필요가 없어질 수 있어요. 이 설정은 데이터베이스 자체가 충돌할 때 트랜잭션 손실 위험을 만들지만(데이터 손상은 아님) 주의가 필요해요.
full_page_writes 끄기. 부분 페이지 쓰기(partial page write)에 대비할 필요가 없어져요.
max_wal_size와 checkpoint_timeout 늘리기. 체크포인트 빈도는 줄어들지만 /pg_wal의 저장 공간 요구는 커져요.
unlogged 테이블 만들기. WAL 쓰기를 피할 수 있지만, 테이블이 비충돌-안전(non-crash-safe)하게 돼요.

이 설정들은 "머신이 죽어도 상관없다"는 전제가 성립하는 곳에서만 의미를 가져요. 실서비스 데이터라면 내구성을 포기하는 대가는 데이터 손실이니까, 꼭 필요한 경우에만 신중하게 사용하길 권해요.