SQL vs NoSQL：正しいデータベースを選ぶ完全ガイド

SQLとNoSQLデータベースの選択は最も重要なアーキテクチャ決定の一つです。

概要

根本的な違いはデータの組織化とクエリの方法にあります。

NoSQLデータベースの種類

NoSQLは単一の技術ではなく、異なるデータモデルのファミリーです。

NoSQL Database Types:

1. Document Databases (MongoDB, CouchDB, Firestore)
   - Store data as JSON/BSON documents
   - Flexible schema per document
   - Good for: content management, catalogs, user profiles
   - Example: { "name": "John", "orders": [...], "prefs": {...} }

2. Key-Value Stores (Redis, DynamoDB, Memcached)
   - Simple key → value pairs
   - Fastest reads/writes
   - Good for: caching, sessions, leaderboards, counters
   - Example: "user:123" → "{\"name\": \"John\"}"

3. Wide-Column Stores (Cassandra, ScyllaDB, HBase)
   - Tables with rows and dynamic columns
   - Optimized for writes and time-series data
   - Good for: IoT, logging, metrics, messaging
   - Example: row_key → {col1: val1, col2: val2, ...}

4. Graph Databases (Neo4j, ArangoDB, Amazon Neptune)
   - Nodes and edges (relationships are first-class)
   - Optimized for traversing relationships
   - Good for: social networks, recommendations, fraud detection
   - Example: (User)-[:FOLLOWS]->(User)

SQL vs NoSQL比較

項目	SQL	NoSQL
データモデル	テーブル（行と列）	ドキュメント、キーバリュー、ワイドカラム、グラフ
スキーマ	厳格	柔軟
クエリ言語	SQL（標準化）	DB固有
スケーリング	垂直スケーリング	水平スケーリング
ACIDトランザクション	完全ACID	DB依存
JOIN	完全JOIN対応	限定的
一貫性	強整合性	結果整合性
パフォーマンス	複雑クエリに優秀	大規模単純クエリに優秀
最適用途	構造化データ、複雑クエリ	柔軟データ、高書込み

ACID vs BASE

SQLはACIDモデル、NoSQLはBASEモデル。

ACID (SQL Databases):
  A - Atomicity:    All operations succeed or all fail (no partial updates)
  C - Consistency:  Data always moves from one valid state to another
  I - Isolation:    Concurrent transactions don't interfere with each other
  D - Durability:   Committed data survives system failures

BASE (Many NoSQL Databases):
  BA - Basically Available:  System guarantees availability (may serve stale data)
  S  - Soft state:           State may change over time (even without input)
  E  - Eventual consistency: System will become consistent eventually

Practical Impact:
  ACID: "Transfer $100 from Account A to Account B"
    → Both debit and credit happen, or neither does
    → No money is lost or created
    → Other transactions see either the before or after state

  BASE: "Update user profile across 3 data centers"
    → All 3 data centers will eventually have the update
    → For a brief period, different data centers may show different data
    → Acceptable for social media posts, not for bank transfers

SQLを選ぶ場合

構造化関連データ
複雑クエリ
ACIDトランザクション

NoSQLを選ぶ場合

柔軟スキーマ
高書込み
水平スケーリング

コード例

SQL (PostgreSQL)

-- PostgreSQL: Relational schema with strong typing
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  role        VARCHAR(20) DEFAULT 'user',
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title       VARCHAR(300) NOT NULL,
  content     TEXT NOT NULL,
  tags        TEXT[],                    -- PostgreSQL array type
  metadata    JSONB DEFAULT '{}',        -- Flexible JSON column!
  published   BOOLEAN DEFAULT false,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
CREATE INDEX idx_posts_metadata ON posts USING GIN(metadata);

-- Complex query: posts with author info and comment count
SELECT
  p.title,
  p.created_at,
  u.name AS author,
  COUNT(c.id) AS comment_count,
  p.metadata->>'views' AS views
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published = true
  AND p.tags @> ARRAY['javascript']
GROUP BY p.id, u.name
ORDER BY p.created_at DESC
LIMIT 20;

-- ACID Transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

NoSQL (MongoDB)

// MongoDB: Document-based, flexible schema
// No schema definition needed — just insert documents

// Insert a user with embedded data
db.users.insertOne({
  name: "Jane Smith",
  email: "jane@example.com",
  role: "admin",
  profile: {
    bio: "Full-stack developer",
    skills: ["TypeScript", "React", "Node.js"],
    social: {
      twitter: "@janesmith",
      github: "janesmith",
    },
  },
  preferences: {
    theme: "dark",
    notifications: true,
  },
  createdAt: new Date(),
});

// Insert posts (embedded or referenced)
db.posts.insertOne({
  userId: ObjectId("user123"),
  title: "Getting Started with MongoDB",
  content: "MongoDB stores data as documents...",
  tags: ["mongodb", "database", "nosql"],
  metadata: { views: 1523, likes: 42 },
  comments: [
    { user: "Bob", text: "Great article!", date: new Date() },
    { user: "Alice", text: "Very helpful", date: new Date() },
  ],
  published: true,
  createdAt: new Date(),
});

