SQL vs NoSQL

The decision

Database selection is foundational: the wrong choice creates performance ceilings, makes certain query patterns impossible, and requires expensive migrations to correct. The primary fork is between relational databases (SQL) that organize data into tables with enforced schemas and relationships, and non-relational databases (NoSQL) that trade some guarantees for flexibility in data modeling or horizontal scalability.

NoSQL is not a single category but an umbrella for four distinct data models: document stores (MongoDB, Firestore) store flexible JSON-like records; key-value stores (Redis, DynamoDB) offer O(1) lookup by key; wide-column stores (Cassandra, Bigtable) organize data by row and column family for time-series and write-heavy workloads; and graph databases (Neo4j, Neptune) model relationships as first-class citizens for highly connected data.

The decision is rarely binary. Polyglot persistence—using the right database for each use case within a single system—is the norm in mature architectures. A user-facing application might use PostgreSQL for transactional data, Redis for session caching, Elasticsearch for full-text search, and DynamoDB for event logs. The skill is knowing which tool fits each access pattern rather than finding a single database that does everything adequately.

Why it matters

Database migrations are among the most expensive operations in software engineering. Changing from a relational to a document database mid-product requires rewriting data access code, migrating existing data, and potentially redesigning the domain model. Teams that discover they chose wrong after eighteen months of production data accrual face months of migration work. Getting this decision right early saves enormous downstream cost.

The stakes differ by access pattern. Relational databases enforce referential integrity at the database level—a foreign key constraint prevents orphaned records from being inserted. NoSQL databases typically enforce these guarantees at the application layer, meaning a bug in application code can silently corrupt data relationships. Conversely, NoSQL databases that replicate writes across multiple nodes provide availability and partition tolerance that relational databases cannot match without significant operational complexity.

Performance characteristics diverge dramatically at scale. PostgreSQL with proper indexing handles hundreds of millions of rows efficiently for relational queries. Cassandra handles millions of writes per second across a distributed cluster. Redis serves hundreds of thousands of requests per second from memory. No single database spans all these regimes; the question is which performance characteristic is critical for each workload.

Choose SQL when

• The data has clear relationships that benefit from joins (e.g., orders belong to customers, line items belong to orders)
• ACID transactions spanning multiple records or tables are required
• The schema is well-understood and stable—schema evolution via migrations is acceptable
• Complex reporting queries, aggregations, or ad-hoc analytics are a core use case
• Regulatory compliance requires strong consistency and audit trail capabilities
• The team has deep SQL expertise and ORM tooling is well-established in your stack
• Data integrity constraints (unique keys, foreign keys, check constraints) are valuable safety guarantees

Choose NoSQL when

• The data model is document-oriented and JSON storage with flexible schemas reduces impedance mismatch
• Horizontal write scaling is required beyond what a single relational node can handle
• Access patterns are simple and well-defined (e.g., always look up by user ID), making joins unnecessary
• Data is highly variable in structure—different records have entirely different fields
• Low-latency key-based lookups are the primary operation (key-value store)
• Time-series data with append-heavy write patterns and range queries by time (wide-column)
• The domain is relationship-centric: social graphs, recommendation engines, fraud networks (graph database)

Comparison

SQL RELATIONAL MODEL                    NOSQL DOCUMENT MODEL
══════════════════════════════          ══════════════════════════════════

users table                             users collection
┌────┬──────────┬─────────────┐         {
│ id │ name     │ email       │           "_id": "u123",
├────┼──────────┼─────────────┤           "name": "Alice",
│ 1  │ Alice    │ a@test.com  │           "email": "a@test.com",
│ 2  │ Bob      │ b@test.com  │           "addresses": [
└────┴──────────┴─────────────┘             { "type": "home", "city": "NY" },
                                            { "type": "work", "city": "LA" }
addresses table                           ],
┌────┬─────────┬──────┬───────┐           "preferences": {
│ id │ user_id │ type │ city  │             "theme": "dark",
├────┼─────────┼──────┼───────┤             "notifications": true
│ 1  │ 1       │ home │ NY    │           }
│ 2  │ 1       │ work │ LA    │         }
└────┴─────────┴──────┴───────┘

SELECT u.name, a.city                    db.users.find({ "_id": "u123" })
FROM users u                             → Single document, no join needed
JOIN addresses a ON u.id = a.user_id
WHERE u.id = 1

NOSQL TYPES — WHEN TO USE EACH
════════════════════════════════════════════════════════════

