데이터베이스 완전 비교 2025: PostgreSQL vs MySQL vs MongoDB vs Redis — 선택 기준과 실전 가이드

         Consistency (C)
            /\
           /  \
          /    \
    CP   / CAP  \ CA
   시스템 / 불가능 \ 시스템
        /________\
  Partition(P)   Availability(A)

┌─────────────────────────────────────────────┐
│                 PostgreSQL                   │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  │
│  │ Backend  │  │ Backend  │  │ Backend  │  │
│  │ Process  │  │ Process  │  │ Process  │  │
│  └────┬─────┘  └────┬─────┘  └────┬─────┘  │
│       │             │             │         │
│  ┌────┴─────────────┴─────────────┴────┐    │
│  │          Shared Memory              │    │
│  │  ┌──────────┐  ┌──────────────┐    │    │
│  │  │ Shared   │  │    WAL       │    │    │
│  │  │ Buffers  │  │  Buffers     │    │    │
│  │  └──────────┘  └──────────────┘    │    │
│  └─────────────────────────────────────┘    │
│                                             │
│  ┌─────────────────────────────────────┐    │
│  │     Background Workers              │    │
│  │  WAL Writer | Checkpointer |        │    │
│  │  Autovacuum | Stats Collector       │    │
│  └─────────────────────────────────────┘    │
└─────────────────────────────────────────────┘

-- 트랜잭션 격리 수준 설정
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MVCC 동작 확인
BEGIN;
SELECT xmin, xmax, ctid, * FROM orders WHERE id = 1;
-- xmin: 생성 트랜잭션 ID, xmax: 삭제 트랜잭션 ID
-- ctid: 물리적 위치 (page, offset)
COMMIT;

-- JSONB 컬럼 생성
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN 인덱스 생성 (JSONB 검색 최적화)
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- JSONB 쿼리 예시
SELECT name, metadata->>'category' AS category,
       (metadata->'specs'->>'weight')::numeric AS weight
FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND (metadata->'specs'->>'weight')::numeric < 500;

-- JSONB 집계
SELECT metadata->>'category' AS category,
       COUNT(*),
       AVG((metadata->'price'->>'amount')::numeric) AS avg_price
FROM products
GROUP BY metadata->>'category';

-- pgvector 확장 설치
CREATE EXTENSION IF NOT EXISTS vector;

-- 벡터 컬럼이 있는 테이블
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536)  -- OpenAI text-embedding-3-small 차원
);

-- HNSW 인덱스 (빠른 근사 최근접 이웃 검색)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- IVFFlat 인덱스 (메모리 효율적)
CREATE INDEX ON documents
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

-- 코사인 유사도 기반 검색
SELECT id, title,
       1 - (embedding <=> query_embedding) AS similarity
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 10;

-- 하이브리드 검색 (벡터 + 전문 검색)
SELECT id, title,
       ts_rank(to_tsvector('korean', content), plainto_tsquery('korean', '검색어')) AS text_score,
       1 - (embedding <=> query_embedding) AS vector_score
FROM documents
WHERE to_tsvector('korean', content) @@ plainto_tsquery('korean', '검색어')
ORDER BY vector_score * 0.7 + text_score * 0.3 DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 트라이그램 인덱스
CREATE INDEX idx_products_name_trgm ON products
USING GIN (name gin_trgm_ops);

-- 유사도 검색 (오타 허용)
SELECT name, similarity(name, 'PostgreSLQ') AS sim
FROM products
WHERE name % 'PostgreSLQ'
ORDER BY sim DESC;

-- 범위 파티셔닝 예시
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    total_amount NUMERIC(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- 파티션 생성
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- 자동 파티션 관리 (pg_partman)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'order_date',
    p_type => 'native',
    p_interval => '3 months'
);

┌─────────────────────────────────────────────┐
│                MySQL Server                  │
│  ┌───────────────────────────────────────┐  │
│  │          Connection Pool              │  │
│  │   Thread 1 | Thread 2 | Thread N     │  │
│  └───────────────────────────────────────┘  │
│  ┌───────────────────────────────────────┐  │
│  │          Query Cache (제거됨 8.0+)     │  │
│  │          Parser -> Optimizer          │  │
│  └───────────────────────────────────────┘  │
│  ┌───────────────────────────────────────┐  │
│  │            InnoDB Engine              │  │
│  │  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │ Buffer   │  │  Change Buffer   │  │  │
│  │  │ Pool     │  │                  │  │  │
│  │  └──────────┘  └──────────────────┘  │  │
│  │  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │ Redo Log │  │  Undo Log        │  │  │
│  │  └──────────┘  └──────────────────┘  │  │
│  └───────────────────────────────────────┘  │
└─────────────────────────────────────────────┘

