database-expert-advisor

Database Expert Advisor

Overview

DB Expert AdvisorThis is a comprehensive database consulting skill that supports all aspects of database design, optimization, and operations. It is built on expert knowledge of major database systems such as PostgreSQL, MySQL, MongoDB, and Redis, along with more than 45 academic papers, official documentation, and industry best practices.

Core Competencies

Query Optimization: EXPLAIN analysis, index strategy, execution plan improvement
Schema Design: ER modeling, normalization (1NF-BCNF), partitioning strategy
Performance Tuning: bottleneck analysis, transaction optimization, caching strategy
Security Management: access control, encryption, SQL Injection prevention, compliance with Korean laws and regulations
Migration: database conversion, sharding, replication design
Troubleshooting: deadlock resolution, memory leaks, performance degradation diagnosis

Supported Databases

Category	Database	Primary Use Cases
Relational	PostgreSQL, MySQL, MariaDB	OLTP, transaction processing
NoSQL	MongoDB, Redis, Cassandra, DynamoDB	Large-scale data, caching, time series
NewSQL	CockroachDB, TiDB, YugabyteDB	Distributed SQL, global scale
Time Series	TimescaleDB, InfluxDB	Monitoring, IoT data
Graph	Neo4j	Relationship analysis, recommendation systems

When to Use

✅ When to use this skill

1. Solving Performance Problems

Analyzing and optimizing slow queries (response time > 1 second)
Diagnosing causes of high CPU/memory usage
Bottlenecks caused by increased concurrent connections
Database Lock and Deadlock issues

Example Questions:

"PostgreSQL에서 특정 쿼리가 10초 이상 걸립니다. 
EXPLAIN ANALYZE 결과를 분석해주세요."

2. Database Design

Schema design for a new application
Reviewing normalization/denormalization of an existing schema
Establishing an index strategy (B-tree, Hash, GIN, GiST)
Partitioning and sharding design

Example Questions:

"전자상거래 주문 시스템의 데이터베이스 스키마를 설계해주세요.
하루 10만 건 이상의 주문을 처리해야 합니다."

3. Migration and Conversion

MySQL → PostgreSQL conversion plan
Monolithic → microservices DB separation
On-premises → cloud migration
Sharding or replication configuration

Example Questions:

"MySQL 5.7에서 PostgreSQL 16으로 마이그레이션하려고 합니다.
주의사항과 단계별 계획을 알려주세요."

4. Security and Regulatory Compliance

Measures for compliance with Korea's Personal Information Protection Act
Meeting the requirements of the Electronic Financial Transactions Act
SQL Injection prevention
Encryption and access control configuration

Example Questions:

"개인정보보호법에 따라 고유식별정보를 암호화해야 합니다.
PostgreSQL에서 어떻게 구현하나요?"

5. Large-Scale Data Processing

Managing hundreds of millions or more records
Real-time analytical query optimization
Improving batch processing performance
Time-series data architecture

Example Questions:

"1억 건 이상의 로그 데이터를 저장하고 실시간 분석해야 합니다.
TimescaleDB vs InfluxDB 중 어떤 것이 적합한가요?"

❌ When this skill is not suitable

Simple SQL syntax questions (refer to official documentation)
How to use ORM by programming language (use a separate skill)
UI manipulation per cloud service (refer to official guides)
Latest update information for a specific DB vendor (web search recommended)

Core Capabilities

1. Query Optimization Engine

EXPLAIN Analysis and Interpretation

-- PostgreSQL 예시
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT o.order_id, c.name, SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2025-01-01'
GROUP BY o.order_id, c.name
HAVING SUM(oi.quantity * oi.price) > 1000000;

Analysis Items:

Seq Scan vs Index Scan: Whether it is a full table scan or uses an index
Cost: Estimated cost (startup cost, total cost)
Rows: Estimated rows vs actual rows
Buffers: Shared buffer usage (hit, read, written)
Planning Time vs Execution Time: Planning time vs execution time

Optimization Strategies:

Add indexes (composite indexes, partial indexes)
Change JOIN order
Change subqueries → CTEs or JOINs
Update statistics (ANALYZE)

Index Strategy

Index Type	Use Case	Database	Performance Characteristics
B-tree	Range search, sorting	PostgreSQL, MySQL	Balanced performance
Hash	Equality comparison (=)	PostgreSQL, Redis	Fast lookup, no range support
GIN	Full-text search, arrays, JSONB	PostgreSQL	Complex search, slow inserts
GiST	Geographic information, range types	PostgreSQL	General-purpose index framework
BRIN	Time series, sequential data	PostgreSQL	Small space, large volume
Full-Text	Natural language search	MySQL, PostgreSQL	Text search optimization

Index Design Principles:

✅ DO:
- WHERE 절에 자주 사용되는 컬럼에 인덱스
- JOIN 키 컬럼에 인덱스
- ORDER BY, GROUP BY 컬럼에 인덱스
- 복합 인덱스는 선택도 높은 컬럼을 앞에 배치

❌ DON'T:
- 모든 컬럼에 무분별한 인덱스 (쓰기 성능 저하)
- 선택도 낮은 컬럼 인덱스 (Boolean 등)
- 자주 변경되는 컬럼에 과도한 인덱스
- 사용하지 않는 인덱스 유지 (공간 낭비)

2. Schema Design and Normalization

Guide by Normalization Stage

First Normal Form (1NF): Atomicity

-- ❌ 위반 사례
CREATE TABLE orders (
    order_id INT,
    product_names TEXT  -- "상품A,상품B,상품C" (반복 그룹)
);

-- ✅ 올바른 설계
CREATE TABLE orders (
    order_id INT PRIMARY KEY
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100),
    PRIMARY KEY (order_id, product_name)
);

Second Normal Form (2NF): Remove partial dependencies

-- ❌ 위반 사례 (order_id + product_id가 키인데 product_name은 product_id에만 종속)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ 올바른 설계
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT REFERENCES products(product_id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF): Remove transitive dependencies

-- ❌ 위반 사례 (customer_id → address → city, address와 city가 이행 종속)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    address VARCHAR(200),
    city VARCHAR(50)
);

-- ✅ 올바른 설계
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    address VARCHAR(200),
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

BCNF (Boyce-Codd Normal Form): Every determinant is a candidate key

-- ❌ 위반 사례 (instructor가 course를 결정하지만 instructor는 후보키가 아님)
CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(100),
    instructor VARCHAR(100),
    PRIMARY KEY (student_id, course_name)
);

-- ✅ 올바른 설계
CREATE TABLE instructors (
    course_name VARCHAR(100) PRIMARY KEY,
    instructor VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course_name VARCHAR(100) REFERENCES instructors(course_name),
    PRIMARY KEY (student_id, course_name)
);

Denormalization Strategy (Performance Optimization)

