Orc Hwang's Wiki

개발 학습 기록과 기술 지식을 체계적으로 관리하는 개인 위키입니다.

어두운 작전 회의실의 원형 전투 지도 앞에서 다음 정복 대상을 정하는 오크 전쟁 회의

⚔️ 전쟁 회의 — 다음 정복 대상 선정

PostgreSQL Essential Curriculum

postgresql curriculum
📖 시리즈: PostgreSQL-Essential (2 / 3)
← 이전: PostgreSQL 설치 및 설정: 개발 환경 구축 가이드 다음: PostgreSQL 아키텍처 심층 분석 →
PostgreSQL Essential — 여덟 단계 도장깨기 학습 여정 (기초에서 고급까지 오르는 계단) PostgreSQL 출발 1 기초 2 SQL 3 모델링 4 최적화 5 동시성 6 성능 7 HA 8 고급
PostgreSQL Essential 학습 여정 — 출발점에서 시작해 기초 → SQL → 데이터 모델링 → 쿼리 최적화 → 트랜잭션·동시성 → 성능·모니터링 → 백업·고가용성 → 고급 주제의 여덟 단계를 한 칸씩 도장깨기로 올라 정상의 깃발에 닿는 길. 위쪽 코끼리는 PostgreSQL의 상징.

소개

PostgreSQL은 가장 진보된 오픈소스 관계형 데이터베이스 시스템 중 하나로, 견고성, 확장성, 표준 준수로 잘 알려져 있습니다. 이 커리큘럼은 PostgreSQL을 기초부터 마스터하고자 하는 개발자들을 위한 체계적인 학습 경로를 제공합니다.

이 가이드는 기본 설치 및 SQL 기초부터 쿼리 최적화, 복제, 성능 튜닝과 같은 고급 주제까지 모든 것을 다룹니다.

한눈에 보기 — 전체 학습 경로

여덟 단계는 따로 노는 주제 묶음이 아니라 아래에서 위로 쌓아 올리는 한 줄기입니다. 기초에서 SQL을 다지면 데이터 모델링과 쿼리 최적화로 이어지고, 동시성·성능·운영을 거쳐 마지막에 고급 기능으로 마무리됩니다. 앞 단계를 건너뛰면 뒷 단계가 흔들립니다.

flowchart TB
    S1["1. PostgreSQL 기초<br/>설치·아키텍처·psql"]
    S2["2. SQL 필수<br/>DDL·DML·고급 SQL"]
    S3["3. 데이터 모델링<br/>정규화·파티셔닝·타입"]
    S4["4. 쿼리 최적화<br/>EXPLAIN·인덱스·튜닝"]
    S5["5. 트랜잭션·동시성<br/>ACID·MVCC·락"]
    S6["6. 성능·모니터링<br/>설정·pg_stat·진단"]
    S7["7. 백업·고가용성<br/>PITR·복제·CNPG"]
    S8["8. 고급 주제<br/>확장·보안·PL/pgSQL"]

    S1 --> S2 --> S3 --> S4 --> S5 --> S6 --> S7 --> S8

    S4 -.->|"실행 계획 이해가<br/>성능 튜닝의 토대"| S6
    S5 -.->|"MVCC가<br/>동시 업데이트의 핵심"| S6

1. PostgreSQL 기초

1.1 설치 및 설정

  • 다양한 플랫폼에서 PostgreSQL 설치하기
  • PostgreSQL 아키텍처 이해하기
  • 설정 파일 및 기본 설정
  • psql 커맨드라인 도구 사용하기
  • GUI 도구 (pgAdmin, DBeaver)

1.2 데이터베이스 기본

  • 데이터베이스 생성 및 관리
  • 스키마 이해하기
  • 사용자, 역할, 권한
  • 연결 관리
  • 데이터베이스 인코딩 및 콜레이션

2. SQL 필수 개념

2.1 데이터 정의 언어 (DDL)

  • 적절한 데이터 타입으로 테이블 생성하기
  • 기본 키, 외래 키, 제약조건
  • 인덱스와 그 종류
  • 뷰와 구체화된 뷰 (Materialized Views)
  • 시퀀스와 아이덴티티 컬럼

