SQL vs NoSQL

A database that stores data in tables of rows and columns with a defined schema, where tables relate to one another via keys, and is queried using SQL. Examples: PostgreSQL, MySQL, SQL Server, Oracle.

"Not only SQL" — a family of non-relational databases (document, key-value, wide-column, graph) that use flexible data models and typically scale horizontally. Examples: MongoDB, Redis, Cassandra, DynamoDB, Neo4j.

Document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra, HBase), and graph (Neo4j).

A column (or set of columns) that uniquely identifies each row in a table. It cannot be null and must be unique, and it's used to reference rows from other tables via foreign keys.

The defined structure of the data — tables, columns, types, and constraints in SQL. Relational schemas are fixed and enforced; many NoSQL stores are schema-flexible, letting each record differ.

A banking or e-commerce order system where money and inventory must stay consistent, relationships (customers, orders, items) are rich, and ad-hoc reporting queries are needed. ACID transactions and JOINs make relational the natural fit.

Storing user session data or a product catalog with varying attributes, an IoT/time-series firehose, or a social feed needing massive write scale and flexible documents — where horizontal scale and schema flexibility beat complex querying.

Atomicity (all-or-nothing transactions), Consistency (transactions move the DB between valid states, respecting constraints), Isolation (concurrent transactions don't interfere, as if serial), and Durability (committed data survives crashes). These properties make relational databases reliable for critical data.

BASE = Basically Available, Soft state, Eventual consistency. It's the philosophy behind many NoSQL systems: prioritize availability and partition tolerance, accepting that replicas converge over time rather than being instantly consistent. It's the pragmatic opposite end of the spectrum from ACID's strict guarantees.

JOINs, transactions, and strong consistency assume data lives together on one node; distributing them across machines requires expensive coordination (distributed transactions, cross-node joins). So the simplest path is a bigger machine (more CPU/RAM/disk). Read replicas and sharding add horizontal scale but with added complexity — which is exactly what many NoSQL stores build in from the start.

Denormalization duplicates related data together (e.g. embedding a user's name in each of their orders) instead of splitting it across normalized tables. NoSQL stores lack cheap JOINs and are modeled around read patterns, so denormalizing lets a single read fetch everything. The trade-off: writes must update multiple copies, and data can become inconsistent if not carefully managed.

An index is an auxiliary data structure (often a B-tree) that speeds up lookups on a column by avoiding full scans. Cost: it consumes storage and slows writes, since every insert/update/delete must also maintain the index. Over-indexing hurts write throughput; under-indexing hurts reads — you index the columns your queries actually filter/sort on.

Document (MongoDB) stores self-contained JSON-like documents and supports rich queries and secondary indexes on fields. Wide-column (Cassandra) stores rows keyed by a partition key with dynamic columns, optimized for huge write throughput and queries along the partition/clustering keys. Document DBs are more query-flexible; wide-column DBs excel at write-heavy, predictable-access, linearly scalable workloads.

When relationships are first-class and queries traverse many hops — social networks (friends-of-friends), recommendation engines, fraud rings, and knowledge graphs. Graph databases store edges directly so traversals are cheap, whereas the same query in SQL would require many expensive recursive JOINs.

Increasingly yes, but with limits. Many started with single-document/single-key atomicity only. Modern versions add multi-document transactions (MongoDB 4.0+) or ACID within a partition (DynamoDB transactions, Cassandra lightweight transactions). They're often more constrained or costlier than relational transactions, especially across partitions/nodes.

Using multiple database technologies within one system, each chosen for the job it does best — e.g. PostgreSQL for orders, Redis for sessions/cache, Elasticsearch for search, Cassandra for event logs, and Neo4j for the social graph. It optimizes each workload at the cost of operational complexity and data-synchronization challenges.

Work backward from queries (query-first design). Enumerate every read/write pattern, then design partition keys so each query hits a single partition, and clustering/sort keys so results come back pre-sorted. Denormalize and duplicate data into multiple tables/item-collections — one per access pattern — since there are no JOINs. Choose keys that spread load evenly (avoid hotspots) while keeping items you read together in the same partition. Accept that adding a new query pattern later may require a new table and a backfill.

B-trees (PostgreSQL, MySQL/InnoDB) update pages in place, giving great read performance and read-optimized workloads. LSM-trees (Cassandra, RocksDB, LevelDB) buffer writes in memory then flush sorted immutable files, merging via compaction — excellent write throughput and compression, at the cost of read amplification and background compaction I/O. Write-heavy, high-ingest systems favor LSM engines; read-heavy transactional systems often favor B-trees. This engine choice underlies many SQL-vs-NoSQL performance differences.

NewSQL systems (Google Spanner, CockroachDB, TiDB, YugabyteDB, Vitess) aim to provide relational SQL semantics and ACID transactions while scaling horizontally like NoSQL. They achieve this with distributed consensus (Paxos/Raft), automatic sharding, and sometimes synchronized clocks (Spanner's TrueTime) for global consistency. They solve the historical trade-off "either strong consistency + SQL, or scale" — at the cost of higher write latency for cross-shard transactions and operational complexity.

Dual-write and backfill: (1) Start writing to both old and new stores (or capture changes via CDC). (2) Backfill historical data in batches into the new store, being idempotent. (3) Reconcile/verify both stores match. (4) Shift reads to the new store gradually (shadow reads first, comparing results), behind a feature flag. (5) Once confident, stop writing to the old store and decommission. Keep the ability to roll back reads at each step, and monitor consistency throughout.

Write amplification: one logical write causes multiple physical writes (e.g. LSM compaction rewriting data, index updates, replication). Read amplification: one logical read touches multiple structures (e.g. checking several LSM levels/SSTables). They matter for capacity and cost: high write amplification burns disk endurance and I/O bandwidth (critical on SSDs), while high read amplification hurts latency. Engine and configuration choices (compaction strategy, bloom filters, index count) tune this trade-off for your read/write ratio.

Push past the buzzword: quantify actual scale (data size, read/write QPS, growth), analyze access patterns and query complexity, and assess consistency/transaction needs. If the data is highly relational with ad-hoc queries and moderate scale, a well-indexed PostgreSQL (with read replicas, partitioning, and connection pooling) often outperforms and simplifies operations. Choose NoSQL when patterns are simple and known, scale genuinely exceeds a single node, or the flexible schema/model is a real fit — not by default. Also weigh team expertise and operational maturity.

Data is partitioned by the primary/partition key, but a secondary index is on a different attribute, so its values are spread across all partitions. Two approaches: local (document-partitioned) indexes — each partition indexes only its own data, making writes cheap but reads scatter-gather across all partitions (fan-out). Global (term-partitioned) indexes — the index itself is partitioned by the indexed term, making reads targeted but writes must update a remote index partition (often asynchronously, so the index is eventually consistent). Both add cost and consistency caveats absent in a single-node relational index.

When the working set no longer fits in RAM and disk I/O dominates, when write throughput saturates a single primary (replication only scales reads), when a single table grows so large that vacuum/index maintenance and query latency degrade, or when you need multi-region low-latency writes. Before sharding, exhaust vertical scaling, read replicas, partitioning (declarative table partitioning), connection pooling (PgBouncer), and query/index tuning. Only then move to sharding (Citus/Vitess) or a distributed SQL system, since sharding sacrifices easy cross-shard JOINs and transactions.

Time-series workloads are append-heavy, queried by time ranges, and rarely updated. A plain table's B-tree indexes and row overhead make ingestion and retention (deleting old data) expensive. Use a purpose-built store (TimescaleDB, InfluxDB, Cassandra, or ClickHouse): partition by time so old chunks are dropped cheaply, use columnar/compressed storage for high compression on similar values, and pre-aggregate/downsample rolling windows. Key by (series, time) so writes and range reads hit contiguous storage. This gives orders-of-magnitude better ingest and retention than a naive relational schema.

A column in one table that references the primary key of another, enforcing a relationship and referential integrity (you can't reference a row that doesn't exist). For example, an orders.user_id foreign key points to users.id.

Normalization organizes data into tables to eliminate redundancy, so each fact is stored once. Benefits: no update anomalies (change a value in one place), less storage, and enforced integrity. Cost: reads may need JOINs across tables. Relational design favors normalization for correctness; denormalization is applied selectively for read performance. NoSQL often denormalizes by default because it lacks cheap JOINs.

You update your profile photo, which writes to one replica of a globally-distributed NoSQL DB. For a few seconds, a friend whose read hits a different, not-yet-updated replica still sees your old photo. Once replication propagates, all replicas converge and everyone sees the new photo. No update is lost; the system is briefly inconsistent then consistent — acceptable for a profile photo, but not for, say, a bank balance.

PostgreSQL's JSONB stores and indexes (GIN) semi-structured JSON while keeping full relational power — transactions, JOINs, constraints, and mixing structured columns with flexible JSON. Prefer it when you also need relational integrity, complex queries/reporting, or a single database for both structured and document data at moderate scale. Prefer MongoDB when documents are the primary model, you need built-in horizontal sharding and flexible schema at very large scale, and access patterns are document-centric rather than relational. Both are viable for JSON; the deciding factors are relational needs, transaction requirements, scale/sharding, and team familiarity — often Postgres wins for mixed workloads, Mongo for pure document scale.

If your app writes to the primary DB and then separately writes to a search index (e.g. Elasticsearch), a crash or failure between the two leaves them inconsistent — the record exists in the DB but not the index (or vice versa), and there's no atomic transaction spanning both systems. Solutions: use change data capture (tail the DB's write-ahead log via Debezium) or the transactional outbox (write an outbox row in the same DB transaction, then a relay publishes it) so the index is updated exactly from committed DB changes, asynchronously and reliably. This makes the DB the single source of truth and the index an eventually-consistent, rebuildable projection, avoiding the unreliable dual write.