When to Denormalize:

When read performance is very important
When JOIN cost is excessive
When aggregate queries are frequent
When performance takes priority over data consistency

Denormalization Techniques:

-- 1. 계산된 컬럼 추가 (중복 허용)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    total_amount DECIMAL(10,2),  -- 실시간 계산 대신 저장
    item_count INT               -- order_items 테이블 집계값 저장
);

-- 2. 머티리얼라이즈드 뷰 (PostgreSQL)
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE(created_at) AS sale_date,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE(created_at);

-- 주기적 갱신
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- 3. 파티셔닝 (시간 기반)
CREATE TABLE logs (
    log_id BIGSERIAL,
    created_at TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2025_01 PARTITION OF logs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE logs_2025_02 PARTITION OF logs
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

3. Transactions and Concurrency Control

Isolation Level Comparison

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance	Use Case
Read Uncommitted	Occurs	Occurs	Occurs	Highest	Log collection (accuracy less important)
Read Committed	Prevented	Occurs	Occurs	High	Most applications (default)
Repeatable Read	Prevented	Prevented	Occurs	Medium	Report generation, consistent reads required
Serializable	Prevented	Prevented	Prevented	Low	Financial transactions, inventory management

Deadlock Resolution Strategies

Deadlock Example:

-- 트랜잭션 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (대기...)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 트랜잭션 2 (동시 실행)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- (대기...)
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- 결과: 데드락! 두 트랜잭션이 서로 상대방의 락을 기다림

Resolution Methods:

-- 1. 일관된 순서로 락 획득
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 항상 ID 순서대로
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 2. 타임아웃 설정
SET lock_timeout = '5s';

-- 3. 명시적 락 사용 (PostgreSQL)
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;  -- 먼저 모든 락 획득
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 4. 낙관적 락 (애플리케이션 레벨)
UPDATE accounts 
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 10;  -- version이 변경되면 실패

-- 5. MySQL 데드락 감지 및 자동 롤백
-- MySQL은 자동으로 데드락을 감지하고 한 트랜잭션을 롤백

MVCC (Multi-Version Concurrency Control)

PostgreSQL MVCC Behavior:

-- 트랜잭션 1: 데이터 읽기
BEGIN;
SELECT * FROM products WHERE id = 1;  -- version 1: price = 10000
-- (트랜잭션 유지)

-- 트랜잭션 2: 데이터 수정
BEGIN;
UPDATE products SET price = 12000 WHERE id = 1;  -- version 2 생성
COMMIT;

-- 트랜잭션 1: 다시 읽기
SELECT * FROM products WHERE id = 1;  -- 여전히 version 1: price = 10000
COMMIT;

-- MVCC 덕분에 읽기와 쓰기가 서로 블로킹하지 않음

Necessity of VACUUM:

-- 오래된 버전 제거 (PostgreSQL)
VACUUM ANALYZE products;

-- 자동 VACUUM 설정 확인
SELECT * FROM pg_settings WHERE name LIKE 'autovacuum%';

-- 테이블별 VACUUM 통계
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

4. NoSQL Database Guide

MongoDB Schema Design

Embedded vs Referenced:

// ✅ Embedded (1:Few, 빈번한 함께 조회)
{
  "_id": ObjectId("..."),
  "title": "MongoDB Best Practices",
  "author": {
    "name": "John Doe",
    "email": "john@example.com"
  },
  "comments": [
    { "user": "Alice", "text": "Great post!" },
    { "user": "Bob", "text": "Very helpful." }
  ]
}

// ✅ Referenced (1:Many, 독립적 조회)
// Posts 컬렉션
{
  "_id": ObjectId("..."),
  "title": "MongoDB Best Practices",
  "author_id": ObjectId("...")
}

// Users 컬렉션
{
  "_id": ObjectId("..."),
  "name": "John Doe",
  "email": "john@example.com"
}

// Comments 컬렉션
{
  "_id": ObjectId("..."),
  "post_id": ObjectId("..."),
  "user": "Alice",
  "text": "Great post!"
}

Indexing Strategy:

// 1. 단일 필드 인덱스
db.users.createIndex({ email: 1 })

// 2. 복합 인덱스 (선택도 높은 필드 앞에)
db.orders.createIndex({ customer_id: 1, created_at: -1 })

// 3. 전문 검색 인덱스
db.articles.createIndex({ title: "text", content: "text" })

// 4. 지리 공간 인덱스
db.stores.createIndex({ location: "2dsphere" })

// 5. 부분 인덱스 (조건부)
db.orders.createIndex(
  { created_at: 1 },
  { partialFilterExpression: { status: "completed" } }
)

Aggregation Pipeline:

db.orders.aggregate([
  // Stage 1: 필터링
  { $match: { created_at: { $gte: ISODate("2025-01-01") } } },
  
  // Stage 2: 조인
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
  }},
  
  // Stage 3: 배열 언팩
  { $unwind: "$customer" },
  
  // Stage 4: 그룹화
  { $group: {
      _id: "$customer.city",
      total_sales: { $sum: "$total_amount" },
      order_count: { $sum: 1 }
  }},
  
  // Stage 5: 정렬
  { $sort: { total_sales: -1 } },
  
  // Stage 6: 제한
  { $limit: 10 }
])

Redis Usage Patterns

1. Caching:

import redis
import json

r = redis.Redis(host='localhost', port=6379, db=0)

# Cache-Aside 패턴
def get_user(user_id):
    # 1. 캐시 확인
    cached = r.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)
    
    # 2. DB 조회
    user = db.query("SELECT * FROM users WHERE id = ?", user_id)
    
    # 3. 캐시 저장 (TTL 1시간)
    r.setex(f"user:{user_id}", 3600, json.dumps(user))
    
    return user

2. Session Storage:

# 세션 생성
session_id = "sess_abc123"
session_data = {
    "user_id": 42,
    "username": "john",
    "login_time": "2025-01-15T10:30:00"
}
r.setex(f"session:{session_id}", 1800, json.dumps(session_data))  # 30분 TTL

# 세션 조회
session = r.get(f"session:{session_id}")

# 세션 갱신
r.expire(f"session:{session_id}", 1800)

3. Rate Limiting:

def is_rate_limited(user_id, limit=100, window=60):
    """1분 동안 최대 100회 요청 허용"""
    key = f"rate:{user_id}"
    
    # 현재 카운트 조회
    count = r.incr(key)
    
    # 첫 요청이면 TTL 설정
    if count == 1:
        r.expire(key, window)
    
    return count > limit

4. Leaderboard (Sorted Set):

# 점수 추가/업데이트
r.zadd("leaderboard", {"player1": 1000, "player2": 1500})

# 상위 10명 조회
top_players = r.zrevrange("leaderboard", 0, 9, withscores=True)
# [('player2', 1500.0), ('player1', 1000.0)]

