SQL vs NoSQL — Dev Helper Library

Intent

Choosing the right database is a modeling decision, not a scale decision. SQL (relational) and NoSQL (non-relational) solve different problems: SQL shines with structured data with complex relationships and a need for transactional consistency; NoSQL offers modeling flexibility, specialized performance, or horizontal scale for specific use cases.

The false "SQL vs NoSQL" dichotomy leads to poor decisions. The right question is: which data model best reflects the structure of the problem? What consistency guarantees does the domain require? Which access pattern dominates — reads by primary key, ad-hoc queries, graph traversal, time series? The answer defines which database — or combination of databases — is the right tool.

Problem

Systems with different data requirements often use the same database for everything because it's the team's established default, not because it's the best tool. This creates concrete friction:

Forced modeling: hierarchical, variable data (a product with different attributes per category) modeled in relational tables requires complex schemas with deep joins or the generic-columns anti-pattern (attr1, attr2, attr3).
Performance mismatched to the access pattern: a relational database reading data by primary key for a session API performs unnecessary joins and index scans. A document database trying to join across different collections in the application is slow and hard to maintain.
Scale mismatched to the model: scaling an ACID relational database horizontally is complex. Using an eventually consistent NoSQL database for a financial system that requires strong consistency introduces integrity risks.
Rigid schema blocking fast iteration: in early product phases, the schema changes frequently. Migrations in SQL with production data can be slow and risky; a document database lets the schema evolve per document without blocking the system.

How it works

SQL — relational databases

Relational databases organize data in tables with a rigid schema, declared relationships between tables (foreign keys), and SQL as the standard query language. The fundamental property is ACID:

Atomicity: a transaction is all-or-nothing — either all operations are applied or none are.
Consistency: a transaction moves the database from one valid state to another, respecting all defined constraints.
Isolation: concurrent transactions behave as if executed sequentially — each sees the database in a consistent state.
Durability: once committed, a transaction persists even in the face of system failures.

Examples: PostgreSQL (the standard for modern applications), MySQL/MariaDB (widely used on the web), SQLite (embedded, serverless), SQL Server, Oracle.

NoSQL — four main models

"NoSQL" isn't a single type of database — it's a label for non-relational databases with radically different data models, each optimized for a specific usage pattern.

Comparison diagram: the same entity in SQL and Document

  ORDER — SQL (relational)                ORDER — Document (MongoDB)

  table: orders                           document in the "orders" collection:
  ┌────┬─────────────┬────────┐             {
  │ id │ customer_id │ status │               "_id": "ord-001",
  ├────┼─────────────┼────────┤               "customer_id": "cus-42",
  │ 1  │      42     │  paid  │               "status": "paid",
  └────┴─────────────┴────────┘               "items": [
                                                { "product": "Keyboard", "qty": 1, "price": 350 },
  table: order_items                            { "product": "Mouse",    "qty": 2, "price": 89  }
  ┌────┬──────────┬──────────┬─────┬───────┐  ],
  │ id │ order_id │ product  │ qty │ price │  "total": 528,
  ├────┼──────────┼──────────┼─────┼───────┤  "created_at": "2026-06-30T10:00:00Z"
  │ 1  │    1     │ Keyboard │  1  │  350  │}
  │ 2  │    1     │  Mouse   │  2  │   89  │
  └────┴──────────┴──────────┴─────┴───────┘  Order and items arrive together.
                                               One read, no JOIN.
  JOIN needed to reconstruct                  Flexible schema per document.
  the order with its items.

Document (MongoDB, CouchDB)

Stores data in JSON/BSON format. Flexible schema — each document can have different fields. Ideal for hierarchical data that arrives and is read as a unit (order with items, article with comments, profile with addresses). Queries within a single document are efficient; queries that need data from multiple collections require a lookup (similar to joins) or prior denormalization.

Key-Value (Redis, DynamoDB)

