검색 여닫기
메뉴 여닫기
50
0
9
2.1천
DB스터디
개인 메뉴 토글
로그인하지 않음
만약 지금 편집한다면 당신의 IP 주소가 공개될 수 있습니다.

PostgreSQL 입문

DB스터디
다른 명령
Dbstudy (토론 | 기여)님의 2026년 11월 4일 (수) 15:43 판 (PostgreSQL 초보 입문자용 문서 생성)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
POSTGRESQL · FOUNDATION COURSE
PostgreSQL 입문
세계에서 가장 진보한 오픈소스 관계형 데이터베이스 — 설치와 psql부터 MVCC·VACUUM, 인덱스, 백업까지
처음 시작하는 사람과 Oracle 사용자를 위한 단계별 가이드
난이도 · 입문학습기간 · 10주기준 · PostgreSQL 16 이상비교 · Oracle 19c
STEP 1
개념 · 설치
PostgreSQL의 특징, 설치와 psql 접속
STEP 2
SQL · 데이터 타입
DDL·DML, IDENTITY, JSONB, 스키마
STEP 3
내부 구조
프로세스·메모리, MVCC와 VACUUM, WAL
STEP 4
성능 · 운영
인덱스, EXPLAIN, 권한, 백업·복구

1. PostgreSQL이란

PostgreSQL(포스트그레스큐엘, 줄여서 Postgres)은 1986년 UC 버클리의 POSTGRES 프로젝트에서 시작된 오픈소스 객체-관계형 데이터베이스(ORDBMS)이다. 표준 SQL 준수도가 높고, 확장성(Extension)과 안정성이 뛰어나 스타트업부터 대기업·공공기관까지 널리 쓰인다.

한 줄 정의
라이선스 비용 없이 쓸 수 있는, 표준 SQL과 확장성에 강한 엔터프라이즈급 오픈소스 RDBMS

1.1 주요 특징

OPEN SOURCE
자유로운 라이선스
PostgreSQL License(BSD 계열). 상용 제품에 포함·수정·배포가 자유롭다.
STANDARD
높은 SQL 표준 준수
CTE, 윈도우 함수, MERGE, 트랜잭션 DDL 등 표준 기능을 폭넓게 지원한다.
EXTENSIBLE
확장 기능
PostGIS(공간), pg_stat_statements(SQL 통계), pgvector(벡터) 등 Extension으로 기능을 추가한다.
MVCC
다중 버전 동시성 제어
읽기와 쓰기가 서로를 막지 않는다. 대신 VACUUM으로 오래된 버전을 정리해야 한다.

1.2 Oracle과 한눈에 비교

구분 Oracle PostgreSQL
라이선스 상용 (Edition·옵션별 비용) 오픈소스 (무료)
프로세스 구조 백그라운드 프로세스 + 서버 프로세스 (전용/공유 서버) 접속 1개 = 백엔드 프로세스 1개 (커넥션 풀 권장)
언두(Undo) 관리 Undo 테이블스페이스에 이전 이미지 저장 테이블 안에 이전 버전 행(튜플)을 함께 저장 → VACUUM 필요
리두 로그 Redo Log / Archive Log WAL(Write-Ahead Log) / WAL 아카이브
논리 구조 DB 1개(CDB/PDB) 안에 스키마 = 사용자 클러스터 → 여러 데이터베이스 → 스키마 (사용자와 스키마는 별개)
트랜잭션 DDL DDL 실행 시 자동 커밋 DDL도 롤백 가능
빈 문자열 '' = NULL '' ≠ NULL (서로 다른 값)
절차형 언어 PL/SQL PL/pgSQL (+ PL/Python 등)
실행계획 확인 DBMS_XPLAN, AUTOTRACE EXPLAIN (ANALYZE, BUFFERS)
고가용성 RAC, Data Guard 스트리밍 복제(Streaming Replication), 논리 복제, Patroni 등 외부 도구

2. 설치와 접속

2.1 가장 빠른 방법 — Docker

docker run -d --name pg-lab \
  -e POSTGRES_PASSWORD=lab1234 \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17

docker exec -it pg-lab psql -U postgres

2.2 Linux 패키지 설치 (Rocky/RHEL 계열 예)

# PostgreSQL 공식 저장소(PGDG) 등록 후
sudo dnf install -y postgresql17-server
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17
sudo -iu postgres psql
Windows / macOS
EDB 설치 프로그램(Windows), Postgres.app 또는 Homebrew(macOS)를 사용하면 GUI로 간단히 설치할 수 있다. GUI 관리 도구로는 pgAdmin, DBeaver가 많이 쓰인다.