# 특정 플레이어 순위 조회
rank = r.zrevrank("leaderboard", "player1")  # 0-based index

5. Security and Regulatory Compliance

SQL Injection Prevention

# ❌ 취약한 코드 (절대 금지!)
user_input = request.GET['username']
query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)

# 공격 시나리오: user_input = "admin' OR '1'='1"
# 결과: SELECT * FROM users WHERE username = 'admin' OR '1'='1'
# → 모든 사용자 정보 노출!

# ✅ 안전한 코드 (파라미터화된 쿼리)
user_input = request.GET['username']
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (user_input,))

# ✅ ORM 사용 (권장)
from sqlalchemy import select
stmt = select(User).where(User.username == user_input)
result = session.execute(stmt)

Compliance with Korean Personal Information Protection Act

Encryption of Unique Identification Information (Required):

-- PostgreSQL pgcrypto 확장 사용
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 암호화하여 저장
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    resident_number BYTEA  -- 주민등록번호 암호화
);

-- 데이터 삽입
INSERT INTO users (name, resident_number)
VALUES ('홍길동', pgp_sym_encrypt('901231-1234567', 'encryption_key'));

-- 데이터 조회
SELECT 
    name,
    pgp_sym_decrypt(resident_number, 'encryption_key') AS resident_number
FROM users;

-- ✅ 권장: 환경 변수로 키 관리, 키 로테이션 주기적 실시

Access Log Retention (3 Years):

-- 접근 로그 테이블
CREATE TABLE access_logs (
    log_id BIGSERIAL PRIMARY KEY,
    user_id INT,
    table_name VARCHAR(100),
    action VARCHAR(50),  -- SELECT, INSERT, UPDATE, DELETE
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET
);

-- 파티셔닝으로 관리 (연도별)
CREATE TABLE access_logs (
    log_id BIGSERIAL,
    user_id INT,
    table_name VARCHAR(100),
    action VARCHAR(50),
    accessed_at TIMESTAMP NOT NULL,
    ip_address INET
) PARTITION BY RANGE (accessed_at);

-- 연도별 파티션 생성
CREATE TABLE access_logs_2025 PARTITION OF access_logs
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE access_logs_2026 PARTITION OF access_logs
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Password Encryption (One-Way):

import bcrypt

# ✅ 안전한 비밀번호 저장
password = "user_password_123"
hashed = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt())

# DB 저장
INSERT INTO users (username, password_hash)
VALUES ('john', hashed)

# 로그인 시 검증
stored_hash = db.query("SELECT password_hash FROM users WHERE username = 'john'")
is_valid = bcrypt.checkpw(password.encode('utf-8'), stored_hash)

6. Performance Tuning and Monitoring

Key PostgreSQL Settings

# postgresql.conf 주요 파라미터

# 메모리 설정 (서버 RAM의 25%)
shared_buffers = 4GB

# 작업 메모리 (쿼리당, 동시 연결 수 고려)
work_mem = 64MB

# 유지보수 작업 메모리 (VACUUM, CREATE INDEX)
maintenance_work_mem = 1GB

# WAL 설정 (Write-Ahead Logging)
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

# 체크포인트 (데이터 안정성 vs 성능)
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# 연결 풀링
max_connections = 200

# 쿼리 플래너
effective_cache_size = 12GB  # OS 캐시 포함 총 메모리의 50-75%
random_page_cost = 1.1  # SSD일 경우 기본 4.0에서 낮춤

# 로깅 (느린 쿼리 추적)
log_min_duration_statement = 1000  # 1초 이상 쿼리 로깅
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Monitoring Queries

-- 1. 느린 쿼리 상위 10개 (pg_stat_statements 확장 필요)
SELECT 
    query,
    calls,
    total_exec_time / 1000 AS total_sec,
    mean_exec_time / 1000 AS mean_sec,
    max_exec_time / 1000 AS max_sec
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 2. 테이블별 캐시 히트율
SELECT 
    schemaname,
    relname,
    heap_blks_read,
    heap_blks_hit,
    ROUND(
        100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0),
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

-- 3. 인덱스 사용률
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- 사용되지 않는 인덱스
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. 블로킹 세션 (락 대기)
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 5. 테이블별 VACUUM 필요성
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC;

Usage Guide

Quick Start

1. Query Optimization Request

"PostgreSQL에서 다음 쿼리가 느립니다. 최적화해주세요:

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.name
HAVING COUNT(o.order_id) > 5
ORDER BY order_count DESC;

테이블 크기:
- customers: 100만 행
- orders: 500만 행"

Expected Response:

EXPLAIN ANALYZE Result Analysis
Bottleneck Identification (Seq Scan, High Cost)
Index Recommendation (CREATE INDEX idx_customers_created ON customers(created_at))
Optimized Query Provided

2. Schema Design Request

"블로그 플랫폼의 데이터베이스 스키마를 설계해주세요.

요구사항:
- 사용자 (회원가입, 로그인)
- 게시글 (제목, 내용, 작성일)
- 댓글 (계층형, 대댓글 지원)
- 태그 (게시글에 여러 태그 가능)
- 좋아요 (사용자가 게시글에 좋아요)

예상 트래픽:
- 일일 게시글: 1,000개
- 일일 댓글: 5,000개
- 동시 접속자: 1,000명"

Expected Response:

ER Diagram
DDL for Each Table (CREATE TABLE Statement)
Index Strategy
Foreign Key Setup
Normalization Level (3NF Recommended)

3. Migration Plan Request

"MySQL 8.0에서 PostgreSQL 16으로 마이그레이션하려고 합니다.

현재 상황:
- DB 크기: 50GB
- 테이블 수: 80개
- 일일 쓰기: 100만 건
- 다운타임 허용: 최대 4시간

알려주세요:
1. 단계별 마이그레이션 계획
2. 주의사항 (MySQL vs PostgreSQL 차이)
3. 데이터 무결성 검증 방법"

4. Security Review Request

"금융 서비스를 개발 중입니다. 보안 체크리스트를 만들어주세요.

환경:
- PostgreSQL 16
- AWS RDS
- Django ORM

준수해야 할 규정:
- 전자금융거래법
- 개인정보보호법"

Examples

Example 1: Slow Query Optimization

Scenario: E-commerce order status query takes 15 seconds

Input:

-- 현재 쿼리 (PostgreSQL)
EXPLAIN ANALYZE
SELECT 
    p.product_name,
    c.category_name,
    COUNT(oi.order_id) AS total_orders,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.status = 'completed'
GROUP BY p.product_name, c.category_name
ORDER BY total_revenue DESC
LIMIT 100;

-- 테이블 크기
-- products: 10,000 행
-- categories: 50 행
-- order_items: 5,000,000 행
-- orders: 2,000,000 행

