---
name: db-expert
description: 관계형 스키마를 설계·검토하거나 인덱스·쿼리 튜닝·트랜잭션·마이그레이션을 다룰 때, 그리고 doksam pig 의 공유 PostgreSQL 클러스터를 운영할 때 사용한다. SQLite 고유 주제는 sqlite-expert 를 쓴다.
---

# db-expert

관계형 설계 일반 + PostgreSQL 운영이 대상이다. SQLite 파일을 직접 다루는 문제는
`sqlite-expert`, 애플리케이션 코드는 각 언어 스킬이 맡는다.

## 1. 스키마 설계 — 판단 기준

정규화는 목적이 아니라 **이상현상(anomaly)을 없애는 수단**이다. 3NF 를 기본으로 두고,
역정규화는 **측정된 병목**이 있을 때만, 그리고 **갱신 경로를 하나로 유지**할 수 있을 때만.

읽기 전에 스스로 답한다:

1. **이 테이블의 한 행은 무엇 하나인가** — 한 문장으로 안 되면 쪼갤 신호다.
2. **자연키인가 대리키인가** — 사업자번호·사번처럼 외부가 소유한 값은 바뀐다.
   대리키(식별자)를 두고 자연키에는 유니크 제약을 건다.
3. **이 컬럼이 NULL 일 수 있는 실제 상황은 무엇인가** — 답이 없으면 `NOT NULL`.
   NULL 은 "모름"이지 "없음"이나 "0"이 아니다.
4. **삭제하면 무엇이 같이 사라져야 하는가** — 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·sonarqube 등.
**내 서비스 하나가 클러스터 전체를 마비시킬 수 있다는 전제**로 다룬다.

- 접속은 `yd_pg` MCP(`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` 가 없으면 실행하지 않는다. 예외 없다.
- 운영 데이터 이동·삭제는 범위가 확정되지 않으면 시작하지 않는다.

### 진단 시작점

```sql
-- 지금 무엇이 돌고 있는가 (오래된 것부터)
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)` 로 확인함
- 마이그레이션이 되돌릴 수 있거나, 불가능함을 명시함
- 운영 클러스터를 만졌으면: 영향 범위를 먼저 세어 보고했고, 커넥션 상한을 확인함
- 시크릿이 출력·로그·이슈에 노출되지 않음