SQL vs NoSQL: A Developer's Comparison

Relational databases store tables linked by keys. PostgreSQL, MySQL, and SQL Server enforce schemas at write time and support ACID transactions — a funds transfer commits fully or not at all. SQL shines at JOINs, aggregations, and ad-hoc reporting across normalized data.

Horizontal write scale is the hard part: you partition deliberately and live with cross-shard query pain. For most business apps with relationships and reports, PostgreSQL (including JSONB when you need nested documents) is a strong default. When not to force SQL alone: proven write throughput or access patterns that map cleanly to key-value or append-only wide-column designs.

NoSQL covers several models. Document stores (MongoDB, CouchDB) keep JSON-like documents. Key-value (Redis, DynamoDB) optimize keyed lookups. Wide-column (Cassandra, HBase) favor high write ingest and time-oriented keys. Graph (Neo4j) treats relationships as first-class.

Request trace (document catalog): Client → API → db.products.findOne({ _id }) → return embedded variants in one round trip. That wins when you always read the whole aggregate. It hurts when you need multi-document transactions and rich JOINs without careful modeling. Most NoSQL systems scale out by partitioning keys — design the key first.

Start with PostgreSQL unless you have a specific, evidenced reason not to. Add Redis for cache/sessions, a search engine for full-text, object storage for blobs. Move a primary workload to a dedicated NoSQL store when profiling shows a bottleneck SQL cannot meet at acceptable cost — or when the data model is naturally a document/graph/wide-column key.

Decision evidence to write down: consistency needs (single-row vs multi-entity), query shapes (JOIN/report vs key get), write rate and key distribution, team operational skill, and failure mode (what happens if a replica lags).

SQL trigger. In the checkout transaction above, the payment insert fails after the order insert already ran inside the same transaction. Symptom. The client sees an error; nothing about the order appears to have happened. Root mechanism. ROLLBACK undoes every statement issued since BEGIN — the order row that was inserted moments earlier is undone along with the failed payment insert, because the database never treats a multi-statement transaction as partially real. Recovery: the caller retries the whole checkout from scratch with the same idempotency key; there's no partial state to reconcile.

NoSQL trigger. In the document update above, a second writer already bumped version before this request's updateOne runs. Symptom. matchedCount === 0 — the write silently matched nothing, because the filter required version: 1 and that's no longer true. Root mechanism. Optimistic concurrency control never locked the document; it just checks, at write time, whether the version it expected still holds. Recovery: the caller must explicitly re-read the current document and re-apply its intended change against the new version — unlike SQL's rollback, nothing automatically restores or retries this for you.