EXPLAIN ANALYZE Result:

Limit  (cost=850000.12..850000.37 rows=100 width=68) (actual time=15234.521..15234.612 rows=100 loops=1)
  ->  Sort  (cost=850000.12..850500.23 rows=200000 width=68) (actual time=15234.519..15234.565 rows=100 loops=1)
        Sort Key: (sum((oi.quantity * oi.price))) DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  HashAggregate  (cost=800000.00..820000.00 rows=200000 width=68) (actual time=14500.234..15100.876 rows=9850 loops=1)
              ->  Hash Join  (cost=50000.00..750000.00 rows=1000000 width=40) (actual time=234.123..12345.678 rows=987654 loops=1)
                    Hash Cond: (oi.product_id = p.product_id)
                    ->  Hash Join  (cost=30000.00..650000.00 rows=1000000 width=24) (actual time=123.456..10234.567 rows=987654 loops=1)
                          Hash Cond: (oi.order_id = o.order_id)
                          ->  Seq Scan on order_items oi  (cost=0.00..150000.00 rows=5000000 width=24) (actual time=0.023..3456.789 rows=5000000 loops=1)
                          ->  Hash  (cost=25000.00..25000.00 rows=400000 width=8) (actual time=123.234..123.234 rows=456789 loops=1)
                                Buckets: 65536  Batches: 8  Memory Usage: 4567kB
                                ->  Seq Scan on orders o  (cost=0.00..25000.00 rows=400000 width=8) (actual time=0.012..89.123 rows=456789 loops=1)
                                      Filter: ((created_at >= '2024-01-01'::date) AND (created_at <= '2024-12-31'::date) AND (status = 'completed'::text))
                                      Rows Removed by Filter: 1543211
                    ->  Hash  (cost=15000.00..15000.00 rows=10000 width=24) (actual time=45.678..45.678 rows=10000 loops=1)
                          Buckets: 16384  Batches: 1  Memory Usage: 789kB
                          ->  Hash Join  (cost=1.25..15000.00 rows=10000 width=24) (actual time=0.034..34.567 rows=10000 loops=1)
                                Hash Cond: (p.category_id = c.category_id)
                                ->  Seq Scan on products p  (cost=0.00..250.00 rows=10000 width=20) (actual time=0.012..12.345 rows=10000 loops=1)
                                ->  Hash  (cost=1.00..1.00 rows=50 width=12) (actual time=0.018..0.018 rows=50 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                      ->  Seq Scan on categories c  (cost=0.00..1.00 rows=50 width=12) (actual time=0.003..0.008 rows=50 loops=1)
Planning Time: 2.345 ms
Execution Time: 15234.789 ms

Analysis:

Bottleneck #1: Seq Scan on orders - Full scan of 2 million rows (1.54 million rows removed by Filter)
Bottleneck #2: Seq Scan on order_items - Full scan of 5 million rows
Bottleneck #3: HashAggregate - Aggregation of 980,000 rows

Optimization Strategy:

Step 1: Create Index

-- orders 테이블에 복합 인덱스
CREATE INDEX idx_orders_completed ON orders(created_at, status) 
WHERE status = 'completed';

-- order_items 테이블에 복합 인덱스
CREATE INDEX idx_order_items_lookup ON order_items(order_id, product_id);

-- products 테이블에 인덱스 (이미 있을 가능성)
CREATE INDEX idx_products_category ON products(category_id);

Step 2: Rewrite Query

-- 최적화된 쿼리
EXPLAIN ANALYZE
SELECT 
    p.product_name,
    c.category_name,
    COUNT(*) AS total_orders,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories c ON p.category_id = c.category_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.status = 'completed'
GROUP BY p.product_id, p.product_name, c.category_name
ORDER BY total_revenue DESC
LIMIT 100;

EXPLAIN ANALYZE After Optimization (Estimated):

Limit  (cost=12000.12..12000.37 rows=100 width=68) (actual time=450.123..450.234 rows=100 loops=1)
  ->  Sort  (cost=12000.12..12500.23 rows=200000 width=68) (actual time=450.121..450.178 rows=100 loops=1)
        Sort Key: (sum((oi.quantity * oi.price))) DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  HashAggregate  (cost=10000.00..11000.00 rows=200000 width=68) (actual time=380.234..420.567 rows=9850 loops=1)
              ->  Hash Join  (cost=3000.00..8000.00 rows=1000000 width=40) (actual time=45.123..320.456 rows=987654 loops=1)
                    Hash Cond: (p.category_id = c.category_id)
                    ->  Hash Join  (cost=2998.75..7000.00 rows=1000000 width=32) (actual time=45.089..280.345 rows=987654 loops=1)
                          Hash Cond: (oi.product_id = p.product_id)
                          ->  Nested Loop  (cost=2748.75..5500.00 rows=1000000 width=24) (actual time=25.123..200.234 rows=987654 loops=1)
                                ->  Bitmap Heap Scan on orders o  (cost=2748.32..4500.00 rows=400000 width=8) (actual time=25.056..80.123 rows=456789 loops=1)
                                      Recheck Cond: ((created_at >= '2024-01-01'::date) AND (created_at <= '2024-12-31'::date) AND (status = 'completed'::text))
                                      Heap Blocks: exact=12345
                                      ->  Bitmap Index Scan on idx_orders_completed  (cost=0.00..2648.32 rows=400000 width=0) (actual time=20.123..20.123 rows=456789 loops=1)
                                            Index Cond: ((created_at >= '2024-01-01'::date) AND (created_at <= '2024-12-31'::date) AND (status = 'completed'::text))
                                ->  Index Scan using idx_order_items_lookup on order_items oi  (cost=0.43..1.50 rows=2 width=24) (actual time=0.001..0.001 rows=2 loops=456789)
                                      Index Cond: (order_id = o.order_id)
                          ->  Hash  (cost=250.00..250.00 rows=10000 width=20) (actual time=19.890..19.890 rows=10000 loops=1)
                                Buckets: 16384  Batches: 1  Memory Usage: 789kB
                                ->  Seq Scan on products p  (cost=0.00..250.00 rows=10000 width=20) (actual time=0.012..12.345 rows=10000 loops=1)
                    ->  Hash  (cost=1.00..1.00 rows=50 width=12) (actual time=0.018..0.018 rows=50 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
                          ->  Seq Scan on categories c  (cost=0.00..1.00 rows=50 width=12) (actual time=0.003..0.008 rows=50 loops=1)
Planning Time: 2.123 ms
Execution Time: 450.456 ms  -- 15초 → 0.45초로 개선! (33배 향상)

Result:

Execution Time: 15,234ms → 450ms (97% decrease, 33x improvement)
Rows Read: 7.5 million rows → 1.46 million rows (81% decrease)
Index Scan: Seq Scan → Index Scan transition
Memory Usage: Slight increase (Hash Join), but disk I/O greatly reduced

Example 2: E-commerce Database Design

Scenario: Small to medium-sized e-commerce platform (10,000 daily orders, 100,000 products)

Requirements:

Member Management (Individual/Corporate Members, SNS Login)
Product Management (Categories, Options, Inventory)
Order Processing (Cart, Payment, Shipping)
Reviews and Ratings
Coupons and Promotions

Design Result:

-- 1. 회원 관리
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    user_type VARCHAR(20) DEFAULT 'individual' CHECK (user_type IN ('individual', 'business')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_addresses (
    address_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    address_type VARCHAR(20) CHECK (address_type IN ('shipping', 'billing')),
    recipient_name VARCHAR(100),
    phone VARCHAR(20),
    postal_code VARCHAR(10),
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city VARCHAR(50),
    state VARCHAR(50),
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_addresses_user ON user_addresses(user_id);

-- 2. 상품 관리
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    parent_category_id INT REFERENCES categories(category_id),
    category_name VARCHAR(100) NOT NULL,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(category_id),
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = TRUE;

CREATE TABLE product_options (
    option_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id) ON DELETE CASCADE,
    option_name VARCHAR(50) NOT NULL,  -- 색상, 사이즈 등
    option_value VARCHAR(50) NOT NULL,  -- 빨강, L 등
    price_adjustment DECIMAL(10,2) DEFAULT 0,
    stock_quantity INT DEFAULT 0,
    UNIQUE (product_id, option_name, option_value)
);

CREATE INDEX idx_product_options_product ON product_options(product_id);

-- 3. 주문 처리
CREATE TABLE carts (
    cart_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart_items (
    cart_item_id SERIAL PRIMARY KEY,
    cart_id INT REFERENCES carts(cart_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id),
    option_id INT REFERENCES product_options(option_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    order_status VARCHAR(20) DEFAULT 'pending' 
        CHECK (order_status IN ('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    final_amount DECIMAL(10,2) NOT NULL,
    shipping_address_id INT REFERENCES user_addresses(address_id),
    payment_method VARCHAR(50),
    payment_status VARCHAR(20) DEFAULT 'pending'
        CHECK (payment_status IN ('pending', 'completed', 'failed', 'refunded')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_orders_created ON orders(created_at);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id),
    option_id INT REFERENCES product_options(option_id),
    product_name VARCHAR(200),  -- 스냅샷 (상품 정보 변경 시 주문 기록 보존)
    option_name VARCHAR(50),
    unit_price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    subtotal DECIMAL(10,2) NOT NULL
);

CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- 4. 리뷰 및 평점
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id) ON DELETE CASCADE,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    order_item_id INT REFERENCES order_items(order_item_id),
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (order_item_id)  -- 주문 항목당 1개 리뷰만
);

CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_reviews_user ON reviews(user_id);

-- 5. 쿠폰 및 프로모션
CREATE TABLE coupons (
    coupon_id SERIAL PRIMARY KEY,
    coupon_code VARCHAR(50) UNIQUE NOT NULL,
    discount_type VARCHAR(20) CHECK (discount_type IN ('percentage', 'fixed_amount')),
    discount_value DECIMAL(10,2) NOT NULL,
    min_purchase_amount DECIMAL(10,2) DEFAULT 0,
    max_discount_amount DECIMAL(10,2),
    usage_limit INT,
    usage_count INT DEFAULT 0,
    valid_from TIMESTAMP,
    valid_until TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE user_coupons (
    user_coupon_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    coupon_id INT REFERENCES coupons(coupon_id),
    used_at TIMESTAMP,
    order_id INT REFERENCES orders(order_id),
    UNIQUE (user_id, coupon_id)
);

-- 6. 통계 및 집계 (머티리얼라이즈드 뷰)
CREATE MATERIALIZED VIEW product_stats AS
SELECT 
    p.product_id,
    p.product_name,
    COUNT(DISTINCT oi.order_id) AS total_orders,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.subtotal) AS total_revenue,
    AVG(r.rating) AS avg_rating,
    COUNT(r.review_id) AS review_count
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name;

CREATE UNIQUE INDEX idx_product_stats_id ON product_stats(product_id);

-- 주기적 갱신 (예: 매일 자정)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;

Indexing Strategy Summary:

Foreign Key Index: All foreign key columns (JOIN optimization)
Search Condition Index: order_status, is_active (frequent in WHERE clause)
Time-based index: created_at (date range query)
Partial index: is_active = TRUE (active products only)
Unique index: email, order_number (duplicate prevention)

Performance estimates:

Order lookup: < 50ms (index used)
Product search: < 100ms (category + full-text search)
Review lookup: < 30ms (product_id index)
Statistics lookup: < 10ms (materialized view)

Example 3: MongoDB sharding design (large-scale log system)

Scenario: Storing IoT sensor logs (100 million per day, 1-year retention period)

Requirements:

Insert more than 1,000 records per second
Frequent lookups by sensor/date
Data size: approximately 5TB per year
High availability (24/7 operation)

Design result:

// 1. 샤드 키 선택
// sensor_id + timestamp 복합 키 (시간 기반 + 해시 분산)

// 컬렉션 정의
db.createCollection("sensor_logs", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["sensor_id", "timestamp", "value"],
      properties: {
        sensor_id: { bsonType: "string" },
        timestamp: { bsonType: "date" },
        sensor_type: { bsonType: "string", enum: ["temperature", "humidity", "pressure"] },
        value: { bsonType: "double" },
        location: {
          bsonType: "object",
          properties: {
            type: { enum: ["Point"] },
            coordinates: { bsonType: "array" }
          }
        },
        metadata: { bsonType: "object" }
      }
    }
  }
})

// 2. 샤딩 활성화
sh.enableSharding("iot_database")

// 3. 샤드 키 설정 (해시 샤딩 - 균등 분산)
sh.shardCollection(
  "iot_database.sensor_logs",
  { sensor_id: "hashed", timestamp: 1 }
)

// 4. 인덱스 생성
db.sensor_logs.createIndex({ sensor_id: 1, timestamp: -1 })  // 센서별 시간 순 조회
db.sensor_logs.createIndex({ sensor_type: 1, timestamp: -1 })  // 타입별 조회
db.sensor_logs.createIndex({ "location": "2dsphere" })  // 지리 쿼리
db.sensor_logs.createIndex({ timestamp: 1 }, { expireAfterSeconds: 31536000 })  // TTL 1년

// 5. 쓰기 관심사 (Write Concern) 설정
db.sensor_logs.insert(
  { /* 문서 */ },
  { writeConcern: { w: "majority", wtimeout: 5000 } }
)

// 6. 읽기 선호도 (Read Preference) 설정
db.sensor_logs.find({ sensor_id: "sensor_001" })
  .readPref("secondaryPreferred")  // 복제본 읽기 우선, 없으면 Primary

Sharding architecture:

Client
  ↓
mongos (쿼리 라우터)
  ↓
Config Servers (샤드 메타데이터)
  ↓
Shard 1 (센서 1-1000)   Shard 2 (센서 1001-2000)   Shard 3 (센서 2001-3000)
   ↓                        ↓                          ↓
Primary + 2 Secondaries  Primary + 2 Secondaries    Primary + 2 Secondaries

Query example:

// 1. 특정 센서의 최근 1시간 데이터 (샤드 키 포함, 단일 샤드 조회)
db.sensor_logs.find({
  sensor_id: "sensor_001",
  timestamp: { $gte: ISODate("2025-01-15T09:00:00Z") }
}).sort({ timestamp: -1 })

// 2. 전체 센서 평균값 (모든 샤드 조회, 병렬 처리)
db.sensor_logs.aggregate([
  { $match: { timestamp: { $gte: ISODate("2025-01-15T00:00:00Z") } } },
  { $group: {
      _id: "$sensor_type",
      avg_value: { $avg: "$value" },
      count: { $sum: 1 }
  }}
])

// 3. 지리 기반 쿼리 (특정 지역 내 센서)
db.sensor_logs.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [127.0276, 37.4979] },  // 서울
      $maxDistance: 5000  // 5km 반경
    }
  },
  timestamp: { $gte: ISODate("2025-01-15T00:00:00Z") }
})

