Bong's Log

운영 환경에서 대규모 로그성 데이터를 다루다 보면, 테이블이 금방 수십~수백 GB로 커져버리는 경우가 많습니다. 특히 audit, log, history 같은 이력성 데이터는 시간이 지남에 따라 계속 누적되기 때문에 성능 저하와 관리 이슈가 발생하기 쉽습니다.

이런 문제를 해결하기 위해 PostgreSQL에서는 파티셔닝(partitioning) 기법을 활용할 수 있습니다. 그중에서도 pg_partman과 pg_cron 확장을 활용하면, 자동 파티션 생성·삭제 및 유지보수 스케줄링을 설정할 수 있습니다.

1. pg_partman을 이용한 파티션 테이블 생성

아래 예시는 cms.audits 테이블을 월 단위 파티션으로 관리하도록 설정한 코드입니다.

• sample.audits 테이블을 기준으로 함
• created_at 컬럼을 기준으로 1개월 단위 파티션 생성
• p_premake = 3 → 앞으로 3개월치 파티션을 미리 생성
• p_start_partition → 현재 시점 기준, 2개월 전부터 파티션 시작

2. 파티션 관리 정책 설정

파티션이 생성된 이후에는 보관 주기와 유지보수 방식을 설정합니다.

• 보관 주기: 최근 5년치 데이터만 유지
• retention_keep_table = false → 오래된 파티션 테이블 자동 삭제
• retention_keep_index = false → 오래된 인덱스 자동 삭제
• automatic_maintenance = on → 유지보수 자동 실행
• infinite_time_partitions = true → 시간이 지나도 계속 파티션 확장

3. pg_cron으로 정기 유지보수 스케줄링

pg_cron을 이용하면 리눅스 cron처럼 PostgreSQL 내부에서 작업을 예약할 수 있습니다.

• 매일 새벽 2시에 기본 유지보수 실행
• 매주 일요일 새벽 3시에 유지보수 + 통계 갱신(ANALYZE) 실행

4. 활용 포인트

• 대용량 로그성 데이터를 효율적으로 관리 가능
• 자동화 덕분에 DBA가 수동으로 파티션을 만들고 삭제할 필요 없음
• 성능 관리와 저장 공간 관리에 큰 도움이 됨
• pg_partman은 월/일/시간 단위까지 다양한 파티션 주기를 지원

마무리

PostgreSQL 단독으로도 파티셔닝은 가능하지만, pg_partman을 활용하면 훨씬 더 손쉽게 자동 파티션 관리를 구현할 수 있습니다. 여기에 pg_cron을 붙이면 스케줄 기반으로 정기적인 유지보수까지 자동화할 수 있습니다.

운영 환경에서 로그성 데이터를 다루고 있다면, pg_partman + pg_cron 조합을 꼭 고려해보시길 추천드립니다.

PostgreSQL 17 기준으로 계정/권한/세션/잠금/복제/Autovacuum/설정을 점검할 때 자주 쓰는 시스템 카탈로그와 통계 뷰, 그리고 실무용 SQL 스니펫을 한 번에 모았습니다. (PaaS에서도 대부분 그대로 동작)

용어 요약: 카탈로그(catalog) = pg_로 시작하는 시스템 테이블/뷰. 보통 SELECT만으로 진단 가능.

0) 계정/역할 기본 개념

• PostgreSQL의 사용자(User) 개념 = 역할(Role). 로그인 가능한 역할과 로그인 불가한 역할(그룹 역할)을 조합해 씀.
• 대표 속성: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, INHERIT, CONNECTION LIMIT, VALID UNTIL(만료), rolpassword(SCRAM)

1) 계정/역할: pg_user, pg_roles, pg_authid, pg_auth_members

• pg_user: 호환성 뷰(읽기 전용). 로그인 가능한 역할 위주 표시.
• pg_roles: 모든 역할(로그인/비로그인) 표시. 실무에선 이걸 기본으로 사용.
• pg_authid: 내부 카탈로그(슈퍼유저만 접근). 비밀번호 해시 포함.
• pg_auth_members: 역할-멤버십 매핑(누가 어떤 역할의 멤버인지).

1-1) 모든 역할과 주요 속성

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication,
       rolbypassrls, rolinherit, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles ORDER BY rolname;

1-2) 멤버십(그룹 역할 포함) — 누가 어떤 역할의 멤버인가

SELECT r.rolname     AS role,
       m.rolname     AS member,
       a.admin_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
LEFT JOIN LATERAL (SELECT am.admin_option) a ON TRUE
ORDER BY role, member;

1-3) 로그인 가능한 계정만

SELECT rolname, rolvaliduntil, rolconnlimit
FROM pg_roles
WHERE rolcanlogin
ORDER BY rolname;

1-4) 곧 만료(VALID UNTIL) 예정 계정

SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin
  AND rolvaliduntil IS NOT NULL
  AND rolvaliduntil < now() + interval '14 days'
ORDER BY rolvaliduntil;

2) 권한/오브젝트: 스키마·테이블·시퀀스·기본권한

• pg_namespace(스키마), pg_class(테이블/뷰/인덱스/시퀀스), pg_attribute(컬럼), pg_default_acl(기본 권한)
• 헬퍼 함수: has_schema_privilege, has_table_privilege, has_sequence_privilege

2-1) 특정 스키마에 대한 USAGE/CREATE 보유자 찾기

SELECT n.nspname, r.rolname,
       has_schema_privilege(r.rolname, n.nspname, 'USAGE')  AS usage,
       has_schema_privilege(r.rolname, n.nspname, 'CREATE') AS create
FROM pg_namespace n
CROSS JOIN pg_roles r
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname <> 'information_schema'
  AND r.rolcanlogin
