NoSQL vs SQL: A Comprehensive Comparison

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

-- Cassandra: Designed for massive write scale
-- This table handles millions of events per second
 
CREATE KEYSPACE analytics WITH REPLICATION = {
    'class': 'NetworkTopologyStrategy',
    'us-east': 3,
    'eu-central': 3,
    'ap-southeast': 3
};
 
-- Time-series events table
-- Partition key distributes load; clustering key orders within partition
CREATE TABLE analytics.events (
    event_date DATE,           -- Partition key: distributes by day
    event_bucket INT,          -- Sub-partition for high-volume days
    event_time TIMESTAMP,      -- Clustering key: orders within partition
    event_id TIMEUUID,
    user_id UUID,
    event_type TEXT,
    properties MAP<TEXT, TEXT>,
    PRIMARY KEY ((event_date, event_bucket), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC);
 
-- Why this works at scale:
-- 1. Each partition lives on a subset of nodes
-- 2. Writes go to any node (coordinator) and propagate
-- 3. Reads for a single partition hit known nodes
-- 4. Adding nodes automatically rebalances data
-- 5. Replication factor ensures availability
-- 6. No single point of failure
 
-- Query patterns that work:
SELECT * FROM events 
WHERE event_date = '2024-01-15' AND event_bucket = 42
AND event_time > '2024-01-15 10:00:00';
 
-- Query patterns that DON'T work (and shouldn't):
-- SELECT * FROM events WHERE event_type = 'purchase';
-- This would scan ALL partitions - design prohibits it

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

// MongoDB: Natural fit for varying product attributes
// Each product has exactly the attributes it needs
 
// Electronics product
{
    "_id": ObjectId("..."),
    "name": "UltraBook Pro 15",
    "category": "electronics",
    "brand": "TechCorp",
    "price": 1299.99,
    "attributes": {
        "screen_size": "15.6 inches",
        "resolution": "3840x2160",
        "processor": "Intel Core i7-12800H",
        "ram": "32GB DDR5",
        "storage": "1TB NVMe SSD",
        "battery_life": "12 hours",
        "weight": "1.8 kg",
        "ports": ["USB-C", "HDMI", "Thunderbolt 4"]
    }
}
 
// Clothing product - completely different attributes
{
    "_id": ObjectId("..."),
    "name": "Classic Wool Sweater",
    "category": "clothing",
    "brand": "FashionStyle",
    "price": 89.99,
    "attributes": {
        "material": "100% Merino Wool",
        "sizes": ["S", "M", "L", "XL"],
        "colors": ["Navy", "Charcoal", "Cream"],
        "care": ["Dry clean only", "Do not tumble dry"],
        "fit": "regular",
        "origin": "Italy"
    },
    "size_chart": {
        "S": { "chest": "36-38", "length": "26" },
        "M": { "chest": "38-40", "length": "27" },
        "L": { "chest": "40-42", "length": "28" }
    }
}
 
// Query across all products
db.products.find({ "price": { $lt: 100 } });
 
// Query category-specific attributes
db.products.find({ 
    "category": "electronics",
    "attributes.ram": { $regex: /32GB/ }
});

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

// User profiles with varying completeness and custom fields
 
// Minimal profile
{
    "_id": ObjectId("..."),
    "email": "minimal@example.com",
    "created_at": ISODate("2024-01-15")
}
 