// 4. 배치 삽입 (성능 최적화)
db.sensor_logs.insertMany([
  { sensor_id: "sensor_001", timestamp: ISODate(), value: 25.3, sensor_type: "temperature" },
  { sensor_id: "sensor_002", timestamp: ISODate(), value: 60.1, sensor_type: "humidity" },
  // ... 1000개 배치
], { ordered: false })  // 순서 무관, 병렬 처리

Performance monitoring:

// 샤드 분산 확인
db.sensor_logs.getShardDistribution()

// 청크 분포 확인
sh.status()

// 현재 실행 중인 쿼리
db.currentOp()

// 느린 쿼리 로그
db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().sort({ ts: -1 }).limit(5)

Results:

Insert speed: average 2,000 docs/sec (10,000+ with batch inserts)
Lookup speed: < 50ms (including shard key), < 500ms (all shards)
Storage space: approximately 1.7TB per shard (3 shards = 5TB)
High availability: 3 replicas per shard (Primary + 2 Secondary)

Best Practices

Database selection guide

Use case	Recommended DB	Reason
OLTP (banking, e-commerce)	PostgreSQL, MySQL	ACID guarantees, transaction stability
Large-scale reads (SNS feed)	Redis (cache) + MySQL	Read load distribution
Document storage (CMS, blog)	MongoDB	Flexible schema, JSON-friendly
Real-time analytics (dashboard)	TimescaleDB, InfluxDB	Time-series optimization
Session storage (login)	Redis	Fast memory access
Geographic information (delivery app)	PostgreSQL (PostGIS)	GIS features
Graph relationships (SNS friends)	Neo4j	Relationship traversal optimization
Search engine (product search)	Elasticsearch	Full-text search, autocomplete
Distributed SQL (global service)	CockroachDB, TiDB	Cross-region replication, scalability