2.3 psql 필수 메타 명령

명령 의미 Oracle에서 비슷한 것
\l 데이터베이스 목록 V$PDBS / show pdbs
\c dbname 다른 데이터베이스로 접속 전환 ALTER SESSION SET CONTAINER
\dn 스키마 목록 DBA_USERS
\dt / \dt+ 테이블 목록 (+크기) USER_TABLES
\d 테이블명 테이블 구조 (컬럼·인덱스·제약) DESC
\di 인덱스 목록 USER_INDEXES
\du 롤(사용자) 목록 DBA_USERS, DBA_ROLES
\x 세로 출력 전환 —
\timing 실행 시간 표시 SET TIMING ON
\i file.sql 스크립트 실행 @file.sql
\q 종료 exit

3. 논리 구조

 클러스터 (하나의 인스턴스, 하나의 데이터 디렉터리 PGDATA)
  ├─ 데이터베이스 postgres     ← 기본 접속용
  ├─ 데이터베이스 template1    ← CREATE DATABASE 시 복제 원본
  └─ 데이터베이스 appdb
       ├─ 스키마 public
       │    ├─ 테이블, 인덱스, 뷰, 시퀀스, 함수 …
       └─ 스키마 sales
  (공통) 롤(사용자) · 테이블스페이스 ← 클러스터 전체에서 공유
중요
PostgreSQL에서 데이터베이스 간 직접 조인은 불가능하다 (dblink/postgres_fdw 확장 필요). 업무를 나눌 때는 보통 하나의 데이터베이스 안에 여러 스키마를 쓴다.

3.1 search_path

SHOW search_path;                 -- 기본: "$user", public
SET search_path TO sales, public; -- 스키마 이름 없이 조회할 때 찾는 순서
ALTER ROLE app_user SET search_path TO sales, public;  -- 사용자 기본값 지정

4. 기본 SQL과 데이터 타입

4.1 주요 데이터 타입

용도 PostgreSQL Oracle 대응 메모
정수 integer, bigint, smallint NUMBER(10) 정수는 정수형을 쓰면 더 빠르고 작다
정밀 숫자 numeric(p,s) NUMBER(p,s) 금액
문자 varchar(n), text VARCHAR2(n), CLOB text도 성능 차이 없음
날짜 date DATE PostgreSQL date는 시간 없음
일시 timestamp, timestamptz DATE, TIMESTAMP 시간대가 있으면 timestamptz 권장
참/거짓 boolean (없음, CHAR(1) 등)
반정형 jsonb JSON 인덱스(GIN) 가능
배열 integer[], text[] (VARRAY)
고유 ID uuid RAW(16)

4.2 테이블 만들기

CREATE TABLE customer (
    cust_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cust_nm     varchar(100) NOT NULL,
    email       varchar(200) UNIQUE,
    grade_cd    varchar(10)  NOT NULL DEFAULT 'NORMAL',
    attrs       jsonb,
    created_at  timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cust_id     bigint NOT NULL REFERENCES customer (cust_id),
    order_dt    date   NOT NULL DEFAULT current_date,
    amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);
CREATE INDEX ix_orders_cust ON orders (cust_id, order_dt);   -- FK 컬럼 인덱스는 자동 생성되지 않는다

COMMENT ON TABLE customer IS '고객';

4.3 자주 쓰는 SQL

-- INSERT ... RETURNING : 생성된 키를 바로 받기
INSERT INTO customer (cust_nm, email) VALUES ('홍길동', 'hong@example.com')
RETURNING cust_id;

-- UPSERT
INSERT INTO customer (cust_nm, email) VALUES ('홍길동', 'hong@example.com')
ON CONFLICT (email) DO UPDATE SET cust_nm = EXCLUDED.cust_nm;

-- 페이징
SELECT * FROM orders ORDER BY order_dt DESC LIMIT 20 OFFSET 40;

-- JSONB 조회
SELECT cust_nm FROM customer WHERE attrs ->> 'channel' = 'mobile';

-- 트랜잭션 DDL: 롤백하면 테이블 생성도 취소된다
BEGIN;
CREATE TABLE tmp_test (id int);
ROLLBACK;

4.4 Oracle 사용자를 위한 문법 대응표

