SQL vs NoSQL: Choosing the Right Database Paradigm

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

// Primary data in PostgreSQL
interface User {
  id: string;
  email: string;
  name: string;
  preferences: Record<string, any>;
}
 
// Cache layer with Redis
const CACHE_TTL = 300; // 5 minutes
 
async function getUser(userId: string): Promise<User> {
  // Try cache first
  const cached = await redis.get(`user:${userId}`);
  if (cached) {
    return JSON.parse(cached);
  }
  
  // Miss: fetch from PostgreSQL
  const user = await db.query("SELECT * FROM users WHERE id = $1", [userId]);
  
  // Populate cache for next request
  await redis.set(`user:${userId}`, JSON.stringify(user), "EX", CACHE_TTL);
  
  return user;
}
 
// Session storage entirely in Redis (no SQL)
async function createSession(userId: string): Promise<string> {
  const sessionId = crypto.randomUUID();
  await redis.set(`session:${sessionId}`, userId, "EX", 86400); // 24h TTL
  return sessionId;
}
 
// Rate limiting in Redis
async function checkRateLimit(userId: string): Promise<boolean> {
  const key = `ratelimit:${userId}:${Math.floor(Date.now() / 60000)}`;
  const count = await redis.incr(key);
  if (count === 1) {
    await redis.expire(key, 60); // First request, set expiry
  }
  return count <= 100; // 100 requests per minute
}

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

// Products stored in PostgreSQL (source of truth)
await db.query(`
  INSERT INTO products (id, name, description, category_id, price)
  VALUES ($1, $2, $3, $4, $5)
`, [product.id, product.name, product.description, product.categoryId, product.price]);
 
// Index product in Elasticsearch for search
await elasticsearch.index({
  index: 'products',
  id: product.id,
  body: {
    name: product.name,
    description: product.description,
    category: categoryName,
    price: product.price,
    searchable_text: `${product.name} ${product.description}`,
    facets: {
      category: categoryName,
      price_range: getPriceRange(product.price),
      brand: product.brand
    }
  }
});
 
// Search uses Elasticsearch
async function searchProducts(query: string, filters: Filters) {
  const results = await elasticsearch.search({
    index: 'products',
    body: {
      query: {
        bool: {
          must: { match: { searchable_text: query } },
          filter: buildFilters(filters)
        }
      },
      aggs: {
        categories: { terms: { field: 'facets.category' } },
        price_ranges: { terms: { field: 'facets.price_range' } }
      }
    }
  });
  
  // Return IDs, fetch full details from PostgreSQL if needed
  return results.hits.hits.map(hit => hit._id);
}

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

// DANGEROUS: Dual-write pattern
async function updateUser(userId: string, data: UserUpdate) {
  // Write to PostgreSQL
  await db.query("UPDATE users SET name = $1 WHERE id = $2", [data.name, userId]);
  
  // Write to Redis cache
  await redis.set(`user:${userId}`, JSON.stringify({...data}));
  
  // Write to Elasticsearch
  await elasticsearch.update({ index: 'users', id: userId, body: { doc: data } });
  
  // PROBLEM: If elasticsearch.update() fails:
  // - PostgreSQL is updated ✓
  // - Redis is updated ✓  
  // - Elasticsearch is stale ✗
  // - No way to roll back PostgreSQL transaction
  // - Systems are now permanently inconsistent
}
 
