ga-commission-system/docs/08_DBA_SPEC.md

# 08_DBA_SPEC — DB 성능 (인덱스 / 파티셔닝 / 뷰)

> core-architect 의 Mapper XML 작성 완료 후, EXPLAIN ANALYZE 로 검증하고
> V13~V15 마이그레이션을 추가한다.

## 1. 검토 절차

1. core-architect 가 작성한 모든 Mapper XML 의 SELECT 쿼리를 추출
2. 운영 DB 또는 테스트 DB 에서 `EXPLAIN ANALYZE` 실행
3. `Seq Scan` 발견 시 인덱스 후보 정리
4. 통계/리포트 쿼리는 View 또는 Materialized View 로 분리
5. 대용량 테이블은 파티셔닝 검토 (recruit_ledger, api_access_log, data_change_log)

---

## 2. V13__인덱스최적화.sql

이미 생성된 V1~V12 의 인덱스 외에 추가:

```sql
-- settle_master: 정산월 기준 빈번한 조회
CREATE INDEX IF NOT EXISTS idx_sm_month_status ON settle_master(settle_month, status);
CREATE INDEX IF NOT EXISTS idx_sm_agent_month  ON settle_master(agent_id, settle_month);

-- recruit_ledger / maintain_ledger: 정산월 + 설계사
CREATE INDEX IF NOT EXISTS idx_rl_month_agent ON recruit_ledger(settle_month, agent_id);
CREATE INDEX IF NOT EXISTS idx_ml_month_agent ON maintain_ledger(settle_month, agent_id);
CREATE INDEX IF NOT EXISTS idx_rl_company     ON recruit_ledger(company_code, settle_month);
CREATE INDEX IF NOT EXISTS idx_ml_company     ON maintain_ledger(company_code, settle_month);

-- exception_ledger: 정산월 + 승인상태
CREATE INDEX IF NOT EXISTS idx_el_month_approve ON exception_ledger(settle_month, approve_status);

-- contract: 만기/실효 처리용 (부분 인덱스)
CREATE INDEX IF NOT EXISTS idx_contract_lapse  ON contract(lapse_date) WHERE lapse_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_contract_status_agent ON contract(status, agent_id);

-- chargeback / payment
CREATE INDEX IF NOT EXISTS idx_cb_month  ON chargeback(settle_month, status);
CREATE INDEX IF NOT EXISTS idx_pay_month ON payment(pay_date, pay_status);

-- raw / 에러 / 매핑 (부분 인덱스 적극 사용)
CREATE INDEX IF NOT EXISTS idx_raw_status     ON raw_commission_data(parse_status, settle_month);
CREATE INDEX IF NOT EXISTS idx_pe_company     ON parse_error_log(company_code, resolve_status);
CREATE INDEX IF NOT EXISTS idx_codemap_lookup ON code_mapping(company_code, code_type, source_code)
    WHERE is_active = 'Y';

-- 로그 테이블 (DESC 인덱스로 최신순 조회 최적화)
CREATE INDEX IF NOT EXISTS idx_dcl_table_recent ON data_change_log(table_name, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_aal_url_recent   ON api_access_log(url, created_at DESC);
```

### 인덱스 설계 원칙
- **WHERE 자주 쓰이는 컬럼 + ORDER BY 컬럼** 을 복합 인덱스로
- **선택도(cardinality) 낮은 컬럼은 후순위** (`status` 보다 `agent_id` 가 앞)
- **부분 인덱스**(`WHERE`) — 활성 데이터만 인덱싱하여 크기 축소
- **DESC 인덱스** — 최신순 조회가 빈번한 로그 테이블
- **외래키는 자동 인덱스 X** — PG 는 FK 컬럼에 자동 인덱스 안 만듦. 직접 추가.

---

## 3. V14__파티셔닝.sql

대용량/시계열 테이블을 RANGE 파티셔닝으로 변환.

