Database Database Selection

Database Selection

Scope: Choosing the right database, SQL vs NoSQL, database comparison Lines: ~280 Last Updated: 2025-10-18

When to Use This Skill

Activate this skill when:

• Starting new projects and choosing database technology
• Evaluating database options for specific use cases
• Migrating from one database to another
• Architecting multi-database systems
• Scaling existing database infrastructure
• Comparing SQL vs NoSQL tradeoffs
• Selecting database for specific workload patterns

Core Concepts

SQL vs NoSQL Decision Criteria

SQL (Relational):

• Structured data with fixed schema
• Complex relationships between entities
• ACID transactions required
• Complex queries with JOINs
• Data integrity is critical

NoSQL (Non-Relational):

• Flexible/dynamic schema
• Simple relationships or denormalized data
• High scalability requirements
• Simple queries on large datasets
• Performance over strict consistency

Database Comparison Matrix

PostgreSQL

Type: Relational (SQL) Best For: General-purpose applications, complex queries, data integrity

Strengths:

• Full ACID compliance
• Rich data types (JSON, arrays, ranges)
• Advanced features (CTEs, window functions, full-text search)
• Excellent for complex queries and JOINs
• Strong consistency
• Mature ecosystem

Weaknesses:

• Vertical scaling easier than horizontal
• Complex sharding setup
• Writes can be slower than NoSQL

Use Cases:

• E-commerce platforms
• Financial systems
• SaaS applications
• Admin dashboards
• CMS platforms

Example:

-- Complex query with JOINs, aggregations, CTEs
WITH top_customers AS (
  SELECT user_id, SUM(total) as spent
  FROM orders
  WHERE created_at > NOW() - INTERVAL '1 year'
  GROUP BY user_id
  ORDER BY spent DESC
  LIMIT 100
)
SELECT u.email, tc.spent, COUNT(o.id) as order_count
FROM top_customers tc
JOIN users u ON tc.user_id = u.id
JOIN orders o ON o.user_id = u.id
GROUP BY u.email, tc.spent;

MySQL

Type: Relational (SQL) Best For: Web applications, read-heavy workloads, simple transactions

Strengths:

• Fast read performance
• Wide hosting support
• Simple replication
• Mature ecosystem
• Lower resource usage than PostgreSQL

Weaknesses:

• Less advanced features than PostgreSQL
• Weaker for complex queries
• Less strict data integrity by default

Use Cases:

• WordPress/Drupal sites
• Read-heavy web apps
• Simple CRUD applications
• Shared hosting environments

When to choose over PostgreSQL:

• Need maximum read performance
• Simpler queries (fewer JOINs)
• Constrained resources
• Existing MySQL ecosystem

MongoDB

Type: Document (NoSQL) Best For: Flexible schemas, hierarchical data, rapid development

Strengths:

• Flexible schema (JSON documents)
• Horizontal scaling (sharding)
• Fast reads/writes
• Embedding reduces JOINs
• Developer-friendly (JSON)

Weaknesses:

• No multi-document ACID (before v4.0)
• Joins ($lookup) are expensive
• Data duplication common
• 16MB document limit

Use Cases:

• Content management
• Catalogs (products, articles)
• Real-time analytics
• User profiles
• Event logging

Example:

// Embedded document (no JOIN needed)
{
  "_id": ObjectId("..."),
  "user": "alice",
  "cart": {
    "items": [
      { "product": "Widget", "price": 29.99, "qty": 2 },
      { "product": "Gadget", "price": 49.99, "qty": 1 }
    ],
    "total": 109.97
  }
}

Redis

Type: In-memory key-value (NoSQL) Best For: Caching, sessions, real-time analytics, queues

Strengths:

• Extremely fast (in-memory)
• Rich data structures (lists, sets, sorted sets, hashes)
• Pub/Sub messaging
• TTL (auto-expiration)
• Atomic operations

Weaknesses:

• Limited by RAM
• Not durable by default (persistence optional)
• No complex queries
• Single-threaded (one CPU core)

