Cassandra vs MongoDB vs MySQL

Answer:

Cassandra (Wide-Column NoSQL):

• Architecture: Peer-to-peer ring, no master, all nodes equal
• CAP: AP (Availability + Partition Tolerance), tunable consistency
• Best For: Write-heavy workloads, time-series data, IoT, always-on availability
• Scalability: Linear horizontal scaling, automatic sharding
• Limitations: No joins, limited transactions, eventual consistency default

MongoDB (Document NoSQL):

• Architecture: Primary-secondary replication, automatic failover
• CAP: CP (Consistency + Partition Tolerance), strong consistency default
• Best For: Flexible schemas, JSON-like documents, rapid development
• Scalability: Good horizontal scaling with sharding
• Strengths: Powerful query language, ACID transactions (4.0+), rich indexes

MySQL (Relational SQL):

• Architecture: Master-slave replication, single master for writes
• CAP: CA (single node), CP (with replication)
• Best For: Structured data, complex joins, ACID transactions
• Scalability: Vertical scaling easy, horizontal requires manual sharding
• Strengths: 30+ years maturity, full ACID, SQL ecosystem, proven reliability

Key Insight: Cassandra = availability, MongoDB = flexibility, MySQL = consistency

Answer:

Choose Cassandra when:

1. Write-Heavy Workloads:

• Cassandra optimized for writes (300K+ writes/sec vs MongoDB's 150K)
• Use cases: IoT sensors, application logs, time-series data
• Example: Netflix logs billions of viewing events daily

2. Always-On Availability Required:

• Cassandra has no single point of failure (peer-to-peer)
• MongoDB has primary node (single point during failover)
• Example: Apple's iCloud needs 99.9999% uptime

3. Linear Scalability Needed:

• Cassandra scales linearly (double nodes = double throughput)
• Add nodes without downtime
• Example: Instagram scaled from 1M to 2B users same architecture

4. Multi-Datacenter by Default:

• Cassandra built for geographic distribution
• Automatic replication across regions
• Example: Global apps need low latency everywhere

5. Eventual Consistency Acceptable:

• Social media likes/views (don't need instant consistency)
• Shopping carts (can merge conflicts)
• Analytics data (aggregate eventually)

DON'T Choose Cassandra when: Need complex queries, joins, or strong consistency guarantees

Answer:

Choose MongoDB when:

1. Flexible Schema Required:

• Product catalogs with varying attributes (electronics vs clothing)
• Content management with different content types
• User profiles with custom fields
• Example: eBay has 1.3B listings with different schemas

2. Complex Queries Needed:

• MongoDB's aggregation pipeline handles complex transformations
• Rich query language with filtering, sorting, grouping
• Geospatial queries
• Cassandra limited to partition key queries

3. Strong Consistency Important:

• MongoDB default is strong consistency
• ACID transactions across documents (4.0+)
• Use case: Financial data, inventory counts
• Cassandra default is eventual consistency

4. Rapid Development:

• JSON-like documents match application objects
• No schema migration needed
• Easier learning curve than Cassandra
• Faster prototyping and iteration

5. Read-Heavy Workloads:

• MongoDB optimized for reads (400K reads/sec vs Cassandra 350K)
• Rich indexing options
• Use case: Content delivery, product catalogs

Trade-off: MongoDB has primary node bottleneck, not as available as Cassandra

Answer:

MySQL Still Makes Sense When:

1. ACID Transactions Critical:

• Financial transactions (bank transfers, payments)
• E-commerce orders and inventory
• Booking systems (can't oversell seats/rooms)
• Strong consistency guarantees required

2. Complex Joins Required:

• Normalized data with many relationships
• Reports joining 5+ tables
• Ad-hoc analytics queries
• NoSQL requires denormalization (data duplication)

3. Data Integrity Crucial:

• Foreign key constraints
• Referential integrity
• Triggers and stored procedures
• Database-enforced validation

4. Mature Ecosystem Needed:

• 30+ years of tools and expertise
• BI tools (Tableau, PowerBI) work out of box
• ETL pipelines well-established
• Easier to hire SQL developers

5. Scale Requirements Moderate:

• Single server can handle millions of rows
• Read replicas for scalability
• Most apps don't need Netflix-scale
• Vertical scaling often sufficient

Real Example: Shopify uses MySQL because e-commerce requires ACID guarantees - you can't charge customer twice or oversell inventory!

Answer:

Yes! This is called "Polyglot Persistence" - using different databases for different parts of your application.

Real-World Example: Spotify

• Cassandra: User playlists, listening history (write-heavy, 300M users)
• PostgreSQL: Payment processing, subscriptions (ACID required)
• BigTable: Music metadata, album art (Google Cloud)
• Redis: Session storage, caching

Common Pattern: E-commerce Site

• MySQL: Orders, payments, inventory (consistency critical)
• MongoDB: Product catalog (flexible schema)
• Cassandra: User activity logs, clickstream
• Elasticsearch: Product search
• Redis: Shopping cart, sessions

Benefits:

• Use right tool for each job
• Optimize each component separately
• Reduce compromises

Challenges:

• Operational complexity (multiple systems to manage)
• Data synchronization between databases
• Team needs expertise in multiple technologies
• Deployment and monitoring more complex

Decision Rule: Start simple (one database), add others when you have a clear problem that the new database solves better.

Answer:

Cassandra Replication (Multi-Master):

• Model: Every node can handle reads AND writes
• Replication Factor: Data copied to N nodes (e.g., RF=3)
• No Master: Peer-to-peer, all nodes equal
• Consistency: Tunable per query (ONE, QUORUM, ALL)
• Conflict Resolution: Last-write-wins (timestamp-based)
• Availability: Zero downtime - any node can serve requests

Example:

-- Cassandra: Write to any node
Client → Node 1 (writes locally, replicates to Node 2, Node 3)
Client → Node 2 (can also handle same write!)
// No coordination needed between nodes

MongoDB Replication (Primary-Secondary):

• Model: One primary (writes), multiple secondaries (reads)
• Replica Set: Typically 3 nodes (1 primary, 2 secondaries)
• Automatic Failover: If primary fails, election picks new primary (10-30 sec)
• Consistency: Strong consistency from primary
• Write Concern: Can wait for replication (majority, all)
• Oplog: Operation log replicated to secondaries

Example:

-- MongoDB: Must write to primary
Client → Primary (writes, logs to oplog)
Primary → Secondary1 (async replication)
Primary → Secondary2 (async replication)
// Secondaries can't accept writes

Key Differences:

• Write Distribution: Cassandra spreads writes across all nodes; MongoDB bottlenecks at primary
• Failover: Cassandra instant (no failover needed); MongoDB 10-30 sec election
• Consistency: Cassandra tunable/eventual; MongoDB strong by default
• Complexity: Cassandra more complex; MongoDB simpler mental model

Answer:

Cassandra's Limitations:

1. No Joins:

• Must denormalize data (duplicate across tables)
• Application-side joins required
• Example: User + Posts requires two queries or denormalized table
• MongoDB has $lookup (limited joins), MySQL has full JOIN support

2. Limited Transactions:

• Lightweight transactions (LWT) only, very slow
• No multi-row ACID transactions
• MongoDB has ACID since 4.0, MySQL has full ACID
• Use case impact: Can't do bank transfer (debit + credit atomic)

3. Query Limitations:

• Must query by partition key (no full table scans)
• Limited filtering without indexes
• No GROUP BY, ORDER BY on arbitrary columns
• MongoDB/MySQL support complex ad-hoc queries

4. Eventual Consistency (Default):

• Read may return stale data
• Must tune consistency level for strong consistency (performance hit)
• MongoDB strong consistency by default
• Problem: User updates profile, reads old data immediately

5. Data Modeling Complexity:

• Must design for queries upfront
• Changing query patterns requires redesign
• Denormalization means data duplication and sync issues
• MongoDB more flexible, MySQL schema can evolve

6. Learning Curve:

• Concepts: partition keys, clustering columns, token ranges
• Tuning consistency, replication factors, compaction strategies
• MySQL familiar (SQL), MongoDB easier (JSON)

7. No Aggregations:

• Simple COUNT, SUM possible but not GROUP BY across partitions
• Analytics require external tools (Spark)
• MongoDB aggregation pipeline powerful
• MySQL full GROUP BY, HAVING, window functions

When Limitations Don't Matter: Time-series data (no joins needed), write-heavy workloads, eventual consistency acceptable, known query patterns

Answer:

MySQL - Normalization (Minimize Redundancy):

• Approach: Divide data into related tables, use foreign keys
• Example: Users table + Posts table, joined by user_id
• Benefits: No duplicate data, easy updates, data integrity
• Query: JOIN tables when reading

-- MySQL: Normalized
Users: {id, name, email}
Posts: {id, user_id, title, content}

-- Query requires JOIN
SELECT * FROM posts 
JOIN users ON posts.user_id = users.id;

MongoDB - Embedding vs Referencing:

• Approach: Flexible - embed related data OR reference
• Embed: Store everything in one document (denormalized)
• Reference: Store IDs, query separately (normalized)
• Rule: Embed if data accessed together, reference if independent

// MongoDB: Embedded (denormalized)
{
  _id: 1,
  name: "Alice",
  email: "alice@example.com",
  posts: [
    {title: "Post 1", content: "..."},
    {title: "Post 2", content: "..."}
  ]
}

// MongoDB: Referenced (normalized)
User: {_id: 1, name: "Alice", email: "alice@example.com"}
Posts: [
  {_id: 101, user_id: 1, title: "Post 1"},
  {_id: 102, user_id: 1, title: "Post 2"}
]

Cassandra - Query-Driven Denormalization:

• Approach: Design tables for queries, duplicate data heavily
• Rule: One query = One table (no joins!)
• Example: Want "posts by user" AND "posts by date"? Create 2 tables!
• Trade-off: Fast reads, but duplicate data and sync issues

-- Cassandra: Denormalized (query-driven)

-- Table 1: Query posts by user
CREATE TABLE posts_by_user (
  user_id UUID,
  post_id UUID,
  user_name TEXT,  -- Duplicated!
  title TEXT,
  content TEXT,
  PRIMARY KEY (user_id, post_id)
);

-- Table 2: Query posts by date
CREATE TABLE posts_by_date (
  date DATE,
  post_id UUID,
  user_id UUID,
  user_name TEXT,  -- Duplicated again!
  title TEXT,
  content TEXT,
  PRIMARY KEY (date, post_id)
);

Comparison Summary:

• MySQL: Design for data integrity, query flexibility via JOINs
• MongoDB: Design for how data is used together (embed or reference)
• Cassandra: Design for exact queries you'll run (one query = one table)

Data Modeling Order:

• MySQL: Model entities → Add relationships → Write queries
• MongoDB: Model entities → Decide embed/reference → Write queries
• Cassandra: Define queries → Design tables → Insert data

Answer:

Cassandra - Automatic Linear Scaling:

• Mechanism: Consistent hashing distributes data across nodes
• Add Node: Automatically rebalances data, no downtime
• Scaling: Linear - 2x nodes = 2x throughput
• Process:
  • 1. Add new node to cluster
  • 2. Node joins ring, gets token range
  • 3. Data streams from existing nodes
  • 4. Done! No application changes
• Benefit: Can scale from 3 to 1000 nodes seamlessly

// Cassandra: Just add nodes
3 nodes → 100K writes/sec
6 nodes → 200K writes/sec (linear!)
12 nodes → 400K writes/sec

// Example: Add node
$ nodetool status
-- New node automatically gets 1/N of data

MongoDB - Sharding (Good Scaling):

• Mechanism: Shard key divides data across shards
• Add Shard: Config servers coordinate, mongos routes queries
• Scaling: Good, but requires planning
• Process:
  • 1. Choose shard key (critical decision!)
  • 2. Deploy config servers + mongos routers
  • 3. Add shards (replica sets)
  • 4. Balancer redistributes chunks
• Gotcha: Poor shard key = uneven distribution

// MongoDB: Sharding setup
sh.enableSharding("mydb")
sh.shardCollection("mydb.users", {user_id: 1})

// Problem: Bad shard key
{region: "US"} // 80% of users in US = hot shard!

// Good shard key
{user_id: "hashed"} // Evenly distributed

MySQL - Manual Sharding (Hard):

• Mechanism: Application-level sharding
• Add Server: Manual split, migrate data, update app
• Scaling: Requires significant engineering
• Process:
  • 1. Decide sharding strategy (e.g., user_id % 4)
  • 2. Deploy new MySQL instance
  • 3. Write scripts to migrate data
  • 4. Update application routing logic
  • 5. Test thoroughly!
• Reality: Most companies use read replicas instead

// MySQL: Application sharding
// In application code
shard_id = user_id % 4
if shard_id == 0: connect to db0
if shard_id == 1: connect to db1
if shard_id == 2: connect to db2
if shard_id == 3: connect to db3

// Problem: Resharding requires downtime!

Comparison:

• Easiest: Cassandra (automatic, zero downtime)
• Middle: MongoDB (built-in but requires planning)
• Hardest: MySQL (manual, application-level)

Real Example: Instagram scaled from 1M to 2B users on same Cassandra architecture. With MySQL, they would have needed massive resharding efforts multiple times!

Answer:

Cassandra - Write-Optimized:

• Write Performance: Extremely fast (300K+ writes/sec per node)
• Why Fast:
  • Sequential writes to commit log (append-only)
  • In-memory memtable (no disk seeks)
  • No read-before-write
  • No locking or blocking
• Read Performance: Fast IF you query by partition key
• Why Slower Reads: May need to check multiple SSTables, merge results
• Best For: IoT sensors (millions of writes/sec), logs, metrics

// Cassandra write path
1. Write to commit log (sequential, fast!)
2. Write to memtable (memory, instant!)
3. Return success to client
4. Later: flush memtable to SSTable

// Write: ~0.5ms
// Read by partition key: ~5ms

MongoDB - Balanced:

• Write Performance: Fast (150K writes/sec)
• Why Fast:
  • WiredTiger storage engine optimized
  • Document-level locking
  • Journal for durability
• Read Performance: Very fast (400K reads/sec)
• Why Fast Reads: Rich indexing, B-tree structure, good caching
• Best For: Balanced workloads, content delivery

// MongoDB performance
Write: ~2ms (with journaling)
Read (indexed): ~2ms
Read (unindexed): Much slower

// Good for: 50/50 read/write ratio

MySQL - Read-Optimized (with tuning):

• Write Performance: Moderate (50K writes/sec)
• Why Slower:
  • Must update indexes on every write
  • Row-level locking can cause contention
  • Foreign key checks
  • Transaction overhead
• Read Performance: Fast (200K reads/sec with indexes)
• Why Fast Reads: B+ tree indexes, query optimizer, buffer pool
• Best For: Read-heavy workloads (blogs, e-commerce catalogs)

// MySQL performance
Write (simple): ~10ms
Write (complex, with triggers): ~50ms
Read (indexed): ~5ms
Read (full table scan): Very slow

// Good for: Read-heavy (90/10 read/write)

Benchmark Summary (3-node cluster):

• Writes/sec: Cassandra 300K > MongoDB 150K > MySQL 50K
• Reads/sec: MongoDB 400K > Cassandra 350K > MySQL 200K
• Write Latency: Cassandra 0.5ms < MongoDB 2ms < MySQL 10ms
• Read Latency: MongoDB 2ms < Cassandra 5ms < MySQL 5ms

Use Case Match:

• Write-Heavy (80/20): Choose Cassandra
• Balanced (50/50): Choose MongoDB
• Read-Heavy (90/10): MySQL or MongoDB

Answer:

Cassandra - Tunable Consistency:

• Model: Eventually consistent by default, tunable per query
• Consistency Levels:
  • ONE: Wait for 1 replica (fastest, eventual)
  • QUORUM: Wait for majority (N/2 + 1)
  • ALL: Wait for all replicas (strongest, slowest)
• Trade-off: Higher consistency = lower availability & performance

// Cassandra: Choose per query
-- Fast, eventual consistency
SELECT * FROM users WHERE id = 123 
  USING CONSISTENCY ONE;

-- Strong consistency (CP behavior)
SELECT * FROM orders WHERE id = 456 
  USING CONSISTENCY QUORUM;

// Formula: R + W > N guarantees consistency
// R=read replicas, W=write replicas, N=replication factor
// Example: N=3, R=2, W=2 → 2+2>3 ✓ Strong consistency

MongoDB - Strong Consistency (Default):

• Model: Strong consistency from primary
• Read Preference:
  • primary: Read from primary (default, strong consistency)
  • primaryPreferred: Primary if available, else secondary
  • secondary: Read from secondary (eventual consistency)
• Write Concern:
  • w:1: Acknowledge after primary writes (default)
  • w:majority: Wait for majority acknowledgment

// MongoDB: Strong consistency default
db.users.find({_id: 123})  // Reads from primary

// Can choose eventual for performance
db.users.find({_id: 123})
  .readPref("secondary")  // Eventual consistency

// Write concern
db.orders.insertOne({...}, {writeConcern: {w: "majority"}})

MySQL - Strong Consistency (ACID):

• Model: Always strong consistency (ACID guarantees)
• Isolation Levels:
  • READ UNCOMMITTED: Dirty reads possible
  • READ COMMITTED: No dirty reads (default)
  • REPEATABLE READ: InnoDB default, no phantom reads
  • SERIALIZABLE: Strongest, locks ranges
• Replication: Async by default (eventual on slaves)

-- MySQL: Strong consistency on master
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Atomic, consistent

-- But: Slaves have eventual consistency
-- Read from slave might show old balance!

Comparison Example - Bank Transfer:

Scenario: Transfer $100 from Alice to Bob

Cassandra (QUORUM):
- Write to 2 of 3 replicas
- Read from 2 of 3 replicas
- Guaranteed to see write
- ✓ Can achieve strong consistency

MongoDB (default):
- Write to primary
- Read from primary
- Always sees latest
- ✓ Strong consistency

MySQL:
- Transaction wraps both updates
- Commit or rollback together
- ✓ Strong consistency + atomicity

Summary:

• Cassandra: Tunable per query (flexibility, complexity)
• MongoDB: Strong by default (simple, good balance)
• MySQL: Always strong (ACID, transactions)

Answer:

Cassandra - High Operational Complexity:

• Setup: Complex initial configuration (seeds, tokens, RF, DC)
• Monitoring: Many metrics to track (compaction, repairs, hints)
• Maintenance:
  • Regular repairs (nodetool repair)
  • Compaction tuning
  • Tombstone cleanup
  • Token range management
• Troubleshooting: Debugging distributed issues hard (network partitions, quorum failures)
• Scaling: Easy to add nodes, but capacity planning important
• Backup: Snapshot all nodes, coordinate restore

// Cassandra maintenance tasks
$ nodetool repair -pr  // Regular repairs needed
$ nodetool compact     // Manual compaction
$ nodetool cleanup     // After adding nodes
$ nodetool status      // Monitor cluster health

// Must understand:
- Consistency levels
- Replication factors
- Token ranges
- Read/write paths
- Compaction strategies

MongoDB - Medium Operational Complexity:

• Setup: Moderate (replica sets, sharding if needed)
• Monitoring: Good built-in tools (Atlas, Ops Manager)
• Maintenance:
  • Replica set health checks
  • Index management
  • Shard balancer (if sharded)
  • Storage engine tuning
• Troubleshooting: Easier - primary/secondary model simpler
• Scaling: Sharding requires planning, but automated balancing
• Backup: mongodump, point-in-time with oplog

// MongoDB maintenance
rs.status()           // Check replica set
db.stats()           // Database stats
sh.status()          // Sharding status (if enabled)
mongodump            // Backup

// Simpler concepts:
- Primary/Secondary (easier than peer-to-peer)
- Automatic failover (less manual intervention)
- Cloud options (Atlas managed service)

MySQL - Low to Medium Complexity:

• Setup: Simple for single instance, moderate for replication
• Monitoring: Mature tools (decades of tooling)
• Maintenance:
  • Regular backups
  • Index optimization
  • Replication lag monitoring
  • Query optimization
• Troubleshooting: Well-documented, large community
• Scaling: Vertical easy, horizontal requires manual work
• Backup: mysqldump, binary logs, snapshots

-- MySQL maintenance
SHOW SLAVE STATUS;           -- Replication health
OPTIMIZE TABLE users;        -- Defragment
ANALYZE TABLE posts;         -- Update statistics
mysqldump -u root mydb > backup.sql  -- Backup

// Benefits:
- 30+ years of battle-testing
- Huge community
- Many DBAs available
- Mature tooling ecosystem

Team Requirements:

• Cassandra: Requires distributed systems expertise, dedicated DBA team
• MongoDB: Moderate expertise, can be managed by developers
• MySQL: Well-known, easy to hire DBAs, developer-friendly

Managed Services:

• Cassandra: DataStax Astra, AWS Keyspaces
• MongoDB: MongoDB Atlas (excellent)
• MySQL: AWS RDS, Azure Database, Google Cloud SQL

Recommendation: If you don't have distributed systems expertise, consider managed services or choose MongoDB/MySQL!

Answer:

Migration Strategy - Phased Approach:

Phase 1: Analysis & Planning (2-4 weeks)

• Identify Query Patterns: Analyze ALL queries in application
• Data Model Redesign: Denormalize MySQL schema for Cassandra
  • One query = one table
  • Duplicate data where needed
  • Design partition keys carefully
• Identify Challenges:
  • JOINs → Create denormalized tables
  • Transactions → Redesign workflow or keep in MySQL
  • Complex queries → May need application-level logic

Phase 2: Dual-Write Setup (4-6 weeks)

• Setup Cassandra Cluster: Start with development environment
• Implement Dual-Write:

  // Application code
  function createUser(userData) {
    // Write to MySQL (primary source of truth)
    mysqlResult = mysql.insert(userData);
    
    try {
      // Also write to Cassandra (async, non-blocking)
      cassandra.insert(transformForCassandra(userData));
    } catch (error) {
      // Log but don't fail - MySQL is still source of truth
      logError("Cassandra write failed", error);
    }
    
    return mysqlResult;
  }

• Historical Data Migration:
  • Export from MySQL in batches
  • Transform data for Cassandra model
  • Use COPY command or bulk loaders

Phase 3: Read Migration (2-4 weeks)

• Shadow Reads: Read from both, compare results
• Gradual Cutover:

  // Feature flag based migration
  function getUser(userId) {
    if (featureFlag.cassandraReads() && randomPercent() < 10) {
      // 10% of reads from Cassandra
      return cassandra.select(userId);
    } else {
      // 90% still from MySQL
      return mysql.select(userId);
    }
  }
  
  // Gradually increase: 10% → 25% → 50% → 100%

• Monitor Carefully: Latency, error rates, data consistency

Phase 4: Full Cutover (1-2 weeks)

• Make Cassandra Primary: Stop writing to MySQL
• Keep MySQL as Backup: Don't delete immediately!
• Monitor for 2-4 weeks: Ensure stability
• Decommission MySQL: Only after proven success

Example - E-commerce Order System:

-- MySQL schema (normalized)
users: {id, name, email}
orders: {id, user_id, total, created_at}
order_items: {id, order_id, product_id, quantity}

-- Cassandra schema (denormalized)
CREATE TABLE orders_by_user (
  user_id UUID,
  order_id TIMEUUID,
  user_name TEXT,        -- Denormalized
  user_email TEXT,       -- Denormalized
  order_total DECIMAL,
  items LIST>,  -- Embedded
  PRIMARY KEY (user_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

CREATE TABLE orders_by_date (
  date DATE,
  order_id TIMEUUID,
  user_id UUID,
  user_name TEXT,        -- Denormalized again!
  order_total DECIMAL,
  PRIMARY KEY (date, order_id)
);

Common Pitfalls to Avoid:

• Big Bang Migration: Don't switch everything at once
• Ignoring Query Patterns: Must design for known queries
• Underestimating Complexity: Budget 3-6 months for large systems
• Not Testing Failure Scenarios: Test network partitions, node failures
• Keeping Transactional Logic: Redesign workflows or hybrid approach

Alternative: Hybrid Approach

• Keep MySQL for: Transactions, payments, inventory
• Use Cassandra for: Logs, time-series, user activity
• Best of Both Worlds: Like Spotify's architecture

Answer:

Cassandra - Higher Initial, Lower at Scale:

• Infrastructure:
  • Minimum 3 nodes for production (HA requirement)
  • Replication factor (RF=3 typical) = 3x storage
  • Example: 1TB data → 3TB storage needed
  • Cost: $500-1000/month for small cluster (3 nodes)
• Personnel:
  • Requires distributed systems expertise
  • Cassandra DBAs expensive ($150K-200K/year)
  • Longer ramp-up time for team
• At Scale:
  • Linear scaling = predictable costs
  • No expensive master upgrades
  • Example: 100 nodes = ~$30K/month (vs MySQL's complexity)
• Managed Service: DataStax Astra ($0.10-0.25/GB-hour)

MongoDB - Medium Cost, Good Value:

• Infrastructure:
  • Minimum 3 nodes for replica set
  • Storage ~1.5x data size (with indexes)
  • Example: 1TB data → 1.5TB storage
  • Cost: $300-600/month for small replica set
• Personnel:
  • Easier to find MongoDB developers
  • Salary: $120K-160K/year (lower than Cassandra)
  • Faster team ramp-up
• At Scale:
  • Sharding adds complexity cost
  • Good middle ground for most companies
• Managed Service: MongoDB Atlas ($0.08-0.72/hour depending on tier)

MySQL - Lowest Initial, Higher at Scale:

• Infrastructure:
  • Can start with single instance
  • Storage = data size (most efficient)
  • Example: 1TB data → 1TB storage
  • Cost: $100-300/month for small instance
• Personnel:
  • Easiest to hire (huge talent pool)
  • MySQL DBAs: $100K-140K/year
  • Many developers already know SQL
• At Scale:
  • Vertical scaling gets expensive ($5K+/month for large instance)
  • Sharding requires custom engineering (expensive!)
  • May need to migrate eventually
• Managed Service: AWS RDS ($0.017-3.00/hour depending on instance)

Cost Comparison Example - 1 Year:

Startup (10GB data, 1K users):
  MySQL:     $1,200/year (1 instance)
  MongoDB:   $3,600/year (3-node replica set)
  Cassandra: $6,000/year (3-node cluster)
  Winner: MySQL

Mid-size (100GB data, 100K users):
  MySQL:     $12,000/year (1 large + 2 replicas)
  MongoDB:   $18,000/year (3-node sharded cluster)
  Cassandra: $18,000/year (6-node cluster)
  Winner: MySQL/MongoDB tie

Large scale (10TB data, 10M users):
  MySQL:     $100K+/year (complex sharding, custom work)
  MongoDB:   $60,000/year (sharded cluster)
  Cassandra: $50,000/year (20-node cluster, predictable)
  Winner: Cassandra

Hidden Costs to Consider:

• Downtime Cost: Cassandra's HA worth it for critical apps
• Development Time: MongoDB faster to develop = lower labor cost
• Migration Cost: Switching databases later is expensive ($100K+)
• Monitoring/Tools: MySQL has free tools, others may need paid solutions

Recommendation:

• Bootstrap/Startup: MySQL (lowest cost, proven)
• Growth Phase: MongoDB (good balance)
• Massive Scale: Cassandra (predictable linear costs)

Answer:

Decision Framework - Ask These Questions:

1. What's Your Scale Requirement?

• Small-Medium (<100K users): MySQL is fine
  • Single instance handles millions of rows
  • Vertical scaling sufficient
  • Example: Blog, small SaaS, internal tools
• Large (100K-10M users): MongoDB
  • Sharding available when needed
  • Good balance of features
  • Example: E-commerce, content platforms
• Massive (10M+ users): Cassandra
  • Linear scalability proven
  • No single point of failure
  • Example: Social media, IoT platforms

2. What's Your Data Structure?

• Highly Relational: MySQL
  • Many relationships, foreign keys
  • Normalized data model
  • Example: ERP, CRM, accounting
• Semi-Structured: MongoDB
  • Flexible schema
  • Document-oriented
  • Example: Product catalogs, CMS
• Time-Series: Cassandra
  • Append-only workloads
  • Time-based queries
  • Example: Logs, metrics, IoT sensors

3. What's Your Consistency Requirement?

• Strong Consistency Required: MySQL
  • Financial transactions
  • Inventory management
  • Cannot tolerate stale reads
• Balanced: MongoDB
  • Strong consistency available
  • Can opt into eventual if needed
• Eventual Consistency OK: Cassandra
  • Social media feeds
  • Analytics
  • Shopping carts (can reconcile)

4. What's Your Availability Requirement?

• 99.9% OK (43min downtime/month): MySQL
  • Planned maintenance windows acceptable
  • Example: Internal tools, B2B SaaS
• 99.99% Required (4min downtime/month): MongoDB
  • Automatic failover
  • Example: E-commerce, mobile apps
• 99.999%+ Critical (26sec downtime/month): Cassandra
  • Always-on requirement
  • Example: Payment processing, streaming

5. What's Your Team's Expertise?

• SQL Experts: MySQL (leverage existing knowledge)
• General Developers: MongoDB (easier learning curve)
• Distributed Systems Team: Cassandra (can handle complexity)

6. What's Your Budget?

• Tight Budget: MySQL (lowest initial cost)
• Medium Budget: MongoDB (good ROI)
• Scale Budget: Cassandra (worth it at scale)

Decision Tree Summary:

START HERE:

Do you need ACID transactions?
├─ YES → MySQL
└─ NO → Continue

Is your write volume extremely high (100K+ writes/sec)?
├─ YES → Cassandra
└─ NO → Continue

Do you need flexible schema?
├─ YES → MongoDB
└─ NO → Continue

Is 99.999% uptime critical?
├─ YES → Cassandra
└─ NO → MongoDB

DEFAULT: Start with MongoDB (good all-around choice)

Real-World Recommendation:

For 80% of new projects, I'd recommend starting with MongoDB:

• Good balance of consistency, availability, performance
• Flexible enough to adapt as requirements change
• Easier to hire for than Cassandra
• Can always add Cassandra later for specific high-volume use cases

Choose MySQL if: Strong ACID guarantees required, team already knows SQL, moderate scale

Choose Cassandra if: Always-on requirement, massive write volume, time-series data, already at scale