2.2 데이터 조작 언어 (DML)

  • INSERT, UPDATE, DELETE 연산
  • SELECT 쿼리와 필터링
  • 조인 (INNER, LEFT, RIGHT, FULL)
  • 서브쿼리와 CTE (Common Table Expressions)
  • 집계와 GROUP BY

2.3 고급 SQL

  • 윈도우 함수 (Window Functions)
  • 재귀 쿼리 (Recursive Queries)
  • JSON 및 JSONB 연산
  • 전문 검색 (Full-text Search)
  • 배열 연산

3. 데이터 모델링

3.1 설계 원칙

  • 정규화 (1NF, 2NF, 3NF, BCNF)
  • 엔티티-관계 모델링
  • 적절한 데이터 타입 선택
  • 비정규화가 필요한 경우
  • 스키마 설계 패턴

3.2 PostgreSQL 특화 기능

  • 상속 (Inheritance)
  • 테이블 파티셔닝
  • 사용자 정의 타입과 도메인
  • 복합 타입 (Composite Types)
  • 범위 타입 (Range Types)

4. 쿼리 최적화

4.1 EXPLAIN 이해하기

  • 실행 계획 읽기
  • 비용과 타이밍 이해하기
  • 병목 지점 식별하기
  • EXPLAIN vs EXPLAIN ANALYZE
  • 시각화 도구

4.2 인덱스 최적화

  • B-tree, Hash, GiST, GIN, BRIN 인덱스
  • 각 인덱스 타입을 언제 사용할지
  • 커버링 인덱스 (Covering Indexes)
  • 부분 인덱스 (Partial Indexes)
  • 인덱스 유지보수

4.3 쿼리 튜닝

  • 더 나은 성능을 위한 쿼리 재작성
  • 통계 이해하기
  • 일반적인 안티 패턴 피하기
  • Prepared Statement 사용
  • 커넥션 풀링

5. 트랜잭션과 동시성

5.1 트랜잭션 관리

  • ACID 속성
  • 트랜잭션 격리 수준
  • SAVEPOINT와 중첩 트랜잭션
  • 2단계 커밋 (Two-Phase Commit)
  • 교착 상태 감지 및 방지

5.2 동시성 제어

  • MVCC (Multi-Version Concurrency Control)
  • 락 타입과 락 모니터링
  • 행 수준 vs 테이블 수준 락킹
  • Advisory Locks
  • 동시 업데이트 처리
MVCC — 읽기와 쓰기가 서로 막지 않는 이유: 같은 행의 여러 버전을 동시에 보관 트랜잭션 A UPDATE (쓰기) 트랜잭션 B SELECT (읽기) 같은 행 (row) — 두 버전 공존 옛 버전 (v1) xmin=100, xmax=200 B의 스냅숏에 보임 새 버전 (v2) xmin=200 A가 만든 행 새 버전 생성 옛 버전 읽음 읽기는 쓰기를, 쓰기는 읽기를 막지 않는다 각 트랜잭션은 자기 스냅숏이 보는 버전만 읽는다 VACUUM 죽은 옛 버전 정리
MVCC의 핵심 직관 — UPDATE는 행을 덮어쓰는 대신 새 버전을 만들고 옛 버전을 남겨 둡니다. 먼저 시작한 읽기 트랜잭션은 자기 스냅숏 기준 옛 버전을 그대로 보므로, 읽기와 쓰기가 서로를 막지 않습니다. 더 이상 어떤 트랜잭션에도 보이지 않게 된 옛 버전은 나중에 VACUUM이 정리합니다.

6. 성능과 모니터링

6.1 성능 튜닝

  • 설정 파라미터 (shared_buffers, work_mem 등)
  • Autovacuum 설정
  • 체크포인트 튜닝
  • WAL (Write-Ahead Logging) 최적화
  • 리소스 관리

6.2 모니터링과 진단

  • pg_stat 뷰
  • 느린 쿼리 식별하기
  • 로그 분석
  • 모니터링 도구 (pg_stat_statements)
  • 알림과 메트릭

7. 백업과 복구

7.1 백업 전략

  • pg_dump와 pg_dumpall
  • 파일 시스템 수준 백업
  • 연속 아카이빙 (Continuous Archiving)
  • 특정 시점 복구 (PITR)
  • 백업 모범 사례