### 헬퍼 함수
```sql
CREATE OR REPLACE FUNCTION create_monthly_partition(
    parent_table TEXT,
    year_month   TEXT          -- 'YYYYMM'
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date   DATE;
BEGIN
    partition_name := parent_table || '_p' || year_month;
    start_date := to_date(year_month || '01', 'YYYYMMDD');
    end_date   := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
        partition_name, parent_table, start_date, end_date
    );
END $$ LANGUAGE plpgsql;
```

### 적용 테이블
- **`recruit_ledger`, `maintain_ledger`**: `settle_month` 기준 (VARCHAR 'YYYYMM')
  - 변환 절차: 임시 테이블로 데이터 이전 → 파티션 마스터 생성 → 월별 파티션 생성
  - 신규 시스템이면 처음부터 파티션 테이블로 정의 (V5 변경)
- **`api_access_log`, `data_change_log`**: `created_at` (TIMESTAMP) 기준 월별
  - 90일 이상 자료는 자동 분리 (별도 cron 으로 detach)

### 자동 파티션 생성 (예약)
```sql
-- 매월 1일 차월 파티션 생성 (pg_cron 또는 외부 스케줄러)
SELECT create_monthly_partition('recruit_ledger', to_char(now() + interval '1 month', 'YYYYMM'));
SELECT create_monthly_partition('api_access_log', to_char(now() + interval '1 month', 'YYYYMM'));
```

### 주의사항
- 파티션 테이블은 글로벌 UNIQUE INDEX 못 만듦 → 파티션 키 포함 UNIQUE 만 가능
- ON CONFLICT 사용 시 모든 파티션에 동일 인덱스 필요
- Flyway 적용 시 운영 데이터가 있으면 별도 데이터 이관 스크립트 필요 (개발 환경에서는 빈 테이블 가정)

---

## 4. V15__뷰.sql

### `v_agent_monthly_summary` — 설계사별 월 실적
```sql
CREATE OR REPLACE VIEW v_agent_monthly_summary AS
SELECT sm.agent_id, a.agent_name, o.org_id, o.org_name, g.grade_name,
       sm.settle_month,
       sm.recruit_total, sm.maintain_total,
       sm.exception_plus, sm.exception_minus,
       sm.override_total, sm.chargeback_total,
       sm.gross_amount, sm.tax_amount, sm.net_amount, sm.status
  FROM settle_master sm
  JOIN agent a            ON a.agent_id = sm.agent_id
  LEFT JOIN organization o ON o.org_id = a.org_id
  LEFT JOIN grade g        ON g.grade_id = a.grade_id;
```

### `v_org_settle_summary` — 조직별 월 합계
```sql
CREATE OR REPLACE VIEW v_org_settle_summary AS
SELECT a.org_id, o.org_name, o.org_type, sm.settle_month,
       COUNT(sm.settle_id) AS agent_count,
       COALESCE(SUM(sm.recruit_total),  0) AS recruit_total,
       COALESCE(SUM(sm.maintain_total), 0) AS maintain_total,
       COALESCE(SUM(sm.gross_amount),   0) AS gross_amount,
       COALESCE(SUM(sm.tax_amount),     0) AS tax_amount,
       COALESCE(SUM(sm.net_amount),     0) AS net_amount
  FROM settle_master sm
  JOIN agent a        ON a.agent_id = sm.agent_id
  JOIN organization o ON o.org_id = a.org_id
 GROUP BY a.org_id, o.org_name, o.org_type, sm.settle_month;
```

### `v_chargeback_risk` — 환수 위험 계약
```sql
CREATE OR REPLACE VIEW v_chargeback_risk AS
SELECT cb.cb_id, cb.contract_id, cb.agent_id, a.agent_name, o.org_name,
       cb.policy_no, cb.lapse_month, cb.cb_rate, cb.cb_amount,
       cb.paid_amount, cb.remain_amount, cb.settle_month, cb.status
  FROM chargeback cb
  JOIN agent a            ON a.agent_id = cb.agent_id
  LEFT JOIN organization o ON o.org_id = a.org_id;
```

### Materialized View 후보 (대시보드 차트)
- `mv_monthly_total` — 월별 전사 합계 (`REFRESH MATERIALIZED VIEW` 일 1회)
- `mv_agent_top10`   — 설계사 실적 상위 10

