Database comparison

SQL vs NoSQL

The "which database?" question is one of the most repeated architectural debates in IT. The right answer almost always depends on access patterns, not on which family is "modern" or "fast".

TL;DR

SQL (relational) — tables, fixed schema, joins, ACID transactions. Examples: PostgreSQL, MySQL, SQL Server, SQLite.
NoSQL — an umbrella for four families: document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra), and graph (Neo4j). Flexible schema, easier horizontal scaling.
Default to relational. Move to NoSQL only when you have a concrete reason: scale, schema flexibility, or a well-fitting data model.

Side-by-side comparison

Aspect	SQL (Relational)	NoSQL
Data model	Tables, rows, columns	Documents, key-value, wide-column, or graph
Schema	Strict, enforced by DB	Flexible, often enforced by app
Query language	SQL (standardised)	Varies — JSON queries, custom DSLs, key lookups
Joins	First-class	Discouraged; denormalise instead
Transactions	ACID by default, multi-row	Varies; often limited to single document/partition
Scaling	Vertical first; sharding is complex	Horizontal-first; designed for sharding
Consistency	Strong by default	Often eventual (configurable)
Use cases	Business systems, financial, CRUD apps	Catalogs, sessions, time-series, search, caching
Examples	PostgreSQL, MySQL, SQL Server, Oracle	MongoDB, DynamoDB, Cassandra, Redis, Elasticsearch, Neo4j

Code side-by-side

Find all orders over $100 placed in the last 7 days by user 42:

SQL (PostgreSQL)

SELECT id, total, created_at
FROM orders
WHERE user_id = 42
  AND total > 100
  AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

NoSQL (MongoDB)

db.orders.find({
  user_id: 42,
  total: { $gt: 100 },
  created_at: {
    $gte: new Date(Date.now() - 7*24*60*60*1000)
  }
}).sort({ created_at: -1 });

When to use SQL

Highly relational data. Users have orders, orders have line items, line items reference products — relational tables map cleanly.
Complex queries and reporting. Ad-hoc JOINs, GROUP BY, window functions, analytical aggregations.
Strong consistency / transactions required. Banking, e-commerce checkout, inventory.
Schema evolves predictably. Migrations via Liquibase / Flyway / Alembic are well-tooled.
Team default skill. SQL is universal; no surprise interview questions.

When to use NoSQL

Massive scale with known access patterns. DynamoDB for global low-latency key lookups, Cassandra for write-heavy time-series.
Document-shaped data. Variable-shape user profiles, product catalogs with per-category attributes.
Caching. Redis for session storage, query caching, rate limiting.
Full-text search. Elasticsearch / OpenSearch — relational full-text is workable but specialised tools win.
Graph traversal. Neo4j for social networks, recommendation engines, fraud detection.

English phrases engineers use

SQL conversations

"We need a join between orders and users."
"This query is doing a full table scan — let's add an index on user_id."
"Wrap the writes in a transaction — we don't want partial state."
"The migration adds a NOT NULL constraint — we need a default for existing rows."
"That's N+1 — the ORM is firing a query per parent row."

NoSQL conversations

"We denormalised the user's profile into the document."
"The partition key is the user ID — all their data lives on one node."
"This query has a hot partition — one shard is taking all the traffic."
"Eventual consistency means the read may show stale data for a few seconds."
"We need to backfill the new field across all existing documents."

Quick decision tree

Building a typical CRUD application → Relational (Postgres)
Need ACID across many rows → Relational
Caching layer in front of any DB → Redis
Full-text search central to UX → Elasticsearch / OpenSearch
Graph relationships dominate (social, recommendations) → Neo4j
Massive write throughput with known queries → Cassandra / DynamoDB
Variable-shape documents (catalog) → MongoDB or Postgres JSONB
When in doubt → Postgres with JSONB columns covers ~80% of cases

Frequently asked questions

What is the main difference between SQL and NoSQL databases in plain English?

SQL databases store data in tables with rows and columns, enforce a fixed schema, and use the SQL query language. NoSQL is an umbrella term for databases that do not use the relational table model — they store documents, key-value pairs, wide columns, or graphs and typically have flexible or no schema enforcement.

Is NoSQL faster than SQL?

Not inherently. NoSQL systems can scale horizontally (across many machines) more easily, which can produce higher throughput for some workloads. But a properly-tuned PostgreSQL instance routinely outperforms misconfigured NoSQL. Performance depends far more on data model, indexes, and access patterns than on the SQL/NoSQL label.

When should I pick a relational database?

Pick relational (Postgres, MySQL, SQL Server) when your data is genuinely relational (entities with foreign keys), when you need ACID transactions across multiple rows, when ad-hoc analytical queries matter, and when your team already knows SQL. The default choice for most CRUD applications should still be a relational database.

When does NoSQL make sense?

NoSQL fits when your access patterns are well-known and limited (key-value or single-document reads), when you need to scale writes across many machines, when your data is naturally hierarchical (JSON documents), or when you handle massive event/time-series data. Examples: MongoDB for product catalogs, DynamoDB for high-scale key lookups, Redis for caching, Elasticsearch for full-text search.

Are NoSQL databases schemaless?

Technically they allow flexible schemas — you can insert documents with different fields. In practice, the schema lives in your application code instead of the database. Most production NoSQL setups end up with an implicit schema enforced by validation libraries (Mongoose, Pydantic, etc.) or the database itself (DynamoDB attribute definitions, MongoDB JSON Schema validators).

Can you do joins in NoSQL?

Most NoSQL databases avoid joins by design — you denormalise (duplicate data) so a single document read returns everything you need. MongoDB has $lookup (a limited join), and DynamoDB has none. If your application requires many ad-hoc joins, relational is almost certainly the right choice.

What about transactions?

Modern relational databases give you full ACID transactions across many rows and tables. NoSQL transaction support varies: MongoDB supports multi-document transactions since 4.0; DynamoDB has TransactWriteItems for up to 100 items; Redis has MULTI/EXEC but no rollback. If complex multi-entity transactions are central, lean relational.