Key-Value (Redis, DynamoDB)
  Use: session data, caching, rate limiting, feature flags
  Access: O(1) by key only, no complex queries
  Example: GET session:abc123 → {userId: 1, expires: ...}

Document (MongoDB, Firestore, Couchbase)
  Use: user profiles, product catalogs, content management
  Access: queries on any field, flexible schema per document
  Example: find({category:"shoes", price:{$lt:100}})

Wide-Column (Cassandra, Bigtable, HBase)
  Use: time-series, IoT telemetry, activity feeds, logs
  Access: partition key (fast) + clustering columns (range)
  Example: SELECT * FROM events WHERE user_id=1 AND ts > now()-1d

Graph (Neo4j, Amazon Neptune, JanusGraph)
  Use: social networks, fraud detection, recommendation engines
  Access: traverse relationships of arbitrary depth efficiently
  Example: MATCH (u)-[:FRIEND*2]->(rec) WHERE u.id=1
          

Trade-offs

Implementation considerations

For SQL databases, invest early in a migration strategy. Tools like Flyway, Liquibase, or Alembic track and apply schema changes reproducibly. Treat migrations as code: version-controlled, reviewed, and tested. Avoid schema changes that lock large tables in production—use online schema change tools like pt-online-schema-change or gh-ost for MySQL, or native concurrent index builds in PostgreSQL. Plan for read scaling with read replicas well before you hit primary capacity limits.

For document databases, the most important design decision is embedding versus referencing. Data that is always read together should be embedded in a single document (e.g., order line items embedded in the order document). Data that is read independently or that would cause unbounded document growth should be stored in separate collections and referenced by ID. MongoDB's document size limit (16MB) and Firestore's subcollection model both enforce thinking about this trade-off explicitly.

For Cassandra and wide-column stores, data modeling is access-pattern driven rather than entity driven. You design tables around queries, not around normalized data. It is normal and expected to duplicate data across multiple tables to serve different query patterns. Partition key selection is critical: a poorly chosen partition key that concentrates writes on a single node creates a hot partition that degrades performance and availability across the entire cluster.

Common mistakes

• Using NoSQL to avoid schema design: Document databases still require careful data modeling—they just enforce it at the application layer rather than the database layer, making bad design harder to detect and more dangerous.
• Using SQL for massive write-scale: Trying to handle millions of writes per second through a single PostgreSQL primary will hit hardware limits; wide-column or key-value stores are designed for this workload.
• Joining across services in microservices: When each service owns its database, cross-service joins are impossible at the database layer—requiring either API composition or denormalization, which is a design signal to reconsider service boundaries.
• Ignoring polyglot persistence: Choosing one database for everything instead of using specialized databases for specific use cases leads to poor performance and complex workarounds.
• Cassandra without partition modeling: Treating Cassandra like a relational database by running unbounded scans or frequent cross-partition queries produces catastrophic performance.

Decision checklist

• What are the primary access patterns? Look up by ID, complex joins, range queries, full-text search?
• Is the data schema stable or does it change frequently with evolving requirements?
• Are multi-record ACID transactions required, or is eventual consistency acceptable?
• What are the write volume and read latency requirements—do they exceed what a single SQL node can provide?
• Are there reporting or analytics requirements that benefit from SQL's ad-hoc query capabilities?
• Is the data highly relational (many joins) or document-oriented (self-contained records)?
• What database expertise does the team have, and what is the operational cost of learning a new system?

Real-world examples

• GitHub: Uses MySQL for most relational data (repositories, users, pull requests) with extensive read replicas, while using Redis for caching and Elasticsearch for code search—a classic polyglot architecture where each database handles what it does best.
• Netflix: Migrated from Oracle to Cassandra for its viewing history and user activity data, requiring massive horizontal write scale across global regions. ACID guarantees were unnecessary for activity logs; availability and write throughput were critical.
• Airbnb: Uses MySQL as its primary operational database for bookings and payments (requiring ACID transactions), while using HBase for large analytical datasets and Elasticsearch for property search—combining relational correctness for financial data with NoSQL performance for specialized workloads.

Related decisions

• SQL Database Selection — choosing between PostgreSQL, MySQL, SQL Server, and distributed SQL
• Caching Strategy Guide — using Redis and other key-value stores alongside your primary database
• Data Architecture — broader patterns for managing data at scale
• Monolith vs Microservices — microservices require database-per-service patterns

Further reading

• NoSQL Explained — MongoDB Documentation
• NoSQL Databases on AWS — Amazon Web Services
• NoSQL Distilled — Pramod Sadalage & Martin Fowler