-- MySQL: UPSERT (INSERT ... ON DUPLICATE KEY)
INSERT INTO products (id, name, stock)
VALUES (1, 'Widget', 100)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);

-- PostgreSQL: UPSERT (INSERT ... ON CONFLICT)
INSERT INTO products (id, name, stock)
VALUES (1, 'Widget', 100)
ON CONFLICT (id)
DO UPDATE SET stock = products.stock + EXCLUDED.stock;

-- HeatWave ML로 모델 학습
CALL sys.ML_TRAIN('my_schema.customer_data',
    'churn_label',
    JSON_OBJECT('task', 'classification'),
    @model_handle);

-- 예측 실행
CALL sys.ML_PREDICT_ROW(
    JSON_OBJECT('age', 35, 'tenure', 24, 'monthly_charge', 79.99),
    @model_handle,
    @prediction);

SELECT @prediction;

-- Group Replication 상태 확인
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- 단일 프라이머리 모드 (기본, 권장)
-- 하나의 쓰기 노드, 나머지는 읽기 전용
SET GLOBAL group_replication_single_primary_mode = ON;

-- ProxySQL 라우팅 룰 설정
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES
    (1, 1, '^SELECT .* FOR UPDATE', 0),   -- 쓰기 그룹
    (2, 1, '^SELECT', 1);                  -- 읽기 그룹

-- 서버 그룹 설정
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
    (0, 'primary.mysql.local', 3306, 1),    -- 쓰기
    (1, 'replica1.mysql.local', 3306, 50),  -- 읽기
    (1, 'replica2.mysql.local', 3306, 50);  -- 읽기

// MongoDB 문서 구조 — 임베딩 패턴
db.orders.insertOne({
  orderId: 'ORD-2025-001',
  customer: {
    name: '김개발',
    email: 'kim@example.com',
    address: {
      street: '강남대로 123',
      city: '서울',
      zipCode: '06123',
    },
  },
  items: [
    {
      productId: 'PROD-001',
      name: '무선 키보드',
      price: 89000,
      quantity: 2,
    },
    {
      productId: 'PROD-002',
      name: '모니터 스탠드',
      price: 45000,
      quantity: 1,
    },
  ],
  payment: {
    method: 'card',
    status: 'completed',
    paidAt: ISODate('2025-03-15T10:30:00Z'),
  },
  totalAmount: 223000,
  createdAt: ISODate('2025-03-15T10:25:00Z'),
})

// 벡터 검색 인덱스 생성
db.collection.createSearchIndex({
  name: 'vector_index',
  definition: {
    mappings: {
      dynamic: true,
      fields: {
        embedding: {
          type: 'knnVector',
          dimensions: 1536,
          similarity: 'cosine',
        },
      },
    },
  },
})

// 벡터 검색 쿼리
db.documents.aggregate([
  {
    $vectorSearch: {
      index: 'vector_index',
      path: 'embedding',
      queryVector: queryEmbedding, // 1536차원 벡터
      numCandidates: 100,
      limit: 10,
      filter: { category: 'tech' },
    },
  },
  {
    $project: {
      title: 1,
      content: 1,
      score: { $meta: 'vectorSearchScore' },
    },
  },
])

// 월별 매출 분석 파이프라인
db.orders.aggregate([
  // 기간 필터
  {
    $match: {
      createdAt: {
        $gte: ISODate('2025-01-01'),
        $lt: ISODate('2026-01-01'),
      },
      'payment.status': 'completed',
    },
  },
  // 주문 항목 분해
  { $unwind: '$items' },
  // 월별 그룹핑
  {
    $group: {
      _id: {
        year: { $year: '$createdAt' },
        month: { $month: '$createdAt' },
        category: '$items.category',
      },
      totalRevenue: { $sum: { $multiply: ['$items.price', '$items.quantity'] } },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: { $multiply: ['$items.price', '$items.quantity'] } },
    },
  },
  // 정렬
  { $sort: { '_id.year': 1, '_id.month': 1 } },
  // 출력 형태 변환
  {
    $project: {
      _id: 0,
      period: { $concat: [{ $toString: '$_id.year' }, '-', { $toString: '$_id.month' }] },
      category: '$_id.category',
      totalRevenue: 1,
      orderCount: 1,
      avgOrderValue: { $round: ['$avgOrderValue', 0] },
    },
  },
])

