SQL vs NoSQL, CAP and JSONB

Part of the free LogicWiz SQL & Databases course, Module 17: Beyond one database. The queries and practice run in the app on real Postgres.

A senior says "use MongoDB, it scales". Another says "Postgres has JSONB, just use that". In the interview, the right answer is neither name: it is the questions you ask first.

You'll learn to

• Compare relational and the four NoSQL families
• Explain CAP without the "pick 2 of 3" myth
• Store and query JSON in Postgres with JSONB
• Choose a database for a given app and defend it

Learn

The NoSQL families

NoSQL means "not only SQL". Each family gives up something (joins, a fixed schema, strong consistency) to be fast or scalable at one job.

Relational vs NoSQL

CAP in one paragraph

In a database spread over several servers, a network partition (servers cannot talk to each other) will happen sooner or later. When it does, you must choose:

• C, consistency: refuse requests that might see stale data (a CP system). Right for a bank balance.
• A, availability: keep answering, even if some answers are a little old (an AP system). Fine for a like count.

PACELC adds: Else (no partition), you still trade Latency for Consistency, because waiting for replicas takes time.

"Pick any 2 of C, A and P" is the classic wrong answer. Partitions are not optional in a distributed system, so P is always there. The real choice is C or A during a partition. A single Postgres server is not "CA"; it simply is not distributed.

JSONB: JSON inside a Postgres column

SELECT id,
       payload -> 'user' AS user_json,
       payload ->> 'user' AS user_text,
       (payload ->> 'amount')::INT AS amount
FROM app_events
WHERE payload ->> 'type' = 'order'
ORDER BY id;

-> returns JSON (note the quotes in "aarav"); ->> returns text. Cast text with ::INT or ::NUMERIC before doing maths. JSONB stores parsed binary JSON, so it is fast to query; plain JSON keeps the original text.

Containment, arrays and a GIN index

CREATE INDEX idx_events_payload ON app_events USING GIN (payload);

-- events that contain this key and value
SELECT id, payload ->> 'user' AS user_name
FROM app_events
WHERE payload @> '{"coupon": "DIWALI50"}';

-- one row per item inside the JSON array
SELECT payload ->> 'user' AS user_name,
       jsonb_array_elements_text(payload -> 'items') AS item
FROM app_events
WHERE payload ? 'items';

@> means "contains", and a GIN index makes it fast. ? asks "does this key exist?". jsonb_array_elements_text turns an array into rows.

Changing a JSONB value

UPDATE app_events
SET payload = jsonb_set(payload, '{amount}', '99')
WHERE id = 4;

UPDATE app_events
SET payload = payload || '{"status": "refunded"}'
WHERE id = 6;

SELECT id, payload
FROM app_events
WHERE id IN (4, 6)
ORDER BY id;

jsonb_set(doc, path, value) replaces one value; || merges in new keys. Notice that Postgres prints JSONB keys in its own order, not the order you wrote them.

How to choose

Ask these, in order:

1. Is the data related, and do you need transactions across it? Money, orders, seats, marks: relational.
2. What are the main queries? Known key lookups at huge scale: key-value or wide-column. Relationship hops: graph.
3. How much scale, really? One Postgres server handles far more than most startups ever reach.
4. Does the shape keep changing? A JSONB column inside Postgres often beats a second database.

Many real systems use both: Postgres for orders and payments, Redis for sessions and the cart.

An IRCTC-style Tatkal booking needs seat counts that never go negative: that is ACID, so relational. The "trains you searched recently" list can live in a key-value store and be a few seconds stale without anyone noticing.

In one line

Relational for related data and transactions, NoSQL families for one job at scale; CAP is C or A during a partition; JSONB gives Postgres documents (-> JSON, ->> text, @> contains).

Interview corner

When would you choose NoSQL over a relational database?

When the access pattern is simple and known (lookups by key, append-only event streams), the scale is beyond what one server or a few replicas can handle, and you can live without joins or multi-row transactions. For money, inventory or anything with relationships and reports, start relational; add Redis or a document store for the specific job.

Explain the CAP theorem.

In a distributed database, when a network partition splits the servers, you must choose consistency (reject requests that could see stale data) or availability (answer anyway, maybe with stale data). P is not optional in practice, so "pick 2 of 3" is misleading. A bank ledger chooses CP; a like counter chooses AP.

What is the difference between JSON and JSONB in Postgres?

JSON stores the exact text and re-parses it on every read; JSONB stores a parsed binary form, drops duplicate keys and whitespace, and does not keep key order. JSONB is faster to query and supports GIN indexes and operators like @> and ?, so it is the usual choice.

What is eventual consistency?

Replicas may disagree for a while after a write, but if no new writes come in, they all converge to the same value. AP systems like Cassandra or DynamoDB (by default) work this way; it is fine for feeds and counters, not for balances.