공식 문서(postgresql.org/docs, postgresql.kr/docs/13) 기반 지식 저장소 개념 이해 → 실무 예제 → 빠른 참조 3단 구성
모든 서비스 엔지니어·DBA·백엔드 개발자가 운영을 효율화하기 위해 참조할 수 있도록 구성했다. 공식 문서의 정확성을 유지하면서, 실제 장애·튜닝 경험에서 얻은 노하우를 함께 담았다.
🎬 시각화 학습 사이트: index.html을 브라우저로 열면 PostgreSQL·ClickHouse·Elasticsearch·Oracle 4개 가이드를 단계별 애니메이션으로 배울 수 있다 (GitHub Pages 배포 가능). Oracle 가이드에는 쿼리 튜닝 팁 모음 페이지가 포함되어 있다.
🧩 RDB 공통 원리: 제품을 가리지 않는 관계형 DB 기본기는 rdb/index.html에 별도 트랙으로 정리했다 — 낙관적·비관적·분산 락, 트랜잭션과 격리 수준, Nested Loop·Hash·Sort Merge 조인, SQL 튜닝 방법론과 안티패턴 20+. 5트랙 16장 커리큘럼과 함께 락 3종 비교표·격리 수준 매트릭스·조인 비교표·벤더 차이표를 바로 참조할 수 있다.
postgresql-guide/
├── chapters/ ← 개념 학습 (1~14장) → 목차: chapters/README.md
├── examples/ ← 실무 도메인 예제 (5개) → 목차: examples/README.md
├── troubleshooting/ ← 장애 케이스 스터디 (13개) → 목차: troubleshooting/README.md
└── cheatsheets/ ← 빠른 참조 (9개) → 목차: cheatsheets/README.md
각 폴더의
README.md는 그 영역의 목차·학습 경로·상황별 선택 가이드를 담고 있다.
- chapters/ — 14장 목차 + 초/중/고급 학습 경로
- examples/ — 5개 도메인 + 도메인 선택 플로우
- troubleshooting/ — 증상으로 빠르게 찾기
- cheatsheets/ — 상황별 치트시트 선택 + 자주 쓰는 쿼리 Top 5
flowchart LR
subgraph CH[chapters 개념]
C1[01 개요/철학]
C2[02 아키텍처]
C3[03 MVCC]
C4[04 Storage/TOAST]
C5[05 인덱스]
C6[06 플래너·EXPLAIN]
C7[07 트랜잭션·격리]
C8[08 VACUUM]
C9[09 WAL·Checkpoint]
C10[10 Replication]
C11[11 Backup·PITR]
C12[12 파티셔닝]
C13[13 Extension]
C14[14 모니터링]
end
subgraph EX[examples 도메인]
E1[E-commerce]
E2[SaaS 멀티테넌시]
E3[시계열 로그]
E4[JSONB 문서]
E5[PostGIS 지리]
end
subgraph TS[troubleshooting 27 cases]
TA[A. Autovacuum·Bloat·Wraparound 6]
TB[B. 쿼리 7]
TC[C. Lock 5]
TD[D. 운영 5]
TE[E. 보안/권한 2]
TF[F. 업그레이드/클라우드 2]
end
subgraph CS[cheatsheets 빠른 참조]
CS1[psql]
CS2[EXPLAIN]
CS3[index]
CS4[type]
CS5[vacuum]
CS6[config]
CS7[pg_stat]
CS8[backup]
CS9[version]
end
CH --> EX
CH --> TS
CH --> CS
TS -.진단 쿼리.-> CS7
EX -.튜닝 참조.-> CS
flowchart TB
Client[클라이언트] -->|libpq/TCP| PM[Postmaster<br/>supervisor]
PM -->|fork| BE[Backend process<br/>per connection]
BE --> Parser[Parser]
Parser --> Rewriter[Rewriter<br/>Rule]
Rewriter --> Planner[Planner/Optimizer]
Planner --> Executor[Executor]
Executor --> SB[(Shared Buffers<br/>기본 25% RAM)]
SB -->|dirty| Checkpointer[Checkpointer]
SB -->|dirty| BGW[Background Writer]
Checkpointer --> Disk[(Heap/Index<br/>8KB pages)]
BGW --> Disk
Executor -->|WAL record| WALB[(WAL Buffers)]
WALB --> WALW[WAL Writer]
WALW --> WAL[(pg_wal/<br/>WAL segments)]
WAL -->|archive_command| Archive[(WAL Archive)]
WAL -->|streaming| Standby[Standby 서버]
AVL[Autovacuum<br/>Launcher] --> AVW[Autovacuum<br/>Worker]
AVW --> Disk
Stats[Stats Collector] -.read.-> BE
chapters/ch01_postgresql_overview.md (왜 PostgreSQL인가, 설계 철학)
↓
chapters/ch02_architecture.md (프로세스 모델, Shared Buffer, WAL)
↓
chapters/ch03_mvcc.md (PostgreSQL 핵심 — MVCC)
↓
cheatsheets/psql_commands.md (psql 명령 레퍼런스)
chapters/ch05_indexes.md (인덱스 타입별 선택)
↓
chapters/ch06_query_planner.md (EXPLAIN 읽는 법)
↓
cheatsheets/explain_reading.md
↓
troubleshooting/B1_missing_index.md ~ B4 (쿼리 실수 케이스)
chapters/ch08_vacuum_autovacuum.md (VACUUM, Bloat, XID Wraparound)
↓
chapters/ch09_wal_checkpoint.md (WAL, Checkpoint, 디스크 I/O)
↓
chapters/ch14_monitoring_troubleshooting.md
↓
troubleshooting/A*, C*, D* (오토배큠/Lock/운영 장애)
↓
cheatsheets/pg_stat_queries.md (진단 쿼리 모음)
- 1장. PostgreSQL 개요와 설계 철학
- 객체-관계형(ORDBMS), 확장성, ACID, 다른 DB와의 차이
- 2장. 아키텍처와 프로세스 모델
- Postmaster, Backend, Background Worker, Shared Buffer, WAL Buffer
- 3장. MVCC — PostgreSQL 성능과 잠금의 비밀
- xmin/xmax, Snapshot, Visibility, Dead Tuple의 탄생
- 4장. Heap, Tuple, Page, TOAST
- 8KB 페이지 구조, HOT 업데이트, TOAST가 자동으로 하는 일
- 5장. 인덱스 타입
- B-tree / Hash / GIN / GiST / BRIN / SP-GiST 선택 기준
- 6장. 쿼리 플래너와 EXPLAIN
- 통계·Cost 모델, 스캔/조인 전략, EXPLAIN (ANALYZE, BUFFERS) 읽기
- 7장. 트랜잭션과 격리 수준
- Read Committed, Repeatable Read, Serializable, Lock 레벨, Deadlock
- 8장. VACUUM과 Autovacuum
- Bloat, Dead Tuple, XID Wraparound, Visibility Map
- 9장. WAL과 Checkpoint
- Durability, full_page_writes, checkpoint_timeout, wal_compression
- 10장. 복제(Replication)
- Streaming, Logical, Synchronous Commit, 스탠바이 지연 모니터링
- 11장. 백업과 복구
- pg_dump, pg_basebackup, PITR, WAL Archiving
- 12장. 파티셔닝
- Declarative Partitioning, Partition Pruning, 주의사항
- 13장. 핵심 확장(Extension)
- pg_stat_statements, pgaudit, postgis, pg_trgm, pgvector 등
- 14장. 모니터링과 트러블슈팅
- pg_stat_* 뷰, 슬로우 쿼리 추적, Lock 분석, Connection 관리
실제 서비스에서 자주 마주치는 요구사항을 PostgreSQL로 어떻게 푸는지.
| # | 도메인 | 핵심 개념 |
|---|---|---|
| 01 | 🛒 E-commerce 주문/재고 | 트랜잭션·Lock, 인덱스, 파티셔닝 |
| 02 | 🏢 SaaS 멀티테넌시 | 스키마 전략, RLS, 대량 테넌트 운영 |
| 03 | 📊 시계열 로그 | BRIN, 파티셔닝, 집계 MV |
| 04 | 📄 JSONB 문서 저장소 | jsonb 연산자, GIN 인덱스 |
| 05 | 🌍 지리정보 | PostGIS, GiST, 공간 쿼리 |
증상 → 원인 → 진단 → 해결 → 예방 5단계. 전체 목차·증상 인덱스는 troubleshooting/README.md 참고.
| 케이스 | 핵심 증상 |
|---|---|
| A1. Bloat 누적 | 테이블 용량 급증, SELECT 느려짐 |
| A2. XID Wraparound 경고 | database is not accepting commands 직전 경고 |
| A3. 긴 트랜잭션이 VACUUM을 막는다 | Dead Tuple 계속 증가 |
| A4. Multixact Wraparound | multixact members limit exceeded, FK row-lock 워크로드 |
| A5. 인덱스 단독 Bloat | 테이블은 멀쩡한데 인덱스만 비대, REINDEX CONCURRENTLY |
| A6. Temp File 디스크 풀 | base/pgsql_tmp/ 급증, work_mem 초과 |
| 케이스 | 핵심 증상 |
|---|---|
| B1. 인덱스 누락 | 갑자기 Seq Scan 폭주 |
| B2. 인덱스가 있어도 Seq Scan | 통계 오차, 함수 래핑, 타입 불일치 |
| B3. 잘못된 조인 순서 | 중간 결과 폭발 |
| B4. N+1 쿼리 | ORM 기본값 주의 |
| B5. 플랜 회귀 | 코드 변경 없이 갑자기 느려짐 |
| B6. work_mem 부족 / 디스크 정렬 | external merge sort, Hash batch 분할 |
| B7. Prepared Statement 함정 | 앱에서만 특정 값이 느림, pgBouncer 함정 |
| 케이스 | 핵심 증상 |
|---|---|
| C1. 데드락 | deadlock detected |
| C2. idle in transaction | VACUUM·DDL 블록 |
| C3. DDL이 쿼리를 막는다 | AccessExclusiveLock |
| C4. FK 숨은 Share Lock | 자식 INSERT/UPDATE가 부모 행을 잠금 |
| C5. Advisory Lock 누수 | 세션 Lock이 종료 후에도 남음 |
| 케이스 | 핵심 증상 |
|---|---|
| D1. Connection 고갈 | too many connections, pgBouncer |
| D2. Replication Lag | 스탠바이 지연 누적 |
| D3. WAL로 인한 디스크 풀 | pg_wal 급증, 슬롯 미회수 |
| D4. Recovery Conflict | Standby 쿼리 canceling statement due to conflict |
| D5. Logical Replication 장애 | apply lag, UNIQUE 충돌, DDL drift |
| 케이스 | 핵심 증상 |
|---|---|
| E1. 권한 오류 (GRANT/Default Privileges) | 새 테이블마다 권한 누락, v15+ public 스키마 |
| E2. RLS 정책 함정 | 소유자 우회, pgBouncer SET 상속, pg_dump 누락 |
| 케이스 | 핵심 증상 |
|---|---|
| F1. pg_upgrade 실패 시나리오 | --check 누락, extension 라이브러리, ANALYZE 회귀 |
| F2. 관리형 PG 제약 | SUPERUSER 부재, 파일시스템 차단, extension 허용 목록 |
| 파일 | 내용 |
|---|---|
| psql_commands.md | psql 메타커맨드, 생산성 팁 |
| explain_reading.md | EXPLAIN 출력 해석, 노드별 특징 |
| index_selection.md | 인덱스 타입 선택 플로우차트 |
| type_selection.md | 타입 선택 가이드, 함정 |
| vacuum_tuning.md | autovacuum 파라미터 튜닝 |
| config_parameters.md | 필수 postgresql.conf 파라미터 |
| pg_stat_queries.md | 진단 쿼리 모음 (Lock, Bloat, 느린 쿼리) |
| backup_recovery_recipes.md | 백업·복구 레시피 |
| version_history.md | 버전별 주요 변경(10~17), LTS 선택 |
PostgreSQL을 쓸 때 반드시 지켜야 할 원칙을 한 페이지에 압축.
1. UPDATE-heavy 워크로드에서 HOT 업데이트가 가능하도록 fillfactor 고려
→ 변경 가능한 컬럼은 가급적 인덱스에서 제외
→ MVCC는 UPDATE에서 새 버전을 "append"하므로 Bloat가 쉽게 쌓인다
2. 인덱스는 "꼭 필요한 쿼리"에만
→ 인덱스마다 WRITE 비용이 늘어나고 Bloat 대상도 증가
3. 트랜잭션은 짧게, 명시적으로 종료
→ idle in transaction이 VACUUM을 막는다
4. 대용량 테이블은 파티셔닝으로 관리성 확보
→ DROP PARTITION은 VACUUM보다 비교할 수 없이 저렴
5. TEXT를 기본으로 쓴다, VARCHAR(N)은 제약이 필요할 때만
→ PostgreSQL에서는 TEXT/VARCHAR 성능 차이 없음
1. 단건 INSERT보다 배치 COPY 또는 multi-row INSERT
→ WAL·트랜잭션 오버헤드 절감
2. 대량 DELETE 대신 파티션 DROP 또는 배치 삭제
→ 한 번에 삭제하면 Autovacuum이 따라가지 못해 Bloat 폭증
3. UPDATE로 인한 Bloat를 인지하고 fillfactor/autovacuum 튜닝
→ pgstattuple, pg_stat_user_tables로 모니터링
1. EXPLAIN (ANALYZE, BUFFERS)을 기본으로 사용
→ 읽은 블록 수(shared hit/read)로 실제 I/O를 측정
2. OR/IN, 함수 래핑, 타입 불일치는 인덱스 비활성의 단골
→ WHERE to_char(created_at,'YYYY-MM-DD') = ... ❌
→ WHERE created_at >= '...' AND created_at < '...' ✅
3. LIMIT + ORDER BY 조합은 인덱스가 ORDER BY 방향과 일치해야 빠르다
4. pg_stat_statements는 기본 탑재
→ "어떤 쿼리가 얼마나 느리고 얼마나 자주 실행되는가"의 최고의 출처
1. pg_stat_statements, auto_explain을 처음부터 켜둔다
2. autovacuum은 "항상 켜두되", 큰 테이블만 개별 튜닝
3. shared_buffers는 메모리의 25% 수준이 일반적 시작점
4. wal_level, max_wal_senders, max_replication_slots는 사전에 여유 있게
5. 장애 시 진단 순서: 연결 수 → Lock → 긴 트랜잭션 → 쿼리
- 공식 문서(영문): postgresql.org/docs
- 공식 문서(한글, 13 기준): postgresql.kr/docs/13
- 성능/운영 위키: wiki.postgresql.org
- pg_stat_statements: postgresql.org/docs/current/pgstatstatements.html
- 소스 아키텍처:
src/backend/README
- 처음 보는 사람: README → 1~3장 → 해당 영역 치트시트
- 운영 중 장애:
troubleshooting/폴더에서 증상 기반 검색 - 튜닝이 필요할 때:
cheatsheets/pg_stat_queries.md→ 해당 챕터 심화 - 리뷰/검수: 각 문서 하단의 "공식 문서 참조" 블록을 확인