Relational vs NoSQL

A teammate says “Postgres won’t scale, let’s move to MongoDB” in a design review, and the whole room nods because nobody wants to look like they don’t get NoSQL. Six months later they’re hand-writing joins in application code and losing data to a partial write that a transaction would have caught. The skill that saves that team is being able to say, calmly, exactly which workload actually needs a non-relational store and which one is reaching for it out of fashion. This module gives you that judgment.

The Goal

By the end of this module you can:

• Name the five database families, one real product and one genuinely good use for each
• Explain ACID vs BASE as a real trade — what you give up and what you buy
• Decide when NoSQL genuinely wins, and recognise the default-to-NoSQL trap when you see it
• Use PostgreSQL as a document store with JSONB, and say honestly where that ends
• Defend “we’re keeping Postgres” in a design review with reasons, not vibes

The Lesson

The families — five shapes of the same job

Every database stores data and gets it back. They differ in the shape they store it in, and that shape decides what’s cheap and what’s painful. There are five families worth knowing. The first one — relational — is what your SplitEase API already runs on.

Read the right-hand column twice. Each family exists because one access pattern is awkward in the others. Redis is a hash map that survives a process restart. Cassandra is built to swallow a firehose of writes across many machines. Neo4j makes “find every account two hops from this fraudster” a single cheap traversal instead of five self-joins.

flowchart TD
    Q["What shape is your data and your access pattern"] --> R["Rows with relationships and rules - Relational - PostgreSQL"]
    Q --> D["Self contained varied documents - Document - MongoDB"]
    Q --> K["Lookup by a single key fast - Key value - Redis"]
    Q --> W["Massive write heavy partitioned - Wide column - Cassandra"]
    Q --> G["Relationships are the query - Graph - Neo4j"]

The “NoSQL” label just means “not the relational shape.” It is four very different families wearing one umbrella, and lumping them together is the first mistake people make. “We need NoSQL” is as vague as “we need a vehicle” — a bicycle and a cargo ship are both vehicles.

Relational, in one paragraph of why it’s the default

Relational databases store data as rows in tables with a fixed, typed schema, and they link tables by keys instead of duplicating data. The killer feature isn’t tables — it’s that the database enforces the rules for you. A foreign key means an expense_share can’t point at a friend who doesn’t exist. A NOT NULL means amount_paise is never missing. A transaction means a transfer either fully happens or fully doesn’t. You met all of this in the SQL track — here the point is just: this is why money runs on relational. The database is a second pair of hands refusing to let bad data in.

ACID vs BASE — the trade you’re actually making

This is the heart of the relational-vs-NoSQL decision, so slow down here.

ACID is the guarantee relational databases give every transaction:

You saw atomicity and isolation up close in SQL transactions. ACID is expensive — it costs coordination — but for money it’s non-negotiable.

BASE is the philosophy many distributed NoSQL systems chose instead, because ACID across many machines is slow:

• Basically Available — the system answers even when parts are down.
• Soft state — data may be in flux; what you read might be a slightly old copy.
• Eventually consistent — given no new writes, all copies converge to the same value, but not instantly.

BASE is not “worse.” It’s a different bet. ACID says “I’ll make you wait so the answer is always correct.” BASE says “I’ll answer instantly even if the answer is a few hundred milliseconds stale.” A like counter on a post can be eventually consistent — nobody is harmed if it reads 1,204 then 1,205 a moment later. A bank balance cannot. The art is knowing which one your data is.

flowchart LR
    A["ACID - wait for the correct answer"] --> A1["Strong consistency"]
    A --> A2["Cost - coordination and latency"]
    A --> A3["Fits - money orders inventory"]
    B["BASE - answer now converge later"] --> B1["High availability and scale"]
    B --> B2["Cost - reads may be stale"]
    B --> B3["Fits - feeds counters caches catalogues"]

A crucial honesty note: this BASE-vs-ACID line is fuzzier than it used to be. Modern MongoDB supports multi-document ACID transactions; some “NewSQL” systems offer ACID across a cluster. So don’t say “NoSQL means no ACID.” Say “distributed systems trade consistency for availability and latency, and each product picks a point on that spectrum.” That sentence is what a senior engineer actually believes.

When NoSQL genuinely wins

There are real cases. Here they are, concretely:

• A pure cache or session store. You want “get the value behind this key in under a millisecond” and you don’t care about joins or durability of every write. Redis. This is the most common, least controversial NoSQL use — and it usually sits alongside your relational database, not instead of it.
• Genuinely schema-less, varied documents. A catalogue where a book has an ISBN and a page count, a t-shirt has a size and colour, and a subscription has a billing cycle — and you’ll add new product types weekly. Forcing that into rigid columns means a sea of nulls or a table per type. A document store holds each item as its own shape. MongoDB.
• Write-heavy at a scale one machine can’t hold. Billions of events a day, sharded across dozens of nodes, where you almost never update and almost always append-and-read-by-key. Cassandra was built for exactly this and is genuinely hard to beat.
• Relationship-first queries. “Find all accounts within three transaction-hops of this flagged account.” In SQL that’s three or four self-joins that explode in cost. In a graph database it’s a native traversal. Neo4j.

Notice what every winning case has in common: a specific access pattern that the relational shape makes awkward, at a scale or variety where the awkwardness actually bites. That’s the test.

The trap — reaching for NoSQL by default

Here’s the pattern that wrecks real projects. Someone reads that “MongoDB scales” and “schemas are rigid,” and chooses a document store for a normal application — users, orders, payments — that has clear relationships and needs correctness. Then reality arrives:

• They need to show “all orders for a user” and “all users in a city” — two access patterns. A document store optimises for one shape; the other becomes a slow scan or a duplicated copy they must keep in sync by hand.
• They need a transaction across two documents. They either upgrade to the database’s transaction support (and lose the speed they came for) or build it themselves in application code (and get it subtly wrong).
• “Schema-less” turns out to mean “schema enforced nowhere,” so every bug that a NOT NULL would have caught now lands in production as a missing field.

They reinvented a worse relational database inside their application. The lesson: a flexible schema is a liability, not a feature, when your data actually has a fixed shape and real relationships. The rigid schema you resented was doing free work — catching mistakes you didn’t know you were making. For most CRUD backends at most Indian product companies, the right default is PostgreSQL, and the burden of proof is on the person who wants to leave it.

Postgres-as-document-store — JSONB, honestly

Here’s the move that quietly ends a lot of “we need MongoDB” arguments: PostgreSQL is also a document database. The JSONB column type stores a JSON document, indexes inside it, and queries into it — while the rest of your tables stay relational and ACID.

-- a relational table with one flexible document column
CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    price_paise BIGINT NOT NULL,
    attributes  JSONB                 -- the varied, schema-less part
);

INSERT INTO product (name, price_paise, attributes) VALUES
  ('Cotton Tee', 49900, '{"size": "M", "color": "navy"}'),
  ('Hardcover Novel', 39900, '{"pages": 320, "isbn": "978-..."}');

-- query inside the document
SELECT name FROM product WHERE attributes ->> 'color' = 'navy';

-- and you can index inside it
CREATE INDEX idx_product_attrs ON product USING GIN (attributes);

So the structured, must-be-correct columns (name, price_paise) get full relational guarantees, and the varied part lives in attributes with document-style flexibility — in one database, in one transaction. For a huge share of “but our data is semi-structured” cases, this is the honest answer: you don’t need a second database, you need a JSONB column.

Now the honesty the brief demands, because this isn’t a silver bullet:

• If your data is mostly flexible documents and rarely relational, you’re fighting Postgres’ grain — a real document database will be more natural and its tooling more aligned.
• JSONB queries are powerful but the syntax (->>, @>, GIN indexes) is clunkier than native document queries, and a deeply nested document is harder to query and index than well-shaped columns.
• Overusing JSONB is its own trap: people dump everything into one data column to “stay flexible” and lose every benefit of a typed schema — back to the no-schema bug factory. JSONB is for the genuinely variable slice, not an excuse to skip modelling.

The rule: reach for JSONB for the variable part of an otherwise relational model. Reach for a real document database only when most of your data and access patterns are document-shaped.

Check The Concept

How This Shows Up At Work

