애플리케이션 개발자를 위한 PostgreSQL 아키텍처 고려사항 : 파트 2

이 글은 AWS Database 블로그의 How PostgreSQL processes queries and how to analyze them by Peter Celentano and Tracy Jenkins 의 한국어 번역입니다.

애플리케이션 계층은 웹사이트에서 눈에 보이는 부분이기 때문에 애플리케이션을 구동하는 데이터베이스를 최적화하는 것이 애플리케이션의 성능, 관리 효율성, 확장성에 매우 중요할 수 있습니다.
데이터베이스가 쿼리를 처리하여 요청된 행을 반환하는 방식을 이해하면 애플리케이션 개발자는 병목 현상을 정확히 파악하여 문제 해결 노력을 강화할 수 있습니다.

PostgreSQL의 쿼리는 결과적으로 어떤 데이터를 반환할지를 지정하지만, 그 데이터를 어떻게 처리할지는 명시하지 않습니다. 클라이언트는 서버로 쿼리 텍스트를 전송할 때 클라이언트-서버 프로토콜을 사용하며, PostgreSQL은 포함된 행 수에 관계없이 전체 결과를 반환합니다. PostgreSQL은 쿼리를 특정 순서로 실행하지만, 이 순서는 다소 복잡할 수 있습니다. 이는 PostgreSQL이 동시 쿼리 실행을 지원하고, 이를 관리하기 위해 다중 버전 동시성 제어(MVCC)를 사용하기 때문입니다. 이러한 실행 순서를 이해하는 것은 애플리케이션 개발자와 데이터베이스 관리자 모두에게 중요합니다. PostgreSQL이 쿼리 경로를 선택하는 방식에 대한 인사이트를 제공하고, 잠재적인 데이터베이스 문제의 발생 위치를 파악하는 데 도움이 될 수 있습니다.

이번 시리즈의 파트 1 에서는 PostgreSQL의 핵심 개념, 매개변수를 변경하는 시기와 방법, 데이터베이스 동작 및 모범 사례에 대해 다루었습니다. 파트 2인 본 글에서는 PostgreSQL의 추가적인 핵심 개념을 다루고자 합니다. 여기에는 단순 쿼리 프로토콜, 실행 계획(Explain Plans), 실행 계획을 읽는 방법, 그리고 Amazon RDS for PostgreSQL 및 Amazon Aurora PostgreSQL 호환 버전에서 이러한 계획을 시각화하는 도구가 포함됩니다.

Statements, commands, and clauses

PostgreSQL 에서 실행이 가능한 쿼리 코드는 SQL 문 (statement) 이라고 부릅니다. Statement 는 데이터베이스에서 작업을 수행하기 위한 전체적인 구조와 조직을 제공합니다. 또한 이러한 문장을 명령 (command) 이라고 부를 수도 있으며, 대표적인 SQL 명령으로는 SELECT, UPDATE, DELETE 등이 있습니다. 절 (clause) 은 조건을 필터링하거나 정의하거나 수정하는 데 사용되며, 단독으로 실행될 수는 없지만 다른 요소들과 결합이 되면 실행 가능합니다. 이러한 구성 요소들을 함께 사용하여 애플리케이션 개발자와 데이터베이스 관리자는 원하는 정보를 조회하거나 변경할 수 있습니다.

단순 쿼리 프로토콜

단순 쿼리 프로토콜은 흔히 쿼리 실행 단계라고 불리며, PostgreSQL의 프론트엔드에서 쿼리 텍스트를 수집하고 백엔드에서 처리하는 방법입니다. 단순 쿼리 프로토콜의 단계는 쿼리의 절이 평가되는 순서를 설명하며, 거의 모든 SQL 명령은 단순 쿼리로 간주됩니다. 쿼리 실행 단계는 다음과 같은 순서로 진행됩니다:

구문 분석 및 분석
변환
계획 수립
실행

이 단계에서 어떤 일이 일어나는지 알면 쿼리가 실행되지 않거나 느리게 실행되는 이유를 진단할 수 있으며, 쿼리를 더 빠르게 최적화하는 데 도움이 됩니다. psql 커맨드 라인 유틸리티는 이 프로토콜을 사용하여 쿼리를 처리합니다.

