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
40
41
42
43
44
45
46
47
48
49
50
51
52
53

// Cache-aside pattern: RDBMS + Redis
 
const redis = require('redis').createClient();
const { Pool } = require('pg');
const pool = new Pool();
 
async function getUser(userId) {
    // 1. Check cache first
    const cacheKey = `user:${userId}`;
    const cached = await redis.get(cacheKey);
    
    if (cached) {
        console.log('Cache hit');
        return JSON.parse(cached);
    }
    
    // 2. Cache miss - query database
    console.log('Cache miss - querying database');
    const result = await pool.query(
        'SELECT * FROM users WHERE id = $1',
        [userId]
    );
    
    if (result.rows.length === 0) {
        return null;
    }
    
    const user = result.rows[0];
    
    // 3. Populate cache (with TTL)
    await redis.setex(cacheKey, 3600, JSON.stringify(user));
    
    return user;
}
 
async function updateUser(userId, updates) {
    // 1. Update database (source of truth)
    await pool.query(
        'UPDATE users SET name = $1, email = $2 WHERE id = $3',
        [updates.name, updates.email, userId]
    );
    
    // 2. Invalidate cache
    const cacheKey = `user:${userId}`;
    await redis.del(cacheKey);
    
    // Next read will repopulate cache with fresh data
}
 
// Benefits:
// - Database handles transactions, queries, integrity
// - Redis provides sub-ms reads for hot data
// - Cache miss still works (degraded performance, not failure)

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

# E-commerce platform database architecture
 
databases:
  primary:
    type: PostgreSQL
    purpose: "System of record"
    stores:
      - User accounts and authentication
      - Orders and transactions
      - Inventory with ACID guarantees
      - Financial data (payments, refunds)
    why: "ACID transactions, referential integrity, complex queries"
 
  cache:
    type: Redis
    purpose: "Performance acceleration"
    stores:
      - Session data
      - Shopping cart contents
      - Rate limiting counters
      - Feature flags
    why: "Sub-millisecond reads, automatic expiration, pub/sub"
 
  search:
    type: Elasticsearch
    purpose: "Full-text search and faceting"
    stores:
      - Product search index
      - Faceted navigation data
      - Typeahead suggestions
    why: "Full-text search, facets, relevance scoring"
    sync: "Sync from PostgreSQL via change data capture"
 
  recommendations:
    type: Neo4j
    purpose: "Recommendation engine"
    stores:
      - User-product interactions
      - Product relationships
      - Category hierarchies
    why: "Graph traversal for collaborative filtering"
    sync: "Event sourcing from application"
 
  activity_stream:
    type: Cassandra
    purpose: "High-volume user activity"
    stores:
      - Clickstream data
      - User activity logs
      - Event sourcing append-log
    why: "Write throughput, time-series, horizontal scale"

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

// PROBLEMATIC: Dual write pattern
 
async function createProduct(product) {
    // Write to PostgreSQL
    const result = await postgres.query(
        'INSERT INTO products (...) VALUES (...) RETURNING id',
        [product.name, product.price, ...]
    );
    const productId = result.rows[0].id;
    
    // Write to Elasticsearch
    await elasticsearch.index({
        index: 'products',
        id: productId,
        body: { name: product.name, description: product.description, ... }
    });
    
    // PROBLEMS:
    // 1. What if PostgreSQL succeeds but Elasticsearch fails?
    //    - Product exists in DB but not searchable
    // 2. What if Elasticsearch succeeds but PostgreSQL fails?
    //    - Search returns product that doesn't exist
    // 3. No transaction spanning both databases
    // 4. Race conditions under concurrent writes
    
    return productId;
}
 
// Slightly better: Write to primary, queue secondary
async function createProductImproved(product) {
    // Write to PostgreSQL (source of truth)
    const result = await postgres.query(
        'INSERT INTO products (...) VALUES (...) RETURNING *',
        [product.name, product.price, ...]
    );
    
    // Queue async sync to Elasticsearch
    await messageQueue.publish('product.created', result.rows[0]);
    
    // Elasticsearch updates eventually via consumer
    return result.rows[0];
}

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

