LeeYudok db-expert
관계형 설계 일반 + PostgreSQL 운영이 대상이다. SQLite 파일을 직접 다루는 문제는
sqlite-expert, 애플리케이션 코드는 각 언어 스킬이 맡는다.
1. 스키마 설계 — 판단 기준
정규화는 목적이 아니라 이상현상(anomaly)을 없애는 수단이다. 3NF 를 기본으로 두고, 역정규화는 측정된 병목이 있을 때만, 그리고 갱신 경로를 하나로 유지할 수 있을 때만.
읽기 전에 스스로 답한다:
- 이 테이블의 한 행은 무엇 하나인가 — 한 문장으로 안 되면 쪼갤 신호다.
- 자연키인가 대리키인가 — 사업자번호·사번처럼 외부가 소유한 값은 바뀐다. 대리키(식별자)를 두고 자연키에는 유니크 제약을 건다.
- 이 컬럼이 NULL 일 수 있는 실제 상황은 무엇인가 — 답이 없으면
NOT NULL. NULL 은 "모름"이지 "없음"이나 "0"이 아니다. - 삭제하면 무엇이 같이 사라져야 하는가 — FK 의
ON DELETE를 의도적으로 정한다. 기본값에 맡기지 않는다.
제약은 애플리케이션이 아니라 DB 에 건다
NOT NULL·UNIQUE·CHECK·FOREIGN KEY 는 마지막 방어선이다. 애플리케이션 검증은
사용자 경험용이고, 데이터 무결성은 DB 가 보장한다. 버그·수동 작업·다른 클라이언트는
애플리케이션을 우회한다.
시간과 통화
- 타임스탬프는
timestamptz.timestamp(무TZ)는 서버·클라이언트 타임존이 갈리는 순간 깨진다. - 저장은 UTC, 표시에서 변환. 사용자 표기는
YYYY-MM-DD HH:MM:SS.mmm(KST 가정). - 돈은
numeric. 부동소수점 금지.
소프트 삭제
deleted_at 을 도입하면 모든 조회에 조건이 붙는다. 빠뜨린 한 곳이 사고가 된다.
정말 필요하면 뷰나 RLS 로 강제하고, 아니면 이력 테이블로 옮기는 편이 낫다.
2. 인덱스
- WHERE·JOIN·ORDER BY 에 쓰이는 컬럼이 후보다. 전부 만들지 않는다 — 인덱스는 쓰기 비용과 저장공간을 먹는다.
- 복합 인덱스는 앞 컬럼부터 쓰인다. 카디널리티가 높은 것 또는 등호 조건이 앞이다.
- 부분 인덱스로 크기를 줄인다:
WHERE status = 'pending'처럼 대부분이 제외되는 경우. - FK 컬럼에 인덱스가 없으면 부모 삭제가 풀스캔이 된다. PostgreSQL 은 자동 생성하지 않는다.
- 확인은 추측이 아니라 실행계획으로.
EXPLAIN (ANALYZE, BUFFERS) <쿼리>.Seq Scan이 큰 테이블에 보이면 원인을 찾는다.
인덱스를 추가하기 전에 쿼리를 고칠 수 있는지 먼저 본다. 함수를 씌운 컬럼
(WHERE lower(name) = ...)은 인덱스를 못 타므로, 표현식 인덱스를 만들거나 쿼리를 바꾼다.
3. 쿼리
SELECT *를 애플리케이션 쿼리에 쓰지 않는다. 컬럼이 늘면 전송량이 늘고, 의도치 않은 필드가 새어나간다.- N+1 을 의심한다. 목록을 돌면서 건마다 조회하는 코드는 조인이나
IN한 번으로 바꾼다. - 페이징은 큰 오프셋에서 느려진다. 정렬 키 기준 커서(
WHERE seq > ?)를 쓴다. - 문자열 조립 금지. 값은 언제나 플레이스홀더. 식별자를 동적으로 넣어야 하면 화이트리스트로 검증하고 인용한다.
4. 트랜잭션
- 경계를 명시적으로 정한다. "이 작업들이 전부 되거나 전부 안 돼야 한다"가 기준이다.
- 트랜잭션 안에서 외부 호출(HTTP·메일)을 하지 않는다. 락을 잡은 채 네트워크를 기다린다.
- 격리수준은 기본(Read Committed)으로 두고, 필요한 경우에만 올린다. 올릴 때는 직렬화 실패 시 재시도가 짝이다.
- 락 순서를 일정하게 유지해 교착을 피한다.
- 긴 트랜잭션은 VACUUM 을 막아 테이블을 부풀린다. 배치는 잘라서 커밋한다.
5. 마이그레이션
- 되돌릴 수 있게 쓴다. 되돌릴 수 없으면(데이터 삭제) PR 본문에 명시한다.
- 운영 중 스키마 변경은 잠금 시간이 관건이다. PostgreSQL 에서
컬럼 추가(기본값 없는 NULL 허용)는 즉시지만, 타입 변경·
NOT NULL추가는 테이블을 다시 쓴다. 큰 테이블이면 단계를 나눈다: 컬럼 추가 → 백필(배치) → 제약 추가 → 구 컬럼 제거. - 인덱스는
CREATE INDEX CONCURRENTLY로 만든다. 일반 생성은 쓰기를 막는다. - 적용 전 백업 또는 되돌릴 계획을 확인한다.
6. doksam PostgreSQL 운영
pig 의 단일 클러스터를 여러 서비스가 공유한다 — gitlab·doksamlabs·srope·openwebui·sonarqube 등. 내 서비스 하나가 클러스터 전체를 마비시킬 수 있다는 전제로 다룬다.
- 접속은
yd_pgMCP(mcp__yd_pg__*). 새로 등록할 때도 이름은yd_pg로 통일한다. max_connections=200을 여럿이 나눠 쓴다. 커넥션 풀 상한을 정하지 않은 서비스는 다른 서비스의 접속을 굶긴다. 애플리케이션마다 상한을 명시한다.- 컨테이너에서는
host.docker.internal(host-gateway)로 접근한다. 호스트에서 공개 도메인으로 붙으면 NAT hairpin 으로 로컬 PG 에 떨어지므로 내부 IP 를 쓴다. - 계정·비밀번호는
gimje/infra레포pig/PG.md. 값을 채팅·로그·이슈에 노출하지 않는다.
쓰기 작업 규율
- 조회는 자유롭게. INSERT/UPDATE/DELETE·DDL 은 사용자의 명시 실행 신호 후에만 한다.
- 대량 변경 전에 영향 행 수를 먼저 센다.
SELECT count(*)로 확인하고 보고한 뒤 실행한다. UPDATE/DELETE에WHERE가 없으면 실행하지 않는다. 예외 없다.- 운영 데이터 이동·삭제는 범위가 확정되지 않으면 시작하지 않는다.
진단 시작점
-- 지금 무엇이 돌고 있는가 (오래된 것부터)
SELECT pid, now() - query_start AS dur, state, left(query, 80)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY dur DESC LIMIT 20;
-- 커넥션을 누가 쓰고 있는가
SELECT datname, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;
-- 테이블 부풀림·죽은 튜플
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
idle in transaction 이 오래 떠 있으면 애플리케이션이 커밋을 안 하고 있는 것이다 —
락과 VACUUM 을 동시에 막으므로 우선 처리한다.
7. 완료 조건
- 새 테이블·컬럼에 적절한 제약(
NOT NULL·FK·UNIQUE)이 있고, NULL 허용은 근거가 있음 - 조회 조건에 인덱스가 있고, 느린 쿼리는
EXPLAIN (ANALYZE)로 확인함 - 마이그레이션이 되돌릴 수 있거나, 불가능함을 명시함
- 운영 클러스터를 만졌으면: 영향 범위를 먼저 세어 보고했고, 커넥션 상한을 확인함
- 시크릿이 출력·로그·이슈에 노출되지 않음