Oracle PostgreSQL 비고
SELECT 1 FROM DUAL SELECT 1 DUAL 불필요
NVL(a, b) COALESCE(a, b) 표준 함수
SYSDATE now(), current_date now()는 트랜잭션 시작 시각
ROWNUM <= 10 LIMIT 10 / FETCH FIRST 10 ROWS ONLY
seq.NEXTVAL nextval('seq') 또는 IDENTITY 컬럼
DECODE(a, 1, 'x', 'y') CASE a WHEN 1 THEN 'x' ELSE 'y' END
TO_DATE('20261104','YYYYMMDD') to_date('20261104','YYYYMMDD') 결과는 date (시간 없음)
a || b (NULL 무시) a || b (NULL이면 결과 NULL) concat(a, b)는 NULL 무시
MERGE INTO MERGE INTO (15+) / ON CONFLICT
CONNECT BY WITH RECURSIVE
MINUS EXCEPT
동의어(SYNONYM) 없음 (search_path, 뷰로 대체)

5. 내부 구조

5.1 프로세스와 메모리

 클라이언트 ──접속──▶ postmaster(주 프로세스) ──fork──▶ backend 프로세스 (접속마다 1개)
                                                         │ work_mem (정렬·해시)
 ┌──────────────── 공유 메모리 ────────────────┐         ▼
 │ shared_buffers (데이터 페이지 캐시, 8KB)    │◀──── 읽기/쓰기
 │ WAL buffers                                  │
 └──────────────────────────────────────────────┘
 백그라운드: checkpointer · background writer · WAL writer · autovacuum launcher/worker
             · stats · archiver(아카이브 사용 시) · walsender(복제 시)
파라미터 역할 출발점 (전용 서버 기준)
shared_buffers 공유 버퍼 캐시 메모리의 약 25%
effective_cache_size OS 캐시 포함 가용 캐시 추정치 (옵티마이저 참고값) 메모리의 50~75%
work_mem 정렬·해시 작업당 메모리 (세션·노드별로 여러 개 사용 가능) 4MB~64MB에서 시작해 조정
maintenance_work_mem VACUUM, CREATE INDEX용 256MB~1GB
max_connections 최대 접속 수 너무 크게 잡지 말고 PgBouncer 같은 커넥션 풀 사용

5.2 MVCC와 VACUUM — 가장 중요한 개념

PostgreSQL MVCC의 핵심
UPDATE는 기존 행을 고치지 않고 새 버전 행을 추가하고, 이전 행은 "죽은 튜플(dead tuple)"로 남긴다. DELETE도 표시만 한다. 이 죽은 튜플을 치우는 작업이 VACUUM이다.
INSERT
새 튜플 생성
UPDATE
새 버전 추가 + 옛 버전 dead
DELETE
dead 표시
VACUUM
dead 공간 재사용 가능 처리
FREEZE
트랜잭션 ID 순환 방지
작업 하는 일 잠금
VACUUM 죽은 튜플 공간을 재사용 가능하게 표시, 가시성 맵 갱신 일반 DML과 동시 수행 가능
VACUUM (ANALYZE) + 옵티마이저 통계 갱신 동시 수행 가능
VACUUM FULL 테이블을 새로 써서 OS에 공간 반환 배타 잠금 — 운영 중 사용 주의
autovacuum 변경량 기준으로 자동 실행 끄지 말 것 
-- 죽은 튜플이 많은 테이블 확인
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;
중요
오래 열려 있는 트랜잭션(예: 커밋하지 않은 세션, idle in transaction)이 있으면 VACUUM이 죽은 튜플을 치우지 못해 테이블이 계속 커진다(bloat). 애플리케이션에서 트랜잭션을 짧게 유지하는 것이 중요하다.

5.3 WAL과 체크포인트

  • 모든 변경은 데이터 파일보다 먼저 WAL에 기록된다 (Oracle의 Redo와 같은 역할).
  • 체크포인트가 공유 버퍼의 변경 페이지를 디스크에 반영한다 (checkpoint_timeout, max_wal_size).
  • WAL을 보관(archive_mode)하면 시점 복구(PITR)와 복제의 기반이 된다.

6. 인덱스와 실행계획

6.1 인덱스 종류

