yd-db-expert · diff
git:20261004.0d5ff50 to git:20261004.c7aa561
72 added, 66 removed. Audit A to A.
---
name: yd-db-expert
- description: 관계형 스키마를 설계·검토하거나 인덱스·쿼리 튜닝·트랜잭션·마이그레이션을 다룰 때, 그리고 doksam pig 의 공유 PostgreSQL 클러스터를 운영할 때 사용한다. SQLite 고유 주제는 yd-sqlite-expert 를 쓴다.
+ description: 엔진과 무관한 관계형 설계를 다룰 때 사용한다 — 스키마 정규화·키·NULL 의미·DB 제약·소프트 삭제·인덱스 선택 원칙·쿼리 규칙(N+1·플레이스홀더·커서 페이징)·트랜잭션 경계·마이그레이션 단계. PostgreSQL 고유 주제는 yd-pg-expert, Oracle 고유 주제는 yd-oracle-expert, SQLite 고유 주제는 yd-sqlite-expert 를 쓴다.
---
# yd-db-expert
- 관계형 설계 일반 + PostgreSQL 운영이 대상이다. SQLite 파일을 직접 다루는 문제는
- `yd-sqlite-expert`, 애플리케이션 코드는 각 언어 스킬이 맡는다.
+ 엔진과 무관한 관계형 설계가 대상이다. 잠금·테이블 재작성·실행계획 읽는 법·타입 세부처럼
+ 엔진마다 다른 동작은 엔진 스킬이 맡는다 — PostgreSQL 은 `yd-pg-expert`, Oracle 은
+ `yd-oracle-expert`, SQLite 파일은 `yd-sqlite-expert`. 애플리케이션 코드는 각 언어 스킬이 맡는다.
+ 아래 단언 가운데 SQL 의미론에 관한 것은 `scripts/verify_db_claims.py` 가 SQLite 로
+ 재현한다(8장). 같은 의미론은 표준 SQL 이라 PostgreSQL·Oracle 에서도 같다. 엔진마다 갈리는
+ 지점은 본문에 적었다.
+
## 1. 스키마 설계 — 판단 기준
정규화는 목적이 아니라 **이상현상(anomaly)을 없애는 수단**이다. 3NF 를 기본으로 두고,
역정규화는 **측정된 병목**이 있을 때만, 그리고 **갱신 경로를 하나로 유지**할 수 있을 때만.
- 읽기 전에 스스로 답한다:
+ 설계하기 전에 스스로 답한다:
1. **이 테이블의 한 행은 무엇 하나인가** — 한 문장으로 안 되면 쪼갤 신호다.
2. **자연키인가 대리키인가** — 사업자번호·사번처럼 외부가 소유한 값은 바뀐다.
대리키(식별자)를 두고 자연키에는 유니크 제약을 건다.
3. **이 컬럼이 NULL 일 수 있는 실제 상황은 무엇인가** — 답이 없으면 `NOT NULL`.
NULL 은 "모름"이지 "없음"이나 "0"이 아니다.
4. **삭제하면 무엇이 같이 사라져야 하는가** — FK 의 `ON DELETE` 를 의도적으로 정한다.
기본값에 맡기지 않는다.
### 제약은 애플리케이션이 아니라 DB 에 건다
`NOT NULL`·`UNIQUE`·`CHECK`·`FOREIGN KEY` 는 마지막 방어선이다. 애플리케이션 검증은
- 사용자 경험용이고, 데이터 무결성은 DB 가 보장한다. **버그·수동 작업·다른 클라이언트**는
- 애플리케이션을 우회한다.
+ 사용자 경험용이고, 데이터 무결성은 DB 가 보장한다. **버그·수동 작업·다른 클라이언트는
+ 애플리케이션을 우회한다.** 제약이 없는 테이블에 수동 SQL 세 줄을 넣으면 NULL·중복·음수 잔액이
+ 그대로 들어가고, 제약이 있는 테이블은 셋 다 거부한다.
+ ### NULL 의 의미론
+
+ - **`NULL = NULL` 은 참이 아니라 NULL 이다.** `WHERE col = NULL` 은 아무 행도 고르지 못한다.
+ `IS NULL` 을 쓴다. `col <> 'x'` 도 NULL 인 행을 고르지 않는다.
+ - **`NOT IN` 서브쿼리에 NULL 이 하나라도 있으면 결과가 통째로 빈다.** 오류도 경고도 없다.
+ 서브쿼리 컬럼이 NULL 을 허용하면 `NOT EXISTS` 를 쓴다.
+ - **`UNIQUE` 는 NULL 끼리 같다고 보지 않는다.** NULL 행이 여러 개여도 통과한다. "값이 하나뿐"
+ 이 규칙이면 `NOT NULL` 을 함께 건다. NULL 도 하나만 허용하는 방법은 엔진마다 다르다.
+
### 시간과 통화
- - 타임스탬프는 `timestamptz`. `timestamp`(무TZ)는 서버·클라이언트 타임존이 갈리는 순간 깨진다.
- - 저장은 UTC, 표시에서 변환. 사용자 표기는 `YYYY-MM-DD HH:MM:SS.mmm` (KST 가정).
- - 돈은 `numeric`. 부동소수점 금지.
+ - 시각은 타임존이 붙은 형식으로 저장한다. 저장은 UTC, 표시에서 변환한다. 엔진별 타입 선택은
+ 엔진 스킬이 맡는다. 사용자 표기는 `YYYY-MM-DD HH:MM:SS.mmm` (KST 가정).
+ - 돈은 고정소수점(`numeric`/`decimal`)이나 최소 단위 정수(원·센트)로 저장한다. 부동소수점 금지.
### 소프트 삭제
`deleted_at` 을 도입하면 **모든 조회에 조건이 붙는다.** 빠뜨린 한 곳이 사고가 된다.
- 정말 필요하면 뷰나 RLS 로 강제하고, 아니면 이력 테이블로 옮기는 편이 낫다.
+ 정말 필요하면 뷰나 행 수준 보안으로 강제하고, 아니면 이력 테이블로 옮기는 편이 낫다.
+ **일반 `UNIQUE` 는 탈퇴한 이메일의 재가입을 막는다.** 삭제 표시만 한 행이 값을 쥐고 있어서다.
+ 살아 있는 행에만 거는 부분 유니크 인덱스(`WHERE deleted_at IS NULL`)는 재가입을 허용하고
+ 활성 중복은 막는다. 부분 인덱스가 없는 엔진은 엔진 스킬에서 대안을 찾는다.
+
## 2. 인덱스
- **WHERE·JOIN·ORDER BY 에 쓰이는 컬럼**이 후보다. 전부 만들지 않는다 — 인덱스는
쓰기 비용과 저장공간을 먹는다.
- - 복합 인덱스는 **앞 컬럼부터** 쓰인다. 카디널리티가 높은 것 또는 등호 조건이 앞이다.
- - 부분 인덱스로 크기를 줄인다: `WHERE status = 'pending'` 처럼 대부분이 제외되는 경우.
- - FK 컬럼에 인덱스가 없으면 부모 삭제가 풀스캔이 된다. PostgreSQL 은 자동 생성하지 않는다.
- - **확인은 추측이 아니라 실행계획으로.** `EXPLAIN (ANALYZE, BUFFERS) <쿼리>`.
- `Seq Scan` 이 큰 테이블에 보이면 원인을 찾는다.
-
- 인덱스를 추가하기 전에 **쿼리를 고칠 수 있는지** 먼저 본다. 함수를 씌운 컬럼
- (`WHERE lower(name) = ...`)은 인덱스를 못 타므로, 표현식 인덱스를 만들거나 쿼리를 바꾼다.
+ - 복합 인덱스는 앞 컬럼부터 쓰인다. 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둔다.
+ - **FK 를 선언해도 자식 컬럼 인덱스는 생기지 않는다** (PostgreSQL·SQLite 실측). 부모를
+ 지우거나 부모 기준으로 자식을 찾을 때 자식 테이블 전체를 훑으므로 직접 만든다.
+ - **함수를 씌운 조건은 컬럼 인덱스를 못 탄다.** `WHERE lower(name) = ...` 는 `name` 인덱스를
+ 쓰지 않는다. 쿼리를 바꾸거나 표현식 인덱스를 만든다.
+ - 인덱스를 추가하기 전에 **쿼리를 고칠 수 있는지** 먼저 본다. 판단은 추측이 아니라 실행계획으로
+ 하고, 실행계획 읽는 법은 엔진 스킬을 따른다.
## 3. 쿼리
- `SELECT *` 를 애플리케이션 쿼리에 쓰지 않는다. 컬럼이 늘면 전송량이 늘고,
의도치 않은 필드가 새어나간다.
- - **N+1 을 의심한다.** 목록을 돌면서 건마다 조회하는 코드는 조인이나 `IN` 한 번으로 바꾼다.
- - 페이징은 큰 오프셋에서 느려진다. 정렬 키 기준 커서(`WHERE seq > ?`)를 쓴다.
- - 문자열 조립 금지. **값은 언제나 플레이스홀더.** 식별자를 동적으로 넣어야 하면
- 화이트리스트로 검증하고 인용한다.
+ - **N+1 을 의심한다.** 고객 50명의 주문 수를 건마다 세면 문이 51개 나간다. `IN` 한 번이면
+ 2개로 끝나고 결과는 같다. 목록을 돌며 조회하는 코드는 조인이나 `IN` 으로 바꾼다.
+ - **오프셋 페이징은 페이지 사이에 행이 들어오면 같은 행을 두 번 보여준다.** 큰 오프셋에서는
+ 느리기도 하다. 정렬 키 기준 커서(`WHERE id < ?`)를 쓴다.
+ - **커서의 정렬 키가 유일하지 않으면 동률 행을 건너뛴다.** 시각처럼 겹칠 수 있는 키는
+ `(at, id) > (?, ?)` 처럼 유일한 키를 덧붙여 비교한다.
+ - 문자열 조립 금지. **값은 언제나 플레이스홀더.** 이어 붙인 `x' OR '1'='1` 은 전 행을 돌려주고,
+ `O'Brien` 같은 정상 입력도 문법 오류를 낸다.
+ - **플레이스홀더는 값 자리에만 쓸 수 있다.** 테이블·컬럼 이름 자리에 넣으면 문법 오류다.
+ 식별자를 동적으로 넣어야 하면 화이트리스트로 검증하고 인용한다.
## 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`. **값을 채팅·로그·이슈에 노출하지 않는다.**
-
- ### 쓰기 작업 규율
+ ## 6. 하지 않는 것
- - 조회는 자유롭게. **INSERT/UPDATE/DELETE·DDL 은 사용자의 명시 실행 신호 후에만** 한다.
- - 대량 변경 전에 **영향 행 수를 먼저 센다.** `SELECT count(*)` 로 확인하고 보고한 뒤 실행한다.
- - `UPDATE`/`DELETE` 에 `WHERE` 가 없으면 실행하지 않는다. 예외 없다.
- - 운영 데이터 이동·삭제는 범위가 확정되지 않으면 시작하지 않는다.
+ - 특정 엔진의 운영(접속·커넥션 상한·진단 쿼리)은 다루지 않는다. doksam pig 공유 PostgreSQL
+ 클러스터 운영은 `yd-pg-expert` 가 맡는다.
+ - 엔진별 타입·잠금·실행계획 세부를 여기에 적지 않는다. 적으면 엔진 스킬과 어긋난다.
- ### 진단 시작점
+ ## 7. 완료 조건
- ```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;
+ - 새 테이블·컬럼에 적절한 제약(`NOT NULL`·FK·`UNIQUE`)이 있고, NULL 허용은 근거가 있다.
+ - NULL 을 허용하는 컬럼을 `NOT IN`·`=` 로 비교하는 곳이 없다.
+ - FK 컬럼과 조회 조건에 인덱스가 있고, 느린 쿼리는 엔진 스킬대로 실행계획을 확인했다.
+ - 목록 조회에 N+1 이 없고, 페이징은 유일한 키 기준 커서다.
+ - 마이그레이션이 되돌릴 수 있거나, 불가능함을 명시했다. 잠금 영향은 엔진 스킬로 확인했다.
+ - 시크릿이 출력·로그·이슈에 노출되지 않았다.
- -- 커넥션을 누가 쓰고 있는가
- SELECT datname, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;
+ ## 8. 이 문서의 단언 재현
- -- 테이블 부풀림·죽은 튜플
- SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
- FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
+ ```bash
+ python3 skills/yd-db-expert/scripts/verify_db_claims.py
```
- `idle in transaction` 이 오래 떠 있으면 애플리케이션이 커밋을 안 하고 있는 것이다 —
- 락과 VACUUM 을 동시에 막으므로 우선 처리한다.
-
- ## 7. 완료 조건
-
- - 새 테이블·컬럼에 적절한 제약(`NOT NULL`·FK·`UNIQUE`)이 있고, NULL 허용은 근거가 있음
- - 조회 조건에 인덱스가 있고, 느린 쿼리는 `EXPLAIN (ANALYZE)` 로 확인함
- - 마이그레이션이 되돌릴 수 있거나, 불가능함을 명시함
- - 운영 클러스터를 만졌으면: 영향 범위를 먼저 세어 보고했고, 커넥션 상한을 확인함
- - 시크릿이 출력·로그·이슈에 노출되지 않음
+ NULL 비교, `NOT IN`, `UNIQUE` 와 NULL, DB 제약, FK 인덱스, 함수 조건, N+1, 오프셋·커서
+ 페이징, 원자성, 플레이스홀더, 소프트 삭제 유니크를 stdlib `sqlite3` 로 재현한다. 하나라도
+ 문서와 다르면 exit 1 이다. 단언과 테스트의 대응은 `tests/claims.json` 이 갖는다.