SQL vs NoSQL: Kompletter Guide zur Datenbankwahl

Die Wahl zwischen SQL und NoSQL ist eine der wichtigsten Architekturentscheidungen.

Ueberblick

Der grundlegende Unterschied liegt in der Datenorganisation.

NoSQL-Datenbanktypen

NoSQL ist keine einzelne Technologie.

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 Vergleich

Aspekt	SQL	NoSQL
Datenmodell	Tabellen	Dokumente, Key-Value, Wide Column, Graph
Schema	Streng	Flexibel
Abfragesprache	SQL	DB-spezifisch
Skalierung	Vertikal	Horizontal
ACID	Vollstaendig	Variiert
Joins	Vollstaendig	Begrenzt
Konsistenz	Stark	Eventuell
Leistung	Komplexe Abfragen	Grosse Skala
Am besten fuer	Strukturierte Daten	Flexible Daten

ACID vs BASE

SQL folgt ACID, NoSQL oft 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 waehlen

Relationale Daten
Komplexe Abfragen
ACID-Transaktionen

NoSQL waehlen

Flexibles Schema
Hoher Schreibdurchsatz
Horizontale Skalierung

Code-Beispiele

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

Skalierungsstrategien

SQL

Hauptsaechlich vertikal.

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

Fuer horizontale Skalierung konzipiert.

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

Beliebte DB-Vergleich

Database          Type          Best For                         License
---------------------------------------------------------------------------
PostgreSQL        SQL           General purpose, JSONB, analytics  PostgreSQL (OSS)
MySQL             SQL           Web applications, WordPress        GPL / Commercial
SQLite            SQL           Embedded, mobile, serverless       Public Domain
MariaDB           SQL           MySQL drop-in replacement          GPL

MongoDB           Document      Flexible schema, content mgmt      SSPL
Redis             Key-Value     Caching, sessions, real-time       BSD (+ commercial)
DynamoDB          Key-Value     AWS serverless, auto-scaling       Proprietary
Cassandra         Wide-Column   Time-series, IoT, high write       Apache 2.0
Neo4j             Graph         Social networks, recommendations   GPL / Commercial
Elasticsearch     Search        Full-text search, logs, analytics  SSPL / Apache 2.0

CockroachDB       NewSQL        Global SQL, horizontal scale       BSL / Commercial
PlanetScale       NewSQL        Serverless MySQL (Vitess-based)    Proprietary
TiDB              NewSQL        HTAP, MySQL compatible             Apache 2.0
Supabase          SQL (PG)      Firebase alternative, PostgreSQL   Apache 2.0

Migrationsmuster

Viele Anwendungen verwenden beides.

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.

Haeufige Fragen

Beide zusammen verwenden?

Ja. Polyglot Persistence.

Ist NoSQL schneller?

Abhaengig vom Abfragemuster.

Fuer Startups?

PostgreSQL empfohlen.

MongoDB in Produktion?

Ja, weit verbreitet.

NewSQL?

Kombiniert ACID und horizontale Skalierbarkeit.

Bestes fuer REST API?

PostgreSQL.