// SAFER: Cache invalidation instead of dual-write
async function updateUserSafe(userId: string, data: UserUpdate) {
  // Write to PostgreSQL (source of truth)
  await db.query("UPDATE users SET name = $1 WHERE id = $2", [data.name, userId]);
  
  // Invalidate cache (next read will refresh)
  await redis.del(`user:${userId}`);
  
  // Emit event for async processing
  await messageQueue.publish('user.updated', { userId, data });
  // Elasticsearch updated by event consumer, can retry on 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

                    PostgreSQL
                         │
                         │ Write-Ahead Log (WAL)
                         ▼
                    ┌─────────────┐
                    │  Debezium   │
                    │  Connector  │
                    └─────────────┘
                         │
                         │ Change Events
                         ▼
                    ┌─────────────┐
                    │    Kafka    │
                    │   Topics    │
                    └─────────────┘
                    ╱      │      ╲
                   ╱       │       ╲
                  ▼        ▼        ▼
           ┌─────────┐ ┌───────┐ ┌───────────┐
           │  Redis  │ │ Elastic│ │ Analytics │
           │ Consumer│ │ Consumer││  Consumer │
           └─────────┘ └───────┘ └───────────┘
           
Benefits:
- All changes captured (including direct SQL updates)
- Consumers can retry failures (Kafka retention)
- Multiple consumers from single stream
- Ordering guarantees per partition

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

┌─────────────────────────────────────────────────────────────────┐
│                        E-Commerce Platform                       │
└─────────────────────────────────────────────────────────────────┘
 
                           Application Layer
                          ╱       │        ╲
                         ╱        │         ╲
                        ╱         │          ╲
              ┌─────────────┐ ┌─────────┐ ┌────────────┐
              │ PostgreSQL  │ │  Redis  │ │Elasticsearch│
              │             │ │         │ │            │
              │ ● Users     │ │ ● Cache │ │ ● Product  │
              │ ● Orders    │ │ ● Sessions│ │   Search   │
              │ ● Products  │ │ ● Cart   │ │ ● Faceted  │
              │ ● Inventory │ │ ● Rate   │ │   Filters  │
              │ ● Payments  │ │   Limits │ └────────────┘
              └─────────────┘ └─────────┘
                    │              
                    │ CDC (Debezium)
                    ▼
              ┌─────────────┐
              │    Kafka    │────────┐
              └─────────────┘        │
                    │                ▼
                    │          ┌────────────┐
                    │          │ ClickHouse │
                    │          │            │
                    └─────────►│ ● Analytics│
                               │ ● Reports  │
                               │ ● Metrics  │
                               └────────────┘
 
Data Flow:
1. User views → PostgreSQL (read) or Redis cache
2. User search → Elasticsearch → Product IDs → PostgreSQL (details)
3. Add to cart → Redis (cart storage)
4. Checkout → PostgreSQL transaction (order, inventory, payment)
5. Order events → Kafka → ClickHouse (analytics)

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

// Abstract repository hides database choice from business logic
interface UserRepository {
  findById(id: string): Promise<User | null>;
  findByEmail(email: string): Promise<User | null>;
  save(user: User): Promise<void>;
  search(query: string): Promise<User[]>;
}
 
class PolyglotUserRepository implements UserRepository {
  constructor(
    private postgres: PostgresClient,
    private redis: RedisClient,
    private elasticsearch: ElasticsearchClient
  ) {}
  
  async findById(id: string): Promise<User | null> {
    // Check cache first
    const cached = await this.redis.get(`user:${id}`);
    if (cached) return JSON.parse(cached);
    
    // Miss: fetch from PostgreSQL
    const user = await this.postgres.query("SELECT * FROM users WHERE id = $1", [id]);
    if (user) {
      await this.redis.set(`user:${id}`, JSON.stringify(user), "EX", 300);
    }
    return user;
  }
  
  async save(user: User): Promise<void> {
    // Write to PostgreSQL (source of truth)
    await this.postgres.query("INSERT INTO users ... ON CONFLICT DO UPDATE ...", [...]);
    
    // Invalidate cache
    await this.redis.del(`user:${user.id}`);
    
    // Update search index (or emit event for async processing)
    await this.elasticsearch.index({ index: 'users', id: user.id, body: user });
  }
  
  async search(query: string): Promise<User[]> {
    // Search goes to Elasticsearch
    const results = await this.elasticsearch.search({ ... });
    return results.hits.hits.map(hit => hit._source as User);
  }
}
 
// Business logic doesn't know about polyglot complexity
class UserService {
  constructor(private users: UserRepository) {}
  
  async getProfile(userId: string) {
    return this.users.findById(userId); // Cache/DB abstracted
  }
}