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 의 인덱스 외에 추가:

-- 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 파티셔닝으로 변환.

헬퍼 함수

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)

자동 파티션 생성 (예약)

-- 매월 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 — 설계사별 월 실적

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 — 조직별 월 합계

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 — 환수 위험 계약

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 패턴:

-- 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)

# 메모리 (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 (현재 구현)

<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배 빠름)

// 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