// 샤드 키 선택 — 카디널리티, 분산도, 쿼리 패턴 고려
sh.enableSharding('ecommerce')

// 해시 샤딩 (균등 분산)
sh.shardCollection('ecommerce.orders', { customerId: 'hashed' })

// 범위 샤딩 (범위 쿼리 최적화)
sh.shardCollection('ecommerce.logs', { timestamp: 1 })

// 존 샤딩 (지역 기반 데이터 분리)
sh.addShardTag('shard-kr', 'ASIA')
sh.addShardTag('shard-us', 'AMERICAS')
sh.addTagRange('ecommerce.users', { region: 'KR' }, { region: 'KS' }, 'ASIA')

-- Strings: 캐시, 세션, 카운터
SET user:session:abc123 "session_data" EX 3600
INCR page:views:homepage

-- Hashes: 객체 저장
HSET user:1001 name "김개발" email "kim@dev.com" login_count 42
HINCRBY user:1001 login_count 1

-- Sorted Sets: 리더보드, 랭킹
ZADD leaderboard 9500 "player:alice"
ZADD leaderboard 8700 "player:bob"
ZADD leaderboard 9200 "player:charlie"
ZREVRANGE leaderboard 0 9 WITHSCORES  -- Top 10

-- Streams: 이벤트 스트리밍
XADD mystream * sensor_id "temp-01" temperature "23.5" humidity "45"
XREAD COUNT 10 BLOCK 5000 STREAMS mystream 0

-- HyperLogLog: 고유 카운트 (근사치)
PFADD unique:visitors:2025-03-23 "user1" "user2" "user3"
PFCOUNT unique:visitors:2025-03-23

-- Redis JSON
JSON.SET product:001 $ '{"name":"무선 키보드","price":89000,"tags":["electronics","keyboard"],"specs":{"weight":350,"wireless":true}}'
JSON.GET product:001 $.specs.weight

-- Redis Search 인덱스 생성
FT.CREATE idx:products ON JSON PREFIX 1 product:
  SCHEMA
    $.name AS name TEXT SORTABLE
    $.price AS price NUMERIC SORTABLE
    $.tags[*] AS tags TAG
    $.specs.weight AS weight NUMERIC

-- 전문 검색
FT.SEARCH idx:products "@name:키보드 @price:[50000 100000]"

-- 벡터 검색 (Redis Stack)
FT.CREATE idx:docs ON HASH PREFIX 1 doc:
  SCHEMA
    title TEXT
    embedding VECTOR HNSW 6 TYPE FLOAT32 DIM 1536 DISTANCE_METRIC COSINE

-- KNN 검색
FT.SEARCH idx:docs "*=>[KNN 10 @embedding $query_vec AS score]"
  PARAMS 2 query_vec "\x00\x00..."
  SORTBY score
  RETURN 2 title score

-- Redis TimeSeries
TS.CREATE temperature:sensor1 RETENTION 86400000 LABELS sensor_id temp-01 location office
TS.ADD temperature:sensor1 * 23.5
TS.RANGE temperature:sensor1 - + AGGREGATION avg 60000  -- 1분 평균

# redis.conf 권장 설정
save 900 1       # 900초 동안 1개 이상 변경 시 RDB
save 300 10      # 300초 동안 10개 이상 변경 시 RDB
appendonly yes   # AOF 활성화
appendfsync everysec  # 1초마다 fsync (성능/안전 균형)

# 6노드 클러스터 생성 (3 마스터 + 3 레플리카)
redis-cli --cluster create \
  node1:6379 node2:6379 node3:6379 \
  node4:6379 node5:6379 node6:6379 \
  --cluster-replicas 1