✅ DO

1. Index strategy

-- ✅ 복합 인덱스는 선택도 높은 컬럼을 앞에
CREATE INDEX idx_orders_lookup ON orders(user_id, created_at);

-- ✅ 부분 인덱스로 공간 절약
CREATE INDEX idx_active_products ON products(product_id) WHERE is_active = TRUE;

-- ✅ 커버링 인덱스 (Index Only Scan)
CREATE INDEX idx_orders_covering ON orders(user_id, created_at) INCLUDE (total_amount);

2. Query optimization

-- ✅ EXISTS 사용 (서브쿼리 최적화)
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.product_id = p.product_id
);

-- ✅ LIMIT으로 불필요한 데이터 조회 방지
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- ✅ 집계 쿼리는 인덱스 활용
SELECT user_id, COUNT(*) FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY user_id;

3. Transaction management

# ✅ 짧은 트랜잭션 유지
with conn.cursor() as cur:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    cur.execute("COMMIT")

# ✅ 타임아웃 설정
cur.execute("SET statement_timeout = '5s'")

4. Connection pooling

# ✅ 연결 풀 사용 (psycopg2)
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    minconn=5,
    maxconn=20,
    host='localhost',
    database='mydb'
)

conn = connection_pool.getconn()
# ... 쿼리 실행
connection_pool.putconn(conn)

5. Security

# ✅ 파라미터화된 쿼리
cur.execute("SELECT * FROM users WHERE email = %s", (user_email,))

# ✅ 최소 권한 원칙
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;
-- DELETE 권한은 부여하지 않음

❌ DON'T

1. Anti-patterns

-- ❌ SELECT * (불필요한 컬럼 조회)
SELECT * FROM orders;  -- 100개 컬럼 중 3개만 필요한데...

-- ✅ 필요한 컬럼만 명시
SELECT order_id, total_amount, created_at FROM orders;

-- ❌ OR 대신 IN 사용 권장
SELECT * FROM products WHERE category_id = 1 OR category_id = 2 OR category_id = 3;

-- ✅ IN 사용 (인덱스 활용 가능)
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- ❌ LIKE 앞에 와일드카드 (인덱스 사용 불가)
SELECT * FROM products WHERE product_name LIKE '%카메라%';

-- ✅ 전문 검색 인덱스 사용
SELECT * FROM products WHERE to_tsvector('korean', product_name) @@ to_tsquery('korean', '카메라');

2. Transaction misuse

# ❌ 긴 트랜잭션 (락 유지 시간 증가)
cur.execute("BEGIN")
cur.execute("SELECT * FROM orders")  # 100만 행 조회
time.sleep(10)  # 외부 API 호출
cur.execute("UPDATE orders SET status = 'processed'")
cur.execute("COMMIT")

# ✅ 트랜잭션 분리
data = cur.execute("SELECT * FROM orders").fetchall()
processed_data = call_external_api(data)  # 트랜잭션 밖에서 실행

cur.execute("BEGIN")
cur.execute("UPDATE orders SET status = 'processed'")
cur.execute("COMMIT")

3. N+1 problem

# ❌ N+1 쿼리 (1 + N번 쿼리)
orders = cur.execute("SELECT * FROM orders").fetchall()
for order in orders:
    customer = cur.execute("SELECT * FROM customers WHERE id = %s", (order['customer_id'],)).fetchone()

# ✅ JOIN 사용 (1번 쿼리)
result = cur.execute("""
    SELECT o.*, c.name, c.email
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
""").fetchall()

