v0.18.0 D12 — 표·인덱스·제약을 한눈에 보는 장부가 없어 기능 PR 마다 인덱스가 제각각 쌓이고, 세트 하나를 지울 때 투영 표 5개를 전체 읽던 것에서, 기계가 뽑는 DB object 장부·닫힌 인덱스 판정·도메인별 제약/권한 시험·대표 조회 실행계획·근거 있는 인덱스 정리로

• 기간: 2026-09-07 ~ 2026-09-07 (세션 2개 — dcfeccf7-0040-4113-b06b-cd23fcf157f0 Phase 0·1, 111cb75f-6794-41c7-888d-ae0ba067d5ac Phase 2~4 이어받음. 오너 지시 "1328 이어서 진행해줘 묻지말고 phase 끝까지 완주")
• 랜딩: PR #1358 (Phase 0~4 한 PR, squash) — 브랜치 feat/1328-db-model-quality, base main bf16e910. 마이그레이션 1개(20260913030100_d12_db_model_quality, 랜딩 시 번호 확정) · 앱 화면·엣지 함수 변경 없음
• 설계서: 없음 — 분석·Phase 계획은 이슈 #1328 계획 댓글
• 정본: docs/data/db-model-ledger.md(DB object 장부 — 읽는 법·규칙·생성 절·D12 결과표·실행계획 전후·쓰기 비용) · supabase/contracts/db-objects.json(생성물) · supabase/contracts/db-objects-policy.json(보존·삭제·결론·증거·인덱스 판정 닫힌 목록) · supabase/contracts/representative-queries.json(대표 조회 34개)
• 도구: npm run db:model(장부 생성) · npm run db:model:check [--strict](드리프트·정책·인덱스 판정, Docker 불필요) · npm run db:explain(대표 조회 실행계획 러너) · npm run db:fixture:domain(소셜·그룹·카탈로그·인입 fixture)
• 게이트: 계약 테스트 tests/react/dbModelLedgerContract.test.mjs(17건, npm test 편입 — 생성물·문서 드리프트, 정책 누락/무효, 중복 인덱스 0·참조 열 인덱스 없는 FK 닫힌 목록) · pgTAP d12_{profile,social,group,catalog,identity_admin,import}_constraints(98 assert) + d12_layer_helpers(14) · 마이그레이션 populated upgrade 증거(D13 harness, 머리 -- upgrade-evidence:)
• 버그리포트: 없음(구조 트랙). 발견 사항은 §4 "작업 중 드러난 것"
• 계약: 새 정본 1(DB object 장부). SQL 원천 예외 목록 24 → 21(D12 몫 3건 해소). 원본 불변 계약·ID·source·수치 정책은 그대로

2026-09-08 종료 상태

#1328은 **Closed · [v0.17.2 반영완료]**다. PR #1358·d1e77113의 기존 배포 포함을 기준으로 관리하며, 사용자가 추가 성능 측정 없이 종료하도록 지정했다. 따라서 이 문서에 후속으로 남겼던 대표 운영 pg_stat_user_tables.seq_scan·save_session_v5 저장 지연 측정은 사용자 결정으로 실행하지 않는다.

아래의 샌드박스 측정값과 당시 Production 미측정 사실은 그대로 보존한다. 이 종료는 추가 측정의 성공이나 운영 성능 개선의 입증을 뜻하지 않는다. 생략 범위는 D12의 추가 대표 운영 측정이며, R04/R05의 전체 성능·릴리스 검증, 통합 후보의 DB/schema·migration 검증과 다른 실행 작업의 수락 조건은 유지한다.

Phase 현황