---

## 5. 테스트 데이터 (V17 — `infra-reviewer` 가 아닌 dba-architect 가 생성)

| 테이블 | 건수 | 설명 |
|---|---:|---|
| insurance_company | 5  | SAMSUNG / HANWHA / KYOBO / DB / KB |
| product           | 20 | 보험사당 4 (생명/건강/저축/연금) |
| organization      | 7  | 본부 1 / 지점 3 / 팀 3 |
| agent             | 50 | GM 3 / DM 3 / SMGR 6 / SFC 12 / FC 26 |
| contract          | 200| ACTIVE 190 / LAPSE 10 |
| commission_rate   | 100| 상품 × 1~5년차 |
| payout_rule       | 120| grade × insurance_type × year |
| override_rule     | 10 | grade pair |
| chargeback_rule   | 4  | 보험종 × 실효 월수 |

생성 SQL 패턴:
```sql
-- 50명 설계사 자동 생성
INSERT INTO agent (org_id, grade_id, agent_name, license_no, phone, status, join_date)
SELECT
  -- org/grade 분배
  CASE WHEN n <= 3 THEN 1 WHEN n <= 6 THEN 2 ... END,
  CASE WHEN n <= 3 THEN 5 WHEN n <= 6 THEN 4 ... END,
  '설계사_' || LPAD(n::text, 3, '0'),
  'L' || LPAD(n::text, 5, '0'),
  '010-' || LPAD((1000 + n)::text, 4, '0') || '-' || LPAD(n::text, 4, '0'),
  'ACTIVE',
  CURRENT_DATE - (n * 30)
FROM generate_series(1, 50) n;
```

---

## 6. PostgreSQL 설정 권장값 (`postgresql.conf`)

```ini
# 메모리 (8GB 서버 기준)
shared_buffers = 2GB                    # 25%
effective_cache_size = 6GB              # 75%
work_mem = 32MB
maintenance_work_mem = 512MB

# WAL / 체크포인트 (대량 INSERT 대비)
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# 병렬
max_parallel_workers_per_gather = 4
max_worker_processes = 8

# 통계 (쿼리 플래너 정확도)
default_statistics_target = 100

# autovacuum (대용량 로그 테이블 대비)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025
```

---

## 7. 대량 배치 쿼리 튜닝

### MyBatis 배치 INSERT (현재 구현)
```xml
<insert id="batchInsert" parameterType="list">
    INSERT INTO recruit_ledger (contract_id, agent_id, policy_no, ...)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.contractId}, #{item.agentId}, #{item.policyNo}, ...)
    </foreach>
</insert>
```
- 1000 건씩 끊어서 호출 (ExcelService.importLargeExcel 의 batchSize)
- 70만건 INSERT: 약 700 회 호출 → 약 1~2 분 소요

### COPY 명령 활용 (10만건+ 시 10배 빠름)
```java
// JDBC CopyManager 직접 사용
CopyManager copy = new CopyManager((BaseConnection) conn);
copy.copyIn("COPY recruit_ledger FROM STDIN WITH (FORMAT csv)", reader);
```
- 트랜잭션 격리 / 트리거 / 제약 조건 주의
- 향후 70만건 이상 단발성 적재용 옵션으로 추가

---

## 8. 검토 체크리스트 (core-architect 와 협업)

- [ ] 모든 SELECT 쿼리에 `ORDER BY` + `LIMIT/OFFSET`(또는 PageHelper) 적용
- [ ] `LIKE '%키워드%'` 가 자주 쓰이는 컬럼은 `pg_trgm` GIN 인덱스 검토
- [ ] JSONB 컬럼 검색은 `jsonb_path_ops` GIN 인덱스
- [ ] 날짜 범위 검색은 BTREE 인덱스 + WHERE `created_at >= ...`
- [ ] N+1 SELECT 가 발생하지 않도록 ResultMap 의 `<collection>` 검토
- [ ] 정산 배치 Step 4/5 의 SELECT 가 `idx_rl_month_agent` 를 쓰는지 EXPLAIN