Enterprise Database Scaling: Operational Intelligence Summary

Critical Decision Framework

PostgreSQL 17.6 - High Performance, High Complexity

Best For: Complex analytical workloads, OLAP systems, advanced query requirements
Avoid For: Simple CRUD applications, teams without specialized expertise

Configuration That Works

• Connection Limit Crisis: Default 100 connections is production-breaking
• Required: PgBouncer connection pooling (4-8MB RAM per connection)
• Memory Requirements: 32GB+ minimum for analytics workloads
• Read Scaling: 3-5 read replicas required for high availability

Critical Failure Modes

• Autovacuum I/O Storms: Can consume 100% I/O during business hours
• Memory Spikes: Version 17.6 parallel queries use 4x more RAM than 17.5
• Connection Exhaustion: Hits limits with traffic that wouldn't stress load tests

Real Costs

• Infrastructure: $2,400/month base + $2,400/replica (AWS RDS)
• Expertise Premium: DBAs cost $140-200k annually (40% more than MySQL)
• 5-Year TCO: $500k-800k for 100k daily users

MySQL 8.4.6 LTS - Operational Simplicity

Best For: OLTP workloads, teams wanting predictable operations
Avoid For: Complex analytical queries, advanced JSON operations

Configuration That Works

• Connection Capacity: 100,000+ concurrent with proper tuning
• High Availability: Multi-AZ doubles cost but includes automated failover
• Read Scaling: Battle-tested read replicas, well-documented

Critical Failure Modes

• Query Optimizer Limits: 1990s optimizer fails on complex JOINs
• Binary Log Growth: Logs can consume all disk space during high traffic
• Enterprise Features: Many security features require commercial license

Real Costs

• Infrastructure: $2,200/month base (AWS RDS)
• Expertise Standard: DBAs cost $120-170k annually
• 5-Year TCO: $300k-500k for 100k daily users

MongoDB 8.0.9 - Development Speed vs. Operational Cost

Best For: Document-heavy applications, rapid prototyping
Avoid For: Strong consistency requirements, cost-sensitive deployments

Configuration That Works

• Sharding Required: Single replica set hits limits quickly
• Shard Key Selection: One-time decision that determines scaling fate
• Memory Requirements: 16GB+ for WiredTiger cache per shard

Critical Failure Modes

• Balancer Chaos: Randomly migrates data during peak traffic
• Election Storms: Replica set elections cause 30-60 second outages
• 16MB Document Limit: Breaks applications with large user data objects

Real Costs

• Infrastructure: $3,200/month base (Atlas M40) + 3x multiplier for sharding
• Atlas Lock-in: Data transfer costs scale with success
• 5-Year TCO: $600k-1.2M for 100k daily users

Redis 7.2 - Speed at Memory Cost

Best For: Caching layers, sub-millisecond response requirements
Avoid For: Primary data storage, large dataset applications

Configuration That Works

• Memory Planning: 70-80% capacity maximum due to fragmentation
• Redis Cluster: 6 nodes minimum for high availability
• Persistence Trade-offs: RDB snapshots vs AOF logging performance impact

Critical Failure Modes

• Memory Fragmentation: Can crash with 40% actual memory usage
• Linear Cost Scaling: Every GB of data requires RAM hosting fees
• Single-threaded Bottleneck: Command processing doesn't parallelize

Real Costs

• Infrastructure: $1,800/month per node (ElastiCache r6g.2xlarge)
• Cluster Minimum: $10,800/month for 6-node high availability
• 5-Year TCO: $400k-800k for 100k daily users

Cassandra 5.0.5 - Unlimited Scale, Extreme Complexity

Best For: Global applications, unlimited write scaling
Avoid For: Applications under 100M users, teams without distributed systems expertise

Configuration That Works

• Multi-datacenter Native: No single points of failure
• Storage-Attached Indexes (SAI): New in 5.0, enables multi-column queries
• Consistency Tuning: Configurable consistency levels for performance

Critical Failure Modes

• Repair Operations: Can take days, degrade performance during execution
• JVM Garbage Collection: Pauses trigger cascading cluster failures
• Tombstone Accumulation: Soft deletes without TTL cause read timeouts

Real Costs

• Managed Service: $0.50-1.00 per million operations (DataStax Astra)
• Expertise Premium: DBAs cost $160-250k annually
• 5-Year TCO: $800k-1.5M for 100k daily users

Scaling Patterns and Thresholds

Connection Scaling Limits

• PostgreSQL: 100 default → 1,000+ with pooling
• MySQL: 151 default → 100,000+ with tuning
• MongoDB: Memory-limited, ~10-20k practical
• Redis: 10,000 default, single-threaded processing bottleneck
• Cassandra: Connection-per-node model, scales with cluster size

Memory Requirements by Workload

• OLTP Applications: MySQL 16GB, PostgreSQL 32GB minimum
• Analytical Workloads: PostgreSQL 64GB+, avoid MySQL
• Document Storage: MongoDB 16GB per shard minimum
• Caching Layer: Redis entire dataset in RAM + 40% overhead
• Global Distribution: Cassandra 32GB+ per node minimum

Performance Breaking Points

• PostgreSQL: Connection exhaustion at 100 concurrent (without pooling)
• MySQL: Complex query optimizer failure on 5+ table JOINs
• MongoDB: UI unusable beyond 1,000 spans in distributed tracing
• Redis: Memory fragmentation crashes at 70-80% capacity
• Cassandra: Read timeouts during repair operations on large clusters

Migration Complexity Matrix

SQL to SQL (PostgreSQL ↔ MySQL)

• Duration: 2-6 months for large applications
• Risk Level: Medium (SQL dialect differences)
• Tools: AWS DMS, pg_loader
• Breaking Changes: Query syntax, data type mappings