// Query with aggregation pipeline
db.posts.aggregate([
  { $match: { published: true, tags: "javascript" } },
  { $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "author"
  }},
  { $unwind: "$author" },
  { $project: {
      title: 1,
      "author.name": 1,
      commentCount: { $size: "$comments" },
      views: "$metadata.views"
  }},
  { $sort: { createdAt: -1 } },
  { $limit: 20 }
]);

NoSQL (Redis)

# Redis: Key-Value store (in-memory, ultra-fast)

# Simple key-value
SET user:123:name "Jane Smith"
GET user:123:name  # → "Jane Smith"

# Hash (object-like)
HSET user:123 name "Jane" email "jane@example.com" role "admin"
HGET user:123 name  # → "Jane"
HGETALL user:123    # → all fields

# List (queue/stack)
LPUSH notifications:user:123 "New follower: Bob"
LPUSH notifications:user:123 "New comment on your post"
LRANGE notifications:user:123 0 9  # Get latest 10

# Set (unique values)
SADD post:456:likes "user:123" "user:456" "user:789"
SCARD post:456:likes  # → 3 (count unique likes)
SISMEMBER post:456:likes "user:123"  # → 1 (true)

# Sorted Set (leaderboard)
ZADD leaderboard 1500 "player:alice"
ZADD leaderboard 2200 "player:bob"
ZADD leaderboard 1800 "player:charlie"
ZREVRANGE leaderboard 0 9 WITHSCORES  # Top 10 players

# TTL (auto-expiring keys — perfect for caching)
SET api:cache:/users "[\"data\"]" EX 300  # expires in 5 minutes
TTL api:cache:/users  # → seconds remaining

スケーリング戦略

SQLスケーリング

SQLは主に垂直スケーリング。

SQL Scaling Strategies:

1. Vertical Scaling (Scale Up)
   ├── More CPU cores
   ├── More RAM (buffer pool)
   ├── Faster SSD (NVMe)
   └── Effective up to ~1TB RAM, 128 cores

2. Read Replicas (Horizontal Read Scaling)
   Primary (writes) → Replica 1 (reads)
                    → Replica 2 (reads)
                    → Replica 3 (reads)

3. Connection Pooling (PgBouncer, ProxySQL)
   App → Pooler → Database (reuse connections)

4. Partitioning (Table-level)
   orders_2024, orders_2025, orders_2026

5. Sharding (Application-level)
   Users A-M → Shard 1
   Users N-Z → Shard 2
   (Complex, usually avoid if possible)

6. Managed Services
   - Supabase, Neon, PlanetScale (auto-scaling)
   - AWS Aurora, Google Cloud SQL

NoSQLスケーリング

NoSQLは水平スケーリング向けに設計。

NoSQL Scaling (Built-in Horizontal):

MongoDB (Sharding):
  Config Servers (metadata)
  ├── Shard 1: users with _id A-F
  ├── Shard 2: users with _id G-M
  ├── Shard 3: users with _id N-S
  └── Shard 4: users with _id T-Z
  Mongos Router → routes queries to correct shard

Redis (Cluster Mode):
  ├── Node 1: hash slots 0-5460
  ├── Node 2: hash slots 5461-10922
  └── Node 3: hash slots 10923-16383
  Each node has a replica for failover

Cassandra (Ring Architecture):
  ├── Node A: token range 0-25%
  ├── Node B: token range 25-50%
  ├── Node C: token range 50-75%
  └── Node D: token range 75-100%
  Replication factor: 3 (each row on 3 nodes)
  Add nodes → data automatically rebalances

移行パターン

多くのアプリケーションがSQLとNoSQLの両方を使用。

Polyglot Persistence Architecture:

                    ┌─────────────────────┐
                    │   Application Layer  │
                    └─────────┬───────────┘
                              │
          ┌───────────────────┼───────────────────┐
          │                   │                   │
  ┌───────▼───────┐  ┌───────▼───────┐  ┌───────▼───────┐
  │  PostgreSQL   │  │    Redis      │  │   MongoDB     │
  │               │  │               │  │               │
  │ - Users       │  │ - Sessions    │  │ - CMS content │
  │ - Orders      │  │ - Cache       │  │ - Product     │
  │ - Payments    │  │ - Rate limits │  │   catalogs    │
  │ - Inventory   │  │ - Leaderboard │  │ - User prefs  │
  │ - Reports     │  │ - Pub/Sub     │  │ - Event logs  │
  └───────────────┘  └───────────────┘  └───────────────┘
     Transactions       Performance        Flexibility

Use each database for what it does best.

よくある質問

SQLとNoSQLを一緒に使える？

はい。ポリグロットパーシステンスと呼ばれる一般的なパターンです。

NoSQLはSQLより速い？

クエリパターンに依存します。

スタートアップはSQL？NoSQL？

PostgreSQLから始めるべきです。

MongoDBは本番環境に適している？

はい。広く使用されています。

NewSQLは？

SQLのACIDとNoSQLのスケーラビリティを組み合わせ。

REST APIに最適なDB？

PostgreSQLが最も安全な選択です。