PostgreSQL 심화 — JSONB·파티셔닝·확장·VACUUM (PostgreSQL 17)

PostgreSQL은 *"가장 발전한 오픈소스 관계형 DB"*를 표방한다. 표준 SQL을 충실히 따르면서도 JSONB·배열·풍부한 인덱스·확장(extension) 생태계로 관계형 + 문서형을 한 엔진에서 다룬다. 이 장은 앞에서 다룬 MVCC·인덱스를 PostgreSQL 고유 기능으로 잇는다. 기준은 PostgreSQL 17(2024-09 출시). (출처: PostgreSQL 17 Release Notes / PostgreSQL 17 Documentation.)

JSONB — 관계형 안의 문서형

PostgreSQL은 JSON을 두 타입으로 담는다 — *json(원문 텍스트 그대로)*과 jsonb(파싱된 이진 형식). 실무는 거의 jsonb다 — 키 중복 제거·정렬 저장·인덱싱 가능하기 때문이다. 주요 연산자:

CREATE TABLE event (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data jsonb NOT NULL
);
INSERT INTO event(data) VALUES
  ('{"type":"login","level":"info","items":[{"name":"A","qty":2}]}');

-- 포함 질의 + GIN 인덱스로 가속
CREATE INDEX idx_event_data ON event USING GIN (data jsonb_path_ops);
SELECT data->>'type' AS type
FROM event
WHERE data @> '{"level":"error"}';     -- GIN 인덱스 사용

PostgreSQL 17은 SQL 표준 JSON_TABLE(JSON을 관계형 테이블로 펼침)과 *SQL/JSON 생성자(JSON, JSON_SCALAR, JSON_SERIALIZE)·질의 함수(JSON_EXISTS, JSON_QUERY, JSON_VALUE)*를 추가해 JSON 다루기가 더 표준화됐다.

-- PostgreSQL 17: JSON_TABLE — JSON 배열을 행으로 펼치기
SELECT e.id, jt.*
FROM event e,
  JSON_TABLE(e.data, '$.items[*]'
    COLUMNS (name text PATH '$.name', qty int PATH '$.qty')) AS jt;
-- 결과: (1, 'A', 2)  ← 배열 원소가 행으로

언제 jsonb를 쓰나 — 스키마가 유동적이거나 희소한 속성에 좋다. 하지만 자주 조인·집계·제약을 거는 핵심 속성은 정규 칼럼으로 빼는 게 정석이다. "전부 jsonb"는 관계형의 강점(제약·조인·옵티마이저)을 버리는 안티패턴.

배열·범위·배제 제약 — 타입으로 규칙을 강제

PostgreSQL은 배열(int[], text[]), 범위 타입(int4range, tstzrange), uuid, inet/cidr, hstore(KV) 등 타입이 풍부하다. 범위 타입 + GiST 인덱스 + 배제 제약으로 "시간이 겹치는 예약 금지" 같은 규칙을 DB가 직접 못 박는다.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE booking (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)  -- 같은 방 시간 겹침 금지
);
-- 겹치는 예약 INSERT 시 DB가 거부 → 애플리케이션 경쟁 조건을 원천 차단

선언적 파티셔닝 — 큰 테이블을 조각으로

수억 행 테이블은 파티셔닝으로 물리적으로 여러 자식 테이블로 쪼갠다(논리적으론 한 테이블). PostgreSQL 10+의 선언적 파티셔닝은 RANGE(날짜)·LIST(지역)·HASH(균등 분산)를 지원한다.

CREATE TABLE logs (id bigint, created_at timestamptz, msg text)
  PARTITION BY RANGE (created_at);
CREATE TABLE logs_2026_06 PARTITION OF logs
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

EXPLAIN SELECT * FROM logs WHERE created_at >= '2026-06-10';
-- → logs_2026_06 만 스캔 (파티션 프루닝); 나머지 파티션은 건너뜀

핵심 이득은 파티션 프루닝 — 옵티마이저가 해당 파티션만 스캔한다. 또 *오래된 파티션을 통째로 DROP*하면 대량 DELETE보다 훨씬 싸게 만료시킨다(시계열·로그의 정석 — DELETE는 죽은 튜플·VACUUM 부담을 남기지만 DROP은 즉시).

