Difference Between NoSQL and SQL

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"

In a document database the same write succeeds, because nothing says customer 999 must exist:

db.orders.insertOne({ _id: 1, customerId: 999, status: "piad", total: "43.50" });

That document has a dangling reference, a misspelled status and a total stored as a string. Every reader of the collection now has to cope with it.

What this means in practice:

• A flexible schema speeds up early development and moves the cost to later, when old documents in three different shapes are still in the collection.
• You still have a schema. It lives in application code, so every service that writes must agree on it. MongoDB's $jsonSchema validation closes part of the gap for types and required fields, but not for references between collections.
• Schema changes in SQL are explicit (ALTER TABLE). Adding a nullable column is quick in current versions of the major engines; rewriting a large table or adding a constraint to one can lock it and needs planning.

Queries: ask anything, or ask what you planned for

SQL lets you combine any tables on any columns after the fact:

SELECT c.email, count(*) AS orders, sum(o.total) AS spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.email
HAVING sum(o.total) > 500;

Nobody had to anticipate that question when the tables were designed. An index may be needed to make it fast, but it will run.

NoSQL stores are fast because they answer a narrower set of questions:

• Key-value: fetch by exact key. Nothing else without extra keys that you maintain.
• Wide-column: fetch by partition key, optionally a range inside it. Cassandra rejects other filters unless you add ALLOW FILTERING, which scans the cluster.
• Document: flexible filters on indexed fields within one collection. MongoDB has $lookup for joins in the aggregation pipeline, but it is not the normal way to read data and it performs poorly when used like a SQL join on large collections.
• Graph: relationship traversals are excellent; bulk aggregation is not.

The test: write down the queries the application will run. If the list is short and stable, a NoSQL model can be designed around it. If the list starts with "and the business will want reports on…", you want SQL, or you will end up exporting the data into a SQL warehouse to answer those questions.

Transactions: several records changing together

A relational database gives you multi-statement ACID transactions without any special setup:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Both updates happen or neither does, and no other session sees the state in between.

NoSQL systems guarantee atomicity for a write to a single record: one key, one document, one partition. That is the reason document design tells you to embed related data. If the order and its line items are one document, saving the order is atomic without a transaction.

Multi-record transactions now exist in several products, with conditions:

If the application moves money, reserves stock or must never double-book, and that logic spans several entities, treat built-in transactions as a requirement.

Consistency: will a read see the write that just happened?

On a single relational primary, yes. Once you add read replicas, a read sent to a replica can lag behind, and that is true of SQL databases as well.

Distributed NoSQL stores make the trade-off explicit and often let you choose per request:

• Cassandra: each read and write names a consistency level. With a replication factor of 3, writing and reading at QUORUM guarantees a read sees the latest acknowledged write. ONE is faster and can return stale data.
• DynamoDB: reads are eventually consistent by default. Set ConsistentRead to get a strongly consistent read, at twice the read cost.
• MongoDB: reads from the primary reflect acknowledged writes. Since 5.0 the default write concern is w: "majority", so an acknowledged write survives a failover. Reads from secondaries can be stale.

Eventual consistency is acceptable for a like counter or a product view count. It causes real bugs in "write, then immediately read back" flows such as showing a user the comment they just posted. Check the default for your driver; do not assume.

Scaling: bigger server or more servers

A relational database scales up first: more CPU, memory and faster disks, plus read replicas for read traffic. That goes much further than its reputation suggests. Writes still go through one primary, so the ceiling is whatever one machine can absorb.

Past that, the options are:

• Sharding a relational database with Citus (PostgreSQL) or Vitess (MySQL).
• Distributed SQL databases such as CockroachDB, YugabyteDB, TiDB and Google Spanner, which keep SQL and ACID transactions while spreading data across nodes. They weaken the old claim that only NoSQL scales horizontally.
• A NoSQL store, where partitioning is built in and adding nodes adds write capacity.

Horizontal scaling is not free in any of them. You must pick a partition key, and a bad one (low cardinality, or one "hot" value such as a single huge tenant) concentrates load on one node whatever the cluster size. That key is hard to change after the data is loaded.

A decision checklist

Answer these in order and stop at the first "yes":

1. Do several entities need to change together, or not at all? SQL.
2. Will people ask new questions of this data, with reports and ad hoc filters? SQL.
3. Is the data a cache, session or counter that can be rebuilt? Key-value store, alongside your main database.
4. Is the workload dominated by relationship traversal several hops deep? Graph database.
5. Is it append-heavy, keyed and time-ordered, with a sustained write rate beyond one server? Wide-column store.
6. Are records self-contained, varied in shape and always read whole? Document store, or a JSON column in a relational database.
7. None of the above? SQL.

Using both is normal: a relational system of record, with Redis for caching and a search engine for text search, is a common and sensible layout.

Common mistakes

• Choosing by expected scale. Decide on the access pattern you have, not the traffic you hope for.
• Rebuilding joins in application code. Fetching a list and then issuing one query per item is slower than the join you avoided.
• Treating "schemaless" as "no migrations". Old documents keep their old shape until you rewrite them.
• Assuming SQL cannot store JSON. PostgreSQL jsonb, MySQL JSON and SQL Server's JSON support cover many "flexible attributes" cases without a second database.
• Comparing against a 2010 picture of either side. NoSQL products have gained transactions and validation; SQL products have gained JSON and horizontal scaling. Check the current documentation for the product in front of you.

Further reading

• PostgreSQL documentation: Transactions
• MongoDB Manual: Data modeling
• Apache Cassandra documentation
• Amazon DynamoDB Developer Guide: Read consistency

How do I insert a row only if it doesn't exist, without race conditions?

Select-then-insert breaks under concurrency. Use a unique constraint plus one atomic statement: ON CONFLICT, ON DUPLICATE KEY UPDATE, or UPDLOCK/SERIALIZABLE in SQL Server.

#concurrency#schema-design#transactions