Maximum simplicity: a value associated with a key. No schema, no relationships, no complex queries — just get, set, and delete. Extremely low latency because the data structure is simple and access is direct. Ideal for user sessions, caching, simple queues, and feature flags. Redis adds rich structures (lists, sets, hashes, sorted sets) that make it versatile beyond simple key-value.

Wide column (Cassandra, HBase)

Organizes data into column families — tables with dynamic columns that can vary per row. Optimized for massive writes and reads by partition-key range or time range. Distributes data horizontally by design, with automatic replication across nodes. Used in IoT (device telemetry), high-throughput event analytics, and time-series (logs, metrics, activity tracking).

Graph (Neo4j, Amazon Neptune)

Models entities as nodes and relationships as edges. Relationships are first-class citizens — stored explicitly with properties — and deep graph traversal is efficient by design. Queries that in SQL would require multiple recursive self-joins are expressed directly in the graph database's query language (Cypher, Gremlin). Used in social networks (friends of friends), recommendation systems (users who bought X also bought Y), and relationship-based fraud detection.

CAP Theorem — consistency, availability, and partition tolerance

The CAP theorem states that a distributed database can guarantee only two of three properties simultaneously:

Consistency (C): every read reflects the most recent write.
Availability (A): every request receives a response (even if not the most recent one).
Partition Tolerance (P): the system keeps working even if messages between nodes are lost or delayed.

In distributed systems, network failures (partitions) are inevitable — P is mandatory. The real choice is between C and A during a partition. Traditional (non-distributed) relational databases prioritize CA: on a single node, there's no partition. Distributed NoSQL databases often choose AP (Cassandra, DynamoDB with eventual consistency) or CP (MongoDB, Redis Cluster).

ACID vs BASE

ACID (relational databases) guarantees strong consistency and full transactions. BASE is the model used by many distributed NoSQL databases:

Basically Available: the system always responds, even with possibly outdated data.
Soft state: the system's state can change over time, even without new writes (due to ongoing replication).
Eventually consistent: after a period with no new writes, all nodes converge to the same value.

When to use SQL

Data with complex relationships between entities: orders, customers, products, invoices — entities that intersect in varied ways and need joins for business queries.
Transactions that span multiple entities: debiting one account and crediting another, reserving inventory and creating an order simultaneously. ACID guarantees either everything happens or nothing does.
Ad-hoc queries and reports: SQL allows arbitrary queries over the data without requiring predefined indexes. Ideal for analytics, operational reports, and data exploration.
Well-understood domain with a stable schema: when the data model is known and changes infrequently, a rigid schema is protection against inconsistencies, not an obstacle.

When to use NoSQL Document

Variable or rapidly evolving schema — different fields per entity, an early-stage product with a changing model.
Hierarchical data that arrives and is read as a unit, without frequent need for joins.
Teams working with JSON objects in the application who want to avoid the object-relational impedance mismatch.

When to use NoSQL Key-Value

Caching query results or API responses (Redis).
User sessions, auth tokens, temporary states.
Feature flags, configuration read at very high frequency.
Simple queues and real-time data structures (leaderboards with Redis Sorted Sets).

When to use NoSQL Wide Column

Time series with massive volume: IoT telemetry, infrastructure metrics, event logs.
Systems with very high write rate and reads by time range.
Data that needs to be distributed globally with multi-region replication by design.

When to use NoSQL Graph

Social networks where the question is "who knows whom" and N-degree traversal is frequent.
Recommendation systems based on relationships between users, products, and behaviors.
Fraud detection through pattern analysis in transaction networks.
Knowledge graphs and permission systems based on complex hierarchies.

Pros and cons

SQL — Pros

Full ACID: strongly consistent transactions, no risk of partially written data.
Standard language: SQL is universally known and supported by BI tools, analytics, and ORMs.
Declarative referential integrity: foreign keys and constraints are enforced by the database, not the application.
Ad-hoc queries without predefined indexes: you can query any combination of fields with SQL.
Maturity and ecosystem: decades of optimizations, tools, knowledge, and support available.

