PostgreSQL DBA를 위한 Admin 이야기

1장. 환경구축

1.1 설치
____PostgreSQL 엔진 설치
____데이터베이스 클러스터 생성
1.2 인스턴스 시작 및 정지
____pg_ctl 명령어를 이용한 인스턴스 제어 방법
____DB 서버 재시작 시에 인스턴스 자동 시작 방법
1.3 접속하기
____로컬 접속
____pg_hba.conf 파일을 이용한 접속 관리
____원격접속을 위한 설정
____유저 패스워드 암호화 방식
1.4 클라이언트 툴
____psql
1.5 데이터베이스 환경 구축
____유저 생성
____롤 생성
____데이터베이스 생성
____스키마 생성
____권한 관리
____슈퍼유저 권한, CREATE 롤과 시스템 롤
____익스텐션 설치


2장. 오브젝트

2.1 테이블
____테이블 생성
____테이블 복사
____테이블 재구성
____테이블 관리 명령어
2.2 인덱스
____인덱스 생성
____CIC (Create Index Concurrently) 옵션
____인덱스 리빌드
____인덱스 관리 명령어
2.3 파티션
____파티션 개요
____RANGE 파티션
____LIST 파티션
____HASH 파티션
____파티션 인덱스
____인덱스 ONLY 옵션
____파티션 관리 명령어
2.4 뷰(View)
____뷰 특징
2.5 시퀀스
____시퀀스 특징
____시퀀스 관리 명령어
2.6 함수
____함수 실행 권한


3장. 아키텍처

3.1 인스턴스
____공유 메모리
____프로세스
____데이터베이스 클러스터 주요 디렉토리와 파일들
3.2 테이블스페이스
____pg_default 테이블스페이스
____pg_global 테이블스페이스
____사용자 테이블스페이스 생성
____TEMP 테이블스페이스
3.3 테이블
____테이블 (Heap 테이블)
____Unlogged 테이블
____Temp 테이블
____TOAST (The Oversized-Attribute Storage Technique)
3.4 (운영 Tip) Template 데이터베이스 기능 활용 방안


4장. 파라미터 설정 가이드

4.1 로그 관련 파라미터
____logging_collector
____log_directory
____log_filename
____log_line_prefix
____log_lock_waits
____log_rotation_size
____log_statement

4.2 접속 관련 파라미터
____max_connections
____superuser_reserved_connections
____listen_addresses
____port
4.3 메모리 관련 파라미터
____shared_buffers
____wal_buffers
____work_mem
____temp_buffers
____hash_mem_multiplier
____maintenance_work_mem
4.4 체크포인트 관련 파라미터
____checkpoint_timeout
____max_wal_size
4.5 WAL (Write Ahead Log) 관련 파라미터
____wal_level
____wal_keep_size
____min_wal_size
____wal_log_hints
____checkpoint_completion_target
4.6 Autovacuum 관련 파라미터
____autovacuum
____autovacuum_max_workers
____log_autovacuum_min_duration
4.7 병렬 처리 관련 파라미터
____max_worker_processes
____max_parallel_workers
____max_parallel_workers_per_gather
____max_parallel_maintenance_workers
4.8 세션 관리 파라미터
____idle_in_transaction_session_timeout
4.9 압축 관련 파라미터
____wal_compression
____default_toast_compression
4.10 모니터링 관련 파라미터
____log_min_duration_statement
____shared_preload_libraries
____pg_stat_statements.max
____pg_stat_statements.track
____track_activity_query_size
____compute_query_id
____auto_explain.log_min_duration
____auto_explain.log_analyze
____auto_explain.log_buffers
4.11 옵티마이저 관련 파라미터
____effective_cache_size
____random_page_cost
____jit
4.12 아카이브 설정 관련 파라미터
____archive_mode
____archive_command
4.13 파라미터 설정 레벨 및 적용 순서
____파라미터 적용 순서
____pg_settings 뷰 살펴보기


5장. 복제

5.1 물리 복제
____물리 복제 구성 절차
____물리 복제 아키텍처
____동기화 모드 유형
____동기 모드 복제 구성 절차
____동기화 레벨
____Cascade 복제 구성 절차
5.2 논리 복제
____논리 복제 구성 절차
____논리 복제 아키텍처와 동작 순서
____Publication 생성 옵션 및 설정 변경
____Subscription 생성 옵션 및 설정 변경
____양방향 논리 복제
5.3 논리/물리 복제를 활용한 전일자 배치 환경 구성
5.4 복제 슬롯
____미사용 슬롯의 문제점
____WAL 디스크 볼륨 공간 부족으로 인스턴스 비정상 종료 시의 조치 방법
____max_slot_wal_keep_size 파라미터를 이용한 장애 방지 방안
5.5 LSN과 WAL 파일명


6장. 백업과 복구

6.1 논리 백업과 복구
____pg_dumpall을 이용한 백업
____pg_dump를 이용한 백업
____psql과 pg_restore를 이용한 복원
____pg_restore를 이용한 복구
____COPY
6.2 물리 백업과 복구
____백업 및 복구 개요
____pgBackRest 설치 및 환경 파일 설정
____pgBackRest를 이용한 백업
____pg_basebackup을 이용한 전체 백업
____pg_basebackup을 이용한 증분 백업
____pgBackRest를 이용한 완전 복구
____pgBackRest를 이용한 완전 복구 (Delta 옵션)
____pg_basebackup을 이용한 완전 복구 (전체 백업)
____pg_basebackup을 이용한 완전 복구 (증분 백업)
____pgBackRest를 이용한 시점 복구 (별도 서버)
____pgBackRest를 이용한 시점 복구 (동일 서버)
____pg_basebackup을 이용한 시점 복구 (별도 서버)
____불필요한 아카이브 파일 정리 방법