쿼리 프로세싱

PostgreSQL에서 쿼리를 실행하는 과정은 데이터를 효율적으로 검색하고 처리하기 위해 여러 단계를 거칩니다. 먼저 파서가 쿼리의 구문을 검사하고 SQL 쿼리를 내부 트리 구조로 변환합니다. 그 후 옵티마이저가 데이터베이스 통계, 인덱스, 제약 조건 등을 기반으로 다양한 실행 전략을 평가하여 가장 효율적인 방법을 결정합니다. 이렇게 생성된 쿼리 플랜은 PostgreSQL이 테이블에 접근하고, 데이터를 필터링하며, 필요한 정렬이나 집계를 수행하는 상세한 청사진이 됩니다.

쿼리가 실행될 때 실행기는 선택된 쿼리 계획에 따라 데이터를 검색하고 처리합니다. 이 과정에서 테이블을 스캔하고, 조건을 적용하며, 결과를 집계하는 등 모든 필요한 작업을 수행합니다. 마지막으로, 처리된 결과는 클라이언트에게 반환됩니다. 이 모든 단계에서 PostgreSQL의 플래너와 실행기는 인덱스를 활용하고 디스크 접근을 최소화하며, 데이터베이스 자원을 효율적으로 사용하여 최적의 성능을 달성하려고 협력합니다

EXPLAIN

EXPLAIN은 PostgreSQL의 쿼리 플래너/옵티마이저가 선택한 실행 계획을 시각적으로 보여주는 명령어로, 쿼리 성능을 평가하는 데 사용됩니다. EXPLAIN 계획을 만들기 위해서 PostgreSQL은 쿼리를 분석하여 옵티마이저가 선택한 계획을 반환합니다. EXPLAIN은 SELECT, INSERT, DELETE, UPDATE, AS, DECLARE (CURSOR) 문에서만 사용할 수 있습니다.

EXPLAIN의 실행 계획 노드는 역트리 형태로 반환됩니다. 응답의 첫 번째 줄은 마지막으로 수행된 작업을 나타내며, 첫 번째로 실행된 단계는 맨 아래에 표시됩니다. 위로 올라갈수록 상위 단계로 전달되는 중간 결과 집합이 나타납니다. 트리의 최상단은 클라이언트에게 결과를 반환하는 최종 단계입니다.

각 노드는 -> 기호로 시작하며, 같은 수준에 있는 노드들은 동일한 단계에서 실행되며, 병렬로 실행될 수도 있습니다. 예를 들어 조인 연산이 그렇습니다. 각 노드와 그 비용을 분석하면 쿼리 실행 중 비효율적인 부분을 찾아 성능 개선에 집중할 수 있습니다.

EXPLAIN을 실행하려면 SQL 문 전체를 EXPLAIN 뒤에 입력하면 됩니다. 각 단계에 대해 다음과 같은 정보가 출력됩니다:

수행해야 할 작업 유형
쿼리 실행의 예상 비용
예상되는 결과 행 수
각 행의 예상 크기(너비)
쿼리 실행에 사용된 필터 조건

EXPLAIN에는 여러 매개변수가 있습니다:

ANALYZE: 쿼리를 실제로 실행하고 계획, 비용 추정치, 실제 전후 실행 시간 및 각 단계별 실제 행 수를 표시합니다. 이 매개변수를 사용하면 테이블 통계도 업데이트됩니다.
BUFFERS: ANALYZE와만 함께 사용할 수 있으며, 각 단계에서 읽고 쓰고 더럽혀진 블록(기본 크기는 8 KiB)의 수를 보여줍니다.
VERBOSE: 쿼리 실행 계획의 각 단계에서 출력되는 모든 표현식을 표시합니다. 자주 호출되며 비용이 많이 드는 함수가 있는 경우 유용합니다.
SETTINGS: 기본값과 다른 성능 관련 매개변수를 출력합니다.
WAL: PostgreSQL 13부터 지원되며 데이터 수정 명령으로 인해 발생한 WAL 로그를 표시합니다. ANALYZE와 함께만 사용할 수 있습니다.
FORMAT: 출력 형식을 지정합니다. 기본값은 TEXT이며, 쿼리 성능 분석에 사용됩니다. XML, JSON, YAML 형식도 지원되며 자동 처리에 유용합니다.