ORDER BY n.nspname, r.rolname;

2-2) 테이블 권한 매트릭스(SELECT/INSERT/UPDATE/DELETE)

SELECT n.nspname AS schema, c.relname AS table, r.rolname AS grantee,
       has_table_privilege(r.oid, c.oid, 'SELECT') AS sel,
       has_table_privilege(r.oid, c.oid, 'INSERT') AS ins,
       has_table_privilege(r.oid, c.oid, 'UPDATE') AS upd,
       has_table_privilege(r.oid, c.oid, 'DELETE') AS del
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN pg_roles r
WHERE c.relkind IN ('r','p','v','m') -- 테이블/파티션/뷰/머터리얼라이즈드뷰
  AND n.nspname NOT LIKE 'pg_%' AND n.nspname <> 'information_schema'
  AND r.rolcanlogin
ORDER BY 1,2,3;

2-3) 기본 권한(ALTER DEFAULT PRIVILEGES) 확인

SELECT defaclrole::regrole AS owner, defaclnamespace::regnamespace AS nsp,
       defaclobjtype, defaclacl
FROM pg_default_acl
ORDER BY owner, nsp, defaclobjtype;

3) 세션/활동/잠금: pg_stat_activity, pg_locks

• pg_stat_activity: 현재 세션/쿼리/상태/대기
• pg_locks: 대상 오브젝트에 대한 잠금 현황

3-1) 오래 실행 중인 쿼리(예: 5분 초과)

SELECT pid, usename, datname, state, now() - query_start AS duration,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

3-2) 블로킹/블록드 세션 찾기

WITH locks AS (
  SELECT pid, locktype, relation::regclass AS rel, mode, granted
  FROM pg_locks
), act AS (
  SELECT pid, usename, datname, state, query
  FROM pg_stat_activity
)
SELECT b.pid   AS blocked_pid, b_us.usename AS blocked_user, b_us.query AS blocked_query,
       a.pid   AS blocker_pid, a_us.usename AS blocker_user, a_us.query AS blocker_query,
       l.rel, l.mode
FROM pg_locks bl
JOIN pg_locks l ON (bl.locktype = l.locktype AND bl.locktype <> 'advisory' AND bl.relation = l.relation AND bl.pid <> l.pid)
JOIN pg_stat_activity b_us ON b_us.pid = bl.pid
JOIN pg_stat_activity a_us ON a_us.pid = l.pid
JOIN act a ON a.pid = l.pid
JOIN act b ON b.pid = bl.pid
WHERE NOT bl.granted AND l.granted
ORDER BY b.pid;

3-3) Idle in transaction(장기 유휴 트랜잭션)

SELECT pid, usename, datname, now() - xact_start AS xact_age, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC;

4) 통계/Autovacuum/인덱스: pg_stat_all_tables, pg_stat_user_indexes, pg_stat_progress_*

• pg_stat_all_tables: 테이블별 스캔/튜플 통계, Autovacuum/Analyze 횟수
• pg_stat_user_indexes: 인덱스 사용 통계(hit/scan)
• pg_stat_progress_vacuum, pg_stat_progress_create_index: 진행 중 작업의 실시간 상태

4-1) Autovacuum가 덜 도는(후보) 테이블 찾기

SELECT schemaname, relname, n_dead_tup, vacuum_count, autovacuum_count,
       now() - last_autovacuum AS since_last_auto
FROM pg_stat_all_tables
WHERE schemaname NOT LIKE 'pg_%'
ORDER BY n_dead_tup DESC
LIMIT 50;

4-2) 사용되지 않는 인덱스 후보

SELECT n.nspname, c.relname AS index, idx.idx_scan
FROM pg_stat_user_indexes idx
JOIN pg_class c ON c.oid = idx.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE idx.idx_scan = 0
ORDER BY n.nspname, c.relname;

4-3) 진행 중 VACUUM/인덱스 생성 확인

SELECT * FROM pg_stat_progress_vacuum;
SELECT * FROM pg_stat_progress_create_index;

5) 설정/파일/접속 제어: pg_settings, pg_file_settings, pg_hba_file_rules

• pg_settings: 현재 파라미터 값
• pg_file_settings: postgresql.conf 파싱 결과(적용/오류 여부 포함)
• pg_hba_file_rules: HBA 규칙 파싱 결과(매치 순서/주석 포함)

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size','random_page_cost','max_connections');

SELECT * FROM pg_file_settings WHERE error IS NOT NULL;  -- 설정 오류 진단

SELECT * FROM pg_hba_file_rules ORDER BY line_number;    -- 접속 규칙 확인

6) 확장/파티셔닝/정책: pg_extension, pg_available_extensions, pg_partitions, pg_policies

• pg_extension: 설치된 확장(예: pg_stat_statements, uuid-ossp, pgvector)
• pg_available_extensions: 설치 가능 목록
• pg_policies: RLS(Row-Level Security) 정책

SELECT extname, extversion, extnamespace::regnamespace AS nsp
FROM pg_extension ORDER BY extname;

SELECT * FROM pg_available_extensions ORDER BY name;

SELECT * FROM pg_policies ORDER BY schemaname, tablename;

7) 복제/논리 슬롯/아카이브: pg_stat_replication, pg_replication_slots, pg_stat_wal

• pg_stat_replication: 물리 복제 상태/지연
• pg_replication_slots: 논리/물리 슬롯 상태(초과 적체 시 WAL 누적 위험)
• pg_stat_wal: WAL 생성량 등

SELECT pid, application_name, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

SELECT * FROM pg_stat_wal;  -- 초당 WAL 증가량 모니터링

8) 쿼리 성능/탑SQL: pg_stat_statements (확장)