Use Cases:

• Session storage
• Caching layer
• Rate limiting
• Leaderboards
• Real-time analytics
• Job queues

Example:

# Cache user session
SET session:abc123 '{"user_id": 42, "email": "alice@example.com"}' EX 3600

# Rate limiting
INCR ratelimit:user:42
EXPIRE ratelimit:user:42 60

# Leaderboard
ZADD leaderboard 9500 "alice"
ZREVRANGE leaderboard 0 9 WITHSCORES

DynamoDB

Type: Key-value/Document (NoSQL, AWS) Best For: Serverless apps, high-scale key-value, event-driven

Strengths:

• Fully managed (auto-scaling)
• Single-digit millisecond latency
• Global tables (multi-region)
• Pay-per-request pricing
• Integrated with AWS ecosystem

Weaknesses:

• Expensive at high throughput
• Limited query patterns (no ad-hoc queries)
• Vendor lock-in (AWS)
• Complex pricing model

Use Cases:

• Serverless applications (Lambda)
• Mobile backends
• Gaming leaderboards
• IoT data
• Session storage

When to choose:

• Using AWS ecosystem
• Need auto-scaling
• Predictable access patterns (partition key design)

SQLite

Type: Relational (SQL, embedded) Best For: Local storage, embedded apps, development

Strengths:

• Zero configuration
• Single file database
• Full SQL support
• Lightweight (small footprint)
• Cross-platform

Weaknesses:

• No multi-user concurrency (writes lock database)
• No network access (local only)
• Limited scalability

Use Cases:

• Mobile apps (iOS, Android)
• Desktop applications
• Local development
• Embedded systems
• Configuration storage

When to choose:

• Single-user applications
• Embedded use cases
• Local-first apps
• Development/testing

ACID vs BASE Tradeoffs

ACID (SQL Databases)

Atomicity: Transaction succeeds completely or fails completely Consistency: Data moves from one valid state to another Isolation: Concurrent transactions don't interfere Durability: Committed data is permanent

Example (PostgreSQL):

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Both succeed or both fail

Best for:

• Financial transactions
• Inventory management
• Order processing
• Any system where data integrity is critical

BASE (NoSQL Databases)

Basically Available: System remains operational Soft state: State may change over time (eventual consistency) Eventual consistency: System will become consistent eventually

Example (MongoDB):

// Write may return before replication completes
db.users.updateOne(
  { _id: ObjectId("...") },
  { $inc: { post_count: 1 } }
)
// Other replicas may see stale data temporarily

Best for:

• Social media feeds
• Analytics dashboards
• Content delivery
• Systems prioritizing availability over consistency

Consistency Models

Strong Consistency

Guarantee: All reads return most recent write

Databases: PostgreSQL, MySQL (default), MongoDB (with read concern "linearizable")

Use case: Banking, inventory systems

Tradeoff: Higher latency, lower availability

Eventual Consistency

Guarantee: Reads will eventually reflect writes (may be stale temporarily)

Databases: DynamoDB (default), Cassandra, MongoDB (with read concern "local")

Use case: Social feeds, product catalogs, analytics

Tradeoff: Lower latency, higher availability, potential stale reads

Causal Consistency

Guarantee: Reads respect causality (if A caused B, all see A before B)

Databases: MongoDB (with causal consistency sessions)

Use case: Chat applications, collaborative editing

Tradeoff: Balance between strong and eventual

Read vs Write Optimization

Read-Optimized Databases

Characteristics:

• Denormalized schemas
• Materialized views
• Aggressive caching
• Read replicas

Databases: MySQL (with read replicas), Elasticsearch, ClickHouse

Patterns:

-- Denormalized for reads (PostgreSQL)
CREATE MATERIALIZED VIEW user_stats AS
SELECT
  user_id,
  COUNT(DISTINCT order_id) as order_count,
  SUM(total) as lifetime_value
FROM orders
GROUP BY user_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW user_stats;

Use cases:

• Analytics dashboards
• Reporting systems
• Content delivery

Write-Optimized Databases