┌───────────────────────────────────────────┐
│              DynamoDB Table                │
│  ┌─────────────────────────────────────┐  │
│  │  Partition Key (PK)  Sort Key (SK)  │  │
│  ├─────────────────────────────────────┤  │
│  │  USER#001            PROFILE        │  │
│  │  USER#001            ORDER#001      │  │
│  │  USER#001            ORDER#002      │  │
│  │  USER#002            PROFILE        │  │
│  │  USER#002            ORDER#001      │  │
│  └─────────────────────────────────────┘  │
│                                           │
│  GSI: OrderDate-Index                     │
│  ┌─────────────────────────────────────┐  │
│  │  OrderDate (PK)     UserId (SK)     │  │
│  └─────────────────────────────────────┘  │
└───────────────────────────────────────────┘

import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('EcommerceTable')

# 사용자 프로필 저장
table.put_item(Item={
    'PK': 'USER#001',
    'SK': 'PROFILE',
    'name': '김개발',
    'email': 'kim@dev.com',
    'type': 'USER'
})

# 같은 테이블에 주문 저장
table.put_item(Item={
    'PK': 'USER#001',
    'SK': 'ORDER#2025-03-15#ORD001',
    'items': [{'name': '키보드', 'price': 89000}],
    'total': 89000,
    'status': 'completed',
    'type': 'ORDER'
})

# 사용자의 모든 주문 조회 (Query)
response = table.query(
    KeyConditionExpression='PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues={
        ':pk': 'USER#001',
        ':sk': 'ORDER#'
    }
)

┌────────┐  ┌────────┐  ┌────────┐
│ Node A │──│ Node B │──│ Node C │
│ Token: │  │ Token: │  │ Token: │
│ 0-33   │  │ 34-66  │  │ 67-100 │
└───┬────┘  └───┬────┘  └───┬────┘
    │           │           │
    └───────────┼───────────┘
            Gossip Protocol

-- 키스페이스 생성 (복제 전략 포함)
CREATE KEYSPACE ecommerce WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'us-east': 3,
    'eu-west': 3
};

-- 테이블 생성 (파티션 키 + 클러스터링 키)
CREATE TABLE ecommerce.user_activity (
    user_id UUID,
    activity_date DATE,
    activity_time TIMESTAMP,
    activity_type TEXT,
    details MAP<TEXT, TEXT>,
    PRIMARY KEY ((user_id, activity_date), activity_time)
) WITH CLUSTERING ORDER BY (activity_time DESC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_size': 1,
                    'compaction_window_unit': 'DAYS'};

┌─────────────────────────────────────────────────┐
│                  Application Layer               │
└───────┬──────────┬──────────┬──────────┬────────┘
        │          │          │          │
   ┌────▼────┐ ┌──▼───┐ ┌───▼───┐ ┌───▼────┐
   │PostgreSQL│ │Redis │ │MongoDB│ │Elastic │
   │(주문/결제)│ │(캐시) │ │(상품)  │ │(검색)  │
   └─────────┘ └──────┘ └───────┘ └────────┘

쓰기 (Command):                   읽기 (Query):
┌──────────┐                     ┌──────────┐
│  Client   │                     │  Client   │
└────┬─────┘                     └────┬─────┘
     │                                │
┌────▼─────┐                     ┌────▼─────┐
│ Command  │                     │  Query   │
│ Service  │                     │ Service  │
└────┬─────┘                     └────┬─────┘
     │                                │
┌────▼─────┐  ──이벤트──▶  ┌─────▼─────┐
│PostgreSQL│              │  Redis /   │
│ (Source  │              │  MongoDB   │
│  of Truth)│              │ (Read Model)│
└──────────┘              └───────────┘

import redis
import psycopg2
import json

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

def get_product(product_id):
    # 1. 캐시 확인
    cache_key = f"product:{product_id}"
    cached = r.get(cache_key)
    if cached:
        return json.loads(cached)

    # 2. 캐시 미스 — DB 조회
    conn = psycopg2.connect("dbname=ecommerce")
    cur = conn.cursor()
    cur.execute("SELECT * FROM products WHERE id = %s", (product_id,))
    product = cur.fetchone()

    if product:
        # 3. 캐시에 저장 (TTL 1시간)
        r.setex(cache_key, 3600, json.dumps(product))

    return product

def update_product(product_id, data):
    # 1. DB 업데이트
    conn = psycopg2.connect("dbname=ecommerce")
    cur = conn.cursor()
    cur.execute("UPDATE products SET name=%s WHERE id=%s",
                (data['name'], product_id))
    conn.commit()

    # 2. 캐시 무효화
    r.delete(f"product:{product_id}")