SQL vs NoSQL

A relational database stores data as tables of rows with a fixed schema, relates tables through keys, and is queried with SQL. NoSQL is an umbrella term for databases that use other data models (key-value, document, wide-column, graph) and usually relax some relational guarantees in exchange for a specific scaling or modeling advantage. The choice is not "old vs new". It is a choice between query flexibility and strong guarantees on one side, and a narrower access pattern with easier horizontal scaling on the other.

The relational model

Data is split into normalized tables so that each fact is stored once. Tables are linked with primary keys and foreign keys.

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "appears in"
    USERS {
        bigint id PK
        text email
    }
    ORDERS {
        bigint id PK
        bigint user_id FK
        timestamp created_at
    }
    ORDER_ITEMS {
        bigint order_id FK
        bigint product_id FK
        int quantity
    }
    PRODUCTS {
        bigint id PK
        text name
        numeric price
    }

Properties of relational databases:

• Schema enforced by the database. Column types, NOT NULL, UNIQUE, and foreign key constraints are checked on write.
• Declarative queries. SQL states what result is wanted. The query planner chooses indexes, join order, and join algorithms.
• Joins and ad-hoc queries. Any combination of tables and predicates can be queried, including access patterns not known at design time.
• Transactions. Multi-row, multi-table changes are atomic and isolated. See ACID transactions and isolation levels.
• Mature tooling. Examples: PostgreSQL, MySQL, Oracle, SQL Server.

A single relational node scales vertically well. Scaling writes beyond one node requires sharding, which makes cross-shard joins and transactions expensive or unavailable. Reads scale with replication.

NoSQL families

Details are covered in key-value and document stores and wide-column, graph, and time-series stores.

Common characteristics, with exceptions per product:

• Schema flexibility. Records in one collection can have different fields. Validation moves to the application (some systems offer optional schema validation).
• Denormalized data. Related data is stored together (embedded or duplicated) so that one read fetches what a request needs. See data modeling and denormalization.
• Query-first design. Data is modeled around known access patterns. Queries outside those patterns can be slow or unsupported.
• Built-in partitioning. Many NoSQL systems distribute data across nodes by partition key (often with consistent hashing) and add capacity by adding nodes.
• Weaker cross-record guarantees by default. Many offer atomicity only within one record or partition, and some offer tunable consistency. Several now support multi-document transactions (MongoDB, DynamoDB), with limits and costs.

Scaling characteristics

flowchart TB
    subgraph SQL["Relational, typical"]
        P["Primary node: all writes"] --> R1["Replica: reads"]
        P --> R2["Replica: reads"]
    end
    subgraph NOSQL["Partitioned NoSQL, typical"]
        N1["Node A: partitions 1, 4"]
        N2["Node B: partitions 2, 5"]
        N3["Node C: partitions 3, 6"]
    end

Distributed relational systems ("NewSQL", for example Google Spanner and CockroachDB) provide SQL and distributed transactions across nodes. They pay coordination latency for cross-partition transactions.

Decision criteria

Choose from the workload, not the label.

• Data has relationships and queries are not fully known in advance. Use relational. Joins, constraints, and ad-hoc queries are the strength.
• Strong integrity is required (money, inventory, bookings). Use relational or a system with real multi-record transactions.
• Access is by key with very high throughput and predictable patterns (sessions, carts, user profiles). Key-value or document stores fit.
• Data is self-contained aggregates read and written as a unit (a product page, a user profile). Document stores fit.
• Write volume exceeds one node and data partitions naturally (events, messages, telemetry). Wide-column or partitioned stores fit.
• Relationship traversal is the query (friends of friends, fraud rings). Graph databases fit; the same queries need recursive joins in SQL.
• Data volume is modest. A single relational database with replicas handles far more than many assume. Adopting a distributed NoSQL store adds operational and modeling cost.

Many systems use several databases, each for the workload it fits (polyglot persistence). Each additional store adds operations, consistency, and data-synchronization work.

Common misconceptions

• "NoSQL does not scale relational data" is inaccurate: sharded relational systems (for example MySQL with Vitess) run at large scale. The trade-off is operational effort and lost cross-shard features.
• "NoSQL has no schema" is inaccurate: the schema is implicit in application code, which must handle every historical shape of the data.
• "SQL cannot store JSON" is inaccurate: PostgreSQL and MySQL have JSON column types with indexing support.

Interview questions

• Why might a document store be chosen over a relational database for a product catalog? Products have varying attributes, and a page reads one product as a whole, so a self-contained document avoids joins and permits different fields per category. Constraints across products, such as inventory integrity, still need separate handling.
• What is lost when moving from a single relational node to a sharded store? Cross-shard joins, foreign key enforcement, and multi-shard atomic transactions become expensive or unsupported. Queries must include the partition key to avoid scatter-gather, where the query is sent to every shard and the results are merged, so its latency is set by the slowest shard.
• Can a NoSQL database provide ACID transactions? Some do, within a scope. DynamoDB and MongoDB offer multi-item or multi-document transactions, and Spanner-style systems offer them globally. The cost is higher latency and lower throughput than single-record operations.
• When is polyglot persistence justified? When distinct workloads (transactions, search, caching, analytics) have requirements one engine cannot meet. The price is keeping stores in sync, usually through events or change data capture.