Phase	내용	상태
Phase 0	object 장부 생성기 — schema.sql 에서 표 77·뷰 3 의 열·PK/FK/UNIQUE/CHECK·인덱스·RLS·정책·트리거·권한을 기계로 뽑고, 함수 본문에서 작성자/읽는 곳, 등급(fact 23·projection 20·reference 18·system 16)·owner 연결을 붙임. 정책 파일(보존·삭제·결론·상태)은 사람이 적음. 도메인 16개 ERD + 기계 판정	✅
Phase 1	도메인별 제약·owner 연쇄·정밀도·RLS 경계 pgTAP 6파일(98 assert): 유효/무효/타 owner fixture, 0≠null, numeric 반올림, 업무 날짜 vs UTC, 본인/타인/비로그인/service_role, 계정·세션·그룹 삭제 연쇄	✅
Phase 2	대표 조회 34개 실행계획(사용자 11명·완료 세션 5,209·세트 62,508 샌드박스) — 읽은 행·seq scan·인덱스·정렬·버퍼, keyset 페이지 3종 중복/누락 검사	✅
Phase 3	마이그레이션 1개: 중복·접두·미사용 인덱스 29 drop, 저장·삭제·계정 삭제 경로 FK 참조 열 인덱스 25, 인입 staging owner 연쇄 복합 FK 4, 층 규칙 예외 3건 해소 함수 2. 전후 실행계획·쓰기 비용(WAL)·populated upgrade 증거	✅
Phase 4	결과표(발견 11 + Phase 1 발견 3 → 결론), ci:local --full, 이 기록·등록 2곳, 인계 댓글(D02·A03·B01·D13·HQ)	✅ PR #1358

1. 배경