먼저 확장 설치 후 사용: CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

9) 보안 점검 빠른 체크리스트

• SUPERUSER/REPLICATION 보유 계정 최소화 (pg_roles)
• 만료 예정 계정/미사용 계정 파악 (rolvaliduntil, 접속 로그)
• 비밀번호 정책(password_encryption=scram-sha-256) 확인 (pg_settings)
• 스키마/테이블 권한 과다 배포 여부 (has_*_privilege 매트릭스)
• RLS 정책 적용 대상 점검 (pg_policies)
• 논리 슬롯 방치로 인한 WAL 적체 (pg_replication_slots)

10) 운영 팁

• 역할 모델링: 로그인 역할 ↔ 권한(그룹) 역할 분리. 애플리케이션은 그룹 역할에만 GRANT.
• DEFAULT PRIVILEGES로 신규 객체 권한 표준화.
• search_path 고정: 애플리케이션 계정의 search_path를 서비스 스키마로 지정.
• 장기 트랜잭션 금지: idle in transaction 점검 배치 상시화.
• 인덱스 위생: 스캔 0 인덱스 주기 점검, 파티션/Autovacuum 현황 모니터.

PostgreSQL 17 자체에는 AI 기능이 내장되지 않았지만, pgvector 확장을 설치하면 "벡터 타입 + 유사도 검색(k‑NN)"을 바로 쓸 수 있어요. 이 글은 PaaS(PostgreSQL 17) 환경을 기준으로 설치 → 스키마 설계 → 인덱스 튜닝 → RAG/추천 시나리오까지 한 번에 정리합니다.

1. 빠른 개요

• 무엇을? 텍스트/이미지/오디오 임베딩(고차원 벡터)을 DB에 저장하고, 질의 벡터와 가장 가까운 문서를 찾습니다.
• 왜 Postgres? 정형(SQL) + 반정형(JSONB) + 벡터(vector)를 한 DB에서 관리 → 운영 단순화, 트랜잭션/권한/백업 일원화.
• 어떻게? pgvector 확장으로 vector 타입/연산자를 활성화하고, IVFFlat/HNSW 인덱스로 근사 최근접 탐색을 가속합니다.

2. 준비물 & 설치

PaaS마다 확장 지원이 다릅니다. 콘솔/문서에서 pgvector 지원 여부를 확인하세요. (미지원이면 자체 호스팅 고려)

-- 슈퍼유저/확장 설치 권한 필요
CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector

버전/권한 팁

• DB 단위(스키마 아님)로 확장을 설치합니다.
• 동일 인스턴스에 여러 DB가 있다면 각 DB마다 CREATE EXTENSION 실행.

3. 스키마 설계 (하이브리드 모델)

핵심 아이디어: 많이 조회/필터되는 메타 필드는 정규 컬럼, 나머지는 jsonb, 검색은 vector.

CREATE TABLE doc (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text,          -- 원문 전문(선택)
  meta        jsonb,         -- 태그/카테고리 등 유연 필드
  embedding   vector(768),   -- 임베딩 차원수 고정(예: 768)
  created_at  timestamptz DEFAULT now()
);

• vector(768): 모델 차원에 맞춰 고정합니다 (예: OpenAI 1536, bge 768 등).
• 자주 쓰는 필터(예: category, lang)는 생성 컬럼으로 승격해 B‑tree 인덱스를 걸면 플래너가 안정적입니다.

ALTER TABLE doc
  ADD COLUMN category text GENERATED ALWAYS AS (meta->>'category') STORED,
  ADD COLUMN lang     text GENERATED ALWAYS AS (meta->>'lang') STORED;
CREATE INDEX ON doc (category);
CREATE INDEX ON doc (lang);

4. 인덱스 선택: IVFFlat vs HNSW

pgvector는 정확 탐색(Seq/B‑tree 등) 외에 근사 최근접 탐색(ANN) 인덱스를 제공합니다.

4‑1) IVFFlat (인버티드 파일)

• 생성 빠름, 파라미터 간단, 대규모 데이터셋에 적합.
• 파라미터: lists(클러스터 개수), 검색 시 probe(탐색 클러스터 수).

-- L2 거리 기준 IVFFlat 인덱스
CREATE INDEX idx_doc_vec_ivf
  ON doc USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 100);

-- 검색 시(세션/쿼리 단위)
SET ivfflat.probes = 10;  -- 더 정확히 찾고 싶으면 상향(지연 증가)

4‑2) HNSW (그래프 탐색)

• 더 높은 정확도/재현율, 읽기 성능 우수. 인덱스 생성 비용은 IVFFlat보다 큼.
• 파라미터: m(연결도), ef_construction(구축), 검색 시 ef_search.

CREATE INDEX idx_doc_vec_hnsw
  ON doc USING hnsw (embedding vector_l2_ops)
  WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 64;  -- 정확도↑ (응답시간은 증가)

거리함수 선택

• vector_l2_ops (유클리드 거리)
• vector_ip_ops (inner product)
• vector_cosine_ops (코사인). 코사인은 정규화(단위 벡터)를 권장합니다.

5. 임베딩 적재 & 검색

5‑1) 적재 예시

INSERT INTO doc (title, body, meta, embedding)
VALUES (
  'RAG 소개',
  '검색 증강 생성(RAG)은...',
  '{"category":"ai","lang":"ko"}',
  '[0.012, -0.044, ... , 0.078]'::vector
);

임베딩은 애플리케이션 레이어(파이썬/노드 등)에서 모델 호출 후 저장합니다.

5‑2) k‑NN 질의 (Top‑k)