종류 적합한 경우 예
B-tree (기본) =, 범위, 정렬 CREATE INDEX ix ON orders (order_dt);
GIN JSONB, 배열, 전문 검색 CREATE INDEX ix ON customer USING gin (attrs);
GiST 공간(PostGIS), 범위 타입, 근접 검색 USING gist (geom)
BRIN 물리적 순서와 값이 비례하는 초대용량 로그·시계열 USING brin (created_at)
Hash = 조건만 드물게 사용
부분 인덱스 일부 행만 인덱싱 ... WHERE status = 'ERROR';
표현식 인덱스 함수 결과 ... (lower(email));
커버링 테이블 방문 없이 결과 ... (cust_id) INCLUDE (amount);
운영 중 인덱스 생성
CREATE INDEX CONCURRENTLY를 쓰면 테이블 쓰기를 막지 않고 인덱스를 만든다 (트랜잭션 블록 안에서는 사용 불가).

6.2 EXPLAIN 읽기

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.cust_nm, sum(o.amount)
  FROM customer c JOIN orders o ON o.cust_id = c.cust_id
 WHERE o.order_dt >= current_date - 30
 GROUP BY c.cust_nm;
HashAggregate  (cost=1250.3..1262.8 rows=1000 width=40) (actual time=12.4..12.9 rows=980 loops=1)
  Buffers: shared hit=845
  ->  Hash Join  (cost=32.5..1200.1 rows=10040 width=18) (actual time=0.6..9.8 rows=9876 loops=1)
        Hash Cond: (o.cust_id = c.cust_id)
        ->  Index Scan using ix_orders_dt on orders o  (... rows=10040) (actual ... rows=9876 loops=1)
              Index Cond: (order_dt >= (CURRENT_DATE - 30))
        ->  Hash  (... rows=1000) (actual ... rows=1000 loops=1)
              ->  Seq Scan on customer c  (... rows=1000) (actual ... rows=1000 loops=1)
Planning Time: 0.3 ms
Execution Time: 13.2 ms
항목 의미 Oracle 대응
cost=시작..전체 옵티마이저 예상 비용 Cost
rows (앞) / actual rows 예상 행 수 / 실제 행 수 E-Rows / A-Rows
loops 해당 노드 반복 횟수 (actual rows는 1회 평균) Starts
Buffers: shared hit / read 캐시 적중 / 디스크 읽기 블록 수 Buffers / Reads
Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan 테이블 접근 방식 Full Scan / Index Range Scan 등
Nested Loop / Hash Join / Merge Join 조인 방식 NL / Hash / Sort Merge
읽는 법은 Oracle과 같다. 안쪽(들여쓰기 깊은 곳)부터 읽고, 예상 rows와 actual rows가 크게 다른 노드를 찾는다. 통계가 오래됐다면 ANALYZE 테이블명;으로 갱신한다. → 실행계획 읽기 실습

7. 사용자·권한·접속 제어

-- 롤 = 사용자 + 그룹 (LOGIN 속성이 있으면 사용자)
CREATE ROLE app_user LOGIN PASSWORD 'change_me';
CREATE ROLE app_read;                       -- 권한 묶음용 그룹 롤

CREATE SCHEMA sales AUTHORIZATION app_user;
GRANT USAGE  ON SCHEMA sales TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO app_read;  -- 앞으로 만들 테이블까지
GRANT app_read TO report_user;

pg_hba.conf — 어떤 호스트·사용자·DB에 어떤 인증 방식을 허용할지 정하는 파일 (Oracle의 sqlnet.ora 접속 제어와 비슷) 

# TYPE  DATABASE  USER      ADDRESS          METHOD
local   all       postgres                   peer
host    appdb     app_user  10.0.0.0/24      scram-sha-256
설정 반영
pg_hba.conf, 대부분의 postgresql.conf 변경은 SELECT pg_reload_conf();로 반영된다. shared_buffers처럼 재시작이 필요한 파라미터는 pg_settings.context = 'postmaster'로 확인한다.

8. 백업과 복구

방식 도구 특징 Oracle 대응
논리 백업 pg_dump / pg_dumpall DB·스키마·테이블 단위, 버전 간 이관 가능 Data Pump (expdp/impdp)
논리 복원 pg_restore, psql 병렬 복원(-j) 가능
물리 백업 pg_basebackup 클러스터 전체 온라인 복사 RMAN 백업
시점 복구 베이스 백업 + WAL 아카이브 원하는 시각으로 복구 (PITR) RMAN + Archive Log
운영 도구 pgBackRest, Barman 증분·압축·보관 정책 관리 RMAN Catalog 
pg_dump -U postgres -d appdb -Fc -f appdb.dump        # 사용자 정의 형식(압축)
pg_restore -U postgres -d appdb_new -j 4 appdb.dump   # 4개 병렬 복원
pg_basebackup -U replicator -D /backup/base -Ft -z -P # 물리 베이스 백업