// Complete profile with preferences
{
    "_id": ObjectId("..."),
    "email": "complete@example.com",
    "display_name": "Alice Developer",
    "avatar_url": "https://...",
    "bio": "Senior engineer passionate about databases",
    "location": {
        "city": "San Francisco",
        "country": "USA",
        "timezone": "America/Los_Angeles"
    },
    "social_links": {
        "github": "alice-dev",
        "twitter": "@alicedev",
        "linkedin": "alice-developer"
    },
    "preferences": {
        "theme": "dark",
        "language": "en",
        "notifications": {
            "email": true,
            "push": false,
            "sms": false
        },
        "privacy": {
            "show_email": false,
            "show_activity": true
        }
    },
    "custom_fields": {
        "favorite_language": "Rust",
        "years_experience": 12,
        "open_to_work": false
    },
    "created_at": ISODate("2024-01-15"),
    "updated_at": ISODate("2024-06-20")
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# Redis: In-memory key-value with sub-millisecond performance
 
# 1. Session Storage
SET session:user123 '{"user_id":"123","roles":["admin"],"expires":1705334400}' EX 3600
GET session:user123
 
# 2. Caching Database Results
SET cache:product:456 '{"name":"Widget","price":29.99}' EX 300
GET cache:product:456
 
# 3. Real-time Counters
INCR pageviews:homepage:2024-01-15
INCRBY downloads:file:789 5
 
# 4. Rate Limiting
SETEX ratelimit:api:user123 60 "15"  # 15 requests, expires in 60s
INCR ratelimit:api:user123
TTL ratelimit:api:user123
 
# 5. Leaderboards
ZADD leaderboard:game1 1500 "player1" 2300 "player2" 1800 "player3"
ZREVRANGE leaderboard:game1 0 9 WITHSCORES  # Top 10
 
# 6. Pub/Sub Messaging
PUBLISH channel:notifications '{"type":"alert","msg":"Server maintenance"}'
SUBSCRIBE channel:notifications
 
# Performance: 100K+ ops/second on single node
# Latency: sub-millisecond for most operations
# Perfect for: Hot data, caching, real-time features

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

// Neo4j: Native graph storage and query
 
// 1. Social Network: Friends of Friends
// Find friends of friends who work at the same company
MATCH (me:Person {id: 'user123'})-[:FRIEND]->(friend)-[:FRIEND]->(fof)
WHERE fof <> me 
  AND NOT (me)-[:FRIEND]->(fof)
  AND (fof)-[:WORKS_AT]->(:Company)<-[:WORKS_AT]-(me)
RETURN fof.name, COUNT(friend) AS mutual_friends
ORDER BY mutual_friends DESC
LIMIT 10;
 
// 2. Fraud Detection: Find Suspicious Patterns
// Identify accounts sharing phone numbers or addresses
MATCH pattern = (a1:Account)-[:HAS_PHONE]->(phone:Phone)<-[:HAS_PHONE]-(a2:Account)
WHERE a1 <> a2
  AND a1.created_at > date('2024-01-01')
RETURN a1, phone, a2, 
       length((a1)-[:TRANSACTION*1..3]-(a2)) AS transaction_distance
LIMIT 100;
 
// 3. Recommendation Engine: Content-Based Filtering
// Find movies liked by users who share my tastes
MATCH (me:User {id: 'user123'})-[:LIKES]->(movie:Movie)<-[:LIKES]-(similar_user)
WHERE similar_user <> me
WITH similar_user, COUNT(movie) AS shared_likes
WHERE shared_likes > 5
MATCH (similar_user)-[:LIKES]->(rec:Movie)
WHERE NOT (me)-[:LIKES]->(rec)
RETURN rec.title, COUNT(similar_user) AS recommender_count
ORDER BY recommender_count DESC
LIMIT 20;
 
// 4. Knowledge Graph: Semantic Queries
// Find all concepts related to 'Machine Learning' within 3 hops
MATCH path = (ml:Concept {name: 'Machine Learning'})-[:RELATED_TO*1..3]-(related)
RETURN related.name, length(path) AS distance
ORDER BY distance;
 
// Why graphs win here:
// - SQL equivalent requires self-joins for each hop
// - Performance degrades exponentially with depth
// - Graph databases optimize for exactly this pattern

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Cassandra: Wide-column store for time-series
 
-- IoT sensor data: billions of readings
CREATE TABLE sensor_data (
    sensor_id UUID,
    date DATE,
    time TIMESTAMP,
    reading_id TIMEUUID,
    temperature DECIMAL,
    humidity DECIMAL,
    pressure DECIMAL,
    battery_level INT,
    PRIMARY KEY ((sensor_id, date), time, reading_id)
) WITH CLUSTERING ORDER BY (time DESC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_size': 1,
                    'compaction_window_unit': 'DAYS'};
 
-- Write millions of points per second
INSERT INTO sensor_data (sensor_id, date, time, reading_id, 
                         temperature, humidity, pressure, battery_level)
VALUES (?, ?, ?, now(), ?, ?, ?, ?);
 
-- Efficient time-range queries
SELECT * FROM sensor_data 
WHERE sensor_id = ? AND date = '2024-01-15'
AND time >= '2024-01-15 10:00:00' AND time < '2024-01-15 11:00:00';
 
-- Time series patterns that work:
-- 1. Write latest readings (append-only, very fast)
-- 2. Query recent data for specific sensor (partition key + range)
-- 3. Archive old data (TTL or partition deletion)
-- 4. Aggregate at ingestion time (materialized views)
 
-- Patterns that DON'T work:
-- SELECT * FROM sensor_data WHERE temperature > 30;  -- Full scan!
-- Aggregation across all sensors requires separate pipeline

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Cassandra: Tunable consistency for availability
 
-- Create keyspace with multi-datacenter replication
CREATE KEYSPACE production WITH REPLICATION = {
    'class': 'NetworkTopologyStrategy',
    'us-east': 3,      -- 3 replicas in US-East
    'us-west': 3,      -- 3 replicas in US-West
    'eu-central': 3    -- 3 replicas in Europe
};
 
-- Consistency levels (per-query tuning):
 
-- LOCAL_ONE: Fastest, one local replica (may read stale)
SELECT * FROM users WHERE user_id = ? CONSISTENCY LOCAL_ONE;
 
-- LOCAL_QUORUM: Majority of local DC (good balance)
INSERT INTO events (...) VALUES (...) CONSISTENCY LOCAL_QUORUM;
 
-- QUORUM: Majority across all DCs (stronger consistency)
UPDATE accounts SET balance = ? WHERE id = ? CONSISTENCY QUORUM;
 
-- ALL: Every replica must respond (slowest, most consistent)
-- Rarely used - sacrifices availability
 
-- With 3 replicas per DC:
-- - LOCAL_ONE: Works if 1+ local nodes up (tolerates 2 failures)
-- - LOCAL_QUORUM: Works if 2+ local nodes up (tolerates 1 failure)
-- - QUORUM: Works if 5+ nodes up across cluster
 
-- Key insight: Cassandra can lose entire datacenters
-- and continue serving traffic from remaining regions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

// DynamoDB: Zero-admin scaling
 
const AWS = require('aws-sdk');
const dynamoDB = new AWS.DynamoDB.DocumentClient();
 
// Create table with on-demand capacity (auto-scales)
// No capacity planning, no instance sizing, no ops
 
// Write item - scales automatically with demand
await dynamoDB.put({
    TableName: 'UserSessions',
    Item: {
        sessionId: 'sess-12345',
        userId: 'user-789',
        createdAt: Date.now(),
        expiresAt: Date.now() + 3600000,
        data: { preferences: { theme: 'dark' } }
    }
}).promise();
 
// Read item - single-digit millisecond latency
const result = await dynamoDB.get({
    TableName: 'UserSessions',
    Key: { sessionId: 'sess-12345' }
}).promise();
 
// Global tables: One API call for multi-region
// Data automatically replicated across regions
// No replication lag management, no failover configuration
 
// What you DON'T manage:
// - Server provisioning
// - Storage expansion
// - Backup configuration
// - High availability setup
// - Security patches
// - Failure recovery
// - Performance tuning (mostly)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

// MongoDB: Iterate on data model in code
 
// Week 1: Basic user model
await users.insertOne({
    email: 'user@example.com',
    name: 'Test User'
});
 
// Week 2: Add preferences (no migration needed!)
await users.insertOne({
    email: 'user2@example.com',
    name: 'Test User 2',
    preferences: {        // New field, just add it
        theme: 'dark',
        notifications: true
    }
});
 
// Week 3: Restructure completely (no schema change!)
await users.insertOne({
    email: 'user3@example.com',
    profile: {           // Nest differently
        displayName: 'Test User 3',
        avatar: null
    },
    settings: {          // Rename/restructure
        ui: { theme: 'dark' },
        comms: { email: true, push: false }
    }
});
 
// Application code handles different document shapes
function getUserTheme(user) {
    // Handle both old and new structure
    return user.settings?.ui?.theme || 
           user.preferences?.theme || 
           'light';
}
 
// Compare to SQL: Each change would require:
// 1. Write migration file
// 2. Test migration locally
// 3. Apply to staging
// 4. Coordinate with team
// 5. Apply to production
// 6. Update ORM models