// Debezium CDC connector configuration
{
  "name": "products-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres.example.com",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${POSTGRES_PASSWORD}",
    "database.dbname": "ecommerce",
    "database.server.name": "ecommerce",
    "table.include.list": "public.products,public.categories",
    "plugin.name": "pgoutput",
    
    "transforms": "route",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "ecommerce.public.(.*)",
    "transforms.route.replacement": "$1-events"
  }
}
 
// This configuration:
// 1. Connects to PostgreSQL's logical replication stream
// 2. Captures all INSERT, UPDATE, DELETE on products and categories
// 3. Publishes events to Kafka topics
// 4. Downstream consumers update Elasticsearch, Redis, etc.
 
// CDC Advantages:
// - Changes captured directly from transaction log
// - Cannot miss updates (log is sequential, durable)
// - Application doesn't know about secondary databases
// - Guaranteed eventual consistency
// - Can replay history for new consumers

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
53
54
55
56
57

// Event Sourcing: Events are the source of truth
 
// 1. Write events to event store
async function handleAddToCart(userId, productId, quantity) {
    const event = {
        type: 'ITEM_ADDED_TO_CART',
        aggregateId: userId,
        timestamp: new Date().toISOString(),
        payload: { productId, quantity }
    };
    
    // Event store is the single source of truth
    await eventStore.append('carts', userId, event);
    
    // Event is published to interested consumers
    await eventBus.publish('cart-events', event);
}
 
// 2. Consumers materialize views in different databases
 
// PostgreSQL consumer: Maintains queryable cart state
async function handleCartEventForPostgres(event) {
    if (event.type === 'ITEM_ADDED_TO_CART') {
        await postgres.query(
            `INSERT INTO cart_items (user_id, product_id, quantity)
             VALUES ($1, $2, $3)
             ON CONFLICT (user_id, product_id)
             DO UPDATE SET quantity = cart_items.quantity + $3`,
            [event.aggregateId, event.payload.productId, event.payload.quantity]
        );
    }
}
 
// Redis consumer: Maintains cache for fast reads
async function handleCartEventForRedis(event) {
    if (event.type === 'ITEM_ADDED_TO_CART') {
        const key = `cart:${event.aggregateId}`;
        await redis.hincrby(key, event.payload.productId, event.payload.quantity);
        await redis.expire(key, 86400); // 24 hour TTL
    }
}
 
// Analytics consumer: Appends to Cassandra for behavior analysis
async function handleCartEventForAnalytics(event) {
    await cassandra.execute(
        `INSERT INTO cart_events (user_id, event_type, product_id, quantity, timestamp)
         VALUES (?, ?, ?, ?, ?)`,
        [event.aggregateId, event.type, event.payload.productId, 
         event.payload.quantity, event.timestamp]
    );
}
 
// Benefits:
// - Complete audit trail of all changes
// - Add new views without changing write path
// - Rebuild any view by replaying events
// - Temporal queries: "What was in cart at 3pm yesterday?"

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

Social Media Polyglot Architecture
===================================
 
User Service:
  - PostgreSQL: User accounts, profiles, authentication
  - Redis: Session tokens, online status, recently viewed
 
Content Service:
  - MongoDB: Posts, comments (flexible structure, varied media)
  - Elasticsearch: Full-text search across posts
  - S3: Media storage (images, videos)
 
Social Graph:
  - Neo4j: Follower/following relationships
  - Redis: Cached friend lists for feed generation
 
Feed Service:
  - Cassandra: Pre-computed feeds (fan-out on write)
  - Redis: Hot feed cache (most recent items)
 
Notifications:
  - Redis: Real-time notification queue (pub/sub)
  - PostgreSQL: Notification history and preferences
 
Analytics:
  - Cassandra: Event ingestion (billions of events)
  - ClickHouse: Aggregated metrics and dashboards
 
Search:
  - Elasticsearch: Users, posts, hashtags
  - Redis: Trending topics (sorted sets with scores)
 