-- 질의 벡터 q가 주어졌을 때, 가장 가까운 5개 문서
SELECT id, title, category, lang, embedding <-> '[...]'::vector AS dist
FROM doc
WHERE category = 'ai'              -- 메타 필터와 결합(하이브리드 검색)
ORDER BY embedding <-> '[...]'::vector
LIMIT 5;

• 연산자 <->는 선택한 거리 함수에 맞는 작을수록 유사한 값.
• 메타 조건과 결합(예: 언어/권한/태그)하면 정확도와 품질이 올라갑니다.

6. RAG(검색 증강 생성) 미니 파이프라인

1. 수집/전처리: PDF/문서 → 청크 분할(예: 500~1,000 토큰) + 메타 작성.
2. 임베딩: 각 청크 → 임베딩 → doc에 저장.
3. 질의 처리: 사용자 질문 → 임베딩 → k‑NN으로 상위 N개 검색.
4. 프롬프트 구성: 상위 문서의 본문/요약을 LLM에 컨텍스트로 주입.

-- 청크 테이블로 세분화
CREATE TABLE doc_chunk (
  id        bigserial PRIMARY KEY,
  doc_id    bigint REFERENCES doc(id) ON DELETE CASCADE,
  chunk_no  int,
  text      text,
  emb       vector(768)
);

CREATE INDEX ON doc_chunk USING hnsw (emb vector_cosine_ops) WITH (m=16, ef_construction=64);

• 하이브리드 검색: tsvector(전문검색) + 벡터 검색을 합쳐 랭킹을 재조정하면 품질이 더 좋아집니다.

-- 예시: 키워드 점수 + 벡터 거리 가중 합산(간단 점수)
WITH kw AS (
  SELECT id, ts_rank_cd(to_tsvector('simple', text), plainto_tsquery('simple', 'RAG')) AS kw_score
  FROM doc_chunk
), nn AS (
  SELECT id, (1.0 - (emb <-> '[...]'::vector)) AS nn_score  -- 코사인 유사도 유사 표현
  FROM doc_chunk
  ORDER BY emb <-> '[...]'::vector
  LIMIT 50
)
SELECT c.id, c.doc_id, (0.6*coalesce(kw.kw_score,0) + 0.4*coalesce(nn.nn_score,0)) AS score
FROM doc_chunk c
LEFT JOIN kw ON kw.id = c.id
LEFT JOIN nn ON nn.id = c.id
ORDER BY score DESC
LIMIT 10;

7. 운영/성능 체크리스트

• 차원/거리 함수를 모델에 맞게 고정했는가? (L2/IP/Cosine)
• 인덱스 타입을 선택했는가? (IVFFlat: 빠른 구축/대용량, HNSW: 고정확도)
• IVFFlat lists/probes, HNSW m/ef_*를 벤치마크로 튜닝했는가?
• 메타 필터(카테고리/언어/권한)를 B‑tree/생성 컬럼으로 최적화했는가?
• 배치 적재 후 ANALYZE/통계 적재, 인덱스 재구성 전략(REINDEX CONCURRENTLY)을 준비했는가?
• 대형 업데이트/삭제 시 파티셔닝과 Autovacuum 상태를 점검하는가?
• PITR/스냅샷/CDC(논리 복제) 등 백업·동기화 전략을 마련했는가?

8. 보안·권한·거버넌스

• 행 수준 보안(RLS)로 테넌트/사용자별 접근 제어.
• 벡터 컬럼도 일반 컬럼과 동일하게 권한을 관리(SELECT 제한 등).
• 임베딩 생성에 사용된 모델/버전/파라미터를 meta에 기록해 재현 가능성 확보.

ALTER TABLE doc ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_doc_tenant ON doc USING ((meta->>'tenant_id') = current_setting('app.tenant', true));

9. 장애/운영 팁

• 인덱스 생성은 오프피크에, CONCURRENTLY 옵션 활용(잠금 완화).
• IVFFlat은 데이터 분포가 바뀌면 REINDEX로 재학습 효과.
• HNSW는 인덱스 크기가 커질 수 있으니 스토리지 여유를 확보.
• 쿼리 플랜 확인: EXPLAIN (ANALYZE, BUFFERS)로 실제 경로/효과 측정.

10. 결론

• PostgreSQL 17 + pgvector는 RAG/의미검색/추천 등 핵심 AI 패턴을 구현하기에 충분한 스택입니다.
• 한 DB에서 정형 + 반정형 + 벡터를 통합 관리할 수 있어 운영 복잡도를 낮출 수 있습니다.
• 인덱스/파라미터 튜닝과 하이브리드 검색(메타/키워드 결합)으로 정확도·성능을 균형 있게 끌어올리세요.

PostgreSQL을 설계할 때 흔히 마주하는 고민이 있습니다. 신규 데이터베이스를 만들 것인가, 기존 DB에 스키마를 추가할 것인가, 혹은 테이블스페이스를 활용할 것인가? 이번 글에서는 각 선택지가 어떤 의미를 가지며, PaaS(PostgreSQL 17 기준) 환경에서는 어떻게 접근해야 할지 정리해보겠습니다.

1. 신규 데이터베이스(DB)를 만들 때

강한 경계(격리)가 필요할 때 유리합니다.

• 백업/복구 라이프사이클 분리: 서비스별로 서로 다른 PITR(시점복구) 정책, 장기 보관 요구가 있을 때.
• 보안 경계: 팀, 벤더, 운영주체가 달라 권한을 완전히 분리해야 할 때.
• 인코딩/Collation 차이: DB 생성 시 지정 가능하므로 서로 다른 로케일이 필요할 때.
• 확장(Extensions) 충돌 방지: 특정 DB만 PostGIS, pgvector 같은 확장을 쓸 때.
• 교차 조인 불필요: PostgreSQL은 DB 간 조인이 불가능(단, FDW/dblink는 예외)하기 때문에 완전 독립 워크로드라면 DB 분리가 깔끔.

