출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
1-2-1. ERD
이 책은 각자 컴퓨터에 DBMS를 직접 설치하고 실습할 수 있도록 구성되어 있다. 실습 데이터베이스는 필자의 이전 저서 ‘SQL STARTER’와 같은 구조를 사용한다. ‘SQL STARTER’는 MySQL 기준으로 작성되었지만, 이 책에서는 PostgreSQL 버전으로 진행한다.
실습 데이터는 가상의 커피 프랜차이즈 시스템을 모델링한 것이다. 전체 구조는 이어지는 [그림-1-2-1-a]와 [그림-1-2-1-b]의 ERD를 참고하기 바란다.
SQL 튜닝을 제대로 실습하려면 많은 데이터가 필요하다. 이를 위해 Big 테이블 5개를 추가로 생성한다. Big 테이블은 기본 테이블의 데이터를 30배 이상으로 늘린 대용량 테이블이다. Big 테이블의 구조는 아래 [그림-1-2-1-c]에서 확인할 수 있다.
실습 과정에서는 기본 테이블과 Big 테이블을 번갈아가며 사용한다. 따라서 실습 스크립트를 실행할 때 어떤 테이블을 사용하는지 확인하고 진행하기 바란다.
1-2-2. 실습환경 구성하기
실습을 위해서는 개인 PC에 Docker(소프트웨어를 컨테이너 단위의 격리된 환경 안에서 실행하도록 해주는 오픈소스 가상화 플랫폼)를 설치하고 그 위에 PostgreSQL을 구성해야 한다. 그리고 DBeaver를 설치해 Docker의 PostgreSQL에 접속하는 방식으로 진행한다. 또한 실습 과정에서 옵티마이저 제어를 위한 힌트 기능이 필수이므로 pg_hint_plan 확장(extension)도 함께 설치해야 한다.
이 책은 PostgreSQL 16.4(Debian 16.4-1.pgdg120+1 on x86_64-pc-linux-gnu)를 기준으로 작성되었다. 추가로 PostgreSQL 17.6에서도 모든 실습 내용이 정상 작동하는 것을 확인했다. 따라서 16.4 이상 버전이면 책의 실습 내용을 모두 따라할 수 있다. 다만 pg_hint_plan 설치의 편의성을 고려하면 책에서 소개하는 16.4 또는 17.6 버전을 권장한다.
실습환경 구성 과정은 다음 6단계로 진행된다.
1.
Docker 다운로드 및 설치하기
2.
Docker에 PostgreSQL 설치하기(16.4 또는 17.6)
3.
DBeaver로 PostgreSQL에 접속해 보기(16.4 또는 17.6)
4.
pg_hint_plan 설치하기(16.4 또는 17.6)
5.
기본 STARTER DB 생성하기
6.
튜닝용 Big 테이블 생성하기
위 6단계를 모두 완료하면 다음과 같은 실습환경이 구성된다.
실습환경 구성 과정은 이 책에 포함하지 않고 온라인 공간에 별도로 정리해 두었다. 아래 URL의 내용을 참고해 실습 환경을 구성하기 바란다.
•
Short URL: https://bit.ly/4qhl0Sm
각자 환경에 따라서 설치 과정에 어려움이 있을 수 있다. 주변 지인들을 통해 설치 과정에 문제가 없음을 확인했지만 모든 환경이 동일할 것이라고 장담할 수는 없다. 설치 과정에 문제가 발생하면 구글과 GPT의 도움을 받아 직접 해결해 볼 것을 부탁한다.
설치 과정 중 특히 주의해야 할 두 가지 설정이 있다. LC_COLLATE 설정과 postgresql.conf 설정인데, 이 부분은 이후 실습 과정과 관련이 있으므로 아래에서 좀 더 자세히 설명하겠다.
1-2-2-1. LC_COLLATE
실습 환경 구성 URL을 참고해 데이터베이스를 생성할 때 아래와 같이 COLLATE 관련 설정을 한다.
[SQL-1-2-2-1-a]
CREATE DATABASE startdbpg
WITH TEMPLATE template0
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C';
SQL
복사
위 SQL에서 지정한 설정은 아래와 같은 의미가 있으니 참고하기 바란다.
•
TEMPLATE template0: 인코딩(Encoding)과 로케일(Locale)을 지정할 수 있도록 template0 사용
◦
지정하지 않으면 template1에 설정된 인코딩과 로케일을 따르게 된다.
•
ENCODING 'UTF8': 다국어와 이모지를 모두 지원하는 사실상 표준에 가까운 인코딩이다.
•
LC_COLLATE, LC_CTYPE 'C': 문자열의 정렬과 비교를 바이트 기준으로 처리한다.
◦
빠르고 결과가 일정하며, Oracle의 BINARY 정렬 방식과 유사하다.
◦
단, 특정 지역에 맞는 사전식 정렬이 불가능하다.
◦
만약 사전식 정렬이 필요하다면 테이블 생성시 컬럼에 별도 지정해야 한다.
위와 같은 설정이 PostgreSQL의 공식 권장사항은 아니다. 하지만 PostgreSQL 관련 커뮤니티와 기술 문서를 찾아보면 LC_COLLATE를 ‘C’로 지정하는 것을 권장한다. 바이트 순서 정렬 방식이 문자열 검색 성능과 인덱스 활용에 유리하기 때문이다.
1-2-2-2. postgresql.conf
설치 과정 중에 postgresql.conf에 다음과 같은 설정을 해야 한다.
•
shared_preload_libraries에 pg_hint_plan을 추가
•
max_parallel_workers_per_gather(병렬 처리)을 0으로 설정
◦
책의 실습과 동일한 결과를 보장하기 위한 설정이다.
◦
실제 운영 DB라면 이 부분을 변경하는 것을 권장하지 않는다.