다음은 EXPLAIN을 사용할 때의 모범 사례입니다:

새로운 쿼리를 애플리케이션에 배포하기 전에, EXPLAIN을 실행하여 해당 쿼리가 시스템 성능에 미칠 영향을 미리 예측하세요.
EXPLAIN 결과는 참고용으로만 사용하고, 변경 전후의 성능과 결과를 반드시 확인하세요.
실행 계획 결과를 런타임이나 추적 파일과 같은 다른 지표와 함께 분석하세요.
시간이 지남에 따라 환경이 변할 수 있으므로, 실행 계획을 정기적으로 검토하고 업데이트하세요.

다음은 EXPLAIN이 제공하지 않는 정보입니다:

플래너가 왜 인덱스를 사용하지 않았는지
쿼리를 어떻게 다시 작성해야 하는지
네트워크 지연과 같은 외부 요인으로 인해 쿼리나 데이터베이스가 느려지는 이유
쿼리가 애플리케이션으로 반환되는 데 걸리는 시간이나 데이터베이스 외부에서 발생하는 작업

PostgreSQL v17에서는 다음과 같은 업데이트된 기능을 제공합니다:

EXPLAIN은 옵티마이저의 메모리 사용량을 보고할 수 있으며, 이 옵션은 MEMORY라고 합니다.
또한 SERIALIZE 옵션을 사용하면 네트워크 전송을 위한 데이터 변환 비용을 확인할 수 있습니다.
EXPLAIN BUFFERS에는 I/O 블록의 읽기/쓰기 시간 통계가 추가되었습니다.
PostgreSQL v17에서는 서브플랜 노드와 출력 매개변수의 표시가 개선되었습니다.

ANALYZE

ANALYZE는 EXPLAIN과 유사한 매개변수로, 주로 비용이 많이 드는 노드를 찾는 데 사용됩니다. 기본적으로 EXPLAIN과 동일하게 작동하지만, EXPLAIN ANALYZE로 설정하면 쿼리를 실제로 실행하고 시작 시간과 총 시간을 밀리초 단위로 반환합니다. 상위 노드의 시작 시간과 총 시간은 하위 노드의 시간보다 크거나 같으며, 특정 노드에서 소요된 순수 시간을 계산하려면 하위 노드에서 소요된 시간을 빼면 됩니다. 병렬 쿼리의 경우, 모든 시간을 합산해야 총 시간을 구할 수 있습니다. 각 노드에서 소요된 총 시간을 계산하려면 비용과 시간을 반복 횟수(루프 수)와 곱해 대략적인 비용과 행 수를 정확히 추정할 수 있습니다.

EXPLAIN ANALYZE를 실행하려면 전체 SQL 문을 EXPLAIN ANALYZE 뒤에 입력하면 됩니다. 각 단계에 대해 EXPLAIN ANALYZE는 일반 실행 계획 외에도 다음과 같은 추가 정보를 출력합니다:

실제 쿼리 실행 시작 시간 및 총 시간 (밀리초 단위)
반환된 행 수
각 단계에서 완료된 루프(반복) 횟수

EXPLAIN 및 EXPLAIN ANALYZE 활용 모범사례는 다음과 같습니다:

실행 시간이 가장 많이 소요된 노드를 찾는 데 사용하세요.
계획에서 예상 행 수와 실제 행 수가 크게 차이나는 노드를 찾아보세요. 이러한 노드는 성능 저하의 원인이 될 수 있으며, 다른 곳에서 긴 실행 시간이 발생하는 원인이 될 수 있습니다.
오래 걸리는 순차 스캔이 있는 경우, 필터 조건을 사용하여 행을 줄여 처리 속도를 높일 수 있습니다. 이러한 경우 인덱스를 추가하는 것도 좋은 방법입니다.

EXPLAIN ANALYZE 사용 시 고려 사항은 다음과 같습니다:

실제로 쿼리를 실행하기 때문에 일부 문장은 실행 시간이 더 길어질 수 있습니다.
첫 번째 쿼리 실행 시 데이터가 캐시에 없을 수 있습니다. 결과 변화를 관찰하기 위해 EXPLAIN ANALYZE를 여러 번 반복 실행하는 것이 유용합니다.
쿼리가 절대 완료되지 않는 경우(예: 트랜잭션 대기 상태)에는 이 방법을 사용하지 마세요. 무한히 대기할 수 있습니다.
인덱스 사용 여부를 확인할 때는 EXPLAIN만 사용할 수 있습니다.
파티션 사용 여부를 확인할 때도 EXPLAIN만 사용할 수 있습니다.

솔루션 개요

다음 섹션에서는 테이블을 생성하고 간단한 PostgreSQL 쿼리에 대해 EXPLAIN과 EXPLAIN ANALYZE를 실행하는 방법을 단계별로 설명합니다. 또한 실행 계획의 비용을 해석하고 계산하는 방법, 그리고 PostgreSQL에서 실행 계획을 시각화하는 데 도움이 되는 도구들에 대해 다룹니다.

필수 조건

본 글의 예제를 수행하기 위해서는 다음 필수조건을 미리 충족해야합니다.

AWS 계정을 생성 및 활성화해야합니다.
Amazon Elastic Compute Cloud (Amazon EC2)나, RDS for PostgreSQL 인스턴스나, Aurora PostgreSQL 에 PostgreSQL 데이터베이스를 생성해야합니다.

테이블 생성

본 글의 예제에서는, PostgreSQL 데이터베이스에 generate_series() 함수를 활용하여 무작위로 추출된 수를 활용하여 테이블을 생성하였습니다:

CREATE TABLE yes_no (id serial, yes_no_id int, repeat int);
INSERT INTO yes_no (yes_no_id, repeat)
SELECT x % 2 + 1, (random() *25)::integer
FROM generate_series(1, 100) AS f(x);

EXPLAIN SELECT * from yes_no;

`EXPLAIN` 계획 읽기

앞서 설명한 것처럼, 매개변수를 사용하지 않은 EXPLAIN 계획은 예상 비용, 반환될 예상 행 수, 그리고 행의 예상 평균 너비를 제공합니다. 이전 코드에서는 yes_no 테이블에 대해 순차 스캔이 사용되었습니다. 괄호 안의 정보는 예상 시작 비용(첫 번째 행을 반환하는 데 드는 비용)과 두 점(..)으로 구분된 두 번째 값은 예상 총 비용(모든 행을 반환하는 데 드는 비용)을 나타냅니다. 순차 스캔의 시작 비용은 일반적으로 0.00인데, 이는 데이터베이스가 첫 번째 행을 빠르게 반환할 수 있기 때문이며, 총 비용은 2.00입니다. 반환된 행의 수는 100개이고, 각 행의 평균 너비는 12바이트입니다.

비용을 계산할 때 필요한 기본값은 전체 테이블 스캔의 경우 seq_page_cost = 1.0, 랜덤 페이지 접근의 경우 random_page_cost = 4.0, 그리고 행이 처리될 때는 기본 튜플 비용인 cpu_tuple_cost = 0.01이 필요합니다.

앞서 언급한 순차 스캔의 비용이 어떻게 결정되었는지 이해하려면 테이블 데이터 페이지 수와 튜플 수를 알아야 합니다. 이를 확인하려면 다음 코드를 사용할 수 있습니다:

SELECT relpages, reltuples from pg_class where relname='yes_no';

random_page_cost와 cpu_tuple_cost 설정을 기반으로 주어진 EXPLAIN 계획의 비용을 더 세부적으로 분석하고 계산할 수 있습니다. 하지만 구체적인 비용 자체는 크게 중요하지 않습니다. 그 값은 옵티마이저가 생성하는 다른 계획들과 상대적이기 때문입니다. 비용이 낮을수록 해당 쿼리 실행 계획의 성능이 더 좋다는 것을 의미합니다. 만약 클러스터 내 다른 실행 계획에 비해 비용이 크게 높다면, 해당 쿼리나 실행 계획에 문제가 있을 수 있으며, 이를 점검해야 합니다.