7장. 모니터링

7.1 성능 모니터링 개요
7.2 데이터베이스 모니터링
____pg_stat_database 뷰를 이용한 데이터베이스 성능 모니터링
7.3 세션 모니터링
____pg_stat_activity 뷰를 이용한 세션 모니터링
7.4 SQL 모니터링
____1회 수행 시 응답시간이 느린 쿼리 모니터링
____런타임 실행계획 확인: auto_explain 활용
____반복 수행으로 과부하를 주는 쿼리 모니터링
7.5 IO 모니터링
____버퍼 액세스 전략과 링 버퍼
____pg_stat_io 뷰를 이용한 시스템 레벨의 I/O 성능 분석
____pg_stat_all_tables와 pg_statio_all_tables 뷰를 이용한 테이블 IO 분석
____pg_stat_wal 뷰를 이용한 WAL I/O 모니터링
7.6 복제 모니터링
____pg_stat_replication 뷰를 이용한 복제 지연 분석
7.7 작업 진행 상황 모니터링
____인덱스 생성 진행 상황 모니터링
____Vacuum 진행 상황 모니터링
____Vacuum Full 진행 상황 모니터링
____COPY 진행 상황 모니터링


8장. 트랜잭션과 대기이벤트

8.1 트랜잭션과 락(Lock)
____로우 레벨(Row-Level) 락
____테이블 레벨 락
____데드락 (Deadlocks)
____멀티 트랜잭션 (Multi-Transaction)
____pg_locks 뷰
____Virtual XID
____fastpath
8.2 대기이벤트
____대기이벤트 유형
____대기이벤트 모니터링 방법
____DataFileRead 대기이벤트
____WAL Insert 대기이벤트
____WALWrite & WalSync 대기이벤트
____BufferMapping 대기이벤트
____transactionid 대기이벤트
____tuple 대기이벤트
____virtualxid 대기이벤트
____relation 대기이벤트


9장. Vacuum

9.1 MVCC 모델과 PostgreSQL MVCC의 특징
____MVCC의 이해
____PostgreSQL MVCC의 특징
____32비트 XID 문제 해결을 위한 2가지 기법
____정리
9.2 Vacuum 기본
____Vacuum 수행 방법과 옵션
____Vacuum 유형별 WAL 발생량
____Vacuum 작업 수행 시 페이지 내의 변경 사항
____vacuumlo를 이용한 Orphan LOB 정리 방법
____AGE란?
____Visibility Map
9.3 Vacuum과 Autovacuum 관련 주요 파라미터
____Autovacuum 프로세스의 역할
____Autovacuum 작업 주기 설정용 파라미터
____Autovacuum 작업 부하 조절용 파라미터
____Autovacuum: Vacuum 및 Analyze 작업 시점 계산용 파라미터
____Autovacuum: Frozen 작업 관련 파라미터
9.4 Slow Query 수행과 Vacuum
____데드 튜플 삭제 기준 (Cutoff) 정책
____테스트를 통한 동일 레코드 반복 업데이트 문제점 확인
9.5 복제 충돌과 Hot Standby Feedback
____복제 충돌 현상 발생 원인
____Vacuum 수행에 의한 스냅샷 복제 충돌
____스냅샷 복제 충돌 현상을 지연시키는 방법
____스냅샷 복제 충돌 현상을 제거하는 방법
____DDL 수행에 의한 락 복제 충돌 현상
9.6 HOT (Heap-Only Tuples)와 fillfactor
____HOT 탄생 배경과 기본 컨셉
____HOT 동작 원리
____fillfactor와 HOT 업데이트 최적화


부록
PSQL 주요 명령어
PSQL 메타 명령어
테이블 주요 Storage 파라미터
주요 익스텐션
____pg_prewarm
____Autoprewarm
____pg_buffercache
____pgstattuple
____pageinspect

김시연 27년 이상 데이터베이스 분야에 몸담아온 성능 튜닝 전문가이다. 20년 넘게 오라클 및 엑사데이터 기반의 성능 진단과 튜닝에 집중해 왔으며, 최근 5년 동안은 주로 PostgreSQL과 EDB 환경에서 성능 컨설팅과 DBA 업무를 수행해왔다. 연세대학교 공과대학원에서 컴퓨터공학을 공부했으며, 이전 직장인 (주)엑셈에서는 약 15년간 삼성전자를 포함한 다양한 고객사를 대상으로 오라클 성능 컨설팅을 수행했으며 컨설팅본부장을 역임했다.
최근 5년간의 주요 프로젝트로는 신한투자증권 메타 프로젝트를 비롯해 교보문고 차세대, KT 지역화폐, 신한은행 '땡겨요' 배달앱 등이 있으며, 이들 프로젝트에서 PostgreSQL 및 EDB 기반의 배치/온라인 시스템의 성능 개선과 DBA 업무를 수행했다.
저서로는 〈PostgreSQL 9.6 성능 이야기〉와 그 영문판 〈PostgreSQL 9.6 Performance Story〉(Amazon 출간) 및 〈Transaction Internals in 10gR2〉(2008)가 있으며, 번역서로는 〈Oracle 코어〉(2012), 〈OWI를 활용한 오라클 진단 & 튜닝〉(2005) 등이 있다.