7.2 고가용성

  • 스트리밍 복제 (Streaming Replication)
  • 논리 복제 (Logical Replication)
  • 장애 조치와 전환 (Failover and Switchover)
  • PgBouncer를 이용한 커넥션 풀링
  • 로드 밸런싱

7.3 Kubernetes에서의 PostgreSQL (CNPG)

  • CloudNativePG (CNPG) 소개
  • CNPG 아키텍처 이해하기
  • Kubernetes에 PostgreSQL 클러스터 배포
  • CNPG Operator를 통한 자동화된 관리
  • 선언적 설정을 통한 클러스터 구성
  • 자동 장애 조치 및 자가 복구
  • 롤링 업데이트와 버전 업그레이드
  • 백업 및 복구 자동화 (Barman)
  • 모니터링 및 메트릭 수집 (Prometheus, Grafana)
  • CNPG 플러그인을 통한 kubectl 통합
  • 프로덕션 환경에서의 CNPG 운영 패턴
  • 스토리지 클래스 및 PVC 관리
  • 리소스 제한 및 QoS 설정

8. 고급 주제

8.1 확장 기능

  • 인기 있는 확장 기능 (PostGIS, pg_trgm, hstore)
  • 사용자 정의 확장 기능 생성
  • 확장 기능 관리
  • Foreign Data Wrappers (FDW)

8.2 보안

  • 인증 방법
  • SSL/TLS 설정
  • 행 수준 보안 (Row-Level Security, RLS)
  • 저장 데이터 암호화 (Encryption at Rest)
  • 감사 로깅

8.3 고급 기능

  • 저장 프로시저와 함수 (PL/pgSQL)
  • 트리거와 이벤트 트리거
  • Pub-Sub을 위한 Listen/Notify
  • 외부 테이블 (Foreign Tables)
  • 병렬 쿼리 실행

핵심 포인트

  • 기초부터 시작: 고급 주제로 넘어가기 전에 SQL과 기본 데이터베이스 개념을 마스터하세요
  • 쿼리 최적화 연습: 성능을 위해 EXPLAIN과 인덱스를 이해하는 것이 중요합니다
  • MVCC 학습: PostgreSQL의 동시성 모델은 독특하고 강력합니다
  • 확장 기능 탐색: PostgreSQL의 확장성은 가장 큰 강점 중 하나입니다
  • 성능에 집중: 설정 튜닝과 모니터링은 필수 기술입니다
  • 백업/복구 이해: 데이터 안전은 항상 우선순위여야 합니다
  • 실제 패턴 학습: 프로덕션 사용 사례와 모범 사례에서 배우세요
  • 클라우드 네이티브 접근: Kubernetes에서 CNPG를 사용한 현대적인 PostgreSQL 운영을 이해하세요

결론

PostgreSQL 마스터하기는 이론적 지식과 실무 경험을 모두 필요로 하는 여정입니다. 이 커리큘럼은 기초부터 시작해 고급 주제로 진행하는 체계적인 PostgreSQL 학습 접근법을 제공합니다.

성공의 열쇠는 실습입니다. 로컬 PostgreSQL 인스턴스를 설정하고, 각 주제를 체계적으로 학습하며, 배운 것을 실제 시나리오에 적용하세요. 기초를 건너뛰지 마세요—MVCC, 쿼리 플래닝, 인덱싱과 같은 핵심 개념을 이해하는 것이 모든 고급 주제의 기반이 됩니다.

다음 학습

  • PostgreSQL 개발 환경 구축 - 설치 및 설정 시작하기
  • PostgreSQL 아키텍처 심층 분석 - PostgreSQL이 내부적으로 어떻게 작동하는지 이해하기
  • SQL 쿼리 최적화 기법 - 효율적인 쿼리 작성을 위한 실용 가이드
  • PostgreSQL 성능 튜닝 가이드 - 종합적인 성능 최적화 전략
  • PostgreSQL로 고가용성 구현하기 - 복제 및 장애 조치 전략
  • CloudNativePG (CNPG) 시작하기 - Kubernetes에서 PostgreSQL 운영하기