SQL to NoSQL (PostgreSQL/MySQL → MongoDB)

• Duration: 6-18 months (application rewrite required)
• Risk Level: High (ACID consistency loss)
• Reality Check: Most teams regret this migration
• Hidden Costs: $50k-200k application rewrite

NoSQL to SQL (MongoDB → PostgreSQL)

• Duration: 8-24 months (schema normalization hell)
• Risk Level: Very High (data model transformation)
• Common Pattern: Companies mature into SQL needs
• Breaking Changes: ObjectId → varchar(24), embedded docs → junction tables

Cassandra Migrations (Any Direction)

• Duration: 12+ months
• Risk Level: Extreme (consistency model changes)
• Reality: Budget 3x initial estimates
• Expertise Required: Distributed systems specialists

Enterprise Cost Optimization

Total Cost of Ownership (5-Year, 100k Daily Users)

1. MySQL: $300k-500k (operational efficiency winner)
2. Redis: $400k-800k (memory scaling limits)
3. PostgreSQL: $500k-800k (expertise cost premium)
4. MongoDB: $600k-1.2M (convenience tax)
5. Cassandra: $800k-1.5M (operational complexity tax)

Hidden Cost Multipliers

• PostgreSQL: 40% higher DBA salaries, 2-3 months training
• MySQL: Predictable costs, 2-3 weeks training
• MongoDB: 2-3x cost premium for Atlas convenience
• Redis: Linear memory cost scaling with dataset growth
• Cassandra: 6-12 months team training, dedicated platform team

Cost Optimization Strategies

• Read Scaling: MySQL read replicas most cost-effective
• Analytics: PostgreSQL best performance per dollar despite expertise costs
• Global Scale: Cassandra only justified for 100M+ users
• Caching: Redis for sub-millisecond needs, watch memory costs
• Compliance: PostgreSQL and MySQL most mature security features

Critical Warnings by Database

PostgreSQL Production Gotchas

• Autovacuum: Will consume 100% I/O during business hours unpredictably
• Connection Limits: Default 100 is production-breaking
• Version Upgrades: Test memory usage thoroughly (17.6 uses 4x RAM)

MySQL Production Gotchas

• Enterprise Features: Many security features require paid licensing
• Oracle Control: Future direction uncertain under Oracle ownership
• Binary Logs: Can fill disk during high traffic, causing crashes

MongoDB Production Gotchas

• Balancer: Randomly reshards during peak traffic
• Shard Key: Immutable decision that determines scaling success/failure
• Document Size: 16MB limit breaks during user data imports

Redis Production Gotchas

• Memory Management: Full-time job, fragmentation causes crashes
• Data Persistence: RDB vs AOF trade-offs affect recovery capabilities
• Scaling Wall: Memory costs become prohibitive for large datasets

Cassandra Production Gotchas

• Repair Operations: Days-long operations that degrade performance
• Clock Sync: Drift between nodes causes mysterious data inconsistencies
• Tombstones: Accumulate without TTL settings, causing read timeouts

Decision Support Framework

Choose PostgreSQL When:

• Complex analytical queries required
• Team has or can acquire PostgreSQL expertise
• Performance per dollar matters more than operational simplicity
• Advanced SQL features needed (window functions, CTEs)

Choose MySQL When:

• Operational simplicity prioritized
• OLTP-heavy workloads
• Team wants predictable scaling costs
• 8-year LTS support needed

Choose MongoDB When:

• Document-heavy data model
• Rapid development velocity required
• Budget allows 2-3x cost premium for convenience
• Schema flexibility essential

Choose Redis When:

• Sub-millisecond response times required
• Dataset fits comfortably in available RAM budget
• Caching layer, not primary storage
• Simple key-value access patterns

Choose Cassandra When:

• Planning for 100M+ users from day one
• Global, multi-datacenter deployment required
• Team has distributed systems expertise
• Unlimited write scaling needed

Resource Requirements

Expertise Investment

• Low Complexity: MySQL (2-3 weeks training)
• Medium Complexity: Redis (3-4 weeks training)
• High Complexity: PostgreSQL, MongoDB (2-3 months training)
• Extreme Complexity: Cassandra (6-12 months training)

Infrastructure Minimums

• Development: Any database on laptop
• Production OLTP: MySQL 16GB, PostgreSQL 32GB
• Analytics: PostgreSQL 64GB+, avoid others
• Global Scale: Cassandra 32GB+ per node, multi-region
• Caching: Redis entire working set + 40% overhead

Support Ecosystem Quality

1. PostgreSQL: Excellent (multiple vendors, strong community)
2. MySQL: Excellent (Oracle + community support)
3. MongoDB: Good (MongoDB Inc. only)
4. Redis: Good (Redis Labs + community)
5. Cassandra: Limited (DataStax + Apache community)

Operational Intelligence Summary

Database choice is operational model choice. PostgreSQL bets on analytical complexity. MySQL bets on operational simplicity. MongoDB bets on development velocity. Redis bets on speed over scale. Cassandra bets on global distribution.

Failure modes are predictable. Each database breaks in documented ways. PostgreSQL: connection exhaustion. MySQL: complex query failures. MongoDB: balancer chaos. Redis: memory fragmentation. Cassandra: repair operation hell.

Team capability matters more than features. The database your team can operate effectively scales better than theoretically superior options they'll struggle to maintain.

Hidden costs dominate. Database licensing is 5-10% of total cost. Operations, expertise, and downtime costs determine real TCO.

Migration complexity is exponential. Budget 3x initial estimates. SQL-to-SQL is manageable. SQL-to-NoSQL requires application rewrites. Cassandra migrations are architectural changes.

Choose the operational model you can afford to maintain, not the theoretical performance you can't afford to optimize.