인덱스 — 부분·표현식·다양한 종류

[06]의 PG 특수 인덱스를 예제로:

-- 부분 인덱스: 조건 맞는 행만 색인 → 작고 빠름
CREATE INDEX idx_active ON member (email) WHERE status = 'active';
-- 표현식 인덱스: 함수 적용 칼럼 검색 가속
CREATE INDEX idx_lower_email ON member (lower(email));
SELECT * FROM member WHERE lower(email) = 'a@b.com';  -- 인덱스 사용

• GIN — 역색인. jsonb·배열·전문검색(tsvector)·pg_trgm.
• GiST/SP-GiST — 기하·범위·근접.
• BRIN — 아주 큰 자연 정렬 테이블(시계열)에 블록 범위만 저장해 초경량.

확장(Extension) 생태계

CREATE EXTENSION으로 엔진에 기능을 더한다 — PostgreSQL의 진짜 강점.

• pg_stat_statements — 쿼리별 성능 집계(튜닝 필수).
• PostGIS — 지리공간(업계 표준).
• pgvector — 벡터 유사도(임베딩·AI). <->(L2 거리), HNSW/IVFFlat 인덱스.
• pg_trgm — 트라이그램(LIKE '%x%'·오타 허용 검색을 GIN으로 가속).
• postgres_fdw — 외부 PostgreSQL 테이블을 로컬처럼 조회.

-- pgvector: 임베딩 최근접 검색 (RAG/추천)
CREATE EXTENSION vector;
CREATE TABLE doc (id bigint, embedding vector(1536));
CREATE INDEX ON doc USING hnsw (embedding vector_l2_ops);
SELECT id FROM doc ORDER BY embedding <-> '[...]' LIMIT 5;  -- 가장 가까운 5개

TOAST·생성 칼럼·MERGE

• TOAST — 한 행은 보통 8KB 페이지. text·jsonb 큰 값은 TOAST가 압축/별도 저장하고 행엔 포인터만. 큰 칼럼을 SELECT에서 빼면 TOAST 접근을 피해 빨라진다(SELECT * 회피의 또 다른 이유).
• 생성 칼럼 — price_with_tax numeric GENERATED ALWAYS AS (price*1.1) STORED 후 인덱싱.
• MERGE(15+) — 매칭되면 UPDATE/DELETE, 아니면 INSERT. PostgreSQL 17은 MERGE에 **RETURNING**과 뷰 갱신 추가. (단순 UPSERT는 INSERT ... ON CONFLICT가 더 간결.)

VACUUM·운영 — MVCC의 뒤치다꺼리

[05]에서 봤듯 PostgreSQL MVCC는 죽은 튜플을 남기고, autovacuum이 청소한다.

-- 갱신 잦은 큰 테이블엔 autovacuum을 공격적으로
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);
-- idle in transaction 방치 차단 (bloat 예방)
SET idle_in_transaction_session_timeout = '5min';

PostgreSQL 17은 vacuum의 새 메모리 구조로 메모리를 최대 20배 덜 쓰고 더 빨라졌으며 인덱스 vacuum 진행률도 보고한다. 운영 포인트 — 긴 트랜잭션을 피하고, 대량 갱신 테이블 autovacuum 튜닝, 트랜잭션 ID 소진(wraparound) 모니터링. 백업은 *17의 pg_basebackup 증분 백업 + pg_combinebackup*으로 가벼워졌다.

한 줄 요약 — PostgreSQL은 관계형 + jsonb/배열/범위·배제제약 + 풍부한 인덱스(GIN/GiST/BRIN/부분/표현식) + 확장(pgvector·PostGIS·pg_stat_statements) 의 종합선물. 큰 테이블은 선언적 파티셔닝+프루닝(오래된 건 DROP), 큰 값은 TOAST, MVCC 뒤처리는 autovacuum(17에서 메모리 20배↓). 유동 스키마만 jsonb, 핵심 속성은 정규 칼럼.

(출처: PostgreSQL 17 — Release Notes·JSON Functions·Partitioning·Routine Vacuuming / pgvector.)