4. Index misuse

-- ❌ 함수 사용 (인덱스 무효화)
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- ✅ 함수 기반 인덱스 또는 데이터 정규화
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 또는
SELECT * FROM users WHERE email = 'john@example.com';  -- 데이터를 소문자로 저장

5. Security vulnerabilities

# ❌ SQL Injection 취약
query = f"SELECT * FROM users WHERE username = '{user_input}'"
cur.execute(query)

# ✅ 파라미터화된 쿼리
query = "SELECT * FROM users WHERE username = %s"
cur.execute(query, (user_input,))

Troubleshooting

Issue 1: Slow query response time (> 1 second)

Symptoms:

A specific query consistently takes more than 1 second
Delayed user page loading

Diagnosis Steps:

-- 1. EXPLAIN ANALYZE 실행
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders WHERE user_id = 123;

-- 2. 실행 계획 확인
-- - Seq Scan인지 Index Scan인지
-- - Cost 값이 높은 부분
-- - Actual Time vs Estimated Rows 차이

-- 3. 인덱스 존재 확인
SELECT * FROM pg_indexes WHERE tablename = 'orders';

-- 4. 통계 정보 업데이트 여부
SELECT last_analyze FROM pg_stat_user_tables WHERE relname = 'orders';

Solution:

-- A. 인덱스 추가
CREATE INDEX idx_orders_user ON orders(user_id);

-- B. 통계 정보 업데이트
ANALYZE orders;

-- C. 쿼리 재작성 (필요 시)
-- 서브쿼리 → JOIN 변경
-- OR → IN 변경

Prevention:

Run regular ANALYZE (enable Autovacuum)
Monitor slow query logs
Periodically review index usage

Issue 2: Deadlock Occurrence

Symptoms:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890
Process 67890 waits for ShareLock on transaction 12345

Diagnosis Steps:

-- 1. 데드락 로그 확인
SELECT * FROM pg_stat_database WHERE datname = 'mydb';

-- 2. 현재 락 상황 조회
SELECT 
    locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE NOT granted
ORDER BY pid;

-- 3. 블로킹 세션 확인
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted AND blocking_locks.granted;

Solution:

-- A. 일관된 락 순서 (애플리케이션 레벨)
-- 항상 ID 순서대로 락 획득
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- B. 타임아웃 설정
SET lock_timeout = '5s';
SET statement_timeout = '10s';

-- C. Isolation Level 낮춤 (신중히)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Prevention:

Minimize transactions (keep them short)
Maintain consistent lock ordering
Use FOR UPDATE only when necessary

Issue 3: High CPU Usage (> 80%)

Symptoms:

Database server CPU usage consistently high
Overall system performance degradation

Diagnosis Steps:

-- 1. 현재 실행 중인 쿼리 확인
SELECT pid, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

-- 2. 장시간 실행 쿼리
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

-- 3. CPU 사용량 높은 쿼리 (pg_stat_statements 필요)
SELECT 
    query,
    calls,
    total_exec_time / 1000 AS total_sec,
    mean_exec_time / 1000 AS mean_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Solution:

-- A. 비효율적 쿼리 종료
SELECT pg_terminate_backend(12345);  -- PID

-- B. 쿼리 최적화
-- EXPLAIN ANALYZE로 분석 → 인덱스 추가

-- C. 연결 제한 (Connection Pooling)
ALTER SYSTEM SET max_connections = 100;
SELECT pg_reload_conf();

Prevention:

Use a connection pool (PgBouncer, pgPool)
Regularly inspect slow queries
Resource monitoring (Prometheus + Grafana)

Issue 4: Insufficient Disk Space

Symptoms:

ERROR: could not extend file "base/16384/12345": No space left on device

Diagnosis Steps:

-- 1. 데이터베이스별 크기
SELECT 
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- 2. 테이블별 크기
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- 3. 인덱스 크기
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- 4. Bloat 확인 (오래된 버전)
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Solution:

-- A. VACUUM 실행 (공간 회수)
VACUUM FULL orders;  -- 주의: 테이블 락 발생, 다운타임 필요

-- 또는
VACUUM ANALYZE orders;  -- 락 없음, 점진적 회수

-- B. 오래된 데이터 아카이브
CREATE TABLE orders_2024_archive AS 
SELECT * FROM orders WHERE created_at < '2024-01-01';

DELETE FROM orders WHERE created_at < '2024-01-01';

VACUUM FULL orders;

-- C. 파티셔닝 (자동 정리)
-- 오래된 파티션 DROP으로 즉시 공간 회수

-- D. 사용하지 않는 인덱스 삭제
DROP INDEX idx_unused_index;

Prevention:

Enable and tune Autovacuum
Manage data with partitioning
Regular archive policy
Monitor disk usage

Issue 5: Connection Refused (Too Many Connections)

Symptoms:

FATAL: remaining connection slots are reserved for non-replication superuser connections
FATAL: sorry, too many clients already

Diagnosis Steps:

-- 1. 현재 연결 수
SELECT count(*) FROM pg_stat_activity;

-- 2. 최대 연결 수 확인
SHOW max_connections;

-- 3. 데이터베이스별 연결 수
SELECT datname, count(*) 
FROM pg_stat_activity 
GROUP BY datname 
ORDER BY count(*) DESC;

-- 4. 유휴 연결 확인
SELECT pid, state, state_change, query_start
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY state_change;

Solution:

-- A. 유휴 연결 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > interval '10 minutes';

-- B. max_connections 증가 (임시 조치)
ALTER SYSTEM SET max_connections = 200;
SELECT pg_reload_conf();
-- 주의: 재시작 필요

-- C. 연결 풀링 도입 (근본 해결)
-- PgBouncer 설정
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Prevention:

Use a connection pool in the application
Introduce PgBouncer or pgPool
Set connection timeout
Set up monitoring alerts

Security Guidelines

South Korean Regulatory Compliance Checklist

Personal Information Protection Act

Encryption: Encrypt and store unique identification information (resident registration number, passport number, etc.)
Access Control: Minimize access permissions to personal information processing systems
Log Retention: Retain access records for at least 3 years
Passwords: One-way encryption (bcrypt, scrypt)
Transmission Encryption: Use TLS/SSL
Backup Encryption: Encrypt backup data as well

-- 암호화 컬럼 예시
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    resident_number BYTEA,  -- 암호화 저장
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 암호화 저장
INSERT INTO users (name, resident_number)
VALUES ('홍길동', pgp_sym_encrypt('901231-1234567', current_setting('app.encryption_key')));

-- 복호화 조회 (권한 있는 사용자만)
SELECT name, pgp_sym_decrypt(resident_number, current_setting('app.encryption_key'))
FROM users
WHERE user_id = 123;