SQL — Cons

Object-relational impedance mismatch: mapping hierarchical objects to flat tables requires ORMs or complex queries.
Rigid schema: schema changes in production with existing data require careful migrations.
Complex horizontal scale: sharding relational databases isn't trivial — most scale vertically up to a limit.
Performance for simple access patterns: a SELECT by primary key in PostgreSQL is slower than a GET in Redis for the same data.

NoSQL — Pros

Schema flexibility: adapt the data model without blocking migrations.
Specialized performance: each type of NoSQL is optimized for its specific access pattern.
Horizontal scale by design: Cassandra, DynamoDB, and MongoDB were designed for distribution across multiple nodes.
Expressive data models: graphs, hierarchical documents, time series — modeled directly without impedance mismatch.

NoSQL — Cons

Eventual consistency: many distributed NoSQL databases don't guarantee that a read after a write reflects the most recent data.
No cross-collection transactions (generally): atomic operations across multiple entities are more complex or impossible depending on the database.
Lower query expressiveness: without full SQL, complex queries need to be planned into the modeling or done in the application.
Smaller ecosystem: fewer tools, fewer experienced professionals, fewer forum answers for specific problems.

Common pitfalls

1. Using NoSQL to escape modeling

A flexible schema isn't the absence of modeling — it's deferred modeling that turns into technical debt. Documents without a defined structure result in inconsistently named fields, different types for the same field across documents, and normalization logic scattered throughout the application. When using a document database, the schema still needs to be defined and enforced — the difference is that the enforcer is the application, not the database.

Rule of thumb: treat a NoSQL database's schema as code: version it, document it, and validate it at the application layer. Database flexibility isn't a license to create arbitrary documents.

2. Joins in code due to lack of planning

The most common anti-pattern in document databases: data is insufficiently denormalized — or not denormalized at all — and the application makes multiple queries to different collections to reconstruct an entity, then joins the results in code. The result is slower and more fragile than the original SQL JOIN, without the benefits of denormalization.

Modeling in document databases should start from the access pattern, not the domain model: define which data arrives together in a single read and denormalize it into the same document.

3. Assuming NoSQL scales and SQL doesn't

PostgreSQL with read replication, connection pooling (PgBouncer), table partitioning, and well-planned indexes handles enormous loads — most applications will never need more than that. The decision between SQL and NoSQL is about data model and access pattern, not scaling capacity. Using Cassandra for a system with 1,000 users because "NoSQL scales" is over-engineering with no benefit.

4. Mixing strong and eventual consistency without realizing it

NoSQL databases with eventual replication can return outdated data on reads immediately after writes. Reading from a read replica in PostgreSQL with asynchronous replication has the same problem — but in SQL, strong consistency is the default and eventual is opt-in. In many NoSQL databases, eventual consistency is the default and strong consistency is opt-in (and more expensive in latency). Know your database's consistency model and configure it explicitly for each critical operation.

Related architectures and patterns

Caching is often layered on top of SQL databases to reduce the load of costly queries: results are stored in Redis and served directly without touching the database for subsequent requests. Redis (NoSQL Key-Value) and PostgreSQL (SQL) are used together in most modern systems — not as competitors, but as complementary layers.

CQRS allows the write model and the read model to use different databases. The Write Model can persist in PostgreSQL (ACID consistency for business transactions) while the Read Model is designed in MongoDB or Redis (read performance optimized for the query pattern). This combination is one of the most concrete use cases for the deliberate choice of multiple database types in the same system.

In Event-Driven Architecture, the event store (where events are durably persisted) often uses a relational database or one specialized in append-only workloads — while projections derived from the events can live in any database type suited to the read pattern: Elasticsearch for full-text, Redis for caching, Cassandra for time series.