Characteristics:

• Append-only logs
• Eventual consistency
• Horizontal partitioning

Databases: Cassandra, ClickHouse (for inserts), Kafka (event streaming)

Patterns:

// Append-only event log (MongoDB)
db.events.insertOne({
  event_type: "page_view",
  user_id: 42,
  page: "/products/123",
  timestamp: new Date()
})
// No updates, only inserts

Use cases:

• Event logging
• Time-series data
• Metrics collection

Scaling Considerations

Vertical Scaling (Scale Up)

Approach: Increase resources (CPU, RAM, disk) on single server

Best for: SQL databases, simple setups

Limits: Hardware limits (expensive at high end)

Databases: PostgreSQL, MySQL, SQLite

Horizontal Scaling (Scale Out)

Approach: Add more servers, distribute data

Strategies:

Replication (Read Scaling)

Write → Primary
Reads → Replicas (multiple)

Databases: PostgreSQL, MySQL, MongoDB

Use case: Read-heavy workloads (10:1 read/write ratio)

Sharding (Write Scaling)

Data partitioned across multiple servers by key
Users A-M → Shard 1
Users N-Z → Shard 2

Databases: MongoDB (built-in), PostgreSQL (Citus extension), Cassandra

Use case: Massive datasets, write-heavy workloads

Complexity: Application-level sharding logic, cross-shard queries expensive

Decision Tree: Scaling Strategy

What's the bottleneck?
│
├─ Reads → Add read replicas
│  └─ Still slow? → Add caching (Redis)
│
├─ Writes → Vertical scaling first
│  └─ Hit limits? → Horizontal sharding
│
└─ Data size → Sharding or partitioning

Use Case → Database Mapping

E-commerce Platform

Requirements: Transactions, inventory, complex queries

Primary: PostgreSQL

• Orders, products, users (relational)
• ACID transactions for checkout
• Complex reporting queries

Secondary: Redis

• Session storage
• Cart caching
• Rate limiting

Example architecture:

PostgreSQL: Orders, products, users, inventory
Redis: Sessions, cart cache, product cache
S3/CloudFlare: Product images, static assets

Real-Time Analytics

Requirements: High write throughput, time-series data

Primary: ClickHouse or TimescaleDB (PostgreSQL extension)

• Event logging
• Metrics aggregation
• Fast analytical queries

Secondary: Redis

• Real-time counters
• Recent data caching

Alternative: MongoDB (with time-series collections)

Social Media Application

Requirements: Flexible schema, high scale, denormalized data

Primary: MongoDB

• User profiles
• Posts (with embedded comments)
• Activity feeds

Secondary: Redis

• Timeline caching
• Real-time notifications
• Rate limiting

Tertiary: PostgreSQL

• User authentication
• Billing/payments

Content Management System

Requirements: Flexible content types, full-text search

Primary: PostgreSQL

• Content storage
• User management
• Full-text search (built-in)

Alternative: MongoDB + Elasticsearch

• MongoDB for content
• Elasticsearch for search

Mobile Application

Requirements: Offline-first, sync, embedded database

Primary: SQLite (local)

• Local data storage
• Offline-first

Backend: PostgreSQL or MongoDB

• Server-side sync
• User data

Sync: Conflict resolution (last-write-wins or CRDTs)

High-Frequency Trading

Requirements: Ultra-low latency, strong consistency

Primary: In-memory database (Redis or custom)

• Sub-millisecond latency
• Atomic operations

Backup: PostgreSQL or TimescaleDB

• Audit trail
• Historical data

Decision Tree: Database Selection