단점: DB 단위로 커넥션 풀을 따로 잡아야 하고, 운영 복잡성이 증가.

2. 같은 DB 안에서 스키마를 추가할 때

느슨한 경계가 필요한 경우 적합합니다.

• 교차 조인/외래키/트랜잭션 가능: 스키마는 같은 DB 안 네임스페이스이므로 서로 참조 가능.
• 권한 제어: 스키마 단위 USAGE/CREATE + 오브젝트 권한으로 팀별 격리 가능.
• 운영 단순화: 백업, Autovacuum, 모니터링을 한 DB 단위에서 일괄 관리.
• 커넥션 풀 효율: 여러 스키마를 같은 커넥션에서 다룰 수 있음.

주의: 같은 DB의 자원(WAL, Autovacuum, 통계)을 공유하므로 한 스키마의 대량 작업이 다른 스키마에 영향을 줄 수 있음.

3. 테이블스페이스를 고려할 때

테이블스페이스는 특정 테이블이나 인덱스를 다른 물리적 스토리지에 저장하기 위한 논리 단위입니다.

• Hot/Cold 데이터 분리: 최근 데이터는 고성능 스토리지, 과거 파티션은 저렴한 스토리지.
• 특정 인덱스 분리: 랜덤 I/O가 많은 인덱스를 IOPS 높은 볼륨에 배치.
• 임시 작업: TEMP 테이블스페이스를 분리해 쿼리 스필 I/O 격리.

⚠️ PaaS(PostgreSQL on Cloud SQL, Azure Database 등)에서는 사용자 정의 테이블스페이스 생성이 제한되는 경우가 많습니다. 이 경우 스토리지 티어 선택, 파티셔닝, 인덱스 전략으로 대체해야 합니다.

4. 의사결정 체크리스트

• 조인/트랜잭션 필요 여부 → 필요하면 스키마, 불필요하면 DB.
• 백업/복구 단위 → 서로 다른 정책 필요하면 DB 분리.
• 보안 경계 → 강한 격리 필요 시 DB, 팀 내부 구분은 스키마.
• 확장/Collation 차이 → DB 단위 분리 필요 여부 확인.
• PaaS 제약 → 테이블스페이스 지원 여부 확인, 대안은 파티셔닝/스토리지 티어.

5. 설계 예시

• 공통 서비스 + 분석: 하나의 DB에서 core, ops, bi 스키마로 분리.
• 외부 벤더 운영 모듈: 별도 DB 생성, 네트워크 레벨 접근 제어.
• 로그/이벤트: 최근 파티션은 빠른 스토리지, 과거 파티션은 저렴 스토리지. (PaaS에서 테이블스페이스 제한 시 파티셔닝으로 대체)

-- (1) 신규 DB 생성
CREATE DATABASE appdb TEMPLATE template1
  LC_COLLATE 'en_US.utf8' LC_CTYPE 'en_US.utf8';

-- (2) 스키마 분리
CREATE SCHEMA core AUTHORIZATION app_owner;
CREATE SCHEMA ops  AUTHORIZATION ops_owner;

GRANT USAGE ON SCHEMA core TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA core TO app_role;

-- (3) (옵션) 테이블스페이스 예시 (온프렘/자유로운 환경일 때)
-- CREATE TABLESPACE fast_ts LOCATION '/mnt/fast';
-- CREATE TABLESPACE cold_ts LOCATION '/mnt/cold';