EXPLAIN ANALYZE 명령을 실행하려면 SQL 문으로 확인할 수 있습니다

EXPLAIN ANALYZE SELECT count (*) from yes_no;

`EXPLAIN ANALYZE` 계획 읽기

ANALYZE 매개변수를 추가하면 실제로 쿼리가 실행되므로, INSERT, UPDATE, DELETE, 또는 CREATE TABLE AS 쿼리를 호출할 때는 주의해야 하는데, 이러한 문장을 BEGIN을 사용하여 트랜잭션 블록으로 감싸면 됩니다. 이 매개변수를 추가하면 쿼리의 실제 계획 시간과 총 실행 시간이 표시되며, 각 노드에서 소요된 시간과 예상 행 수 대비 실제 반환된 행 수를 비교할 때 유용합니다.

`EXPLAIN` 계획 시각화 도구

지금까지는 PostgreSQL의 실행 계획을 터미널에서만 확인했습니다. 여기서는 터미널에서 생성된 실행 계획을 시각화하는데 도움을 주는 온라인 도구를 소개하겠습니다. 여기서 소개하는 도구가 많은 사용자들에게 인기가 있지만, 이것이 유일한 웹 기반 실행 계획 시각화 도구는 아닙니다.

explain.depesz.com은 EXPLAIN 결과 텍스트를 입력할 수 있는 온라인 도구입니다. Depesz는 스크립트를 분석하여 일반적인 패턴을 찾고 계획을 표 형태로 보여줍니다. 이 도구는 원본 EXPLAIN 텍스트를 컬럼으로 표시하며 문제가 있는 노드를 강조 표시합니다. 또한 실행 시간이 오래 걸린 노드도 강조해서 보여줍니다.

이는 독점적(exclusive) 시간과 포괄적(inclusive) 시간을 사용하여 비용을 계산합니다:

포괄적 시간: 해당 노드와 모든 하위 노드에서 소요된 시간
독점적 시간: 단일 노드에서만 소요된 시간

이러한 시간들은 플래너의 부정확도를 색상으로 표시합니다:

노란색: 플래너가 행 수를 10배 잘못 예측
주황색: 100배 잘못 예측
빨간색: 1,000배 잘못 예측

explain.depesz.com은 공개 웹에서 사용할 수 있습니다. 데이터 전송 제한이 있는 경우, 소스코드를 내부적으로 실행하거나 컬럼 이름을 가리고 사이트에 계획을 저장하지 않는 옵션을 선택할 수 있습니다.

추가 기능에는 다음이 포함됩니다:

과대/과소 예측된 행 수를 보여주는 rows x 컬럼 (잘못된 예측은 색상으로 강조)
노드를 선택하면 하위 노드들을 숨길 수 있어, 긴 쿼리 실행 계획에서 특정 노드에 집중 가능
노드에 마우스를 올리면 직계 하위 노드들이 별표로 강조되어, 긴 쿼리 실행 계획에서 쉽게 위치 파악 가능

이 도구를 사용하려면 앞서 나온 EXPLAIN ANALYZE 쿼리의 출력 결과를 explain.depesz.com 웹사이트의 텍스트 상자에 입력하면 됩니다.

결론

단순 쿼리 프로토콜과 실행 계획은 PostgreSQL을 사용하는 애플리케이션 개발 시 반드시 이해해야 할 중요한 개념입니다. 잘못 작성된 쿼리는 아무리 잘 설계된 데이터 모델이라도 성능을 저하시킬 수 있으며, PostgreSQL에서 쿼리가 어떻게 실행되는지와 실행 계획을 다루는 방법을 이해하는 것은 데이터베이스 개발자에게 필수적인 주제입니다. 심지어 잘 작성된 쿼리도 모델, 데이터 양, 또는 접근 패턴이 변경되면 성능이 저하될 수 있기 때문에, 전체적인 데이터베이스 동작을 이해하는 것이 매우 중요합니다. 이번 글에서는 PostgreSQL에서 쿼리가 처리되는 단계와 실행 계획을 사용하여 쿼리 실행을 설명하고, 비효율적인 부분을 빠르게 찾아 수정하는 방법에 대해 다루었습니다.

AWS 기술 블로그