Choosing the Right Database - Decision Framework
Learning Objectives: By the end of this lesson, you will be able to:
- Apply a systematic framework for database selection
- Evaluate databases based on data model, query patterns, and scalability requirements
- Understand the trade-offs between consistency, availability, and performance
- Design polyglot persistence architectures for complex applications
- Make cost-effective database decisions considering total cost of ownership
1. Welcome and Introduction (5 minutes)
Icebreaker Activity
You’re the lead architect for a new startup. The CEO asks: “What database should we use?” You know there’s no single right answer—it depends on what you’re building. An e-commerce platform has different needs than a real-time gaming leaderboard, which differs from a financial trading system. How do you cut through the hype and make the right choice?
This lesson provides a practical framework for making database decisions that you’ll use throughout your career.
Lesson Overview
- Duration: 90 minutes
- Prerequisites: Understanding of database types covered in previous lessons
- Materials Needed: Decision matrix templates, case study materials
2. Learning Objectives and Session Outline (5 minutes)
What We’ll Cover Today:
- Decision Framework - Systematic approach to database selection
- Key Evaluation Criteria - What matters most for your use case
- Common Patterns - Matching workloads to database types
- Polyglot Persistence - Using multiple databases effectively
- Migration Strategies - When and how to change databases
- Case Studies - Real-world decision scenarios
Success Criteria:
By the end of this session, you should be able to evaluate database options systematically and make informed architectural decisions.
3. Foundation Knowledge (15-20 minutes)
Core Concepts
The Database Selection Framework
Making good database decisions requires evaluating multiple factors systematically:
┌─────────────────────────────────────────────────────────────────┐
│ DATABASE SELECTION FRAMEWORK │
├─────────────────────────────────────────────────────────────────┤
│ 1. DATA MODEL FIT │
│ What does your data look like? │
│ How is it structured? How will it evolve? │
├─────────────────────────────────────────────────────────────────┤
│ 2. QUERY PATTERNS │
│ How will you access and manipulate data? │
│ Read vs write ratio? Query complexity? │
├─────────────────────────────────────────────────────────────────┤
│ 3. SCALABILITY REQUIREMENTS │
│ Current and projected data volume? │
│ Traffic patterns and growth expectations? │
├─────────────────────────────────────────────────────────────────┤
│ 4. CONSISTENCY REQUIREMENTS │
│ What level of consistency is required? │
│ Can you tolerate eventual consistency? │
├─────────────────────────────────────────────────────────────────┤
│ 5. OPERATIONAL CONSIDERATIONS │
│ Team expertise? Managed vs self-hosted? │
│ Monitoring, backup, disaster recovery needs? │
├─────────────────────────────────────────────────────────────────┤
│ 6. TOTAL COST OF OWNERSHIP │
│ Licensing, infrastructure, operations, development? │
│ Long-term cost at scale? │
└─────────────────────────────────────────────────────────────────┘
The CAP Theorem in Practice
The CAP theorem states distributed databases can only guarantee two of three:
- Consistency: All nodes see the same data
- Availability: Every request gets a response
- Partition Tolerance: System works despite network issues
In Reality:
- Network partitions WILL happen
- You’re choosing between CP (consistency) and AP (availability)
- Most databases offer tunable consistency
| Database | Default Position | Notes |
|---|---|---|
| PostgreSQL | CA (single node) | CP with replication |
| MongoDB | CP (configurable) | AP with lower write concern |
| Cassandra | AP | Tunable consistency |
| DynamoDB | AP | Strong consistency option |
| CockroachDB | CP | Serializable by default |
PACELC: Beyond CAP
PACELC extends CAP: “If Partition, choose A or C; Else, choose Latency or Consistency”
Even without partitions, there’s a trade-off:
- PC/EC: Consistency always (traditional RDBMS)
- PA/EL: Availability + low latency (Cassandra, DynamoDB)
- PA/EC: Available during partition, consistent otherwise (MongoDB)
- PC/EL: Consistent during partition, low latency otherwise (CockroachDB)
Key Evaluation Criteria
1. Data Model Fit
| If Your Data Is… | Consider… |
|---|---|
| Highly structured, relational | PostgreSQL, MySQL |
| Semi-structured, hierarchical | MongoDB, DocumentDB |
| Simple key-value pairs | Redis, DynamoDB |
| Heavily relationship-focused | Neo4j, Neptune |
| Time-ordered measurements | InfluxDB, TimescaleDB |
| Unstructured text for search | Elasticsearch |
| High-dimensional vectors | Pinecone, Milvus |
2. Query Pattern Analysis
| Query Pattern | Best Fit |
|---|---|
| Complex joins and aggregations | PostgreSQL, SQL Server |
| Document retrieval by ID | MongoDB, DynamoDB |
| Full-text search | Elasticsearch, Algolia |
| Graph traversal | Neo4j, Neptune |
| Time-range analytics | TimescaleDB, InfluxDB |
| Real-time aggregations | Redis, ClickHouse |
| Ad-hoc analytics | BigQuery, Snowflake |
3. Scale Requirements
| Scale | Recommendation |
|---|---|
| < 100GB, moderate traffic | Start simple (PostgreSQL, MySQL) |
| 100GB-1TB, growing traffic | Consider read replicas, caching |
| 1TB+, high concurrency | Evaluate distributed databases |
| 10TB+, global distribution | NewSQL or purpose-built NoSQL |
| Petabyte-scale analytics | Data warehouse (Snowflake, BigQuery) |
4. Detailed Explanation and Examples (25-30 minutes)
Decision Matrix Template
Use this matrix to evaluate database options:
| Criteria | Weight | Database A | Database B | Database C |
|---|---|---|---|---|
| Data Model Fit | 25% | ? / 10 | ? / 10 | ? / 10 |
| Query Support | 20% | ? / 10 | ? / 10 | ? / 10 |
| Scalability | 15% | ? / 10 | ? / 10 | ? / 10 |
| Performance | 15% | ? / 10 | ? / 10 | ? / 10 |
| Operational Ease | 10% | ? / 10 | ? / 10 | ? / 10 |
| Team Expertise | 10% | ? / 10 | ? / 10 | ? / 10 |
| Cost | 5% | ? / 10 | ? / 10 | ? / 10 |
| Weighted Total | 100% | ? | ? | ? |
Common Workload Patterns
Pattern 1: CRUD Web Application
Characteristics:
- User authentication, profiles, content
- Read-heavy (80/20 read/write)
- Relational data with joins
- Moderate scale (< 1TB)
Recommendation: PostgreSQL or MySQL
Why: Mature, well-understood, excellent tooling, SQL flexibility
Consider: MongoDB if schema flexibility is critical
Pattern 2: E-commerce Platform
Characteristics:
- Product catalog (varying attributes)
- Shopping cart (session data)
- Orders (transactional)
- Search (full-text)
- Analytics (business intelligence)
Recommendation: Polyglot Persistence
- PostgreSQL: Orders, users, inventory (transactional core)
- Elasticsearch: Product search
- Redis: Sessions, cart, caching
- Data Warehouse: Analytics (BigQuery/Snowflake)
Why: Each database optimized for its workload
Pattern 3: IoT Data Platform
Characteristics:
- Millions of devices
- Time-series sensor data
- High write throughput
- Time-range queries
- Long-term storage
Recommendation: TimescaleDB or InfluxDB
- Primary: TimescaleDB (SQL compatibility, compression)
- Alternative: InfluxDB (purpose-built, simpler)
- Companion: PostgreSQL for device metadata
Why: Optimized for time-series ingestion and queries
Pattern 4: Social Network
Characteristics:
- User relationships (follows, friends)
- Content feeds
- Real-time interactions
- Recommendations
Recommendation:
- Neo4j/Neptune: Social graph, relationships
- PostgreSQL: User profiles, content
- Redis: Feeds, real-time counters
- Elasticsearch: Content search
Why: Graph DB excels at relationship queries
Pattern 5: Financial Trading System
Characteristics:
- Strict consistency requirements
- High transaction volume
- Audit trail requirements
- Low latency critical
- Regulatory compliance
Recommendation:
- Primary: PostgreSQL or CockroachDB (ACID)
- Audit: Ledger database (QLDB) or immutable append
- Cache: Redis (with persistence)
- Analytics: Specialized OLAP
Why: Financial data requires strong consistency
Polyglot Persistence Architecture
Modern applications often use multiple databases:
┌─────────────────────────────────────────────────────────────┐
│ APPLICATION LAYER │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ API │ │ Worker │ │ Search │ │Analytics│ │
│ │ Service │ │ Service │ │ Service │ │ Service │ │
│ └────┬────┘ └────┬────┘ └────┬────┘ └────┬────┘ │
│ │ │ │ │ │
├───────┼────────────┼────────────┼────────────┼──────────────┤
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │PostgreSQL│ │ Redis │ │Elastic │ │Snowflake│ │
│ │(Primary) │ │ (Cache) │ │(Search) │ │(OLAP) │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │
│ DATA SYNCHRONIZATION │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ CDC / Event Streaming (Kafka/Debezium) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Key Principles:
- Single Source of Truth: One database is authoritative
- Eventual Consistency: Secondary stores sync via CDC/events
- Right Tool for Job: Each database serves its purpose
- Clear Ownership: Each service owns its data
Migration Considerations
When to Migrate
- Current database can’t meet performance needs
- Operational costs are unsustainable
- Missing critical features
- Better options available for your use case
When NOT to Migrate
- “Grass is greener” syndrome
- Team unfamiliar with new technology
- No clear, measurable benefit
- Current solution works fine
Migration Strategies
Strategy 1: Strangler Fig Pattern
1. New writes go to both databases
2. Gradually migrate reads to new database
3. Backfill historical data
4. Verify consistency
5. Cutover completely
6. Decommission old database
Strategy 2: Dual Write with Shadow Reads
1. Write to both databases
2. Read from old, shadow read from new
3. Compare results, fix discrepancies
4. Cutover reads when confident
5. Stop writes to old database
Strategy 3: Blue-Green Deployment
1. Set up new database with data copy
2. Implement sync mechanism
3. Test thoroughly
4. Switch traffic at once
5. Keep old database as fallback
6. Decommission after stability period
5. Case Study Analysis (20-25 minutes)
Case Study 1: Startup MVP
Scenario: Early-stage startup building a B2B SaaS project management tool.
Requirements:
- Quick time to market
- Limited engineering resources
- Uncertain scale requirements
- Need for rapid iteration
Analysis:
Data Model: Users, Projects, Tasks, Comments (relational)
Query Patterns: CRUD, some reporting
Scale: < 100GB initially
Team Expertise: Full-stack, PostgreSQL experience
Budget: Limited
Decision: PostgreSQL (managed - AWS RDS or Supabase)
Reasoning:
✓ Team already knows PostgreSQL
✓ Handles all current requirements
✓ Managed service reduces operational burden
✓ Can scale to millions of rows easily
✓ SQL flexibility for evolving needs
✓ Cost-effective at small scale
Future Considerations:
- Add Redis for caching if performance issues arise
- Consider read replicas for scaling reads
- Elasticsearch if advanced search needed
Case Study 2: Growing E-commerce
Scenario: E-commerce company scaling from $10M to $100M revenue.
Current State:
- MySQL on single large instance
- Struggling with search performance
- Session management issues
- No good analytics
Analysis:
Pain Points:
1. Search: LIKE queries are slow, no relevance
2. Sessions: Storing in MySQL, causing contention
3. Analytics: Queries blocking production
Recommended Architecture:
┌──────────────────────────────────────────────────┐
│ │
│ PostgreSQL (migrate from MySQL) │
│ - Orders, users, inventory │
│ - Read replicas for read scaling │
│ │
│ Elasticsearch │
│ - Product search │
│ - Category navigation │
│ - Autocomplete │
│ │
│ Redis │
│ - Session storage │
│ - Cart data │
│ - API caching │
│ │
│ BigQuery (or Snowflake) │
│ - Business analytics │
│ - Marketing reports │
│ - ETL from PostgreSQL │
│ │
└──────────────────────────────────────────────────┘
Migration Plan:
Phase 1: Add Redis for sessions (immediate relief)
Phase 2: Add Elasticsearch for search (high impact)
Phase 3: Migrate MySQL to PostgreSQL
Phase 4: Set up data warehouse for analytics
Case Study 3: Global Financial Application
Scenario: Fintech building a global payments platform.
Requirements:
- Strong consistency for transactions
- Global presence (US, EU, Asia)
- < 100ms latency requirement
- Audit trail for compliance
- High availability (99.99%)
Analysis:
Critical Constraints:
1. Transactions MUST be ACID compliant
2. Regional data residency requirements
3. Complete audit trail
4. Zero data loss acceptable
Recommended Architecture:
Primary Database: CockroachDB Enterprise
- Global distribution with regional tables
- Serializable transactions
- Automatic replication and failover
- SQL compatibility
Regional Configuration:
- US data stays in US regions
- EU data stays in EU regions
- Asia data stays in Asia regions
- Global reference data replicated everywhere
Audit Layer: Amazon QLDB or Custom Solution
- Immutable transaction history
- Cryptographic verification
- Regulatory compliance
Caching: Redis Cluster (per region)
- Balance lookups
- Rate limiting
- Non-critical reads
Analytics: Snowflake
- Cross-regional reporting
- Regulatory reports
- Real-time dashboards via streaming
Architecture Diagram:
┌────────────────────────────────────────────────┐
│ Global Load Balancer │
└───────────┬────────────┬────────────┬──────────┘
│ │ │
┌──────▼──────┐┌────▼────┐┌──────▼──────┐
│ US-EAST ││ EU-WEST ││ AP-SOUTH │
│ CockroachDB ││CockroachDB││CockroachDB │
│ Nodes ││ Nodes ││ Nodes │
└──────┬──────┘└────┬────┘└──────┬──────┘
│ │ │
└──────────┬─┴────────────┘
│
┌─────▼─────┐
│ QLDB │
│(Audit Log)│
└───────────┘
Case Study 4: Real-Time Gaming
Scenario: Mobile game with leaderboards, user progress, and real-time multiplayer.
Requirements:
- Low latency (< 50ms)
- High concurrent users (1M+)
- Leaderboards updated in real-time
- User profiles and game state
- Eventual consistency acceptable
Analysis:
Workload Characteristics:
- Write-heavy (game events)
- Read-heavy (leaderboards, profiles)
- Bursty traffic (new game launches)
- Time-decay queries (daily/weekly boards)
Recommended Architecture:
Redis Cluster (Primary for real-time)
- Leaderboards (Sorted Sets)
- Session state
- Real-time game state
- Pub/sub for multiplayer
DynamoDB (Durable storage)
- User profiles
- Game progress
- Purchase history
- High availability
PostgreSQL (Back-office)
- Game configuration
- Content management
- Analytics queries
Architecture:
┌──────────────────────────────────────────┐
│ Game Servers │
└─────────────┬──────────────┬─────────────┘
│ │
┌──────▼──────┐ ┌─────▼─────┐
│ Redis │ │ DynamoDB │
│ Cluster │ │ │
│ (Real-time) │ │ (Durable) │
└─────────────┘ └───────────┘
│ │
└──────┬───────┘
│
┌──────▼──────┐
│ PostgreSQL │
│ (Analytics) │
└─────────────┘
Data Flow:
1. Game events → Redis (immediate)
2. Redis → DynamoDB (periodic persistence)
3. DynamoDB → PostgreSQL (ETL for analytics)
6. Hands-On Practice (30-40 minutes)
Exercise 1: Decision Matrix Application (15 minutes)
Scenario: You’re building a content management system for a media company.
Requirements:
- Store articles, images, videos (metadata)
- Full-text search across all content
- Version history for articles
- Multiple authors with workflows
- Public API for content delivery
- Expected: 10,000 articles, 100,000 daily visitors
Task: Complete this decision matrix:
| Criteria | Weight | PostgreSQL | MongoDB | Elasticsearch |
|---|---|---|---|---|
| Data Model Fit | 25% | |||
| Query Support | 20% | |||
| Scalability | 15% | |||
| Performance | 15% | |||
| Operational Ease | 10% | |||
| Team Expertise | 10% | |||
| Cost | 5% | |||
| Weighted Total | 100% |
Solution:
| Criteria | Weight | PostgreSQL | MongoDB | Elasticsearch |
|---|---|---|---|---|
| Data Model Fit | 25% | 8 | 9 | 6 |
| Query Support | 20% | 9 | 7 | 9 |
| Scalability | 15% | 7 | 8 | 8 |
| Performance | 15% | 7 | 8 | 9 |
| Operational Ease | 10% | 8 | 7 | 6 |
| Team Expertise | 10% | 9 | 6 | 5 |
| Cost | 5% | 9 | 7 | 6 |
| Weighted Total | 100% | 8.0 | 7.6 | 7.25 |
Recommendation:
- Primary: PostgreSQL (version history, workflows, relational data)
- Secondary: Elasticsearch (search functionality)
- Caching: Redis (API response caching)
Exercise 2: Architecture Design (15 minutes)
Scenario: Design the database architecture for a food delivery app.
Requirements:
- Restaurants with menus (varying attributes)
- User orders and order tracking
- Real-time driver location
- Search by location and cuisine
- Ratings and reviews
- Promotions and discounts
Task: Design the database architecture and justify your choices.
Solution:
Database Architecture:
1. PostgreSQL (Primary - Transactional Data)
Tables:
- users (customers, drivers, restaurant owners)
- orders (order details, status, history)
- payments (transactions, refunds)
- restaurants (basic info, verification)
- promotions (codes, rules, usage)
Why: ACID transactions for orders/payments,
relational data for complex business logic,
mature tooling and expertise available
2. MongoDB (Catalog - Semi-structured)
Collections:
- menus (flexible schema for varied items)
- reviews (embedded ratings, photos)
Why: Menu items vary significantly between restaurants,
schema flexibility for rapid changes,
good for read-heavy catalog access
3. Redis (Real-time & Caching)
Use cases:
- Driver location (geospatial)
- Order status pubsub
- Session management
- API caching
- Rate limiting
Why: Sub-millisecond latency for real-time features,
built-in geospatial commands,
pub/sub for notifications
4. Elasticsearch (Search)
Indices:
- restaurants (name, cuisine, location)
- menu_items (name, description, dietary)
Why: Full-text search with relevance,
geospatial queries for nearby,
faceted filtering (cuisine, price, rating)
Data Flow:
┌─────────────────────────────────────────────────────┐
│ Mobile App │
└─────────────────────────────────────────────────────┘
│
┌───────────────┼───────────────┐
│ │ │
▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Order API│ │Search API│ │Track API │
└────┬─────┘ └────┬─────┘ └────┬─────┘
│ │ │
▼ ▼ ▼
PostgreSQL Elasticsearch Redis
│ │ │
└──────────────┼──────────────┘
│
┌───▼───┐
│MongoDB│
└───────┘
Sync Strategy:
- PostgreSQL → Elasticsearch (Debezium CDC)
- MongoDB changes → Elasticsearch (Change Streams)
- Redis driver location → PostgreSQL (periodic)
Exercise 3: Migration Planning (10 minutes)
Scenario: Your startup has grown from 10K to 500K users. The MySQL database is struggling.
Current Pain Points:
- Writes are slow (500ms average)
- Read replicas have 30s lag
- Full table scans on reports
- No good search experience
Task: Create a migration plan with phases and risk mitigation.
Solution:
Current State Analysis:
- Single MySQL primary, 2 read replicas
- 200GB data, 50GB RAM instance
- 5,000 QPS peak, 500 connections
- Tables: users, orders, products, search_log
Migration Plan:
Phase 1: Quick Wins (Week 1-2)
-----------------------------------------
Actions:
1. Add Redis for hot data caching
- Session data
- Product catalog cache
- Rate limiting
2. Optimize MySQL
- Review slow query log
- Add missing indexes
- Tune buffer pool
Risk Mitigation:
- Redis in front, MySQL unchanged
- Easy rollback (disable cache)
Expected Result: 50% latency reduction
Phase 2: Search Offload (Week 3-4)
-----------------------------------------
Actions:
1. Deploy Elasticsearch cluster
2. Index products from MySQL (CDC)
3. Migrate search queries
4. Remove search_log table
Risk Mitigation:
- Dual read during transition
- Compare results between MySQL and ES
- Feature flag for gradual rollout
Expected Result: Sub-50ms search
Phase 3: Analytics Separation (Week 5-6)
-----------------------------------------
Actions:
1. Set up data warehouse (BigQuery)
2. Stream data from MySQL
3. Migrate all reporting queries
4. Remove analytics queries from MySQL
Risk Mitigation:
- Reports are non-critical
- Can revert to MySQL queries if issues
Expected Result: Production DB free from analytics
Phase 4: Database Upgrade (Week 7-8)
-----------------------------------------
Actions:
1. Migrate MySQL to PostgreSQL (or upgrade MySQL)
2. Implement proper connection pooling
3. Configure optimized replication
Options:
A) Upgrade MySQL 5.7 → 8.0 (lower risk)
B) Migrate to PostgreSQL (better long-term)
C) Migrate to PlanetScale (managed, horizontal)
Risk Mitigation:
- Blue-green deployment
- Extensive testing
- Rollback plan ready
Expected Result: Sustainable architecture
Timeline Summary:
Week 1-2: Redis caching (immediate relief)
Week 3-4: Elasticsearch (search performance)
Week 5-6: Analytics offload (reduce load)
Week 7-8: Database upgrade (long-term fix)
7. Common Pitfalls and Troubleshooting (10 minutes)
Decision Anti-Patterns
-
Resume-Driven Development
- Choosing tech because it’s trendy
- Solution: Base decisions on requirements
-
Premature Optimization
- Over-engineering for scale you don’t have
- Solution: Start simple, evolve as needed
-
Single Tool Syndrome
- Forcing one database for all use cases
- Solution: Right tool for the job
-
Ignoring Operations
- Not considering who will run it
- Solution: Factor in team expertise
-
Vendor Lock-in Blindness
- Not considering exit strategy
- Solution: Evaluate portability
Red Flags to Watch
- “We need MongoDB because it’s webscale”
- “Let’s use [new hotness] for everything”
- “Postgres is boring, let’s try…”
- “We don’t need to plan for scale yet”
- “The ORM handles all the database stuff”
8. Best Practices and Professional Tips (5 minutes)
Decision Checklist
Before finalizing a database choice:
- Written requirements document
- Evaluated at least 3 options
- Completed decision matrix
- Tested with realistic data
- Considered operational aspects
- Calculated total cost of ownership
- Documented decision rationale
- Identified migration path if wrong
Long-term Success Factors
- Document Everything: Future you will thank present you
- Plan for Change: Assume you’ll migrate eventually
- Measure Performance: Establish baselines early
- Automate Operations: Backup, monitoring, alerts
- Stay Current: Follow database community and updates
Next Steps in Your Learning Journey
- Deep Dive: Pick one database type to master
- Practice: Build projects with different databases
- Benchmark: Learn to performance test databases
- Operations: Understand production database management
- Architecture: Study large-scale system designs
9. Summary and Review (5 minutes)
Key Takeaways
- No universal “best” database - It depends on your specific requirements
- Systematic evaluation using decision frameworks leads to better choices
- Polyglot persistence is often the right answer for complex applications
- Start simple and evolve as your needs become clearer
- Consider total cost including operations, not just licensing
Quick Review Questions
-
What are the key factors in a database selection decision? Answer: Data model fit, query patterns, scalability, consistency needs, operational considerations, and total cost of ownership
-
When should you use multiple databases (polyglot persistence)? Answer: When different parts of your application have fundamentally different data access patterns that would be poorly served by a single database
-
What is the biggest mistake teams make in database selection? Answer: Choosing based on hype or trends rather than specific requirements, or over-engineering for scale they don’t yet need
Final Decision Framework Summary
┌─────────────────────────────────────────────────────────┐
│ DATABASE SELECTION QUICK GUIDE │
├─────────────────────────────────────────────────────────┤
│ │
│ START HERE: What's your primary workload? │
│ │
│ ┌─────────────────────────────────────────────────┐ │
│ │ Transactional (OLTP) → PostgreSQL, MySQL │ │
│ │ Analytical (OLAP) → Snowflake, BigQuery │ │
│ │ Key-Value/Caching → Redis, DynamoDB │ │
│ │ Document Store → MongoDB, Couchbase │ │
│ │ Graph Queries → Neo4j, Neptune │ │
│ │ Time Series → TimescaleDB, InfluxDB │ │
│ │ Full-Text Search → Elasticsearch │ │
│ │ Global SQL → CockroachDB, Spanner │ │
│ │ Vector/AI → Pinecone, pgvector │ │
│ └─────────────────────────────────────────────────┘ │
│ │
│ WHEN IN DOUBT: Start with PostgreSQL │
│ │
│ SCALE STRATEGY: Add specialized databases │
│ as specific needs emerge │
│ │
└─────────────────────────────────────────────────────────┘
For Instructors
Teaching Tips
- Use real case studies from your experience
- Have students debate different choices
- Show both successful and failed database decisions
- Bring in guest speakers from companies with interesting architectures
Assessment Ideas
- Decision matrix completion for given scenarios
- Architecture design presentations
- Migration plan development
- Debate between database options
Adaptation Notes
- For beginners: Focus on PostgreSQL as default, when to add others
- For advanced students: Deep dive into CAP/PACELC trade-offs
- Online delivery: Use collaborative tools for group exercises
- Time constraints: Focus on framework and one case study
Appendix: Quick Reference Cards
Database Category Quick Reference
| Category | Top Choice | Alternative | When to Use |
|---|---|---|---|
| General Purpose | PostgreSQL | MySQL | Default for most apps |
| Document Store | MongoDB | Couchbase | Flexible schemas |
| Key-Value | Redis | DynamoDB | Caching, sessions |
| Graph | Neo4j | Neptune | Relationship-heavy |
| Time Series | TimescaleDB | InfluxDB | IoT, metrics |
| Search | Elasticsearch | Algolia | Full-text search |
| Analytics | Snowflake | BigQuery | OLAP, BI |
| NewSQL | CockroachDB | TiDB | Global SQL |
| Vector | Pinecone | pgvector | AI/ML apps |
Cost Comparison Framework
| Factor | Self-Hosted | Managed | Serverless |
|---|---|---|---|
| Initial Cost | High | Medium | Low |
| Ongoing Cost | Medium | Medium | Variable |
| Operations | High | Low | Minimal |
| Scalability | Manual | Assisted | Automatic |
| Best For | Enterprise | Most Teams | Startups |