-- (4) 파티션 예시
CREATE TABLE logs (
  id bigserial, ts timestamptz NOT NULL, data jsonb, PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

결론

• DB 분리: 강한 경계(보안, 백업, 확장 차이).
• 스키마 분리: 느슨한 경계(조인 필요, 운영 단순화).
• 테이블스페이스: 스토리지 I/O 최적화(단, PaaS 제약 고려).

PostgreSQL 17 기반 PaaS 환경에서는 스키마 중심의 설계가 일반적이고, DB/테이블스페이스 분리는 보안·복구·성능 요구사항에 따라 선택적으로 활용하는 것이 좋습니다.

반정형 데이터를 많이 다루는 요즘, PostgreSQL의 [jsonb]는 강력한 무기입니다.

단순히 JSON을 저장하는 수준을 넘어, 인덱싱과 연산자를 활용하면 “문서 DB처럼” 빠른 조회가 가능합니다.

이 글에서는 jsonb의 핵심 개념, 쿼리/연산자, 인덱싱 전략(GIN/GiST/표현식/부분 인덱스), 운영 주의점, 그리고 MySQL과의 접근 차이를 실무 관점에서 정리합니다.

1. jsonb 기본 개념

• 저장 포맷: json은 텍스트, jsonb는 바이너리(정규화된) 포맷. jsonb가 일반적으로 검색/인덱싱에 유리.
• 연산자:
  • ->(JSON), ->>(텍스트), #>(경로 JSON), #>>(경로 텍스트)
  • 존재/포함: ?(키/요소 존재), ?|/?&(여러 키/요소 중 하나/모두), @>(우변 JSON이 좌변에 포함되는가)
  • JSONPath: @?(경로 매칭), @@(경로 필터)

팁: 성능 관점에서 @>(containment)와 키 존재 연산자들은 GIN 인덱스를 가장 잘 활용합니다.

2. 기본 테이블 & 샘플 데이터

3. 쿼리 패턴 — 무엇을 어떻게 찾는가

4. 인덱싱 전략 — 업무별로 고르는 법

4-1. GIN 인덱스 (기본)

@>(containment), 키/요소 존재 ?, ?|, ?&를 빠르게 만듭니다.

• 기본 GIN: 다양한 연산자를 지원(범용)
• jsonb_path_ops: 인덱스 크기가 작고 빠를 수 있으나, 주 사용처는 @> 중심. 키 존재/JSONPath 등은 제한될 수 있습니다.

4-2. 표현식(Functional) + B-tree 인덱스

값 비교(=, <, >), 정렬/그룹핑에 유리합니다.

4-3. 생성(Stored Generated) 컬럼 + 전통 인덱스

자주 필터링하는 키를 정규 컬럼으로 끌어내 저장하고 B-tree로 인덱싱.

• 장점: 통계 수집/카디널리티 추정이 좋아져 플래너의 선택이 안정적.
• 단점: 스키마가 늘어나고 업데이트 비용이 증가.

4-4. 부분(Partial) 인덱스

쿼리의 전형적 조건을 인덱스에 내장해 크기↓, 효율↑.

4-5. GiST 인덱스 (특수 케이스)

GiST는 범위/공간/유사도 등 특화 인덱스. JSONB에도 적용할 수 있으나, 일반적인 @>/존재 쿼리는 GIN이 보통 더 적합합니다.

5. 운영/성능 팁

1. 쿼리/인덱스 정합: 사용 연산자에 맞는 인덱스를 선택합니다. @>/? → GIN, 값 비교/정렬 → B-tree(표현식/생성 컬럼).
2. -> vs ->>:
   • ->는 JSON, ->>는 텍스트.
   • 숫자 비교는 캐스팅을 명시하세요: ((data->>'amount')::numeric).
3. GIN 튜닝: fastupdate(기본 ON) 사용, 대량 쓰기 후 gin_pending_list_limit 모니터링. 필요 시 VACUUM/REINDEX CONCURRENTLY.
4. 대형 문서 주의: 매우 큰 jsonb는 TOAST로 분리 저장. 업데이트 빈도가 높으면 페이지 분할/쓰기 증폭 발생 → 핫 키는 정규 컬럼화 검토.
5. 통계/플래너: 표현식/생성 컬럼에 대해 ANALYZE가 통계를 수집하도록 하고, 빈번한 키는 default_statistics_target 상향을 고려.
6. 설계 패턴:
   • 하이브리드 스키마: 자주 필터링하는 키는 정규 컬럼 + 나머지는 jsonb.
   • 검증: CHECK 제약으로 최소한의 스키마 유효성(타입/필수 키) 검증.

6. MySQL과의 비교 — 왜 접근이 다른가

• MySQL: JSON 자체에 대한 범용 인덱스(예: GIN)는 없고, 보통 생성 컬럼(Generated Column) + 표현식 인덱스로 특정 경로를 인덱싱합니다.
  • 예) user_id 인덱싱: ALTER TABLE ... ADD COLUMN user_id GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.user_id'))) STORED, ADD INDEX (user_id);
  • 배열 포함/중첩 구조는 인덱싱 경로를 명확히 지정해야 하며, 범용성은 PostgreSQL의 GIN보다 좁습니다.
• PostgreSQL: jsonb 전체에 대한 GIN 인덱스 1개로 다양한 연산자(@>, ?, ?|, ?&)를 가속할 수 있어, 변화가 많은 스키마나 다양한 검색 패턴에서 유연성이 높습니다.

7. 실무 레시피 — 자주 쓰는 조합

• 로그/이벤트 스트림: jsonb + 기본 GIN + 주문 전용 Partial GIN + user_id(생성 컬럼) B-tree.
• 카탈로그/메타데이터: 빈번한 속성은 정규 컬럼, 나머지는 jsonb. 다국어/옵션 필드는 jsonb.
• 검색형 서비스: 태그/속성 필터는 @>/?로, 정렬/페이지네이션은 생성 컬럼 B-tree.

8. 체크리스트 (실무 적용 전 확인할 것)

• 쿼리에서 자주 쓰는 연산자(@>, ?, ->>, JSONPath 등)를 파악했는가?
• 해당 연산자에 최적화된 인덱스를 설계했는가? (GIN/표현식/B-tree/Partial)
• 자주 필터링하는 키는 생성 컬럼으로 승격했는가?
• GIN 인덱스의 pending list와 Autovacuum 상태를 주기적으로 점검하고 있는가?
• EXPLAIN (ANALYZE, BUFFERS)로 인덱스 효과를 실제로 검증했는가?

결론

• jsonb는 PostgreSQL을 문서 지향 저장소처럼 활용하게 해주는 핵심 기능입니다.
• GIN(또는 path_ops) + 표현식/B-tree + 부분 인덱스를 상황에 맞게 조합하면, 정형/반정형 데이터를 한 DB에서 안정적으로 처리할 수 있습니다.
• MySQL도 JSON은 잘 다루지만, 인덱싱 전략은 경로 지정(생성 컬럼/표현식) 중심으로 설계해야 하며, 범용성/유연성 측면에선 PostgreSQL의 jsonb + GIN 조합이 유리한 경우가 많습니다.

PostgreSQL을 다루다 보면 반드시 마주치는 키워드가 바로 VACUUM입니다. 많은 RDBMS 사용자, 특히 MySQL에 익숙한 개발자라면 처음 접했을 때 낯설 수 있습니다. 이번 글에서는 VACUUM의 개념과 동작 방식, PostgreSQL 17에서의 개선점, 그리고 MySQL과의 차이를 정리해보겠습니다.

1. VACUUM이란 무엇인가?