Electronic Financial Transactions Act

Transaction Integrity: Guarantee ACID
Backup Frequency: Daily backup + real-time replication
Recovery Time: RTO < 4 hours, RPO < 1 hour
Audit Logs: Retain records of all financial transactions

-- 금융 거래 로그 테이블
CREATE TABLE transaction_logs (
    log_id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR(50) NOT NULL,
    user_id INT,
    transaction_type VARCHAR(50),
    amount DECIMAL(15,2),
    status VARCHAR(20),
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_amount CHECK (amount >= 0)
);

-- 파티셔닝으로 관리
CREATE TABLE transaction_logs (
    log_id BIGSERIAL,
    transaction_id VARCHAR(50) NOT NULL,
    -- ... 기타 컬럼
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 월별 파티션
CREATE TABLE transaction_logs_2025_01 PARTITION OF transaction_logs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Performance Benchmarks

PostgreSQL Benchmark (based on TPC-C)

Hardware	Connections	TPS	Average Response Time
4 vCPU, 16GB RAM, SSD	50	1,200	15ms
8 vCPU, 32GB RAM, SSD	100	3,500	12ms
16 vCPU, 64GB RAM, NVMe	200	8,000	8ms

MongoDB Benchmark (based on YCSB)

Workload	Read	Write	Throughput (ops/sec)
Read-Heavy	95%	5%	15,000
Balanced	50%	50%	8,000
Write-Heavy	5%	95%	5,500

Redis Benchmark

Commands	QPS	Average Latency
GET	100,000	0.2ms
SET	80,000	0.3ms
INCR	100,000	0.2ms
LPUSH	70,000	0.4ms

Migration Guides

MySQL → PostgreSQL

Compatibility Issues:

-- 1. AUTO_INCREMENT → SERIAL
-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

-- 2. 날짜 함수
-- MySQL: NOW()
-- PostgreSQL: CURRENT_TIMESTAMP

-- 3. 문자열 연결
-- MySQL: CONCAT(a, b)
-- PostgreSQL: a || b

-- 4. LIMIT OFFSET
-- MySQL: LIMIT 10 OFFSET 20
-- PostgreSQL: LIMIT 10 OFFSET 20 (동일, 하지만 FETCH FIRST 권장)

-- 5. 대소문자 구분
-- MySQL: 기본적으로 대소문자 무시 (collation 의존)
-- PostgreSQL: 대소문자 구분 (ILIKE 사용)

Migration Tools:

# pgLoader 사용
apt-get install pgloader

# MySQL → PostgreSQL 마이그레이션
pgloader mysql://user:pass@localhost/mydb postgresql://user:pass@localhost/mydb

# 스키마만 마이그레이션
pgloader --schema-only mysql://... postgresql://...

# 데이터 검증
SELECT COUNT(*) FROM users;  -- MySQL
SELECT COUNT(*) FROM users;  -- PostgreSQL

Migration Checklist:

Schema Conversion (DDL Syntax Differences)
Data Type Mapping (DATETIME → TIMESTAMP)
Index Recreation
Trigger and Procedure Rewriting
Application Code Modification (SQL Syntax)
Performance Testing (EXPLAIN Analysis)
Rollback Plan Establishment

Version History

v1.0.0 (2025-01-15)

Initial Release
PostgreSQL, MySQL, MongoDB, Redis support
Includes query optimization, schema design, security guide
Added Korea legal compliance section

Future Plans

v1.1.0: Add Elasticsearch, ClickHouse
v1.2.0: Cloud DB (AWS RDS, Aurora) optimization guide
v1.3.0: Interactive query analysis (automatic EXPLAIN)
v2.0.0: Real-time performance monitoring integration

Additional Resources

Official Documentation

PostgreSQL 16: https://www.postgresql.org/docs/16/
MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/
MongoDB 7.0: https://docs.mongodb.com/manual/
Redis: https://redis.io/docs/

Learning Materials

"Designing Data-Intensive Applications" by Martin Kleppmann
"Database Internals" by Alex Petrov
CMU 15-445 (Database Systems): https://15445.courses.cs.cmu.edu/

Korean Community

PostgreSQL Korean User Group: https://postgresql.kr
MySQL Korean User Group: https://www.facebook.com/groups/mysqlkorea

Benchmark

TPC-C (OLTP): http://www.tpc.org/tpcc/
YCSB (NoSQL): https://github.com/brianfrankcooper/YCSB

License

This skill is distributed under the MIT License.

Support

Contact: db-expert-skill@example.com

Last Updated: 2025-01-15
Author: Claude Skills Generator
Version: 1.0.0

About this skill

Overview

Core Competencies

Supported Databases

When to Use

✅ When to use this skill

1. Solving Performance Problems

2. Database Design

3. Migration and Conversion

4. Security and Regulatory Compliance

5. Large-Scale Data Processing

❌ When this skill is not suitable

Core Capabilities

1. Query Optimization Engine

EXPLAIN Analysis and Interpretation

Index Strategy

2. Schema Design and Normalization

Guide by Normalization Stage

Denormalization Strategy (Performance Optimization)

3. Transactions and Concurrency Control

Isolation Level Comparison

Deadlock Resolution Strategies

MVCC (Multi-Version Concurrency Control)

4. NoSQL Database Guide

MongoDB Schema Design

Redis Usage Patterns

5. Security and Regulatory Compliance

SQL Injection Prevention

Compliance with Korean Personal Information Protection Act

6. Performance Tuning and Monitoring

Key PostgreSQL Settings

Monitoring Queries

Usage Guide

Quick Start

1. Query Optimization Request

2. Schema Design Request

3. Migration Plan Request

4. Security Review Request

Examples

Example 1: Slow Query Optimization

Example 2: E-commerce Database Design

Example 3: MongoDB sharding design (large-scale log system)

Best Practices

Database selection guide

✅ DO

1. Index strategy

2. Query optimization

3. Transaction management

4. Connection pooling

5. Security

❌ DON'T

1. Anti-patterns

2. Transaction misuse

3. N+1 problem

4. Index misuse

5. Security vulnerabilities

Troubleshooting

Issue 1: Slow query response time (> 1 second)

Issue 2: Deadlock Occurrence

Issue 3: High CPU Usage (> 80%)

Issue 4: Insufficient Disk Space

Issue 5: Connection Refused (Too Many Connections)

Security Guidelines

South Korean Regulatory Compliance Checklist

Personal Information Protection Act

Electronic Financial Transactions Act

Performance Benchmarks

PostgreSQL Benchmark (based on TPC-C)

MongoDB Benchmark (based on YCSB)

Redis Benchmark

Migration Guides

MySQL → PostgreSQL

Version History

v1.0.0 (2025-01-15)

Future Plans

Additional Resources

Official Documentation

Learning Materials

Korean Community