• The design-review claim. Someone says “Postgres won’t scale, let’s go Mongo.” The engineer who asks “which access pattern is it failing, and have we tried read replicas, a cache, and JSONB first?” reframes the whole conversation. Nine times out of ten the answer is no migration, just an index or a Redis cache.
• The polyglot reality. Mature systems are rarely one database. A typical fintech backend is PostgreSQL for money and accounts, Redis for sessions and rate-limits, maybe Cassandra or a data warehouse for event logs. Knowing which job each does is the actual skill — not picking one winner.
• The JSONB rescue. A team mid-migration to MongoDB because “our config data is too flexible for columns” learns about JSONB, adds one column, and cancels the migration. This happens often enough that it’s a known pattern.
• The interview filter. “When would you choose NoSQL over a relational database?” A junior answers “when you need scale.” A strong candidate names a specific family, a specific access pattern, and then volunteers the trap — “but the common mistake is defaulting to it for normal CRUD.” That last sentence is what gets the offer.

Build This

You’ll prove the JSONB point on your own machine — Postgres being a document store, no MongoDB install needed. All in psql.

1. Connect to your database (use splitease_sql or any scratch DB):

psql -d splitease_sql

2. Create a table that is relational with one document column, and insert two products with totally different shapes:

CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    price_paise BIGINT NOT NULL,
    attributes  JSONB
);

INSERT INTO product (name, price_paise, attributes) VALUES
  ('Cotton Tee',      49900, '{"size": "M", "color": "navy", "material": "cotton"}'),
  ('Hardcover Novel', 39900, '{"pages": 320, "language": "English"}'),
  ('Yoga Mat',        89900, '{"color": "navy", "thickness_mm": 6}');

Two of those documents share no fields with the third. A rigid relational schema would need a forest of nullable columns; the JSONB column just holds each shape.

3. Query into the documents — find everything navy:

SELECT name, price_paise FROM product WHERE attributes ->> 'color' = 'navy';

Expected: Cotton Tee and Yoga Mat. The ->> operator pulls a field out of the JSON as text. You just did a document query in a relational database.

4. Index inside the document and prove a containment query works:

CREATE INDEX idx_product_attrs ON product USING GIN (attributes);

SELECT name FROM product WHERE attributes @> '{"color": "navy"}';

@> means “contains this JSON.” GIN is the index type built for searching inside documents. Expected: the two navy products again — now index-backed.

5. Break it on purpose — see the flexibility cut both ways. The structured columns are protected; the document is not:

-- relational guarantee still holds: this is rejected
INSERT INTO product (name, price_paise) VALUES (NULL, 100);

-- but garbage in the document is happily accepted
INSERT INTO product (name, price_paise, attributes)
VALUES ('Mystery Item', 100, '{"colour": "navy", "siz": "L"}');

The first fails — name NOT NULL did its job. The second succeeds with a typo’d key (colour, siz) that your color query will silently miss. That is exactly the trade: the document part has no schema to catch mistakes. Remember this the next time someone calls schema-less a pure win.

Interview Practice

These are the relational-vs-NoSQL questions actually asked at Indian product and fintech companies. Answer out loud before opening each block.

Where to Practice

Check Yourself

1. Name the five database families and one real product for each.
2. What does each letter of ACID guarantee, in one line?
3. What does BASE stand for, and what bet is it making versus ACID?
4. Give two genuinely good uses for NoSQL and say what they have in common.
5. Describe the default-to-NoSQL trap — what goes wrong and why.
6. How can PostgreSQL act as a document store, and what’s the one-column move?
7. When does JSONB stop being the right answer and a real document database become better?
8. Why is “schema-less is a feature” only half true?

Explain it out loud: Explain to an empty chair why your SplitEase API runs on PostgreSQL and not MongoDB — name the relationships and the money guarantees that make ACID non-negotiable — and then describe one part of a hypothetical feature where you would reach for Redis or a JSONB column, and why. If you can’t name a specific access pattern for the NoSQL part, you’ve found the section to re-read.

Why AI Can’t Do This For You

AI will confidently recommend MongoDB for your “scalable modern app,” because that pairing appears in a million blog posts it trained on — and it can’t see that your data is orders and payments with hard relationships and correctness rules. The database choice is a judgment about your access patterns, your consistency needs, and your team’s ability to maintain a second system, and a model that doesn’t know any of those defaults to the popular answer.

The skill is sitting in the design review and asking the three questions — which access pattern is failing, have we tried index/replica/cache/JSONB, and what guarantees would we lose — then defending the answer with the ACID-vs-BASE trade in your own words. That comes from understanding the families well enough to argue against the fashionable choice, and no prompt hands you that spine.

Module done? Add it to today’s tracker