PostgreSQL은 MVCC 구조로 인해 테이블 안에 [죽은 튜플(dead tuple)]이 쌓입니다. DELETE나 UPDATE가 발생하면 행을 바로 제거하지 않고, 새로운 버전을 만들어 기존 버전은 더 이상 보이지 않게 처리할 뿐입니다. 하지만 물리적으로는 남아있기 때문에, 이를 정리해주는 과정이 필요합니다.

➡️ 이 과정을 수행하는 명령이 바로 VACUUM입니다.

2. VACUUM의 주요 역할

1. 죽은 튜플 공간 회수: 더 이상 참조되지 않는 데이터를 치워 디스크 공간 확보.
2. 통계 업데이트: 옵티마이저가 최신 실행 계획을 세울 수 있도록 통계 정보를 갱신.
3. 트랜잭션 ID Wraparound 방지: PostgreSQL은 트랜잭션 ID가 40억 정도에서 다시 0으로 돌아오는데, VACUUM이 이를 예방해 데이터 일관성을 유지.

3. Autovacuum

• VACUUM을 수동으로 계속 실행하기는 어렵기 때문에, PostgreSQL은 Autovacuum 데몬을 기본으로 제공합니다.
• 특정 테이블에 죽은 튜플이 일정 비율 이상 쌓이면 자동으로 VACUUM을 실행합니다.
• 주요 파라미터:
  • autovacuum_vacuum_scale_factor: 테이블 크기에 비례한 트리거 비율.
  • autovacuum_analyze_scale_factor: ANALYZE 자동 실행 비율.
  • autovacuum_naptime: 주기적 확인 주기.

🔧 운영에서는 대용량 테이블마다 Autovacuum 튜닝이 필수입니다.

4. PostgreSQL 17에서의 개선점

• VACUUM 메모리 관리 최적화: 대규모 테이블 VACUUM 시 메모리 사용량이 줄어 안정성이 향상되었습니다.
• 대량 적재/삭제 환경에서 Autovacuum 동작 효율이 올라가 관리 부담이 줄었습니다.

➡️ 대규모 PaaS 환경에서는 특히 체감 차이가 큽니다.

5. MySQL에서는 왜 VACUUM이 없는가?

• MySQL(InnoDB)은 삭제/갱신 시 Undo Log에만 기록하고, 테이블에는 최신 버전만 유지합니다.
• 불필요해진 Undo Log는 Purge Thread가 비동기적으로 정리합니다.
• 공간 단편화가 심한 경우에는 OPTIMIZE TABLE 명령으로 테이블을 재구성합니다.

➡️ 즉, VACUUM은 PostgreSQL 특유의 개념이며, MySQL에서는 Purge Thread + OPTIMIZE TABLE로 대체된다고 보면 됩니다.

6. 실무에서의 주의사항

• 긴 트랜잭션: 오랫동안 열린 트랜잭션은 Autovacuum이 죽은 튜플을 정리하지 못하게 막습니다. → 디스크 폭증 위험.
• Autovacuum 튜닝: 테이블마다 업무 패턴에 맞는 스케일 팩터 조정 필요.
• 수동 VACUUM: 배치 작업 직후, 대량 삭제 후에는 VACUUM FULL로 디스크 공간 회수.

7. 정리

• VACUUM은 PostgreSQL 운영에서 가장 중요한 관리 개념 중 하나.
• MySQL에는 직접 대응되는 개념이 없고, Undo Log/Purge Thread로 처리.
• PostgreSQL 17에서는 VACUUM 성능이 개선되어 대규모 운영 환경에서 안정성이 강화.

다음 편 예고

3편에서는 PostgreSQL의 jsonb와 인덱싱 기법을 살펴보겠습니다. 이 기능은 반정형 데이터를 다루는 현대 애플리케이션에서 PostgreSQL이 MySQL보다 선호되는 가장 큰 이유 중 하나입니다.

PostgreSQL 17을 다루면서 가장 먼저 눈에 띄는 개념 중 하나는 바로 MVCC (Multi-Version Concurrency Control) 입니다. 사실 MVCC 자체는 PostgreSQL 전용 개념은 아니고, MySQL(InnoDB)도 활용합니다. 하지만 구현 방식과 운영상의 의미가 다르기 때문에 현업에서 체감 차이가 큽니다.

1. MVCC란 무엇인가?

동시에 여러 트랜잭션이 데이터에 접근할 때, 충돌 없이 읽기/쓰기를 보장하는 기법입니다. 핵심 아이디어는 데이터의 여러 버전을 관리하여 각 트랜잭션이 "자신만의 스냅샷"을 본다는 것입니다.

• 동시 읽기 보장: SELECT 쿼리는 쓰기 작업에 막히지 않고 이전 버전을 참조합니다.
• 쓰기 충돌 방지: UPDATE/DELETE는 새로운 버전을 만들어내고, 다른 트랜잭션은 이 버전을 필요할 때만 확인합니다.

2. PostgreSQL의 MVCC 구현

• 각 행(row)은 내부적으로 xmin, xmax 같은 숨은 컬럼을 가지고 있습니다.
• 트랜잭션이 실행되면, 현재 스냅샷에 맞는 행 버전만 보이도록 필터링합니다.
• 오래된 버전(더 이상 참조되지 않는 튜플)은 테이블 안에 그대로 남고, 이후 VACUUM 과정에서 정리됩니다.

➡️ 즉, PostgreSQL은 "행 버전이 테이블 안에 계속 쌓인다 → Autovacuum이 치워준다"라는 구조를 가집니다.

3. MySQL(InnoDB)의 MVCC 구현

• InnoDB는 Undo Log에 이전 버전을 기록합니다.
• SELECT 쿼리가 과거 데이터를 필요로 할 때 Undo Log를 거슬러 올라갑니다.
• 트랜잭션이 종료되면, Undo Log를 Purge Thread가 정리합니다.