v0.18.0 은 서버가 canonical 데이터·확정 통계를, 클라이언트가 편집 중 상태·미전송 쓰기를 책임지는 구조로 가는 릴리스다. D03 가 함수·정책·트리거의 원천(supabase/definitions/**)과 registry 를, D13 가 데이터가 있는 DB 에서의 마이그레이션 증거를 만들었지만, 표·열·인덱스·FK·RLS 는 3.4MB schema.sql 안에만 있었다. 기능 PR 마다 "그 화면에 필요한 인덱스" 를 더했고 이미 있는 것과 겹치는지 아무도 보지 않았다. 유저 A 가 어제 기록의 세트 하나를 고쳐 저장하면 서버는 A 가 보내지 않은 옛 세트 행을 지우고 새 행을 넣는데, 세트 행 하나를 지울 때마다 Postgres 는 그 행을 가리키는 투영 표 5개에서 "참조하는 행이 남았나" 를 확인하고, 그 참조 열에 인덱스가 없어 표 전체를 읽었다.

2. 문제 제기

같은 열 집합을 두 번 인덱스한 것 9쌍, 앞부분이 다른 인덱스에 포함된 것 8쌍

Production 2026-09-07 실측: user_exercise_period_stats 기본키(0회 사용) 옆에 같은 열의 _user_exercise_idx(79만 회) — 투영 표는 매분 지우고 다시 쓰므로(삽입 685만·삭제 682만) 중복 인덱스가 쓰기·WAL 비용을 그만큼 두 배로 만든다. 같은 모양이 pr_states·strength_daily·calendar_day_summaries·training_period_stats·pr_summary_snapshots·daily_conditions·wodup_import_rows·wodup_session_timing 에 있었다.

참조 열 인덱스가 없는 FK 65/147, 그중 저장·삭제 경로 12

user_exercise_records(session_exercise_id·source_set_id·session_id)·user_exercise_session_rollups·user_exercise_strength_daily·user_exercise_strength_states·user_exercise_max_rep_observations·user_exercise_strength_observations — Production user_exercise_records seq scan 3.4만 회·7,400만 행 읽음(살아 있는 행 3,974).

한 번도 안 쓰인 인덱스 24, 보존·삭제 정책 없음, JSON 이중 권위, RLS 시험 편차, 층 규칙 예외 3

계획 댓글 표 11건. 전부 §4 결과표(장부 §4-1)에 결론이 붙었다.

(Phase 1 에서 드러난 것) 인입 staging 행과 배치의 소유자를 잇는 제약이 없음

다른 계정의 배치에 행을 넣어도 막히지 않았다(엔진은 "배치 소유자 = 호출자" 만 허용하지만 DB 가 지키지는 않았다). 커밋 시점 검사 FK 11개(DEFERRABLE)는 함수 안 삭제가 성공한 것처럼 보이다 커밋에서 실패하는 모양이라 목록으로 고정했다.

3. 해결 방안

원칙 (오너 결정: 없음 — 전제 3개)

① 데이터 삭제(인입 staging·영수증·raw_payload 값)는 이 트랙에서 하지 않고 정책표 + 담당 인계까지만 ② 인덱스 추가/삭제는 되돌릴 수 있는 변경이라 실측 근거로 진행 ③ 새 도구는 scripts/sql/, 계약 JSON 은 supabase/contracts/, 문서는 docs/data/db-model-ledger.md.

접근

안	내용	채택
A. 장부 + 검사 + 근거 있는 forward 변경	표 구조를 기계로 뽑아 장부를 생성하고, 생성물이 스냅샷과 어긋나면 npm test 가 잡는다. 인덱스 판정은 닫힌 목록(중복 0, FK 참조 열·접두 인덱스는 이유 있는 것만). 실측 근거가 있는 것만 마이그레이션으로 고친다	채택
B. 발견 목록만 문서화, 인덱스는 손으로 정리	다음 기능 PR 에서 같은 중복이 다시 쌓인다(§22 자기 검사 = 예)	불채택
C. 인덱스 전면 재설계·표 재구성	측정 근거 없는 재설계는 이슈가 금지한 범위	불채택

4. 적용한 내용

Phase 0 — object 장부 생성기 (커밋 4fcfc701 → 리베이스 뒤 e3e9b537)

scripts/sql/dbModelLedger.mjs(해석기·분석·검사·렌더러) · db-model-ledger.mjs(CLI --check·--strict·--init-policy) · supabase/contracts/db-objects{,-policy}.json · docs/data/db-model-ledger.md 생성 절(도메인 16 ERD·object 표·기계 판정) · 계약 테스트 14건 · 정적 파싱 vs 샌드박스 실제 카탈로그 77표 차이 0.

Phase 1 — 제약·owner 연쇄·정밀도·RLS pgTAP (커밋 cb486b20 → 211c0ac2)

supabase/tests/database/d12_*_constraints.test.sql 6파일 98 assert. 발견: profiles 는 본인 행만 읽힘(Phase 0 보고 정정), DEFERRABLE FK 11, staging owner 연쇄 없음(TODO → Phase 3), staging·InBody 배치 표는 소유자가 표를 직접 UPDATE/DELETE 가능(정책 ALL — I01/D09 인계).

Phase 2 — 대표 조회 실행계획 (커밋 12ee7647 일부)

scripts/sql/explain-representative.mjs(npm run db:explain): 목록 파일의 조회를 역할(본인·팔로워·관리자·비로그인·postgres)로서 부르고 auto_explain(nested, JSON) 으로 문 함수·RI 트리거 안의 중첩 문장까지 컨테이너 로그에서 모아 집계. 조회마다 Docker VM 안의 로그 파일을 0 으로 비우고, 파일로 받아 한 줄씩 집계한다(계정 삭제 연쇄 1회가 2.9GB). scripts/sql/fixtures/domain-fixture.mjs(npm run db:fixture:domain): 추가 사용자 40·팔로우 290·좋아요 1,200·댓글 400·차단·신고·그룹 5(채팅 500)·커스텀 종목 100·Wodup staging 배치(행 2,000·세트 2,000)·InBody·신체 기록, 고정 uuid(멱등). keyset 페이지 3종(피드 65쪽·검색 22쪽·PR 기록) 중복 0·누락 0. 표는 장부 §4-2.

Phase 3 — 마이그레이션 20260913030100_d12_db_model_quality (커밋 12ee7647·리베이스 커밋)

• ① 인덱스 drop 29: 같은 열 집합 9(비고유 쪽) + 접두 포함 7 + 소비자 없는 13(함수 본문·앱 조회·pgTAP 전수 대조 — 표는 이슈 Phase 3 보고). exercise_external_mappings 는 접두 쌍 중 소비자 없는 넓은 쪽을 지웠다.
• ② FK 참조 열 인덱스 25: 투영 표 7개 15 + 세션·그룹·종목·staging·그룹 채팅/공지/초대 10. nullable 열은 is not null 부분 인덱스(참조 검사는 "열 = $1" 이라 쓸 수 있다).
• ③ 인입 staging owner 연쇄: wodup_import_batches (id, user_id) UNIQUE + 4 표에 복합 FK(NOT VALID → VALIDATE). Production 프로브(읽기 전용) 불일치 0행 확인 뒤.
• ④ 층 규칙 예외 3건: exercise_target_muscles_valid_core_v1(엔진이 부르는 내부용 쌍둥이 — 문은 exercises CHECK 제약이 authenticated 로서 부르므로 그대로)·is_lift_guild_admin_core_v1(내부 함수 2개가 부름 — 문은 B01 소유라 그대로). 같은 답을 내는 것은 pgTAP d12_layer_helpers 14 assert.
• 장부 규칙: "열 IS NOT NULL" 부분 인덱스와 복합 FK 의 선두 열 인덱스는 덮는 것으로 판정 · db:model:check 의 닫힌 목록(policy.findings) · 정책 80 object 전부 검증(유지 근거 71·인계 9, --strict 통과).
• 증거: 실행계획 전후(장부 §4-2) · 쓰기 비용 WAL 전후(§4-3) · populated upgrade(4년 이력: 적용 337ms·WAL 1.07MB·잠금 대기 0·프로브 오류 0/15·중단 뒤 재실행 사실 보존·공존 break 0).

Phase 4 — 통합·랜딩·인계

결과표(장부 §4-1) · ci:local --full · 이 기록·등록 2곳 · 인계 댓글(D02 #1327·A03 #1330·B01 #1331·D13 #1287·HQ #1279 — 아직 이슈가 없는 D04/D06/D07/D08/D09/D11/R04/R05/I01 몫은 HQ 댓글과 §남은 것).

주요 결정과 그 근거

• 중복 인덱스는 비고유 쪽을 지운다 — PK/UNIQUE 가 남고, 한 방향 인덱스는 역방향으로도 읽히므로 DESC 변형도 중복이다. Production 에서 비고유 쪽만 쓰이던 표(period_stats 79만 회)도 planner 가 자동으로 옮겨 간다(후 실행계획에 seq scan 증가 없음).
• FK 참조 열 인덱스는 저장·삭제·계정 삭제 경로에만 — 종목 삭제(관리자·연 수회)·참조 데이터(지워지지 않음)·관리자 행위자 열은 이유를 적어 닫힌 목록에 두었다. 남은 42개는 새 FK 가 생겨도 이유 없이는 통과 못 한다.
• 쓰기 비용을 재고 채택 — 10년 이력 사용자 전량 재계산 WAL 28.4 → 37.5MB(+32%, 최악 사례). 사용자가 느끼는 저장·삭제 쪽(세트 삭제 버퍼 45배·세션 삭제 30배·계정 삭제 36초 → 8초)이 더 크다고 판단. WAL 예산 초과 시 되돌릴 순서(관측 표 2개의 인덱스 4개 먼저)를 장부 §4-3 에 적었다.
• 문(door)은 건드리지 않고 내부용 쌍둥이를 둔다 — is_lift_guild_admin 은 B01 소유이고 RLS 정책 20여 곳이 부른다. 쌍둥이의 판정이 같음을 pgTAP 로 고정해 두 정의가 갈라지면 잡힌다.
• 이중 FK(exercise_external_mappings.exercise_id)는 유지 — cascade 가 먼저 지워 NO ACTION 검사가 빈 집합을 보므로 실질 cascade, DEFERRABLE 쪽은 정체성 병합 트랜잭션용. 둘 중 하나를 빼면 의미가 사라진다.

작업 중 드러난 것

• 컨테이너 로그 4.3GB — 이전 세션의 실행계획 전량 기록으로 docker logs --since 가 몇 분씩 멈췄다. Docker VM 안 파일을 docker run -v /var/lib/docker/containers alpine truncate 로 비우면 된다(Git Bash 는 MSYS_NO_PATHCONV=1). Postgres 로그는 컨테이너 stderr 라 stdout 만 받으면 실행계획 0개.
• spawnSync 의 문자열 상한(0x1fffffe8) — 계정 삭제 연쇄의 중첩 문장 로그 2.9GB 를 문자열로 올리다 죽었다. 파일로 받아 readline 으로 집계.
• 임시 마이그레이션 번호 충돌 — 작업 중 main 에 같은 번호(20260913010100, #1345)가 랜딩됐다. harness 가 두 파일을 다 적용했고 internal-function-removed 공존 경고가 났다(내 base 가 옛것이라). 리베이스 뒤 20260913030100 으로 바꾸고 harness 를 다시 돌렸다 — harness·PR 직전에는 fetch 로 꼬리를 확인한다.
• schema:snapshot 은 리셋 직후에 — pgTAP 가 dblink 확장을 만들어 두어 스냅샷에 create extension dblink 가 섞였다. --reset 뒤 스냅샷.
• 리베이스 시 생성물 충돌 — schema.sql·registry.json·db-objects.json·커버리지 장부는 main 쪽을 받고 생성기를 다시 돌린다. coverage-inventory.json 규칙 배열은 양쪽 합치되 마지막 항목 쉼표를 확인(JSON 깨짐 1회).
• 실행계획 "읽은 행" 은 노드 종류에 따라 계수가 다르다 — Index Scan 은 필터 뒤 행, Bitmap Index Scan 은 필터 전 행. calendar.day 4,347 → 38,419 는 이 차이(장부 §4-2). 일의 양은 버퍼 수로 본다.
• 같은 PC 에서 샌드박스 두 개를 동시에 돌리면 ms 가 ±30% 흔들린다 — 전후 비교는 seq scan·버퍼 수로, ms 는 조용한 재측정으로.
• main 의 커버리지 규칙 1개가 앞 규칙에 가려져 "쓰이지 않는 규칙" 으로 판정(#1347 이 authResumePolicy 를 더한 규칙과 옛 규칙을 둘 다 남김) — check-coverage-inventory 가 main 에서도 실패하는 상태라 가려진 옛 규칙 한 줄을 이 PR 에서 뺐다.
• Phase 1 profiles 정책 오독(정책 이름과 달리 본인 행만) · wodup_session_timing_materializations 는 1회성 백필 표가 아니라 살아 있는 표(매 인입마다 쓰고 시간 통계마다 읽음) · user_pr_overview_rollover_state 는 cron 이 매분 읽음(Phase 0 "읽는 함수 없음" 은 registry 의존성 누락이 아니라 내 검색 범위 문제).

5. 적용 결과

항목	전	후	근거
표·인덱스·FK·RLS 장부	없음(schema.sql 3.4MB 안)	표 77·뷰 3·인덱스 133·FK 151 전부 도메인·등급·작성자·읽는 곳·보존·삭제·결론·증거, 미확인 0 (--strict 통과)	장부 §3
같은 열 집합 인덱스 쌍 / 접두 포함	9 / 9	0 / 0 (npm test 가 0 강제)	기계 판정
참조 열 인덱스 없는 FK	69(부분 인덱스 미인정 포함)	42(전부 이유 있는 닫힌 목록)	정책 findings
세트 1개 삭제(참조 검사)	버퍼 3,394 hit + 3,793 read · 17~30ms	160 hit · 6~13ms	장부 §4-2
세션 삭제 연쇄	버퍼 96,710 · 190~220ms	3,280 · 42~76ms	〃
계정 삭제 연쇄(1년 이력)	버퍼 2,950만 · 36.3초 · 중첩 문장 14,880	808만 · 8.4초 · 3,763	〃
읽기 조회(인덱스 29 drop 뒤)	—	seq scan 새로 생긴 조회 0, 페이지 중복/누락 0	〃
투영 전량 재계산 WAL(10년 사용자)	28.4MB · 1,820ms	37.5MB(+32%) · 1,551ms	장부 §4-3
RLS·제약 경계 시험	도메인 편차(1~34파일)	6 도메인 각 본인/타인/비로그인/service_role ≥1 + 삭제 연쇄 (98 assert)	pgTAP
인입 staging owner 연쇄	제약 없음(다른 계정 배치에 삽입 가능)	복합 FK 4 (23503) · Production 불일치 0행	pgTAP 7번
층 규칙 예외	24(D12 몫 3)	21	sql:check
populated upgrade	—	4년 이력: 적용 337ms·WAL 1.07MB·잠금 대기 0·프로브 오류 0/15·중단 재실행 보존·verdict=passed	마이그레이션 머리
Production 적용	당시 미적용 기록	v0.17.2 반영완료. 추가 대표 운영 측정은 사용자 결정으로 실행 안 함; 인덱스/FK 목록·seq scan·저장 지연의 새 운영 측정 결과 없음	#1328 종료 상태

운영 미측정: Production의 실제 저장 지연 변화와 추가 pg_stat_user_tables.seq_scan/save_session_v5 평균 ms는 측정하지 않았으며, 2026-09-08 사용자 결정으로 D12 종료 뒤 추가 실행하지 않는다. 기존 표의 ms·버퍼·WAL 수치는 해당 샌드박스 결과다. WAL 증가분의 운영 영향 및 전체 성능·릴리스 검증은 R04/R05의 범위로 유지한다.

6. 이번 개선으로 향상된 것

• 다음 데이터 트랙(D04·D08·R04·R05)이 표 하나의 등급·작성자·인덱스·보존·결론을 한 문서에서 읽는다. 새 표·새 인덱스·새 FK 는 같은 PR 에서 정책·이유를 적지 않으면 npm test 가 거부한다.
• 샌드박스 대표 실행계획에서 저장·삭제 경로의 투영 표 전체 읽기가 제거됐고, 세트 삭제 1개당 버퍼가 45배 감소했다. 같은 샌드박스의 계정 삭제는 36초에서 8초로 줄었다. 추가 운영 성능 측정은 하지 않았으므로 운영 환경의 같은 개선 폭을 입증한 수치로 사용하지 않는다.
• 대표 조회 34개의 실행계획을 같은 명령으로 다시 잴 수 있다(R04 릴리스 전후 비교 절차).
• 6개 도메인의 제약·권한 경계가 fixture 로 고정돼 "남의 데이터가 보이거나 지워지는" 회귀를 pgTAP 이 잡는다.

종료 결정과 후속 인계

• 2026-09-08 사용자 결정으로 D12의 추가 대표 운영 측정은 실행하지 않고 이슈를 닫았다. 종전 후속 프로브(인덱스 29 삭제·25 추가·복합 FK 4 목록, 투영 표 seq_scan, save_session_v5 평균 ms)에 새 측정 결과를 채우거나 측정 통과로 표시하지 않는다. 코드의 v0.17.2 반영과 운영 효과의 미측정은 별도 사실이다. R04/R05의 전체 성능·릴리스/DB 검증은 그대로 남는다.
• 인계(HQ #1279 댓글): D06 달력 함수 2곳의 session.id::text = item->>'id' 조인(기본키 못 씀) → (item->>'id')::uuid · A03 #1330 카탈로그 전체/증분 조회(종목 803행 seq scan·정렬 804회, 2.3초/822KB) · D04/D08 세션 검색(읽은 행 21,361·정렬 1,080·2.7초/491KB)·볼륨(57,143행)·투영 재작성 방식과 churn(period_stats 인덱스 45MB) · D07 피드 첫 쪽 정렬 181회 · D02 #1327 raw_payload->'details' 열 승격 후보와 저장 문의 자식 삭제 후 재삽입 구조 · D09/I01 완료 staging 보존(rows 2,243·sets 20,694 6월부터)과 소유자 직접 UPDATE 정책(ALL) · D13/D02 계정 삭제 잔여 8초의 user_fact_history 경로 · R05 WAL 증가분·VACUUM/REINDEX(session TOAST 16배·period_stats 인덱스 부풀음) · B01 #1331 is_lift_guild_admin 을 _core_v1 의 wrapper 로 바꾸는 것.