SQL vs NoSQL Decision Framework
A practical decision framework for choosing between SQL and NoSQL: data model, query patterns, consistency needs, and scaling requirements.
The Real Question Is Not SQL vs NoSQL
The SQL vs NoSQL debate is often framed incorrectly. The question is not "which is better" — it's "which data model fits this access pattern?" Both SQL and NoSQL databases can operate at massive scale. Both can handle high write throughput. The decision comes down to four dimensions: data model, query flexibility, consistency requirements, and operational constraints.
Dimension 1: Data Model
Start by asking: How is my data naturally structured? Relational data has clear entity types with relationships between them (users, orders, products). Document data is hierarchical and self-contained (a blog post with embedded comments). Wide-column data is event-driven (sensor readings, activity logs). Graph data is defined by its connections (social networks, fraud graphs).
| Data Shape | Recommended Model | Example |
|---|---|---|
| Entities with many relationships and joins | Relational (PostgreSQL) | E-commerce: users, orders, products, inventory |
| Hierarchical, variable-schema documents | Document (MongoDB) | Product catalog with different attributes per category |
| High-volume time-ordered events | Wide-column (Cassandra) or Time-series | IoT sensor logs, activity feeds |
| Heavily connected data, traversal queries | Graph (Neo4j) | Social network, fraud detection |
| Simple lookup by single key | Key-value (Redis, DynamoDB) | Session store, feature flags, counters |
Dimension 2: Query Patterns
Know your queries before choosing your database. SQL's strength is ad-hoc query flexibility — you can filter, sort, group, and join on any column. NoSQL databases trade query flexibility for performance on predefined access patterns. If your application will need to support complex, evolving analytics, SQL wins. If your top 3 queries are known and unchanging, NoSQL may win on raw performance.
- Favor SQL when: you need aggregations, complex filters, JOINs across entities, or reporting queries you can't predict upfront.
- Favor NoSQL when: your queries are predictable, performance-critical, and map to simple key lookups or document fetches.
- Favor wide-column when: your queries always include the partition key and you need massive write throughput.
Dimension 3: Consistency Requirements
ACID transactions are non-negotiable in some domains. If your system handles money, inventory, or legal records, strong consistency is required. If it handles social feeds, analytics, or content recommendations, eventual consistency is acceptable and unlocks massive scalability.
| Use Case | Consistency Need | Recommended DB |
|---|---|---|
| Bank transfers, double-spend prevention | Strong (ACID) | PostgreSQL, MySQL |
| Flight seat booking | Strong (serializable) | PostgreSQL with `SELECT FOR UPDATE` |
| User profile updates | Read-your-writes suffices | MongoDB (with majority write concern) |
| Social media likes counter | Eventual | Cassandra, Redis INCR |
| Product catalog reads | Eventual | DynamoDB, MongoDB |
Dimension 4: Scale Requirements
Do not over-engineer for scale you don't have. PostgreSQL with proper indexing can handle 100,000 QPS. Amazon uses PostgreSQL-derived Aurora at enormous scale. NoSQL databases are not automatically "more scalable" — they simply make different trade-offs that enable horizontal scaling at the cost of consistency and query flexibility.
Start with SQL, Migrate if Needed
The most practical advice for new systems: start with PostgreSQL. It's battle-tested, has excellent tooling, supports JSON columns for semi-structured data, and scales further than most teams realize. Only migrate to NoSQL when you've identified a specific bottleneck that SQL cannot address with sharding and read replicas.
Decision Flowchart
Using Both: Polyglot Persistence
Production systems rarely use a single database. Polyglot persistence means using different databases for different parts of the system based on their strengths. A typical e-commerce stack might use PostgreSQL for orders, Redis for sessions and cart, Elasticsearch for product search, and Cassandra for user activity feeds. Each database is chosen for its specific use case.
Interview Tip
In a system design interview, after choosing your databases, always justify the choice. The magic phrase is: 'I'm choosing X because the primary access pattern is Y, and we need Z consistency.' For example: 'I'm choosing Cassandra for the activity feed because the primary access pattern is time-ordered reads by user_id, we write millions of events per second, and eventual consistency is acceptable since users don't need to see their own post immediately.' This demonstrates deliberate reasoning, not memorized answers.