Data Flow:
  - Events published to Kafka on every action
  - CDC captures database changes
  - Stream processors update derived views
  - Each database optimized for its access 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

# Define clear ownership of each database
 
services:
  user-service:
    owns:
      - database: PostgreSQL/users
        type: primary
        responsibilities:
          - User CRUD operations
          - Authentication
          - Profile management
      - database: Redis/sessions
        type: cache
        responsibilities:
          - Session storage
          - Online status
    consumes:
      - database: Elasticsearch/users
        type: derived
        updated_via: CDC
        read_only: true
 
  product-service:
    owns:
      - database: PostgreSQL/products
        type: primary
        # ... 
      - database: Elasticsearch/products
        type: derived
        sync_mechanism: CDC/Debezium
        lag_tolerance: 5s
 
# Benefits:
# - Clear accountability for each database
# - Known source of truth for each data type
# - Documented sync mechanisms and tolerances

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

# Unified monitoring across databases
 
dashboards:
  polyglot-health:
    panels:
      - title: "Data Pipeline Health"
        metrics:
          - cdc_lag_seconds{source="postgres", target="*"}
          - kafka_consumer_lag{consumer_group="*"}
          - sync_errors_total{pipeline="*"}
        
      - title: "Database Health Overview"
        metrics:
          - postgres_connections_active
          - redis_connected_clients
          - elasticsearch_cluster_health
          - cassandra_node_status
          
      - title: "Cross-Database Consistency"
        metrics:
          - consistency_check_failures{check="product_count"}
          - last_sync_timestamp{source="*", target="*"}
 
alerts:
  - name: CDC Pipeline Delayed
    condition: cdc_lag_seconds > 60
    severity: warning
    
  - name: Cross-DB Count Mismatch
    condition: |
      abs(postgres_product_count - elasticsearch_product_count) > 100
    severity: critical
    
  - name: Sync Pipeline Errors
    condition: rate(sync_errors_total[5m]) > 0
    severity: warning

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

// Abstract database access behind repositories
// Application code doesn't know about multiple databases
 
interface Product {
    id: string;
    name: string;
    price: number;
    description: string;
}
 
interface ProductRepository {
    findById(id: string): Promise<Product | null>;
    search(query: string): Promise<Product[]>;
    save(product: Product): Promise<void>;
}
 
// Implementation uses multiple databases internally
class ProductRepositoryImpl implements ProductRepository {
    constructor(
        private postgres: PostgresClient,
        private redis: RedisClient,
        private elasticsearch: ElasticsearchClient
    ) {}
    
    async findById(id: string): Promise<Product | null> {
        // Check cache first
        const cached = await this.redis.get(`product:${id}`);
        if (cached) return JSON.parse(cached);
        
        // Fallback to database
        const result = await this.postgres.query(
            'SELECT * FROM products WHERE id = $1', [id]
        );
        
        if (result.rows[0]) {
            // Populate cache
            await this.redis.setex(
                `product:${id}`, 3600, JSON.stringify(result.rows[0])
            );
        }
        
        return result.rows[0] || null;
    }
    
    async search(query: string): Promise<Product[]> {
        // Use Elasticsearch for search
        const results = await this.elasticsearch.search({
            index: 'products',
            body: { query: { match: { name: query } } }
        });
        return results.hits.hits.map(h => h._source as Product);
    }
    
    async save(product: Product): Promise<void> {
        // Write to PostgreSQL (source of truth)
        await this.postgres.query(
            `INSERT INTO products (id, name, price, description)
             VALUES ($1, $2, $3, $4)
             ON CONFLICT (id) DO UPDATE SET 
                name = $2, price = $3, description = $4`,
            [product.id, product.name, product.price, product.description]
        );
        
        // Invalidate cache
        await this.redis.del(`product:${id}`);
        
        // Note: Elasticsearch updated via CDC, not here
    }
}
 
// Application code uses the interface, unaware of complexity
async function handleGetProduct(productId: string) {
    const product = await productRepository.findById(productId);
    return product;
}