Start: What's your primary requirement?
│
├─ Strong consistency + ACID?
│  ├─ Yes → SQL (PostgreSQL or MySQL)
│  │   └─ Complex queries? → PostgreSQL
│  │   └─ Read-heavy? → MySQL
│  └─ No → Continue
│
├─ Flexible schema?
│  ├─ Yes → MongoDB
│  └─ No → SQL
│
├─ Key-value access patterns?
│  ├─ Yes + In-memory → Redis
│  ├─ Yes + Persistent → DynamoDB or PostgreSQL
│  └─ No → Continue
│
├─ Time-series data?
│  ├─ Yes → TimescaleDB, ClickHouse, or InfluxDB
│  └─ No → Continue
│
├─ Full-text search?
│  ├─ Primary feature → Elasticsearch
│  ├─ Secondary → PostgreSQL (built-in) or MongoDB
│  └─ No → Continue
│
├─ Graph relationships?
│  ├─ Yes → Neo4j or PostgreSQL (recursive CTEs)
│  └─ No → Continue
│
├─ Embedded/Local?
│  └─ Yes → SQLite
│
└─ Default → PostgreSQL (most versatile)

Multi-Database Architectures

When to Use Multiple Databases

Polyglot persistence: Using different databases for different parts of the system.

Example architecture:

PostgreSQL: Core business data (users, orders, products)
MongoDB: User-generated content (posts, comments, reviews)
Redis: Caching, sessions, rate limiting
Elasticsearch: Full-text search
S3: File storage (images, videos)

Common Patterns

Pattern 1: Cache-Aside (PostgreSQL + Redis)

def get_user(user_id):
    # Try cache first
    cached = redis.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)

    # Cache miss, query database
    user = db.query("SELECT * FROM users WHERE id = %s", [user_id])

    # Cache result
    redis.setex(f"user:{user_id}", 3600, json.dumps(user))
    return user

Pattern 2: Search Index (PostgreSQL + Elasticsearch)

# Write to PostgreSQL
db.execute("INSERT INTO products (name, description) VALUES (%s, %s)", [name, desc])

# Async sync to Elasticsearch
elasticsearch.index(
    index="products",
    document={"name": name, "description": desc}
)

# Search via Elasticsearch
results = elasticsearch.search(
    index="products",
    query={"match": {"description": "laptop"}}
)

Pattern 3: Event Sourcing (PostgreSQL + MongoDB)

# Write events to MongoDB (append-only log)
events.insert_one({
    "event_type": "order_placed",
    "order_id": 123,
    "user_id": 42,
    "timestamp": datetime.now()
})

# Aggregate to PostgreSQL (current state)
db.execute("UPDATE orders SET status = 'placed' WHERE id = 123")

Migration Considerations

SQL → SQL (PostgreSQL → MySQL)

Difficulty: Low to Medium

Challenges:

• Syntax differences (AUTO_INCREMENT vs SERIAL)
• Feature gaps (CTEs, window functions)
• Data type differences

Tools: pgloader, AWS DMS

SQL → NoSQL (PostgreSQL → MongoDB)

Difficulty: High

Challenges:

• Schema redesign (normalization → denormalization)
• Relationship mapping (JOINs → embedded or references)
• Transaction changes (ACID → eventual consistency)

Strategy:

1. Identify access patterns
2. Denormalize for reads
3. Embed related data when accessed together
4. Reference when data is large or changes frequently

Example:

-- Before (PostgreSQL)
SELECT u.name, p.title, p.content
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.id = 42;

// After (MongoDB, embedded)
{
  "_id": ObjectId("..."),
  "title": "My Post",
  "content": "...",
  "author": {
    "name": "Alice"  // Embedded
  }
}

NoSQL → SQL (MongoDB → PostgreSQL)

Difficulty: Medium

Challenges:

• Schema definition (flexible → fixed)
• Normalizing embedded data
• Relationship extraction

Strategy:

1. Analyze document structure
2. Extract entities (normalize)
3. Define relationships (foreign keys)
4. Add constraints and indexes

Quick Reference Table

Related Skills

• postgres-schema-design.md - Designing PostgreSQL schemas
• mongodb-document-design.md - Designing MongoDB documents
• redis-data-structures.md - Using Redis effectively
• database-connection-pooling.md - Optimizing database connections
• postgres-query-optimization.md - Optimizing PostgreSQL queries

Last Updated: 2025-10-18 Format Version: 1.0 (Atomic)