➡️ PostgreSQL처럼 테이블 안에 버전이 쌓이는 구조가 아니므로, 별도의 VACUUM은 필요 없습니다.

4. 트랜잭션 격리 수준

PostgreSQL과 MySQL 모두 표준 SQL의 4단계 격리 수준을 지원합니다.

• PostgreSQL: READ COMMITTED(기본), REPEATABLE READ, SERIALIZABLE
• MySQL InnoDB: REPEATABLE READ가 기본, READ COMMITTED도 옵션 제공

🔎 차이점: PostgreSQL의 REPEATABLE READ는 사실상 Snapshot Isolation 수준으로 동작하여, MySQL보다 팬텀 리드 방지 측면에서 강력합니다.

5. 실무에서의 체감 차이

• PostgreSQL:
  • 긴 트랜잭션이 존재하면 VACUUM이 오래된 버전을 정리하지 못해 디스크가 불어남.
  • 따라서 트랜잭션을 짧게 유지하고, Autovacuum 튜닝이 중요합니다.
• MySQL:
  • Undo Log가 길어지고 Purge Thread가 밀리면 성능 저하.
  • 일반적으로는 DBA가 Undo Tablespace를 관리하거나, 장기 트랜잭션을 피하는 전략을 씁니다.

6. 정리

• MVCC 자체는 공통 개념이지만,
  • PostgreSQL은 테이블 내부에 버전을 쌓는 구조 → VACUUM 필요.
  • MySQL(InnoDB)은 Undo Log 기반 → Purge Thread 관리.
• 운영상의 차이가 크기 때문에, DBA 입장에서는 PostgreSQL과 MySQL을 똑같이 취급할 수 없습니다.

다음 편 예고

다음 글에서는 PostgreSQL만의 독특한 개념인 VACUUM을 깊이 다뤄보겠습니다. VACUUM은 PostgreSQL 운영에서 가장 자주 듣는 단어이자, 성능 안정성에 직접적인 영향을 주는 핵심입니다.

PostgreSQL 17을 PaaS 환경에서 직접 구성하다보니, 기존에 많이 사용하던 MySQL과는 확실히 다른 특징들이 눈에 들어왔습니다. 여기서는 두 DBMS를 비교하며 PostgreSQL 17의 장점을 정리해보겠습니다.

1. 표준 준수 & JSON 기능

• SQL 표준 지원: PostgreSQL은 SQL 표준을 폭넓게 지원합니다. 윈도 함수, CTE, 부분/표현식 인덱스, 범위 타입까지 기본 제공.
• JSON 처리: PostgreSQL의 jsonb + GIN 인덱스는 매우 강력합니다. PostgreSQL 17에서는 JSON_TABLE() 같은 SQL 표준 JSON 기능이 추가되어 대량 JSON ETL이 한층 수월해졌습니다.
• MySQL과 차이점: MySQL 8.0도 JSON 타입을 지원하지만, 인덱싱/함수 옵션의 다양성에서는 PostgreSQL이 우위입니다.

2. 인덱싱과 자료형

• 다양한 인덱스: GIN, GiST, BRIN, 부분 인덱스, 표현식 인덱스 지원.
• 자료형: 배열, 범위, 네트워크 타입 등 실무에서 쓸 수 있는 특수 타입이 풍부.
• 비교: MySQL은 B-tree 중심이며, Fulltext/Spatial은 옵션이 제한적.

3. 동시성 제어 (MVCC) & VACUUM

• MVCC 구현 차이: PostgreSQL은 버전 관리를 힙에 쌓는 구조라 VACUUM이 필수. PostgreSQL 17은 VACUUM 메모리 관리가 개선되어 대규모 테이블에서 안정성이 향상.
• 대량 적재/내보내기: 17 버전에서 성능 최적화가 이뤄져 초기 데이터 마이그레이션이 더 빨라짐.

4. 복제 & HA (고가용성)

• 복제 방식:
  • PostgreSQL: 물리(WAL) + 논리(pub/sub) 복제 모두 가능.
  • MySQL: 주로 binlog 기반 논리 복제.
• PostgreSQL 17 특징:
  • pg_createsubscriber: 물리 스탠바이를 논리 구독자로 빠르게 전환.
  • 논리 복제의 장애 조치(failover) 제어 강화 → HA 시나리오에서 신뢰도 ↑.

5. 확장성과 에코시스템

• 확장(Extension): pg_stat_statements, postgis, pgvector 등 강력한 기능을 쉽게 확장 가능.
• FDW(Foreign Data Wrapper): 다른 DB나 외부 데이터를 외부 테이블로 붙여 조인 가능 → 데이터 허브/점진적 이관에 유용.

6. 파티셔닝 & DDL

• 파티셔닝: 선언적 파티셔닝이 성숙. 대용량 테이블 관리 효율적.
• DDL 트랜잭션 지원: 대부분의 DDL이 트랜잭션에 묶여 실수 시 롤백 가능.

7. 운영/모니터링 (특히 PaaS)

• Autovacuum 개선: PostgreSQL 17에서 VACUUM 효율성이 올라가 관리 부담 완화.
• 모니터링: pg_stat_statements 확장을 통해 상시 쿼리 성능 분석 가능.
• CDC/ETL: 클라우드 환경에서 논리 복제/디코딩 지원으로 Debezium 같은 CDC 대체가 쉬워짐.

결론

• PostgreSQL 17은 표준 준수, JSON 기능, 고급 인덱싱, 확장성, 논리 복제 측면에서 MySQL보다 한발 앞서 있습니다.
• MySQL은 단순 읽기 스케일아웃, 운영 편의성, 기존 인프라와의 친화성에서는 여전히 강점이 있습니다.