9. 모니터링 기본 쿼리

-- 현재 세션과 대기 상태 (Oracle V$SESSION)
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle'
 ORDER BY xact_age DESC NULLS LAST;

-- 락 대기 원인 세션 찾기
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60)
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 부하 상위 SQL (pg_stat_statements 확장 필요, Oracle V$SQL)
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms,
       shared_blks_hit + shared_blks_read AS blks, left(query, 60)
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 10;

-- 테이블·인덱스 크기
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
  FROM pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

10. 학습 커리큘럼 (10주)

설치 → SQL → 내부 구조 → 성능 → 운영 ·한 주에 이론 3시간 + 실습 3시간Oracle 경험자는 1~2주차를 빠르게 넘어가도 좋습니다.
주차 단계 주제 세부 내용 실습
1 기초 PostgreSQL 소개와 설치 역사·특징·라이선스, Docker/패키지 설치, psql, pgAdmin/DBeaver 실습 서버 구축, psql 메타 명령
2 논리 구조와 기본 SQL 클러스터·DB·스키마, search_path, DDL/DML, 트랜잭션 DDL 샘플 스키마 생성
3 SQL 데이터 타입과 제약조건 숫자·문자·날짜, IDENTITY·시퀀스, JSONB, 배열, 제약조건 쇼핑몰 테이블 설계
4 고급 SQL CTE·WITH RECURSIVE, 윈도우 함수, UPSERT, RETURNING, Oracle 문법 차이 Oracle SQL 10개 변환
5 내부 구조 아키텍처 프로세스·공유 메모리, WAL, 체크포인트, 주요 파라미터 postgresql.conf 분석
6 MVCC와 VACUUM 튜플 버전, dead tuple, autovacuum, bloat, 트랜잭션 ID 순환 UPDATE 반복 후 bloat 관찰
7 성능 인덱스 B-tree·GIN·BRIN·부분·표현식·커버링, CONCURRENTLY JSONB GIN 인덱스 효과 비교
8 실행계획과 통계 EXPLAIN (ANALYZE, BUFFERS), 조인 방식, ANALYZE, pg_stat_statements 느린 SQL 3개 튜닝
9 운영 보안과 모니터링 롤·권한, pg_hba.conf, pg_stat_activity, 락 분석 읽기 전용 계정 설계
10 백업·복구·복제 pg_dump/pg_restore, pg_basebackup, PITR, 스트리밍 복제 개요 특정 시점 복구 실습

11. 입문자 체크리스트

SELF CHECK
이 정도면 입문 완료
□ psql로 접속해 DB·스키마·테이블 구조를 확인할 수 있다
□ 클러스터 · 데이터베이스 · 스키마 · 롤의 관계를 설명할 수 있다
□ IDENTITY, JSONB, timestamptz를 사용한 테이블을 만들 수 있다
□ MVCC에서 UPDATE가 dead tuple을 남기는 이유와 VACUUM의 역할을 설명할 수 있다
□ EXPLAIN (ANALYZE, BUFFERS)로 예상 행 수와 실제 행 수를 비교할 수 있다
□ pg_dump로 백업하고 pg_restore로 복원할 수 있다

12. 용어집

용어 설명 Oracle 대응
클러스터 (Cluster) 하나의 PostgreSQL 인스턴스가 관리하는 데이터베이스 모음 인스턴스 + DB
튜플 (Tuple) 테이블의 행 한 버전 행
페이지 (Page) 저장 단위 (기본 8KB) 블록
WAL 변경 선기록 로그 Redo Log
VACUUM 죽은 튜플 정리 (Undo 자동 관리)
Bloat 죽은 튜플로 인한 테이블·인덱스 비대화 단편화
롤 (Role) 사용자와 그룹을 통합한 권한 주체 User / Role
확장 (Extension) 기능 추가 모듈 (CREATE EXTENSION) 옵션 / 패키지
TOAST 큰 값(약 2KB 초과)을 별도 저장하는 구조 LOB 세그먼트
pg_hba.conf 클라이언트 인증 설정 파일 sqlnet.ora 접속 제어

13. 참고 자료

이전 글데이터 모델링 입문
다음 글SQL 튜닝 입문
원본 주소 "https://dbstudy.co.kr/w/index.php?title=PostgreSQL_입문&oldid=2043"