“Should we use Postgres or MongoDB?”

This question starts more flame wars than “Vim vs. Emacs.”

The SQL camp says: “NoSQL loses data. It’s a toy.” The NoSQL camp says: “SQL is too rigid. It can’t scale.”

Both are wrong. They just have different jobs.

This is the Mastery Guide to Databases. We’ll use the “Library vs. Warehouse” model to understand exactly when to use which (and why Postgres might be the only one you ever need).

Part 1: Foundations (The Mental Model)

SQL = The Public Library

Think of a Relational Database (SQL) as a perfectly organized Public Library.

• Schema (The Dewey Decimal System): You cannot put a book on a shelf unless you know exactly where it belongs. Authors, Titles, and ISBNs must follow strict rules.
• Relations (Foreign Keys): Books are linked to Authors. If you delete an Author, you must decide what to do with their Books (Cascade delete?).
• Strength: Consistency. If the catalogue says the book is there, it is definitely there.
• Weakness: Rigidity. Trying to store a weird object (like a 3D-printed statue) is impossible because it doesn’t fit the shelves.

NoSQL = The Warehouse

Think of a Document Database (NoSQL) as a massive Amazon Warehouse.

• Schema-less (The Box): You just throw things into a box. One box has books; another has shoes; another has a surfboard. The warehouse doesn’t care.
• Denormalization: You don’t link the shoe to the shoemaker. You just put the shoemaker’s name inside the shoe box.
• Strength: Flexibility & Speed. You can store anything instantly without asking permission.
• Weakness: Chaos. If the shoemaker changes their name, you have to go find every single shoe box to update it.

Part 2: The Logic (ACID vs. BASE)

This is the technical tradeoff you are actually making.

1. SQL loves ACID

• Atomicity: “All or Nothing.” If you transfer money, money leaves A and enters B. If B fails, A is refunded instantly.
• Consistency: The data never breaks the rules.
• Isolation: Nobody sees the money transfer until it is finished.
• Durability: Once it says “Saved”, it is saved forever (even if power fails).

2. NoSQL loves BASE

• Basic Availability: The system is up, even if some parts are broken.
• Soft state: The data might change without you touching it (syncing).
• Eventual consistency: If you stop writing, eventually everyone will see the same data. (But for a few seconds, User A might see an old price).

Rule of Thumb:

• Banking App? Inventory System? SQL (ACID). You cannot afford to “eventually” have the right balance.
• Social Media Likes? Chat Logs? NoSQL (BASE). If a Like appears 2 seconds late, nobody dies.

Part 3: The Investigation (Debug Like a Pro)

1. The “Join” Tax (SQL)

Why is SQL slow at scale? Joins. To fetch a “User Profile”, SQL might have to check 5 tables (User, Address, Orders, Preferences, history).

• Cost: CPU intensive.
• Scale: Hard to split across servers (Sharding is a nightmare with Joins).

2. The “Duplication” Tax (NoSQL)

NoSQL reads are fast because the “User Profile” is just one document. But what if the User changes their Email?

• SQL: Update 1 row in users table. Done.
• NoSQL: Find the User doc. Find their 50 Order docs. Find their 100 Comment docs. Update them all.
• Cost: Write intensive and complex code.

Part 4: The Resolution (Postgres is the Winner)

For 95% of projects, the answer is: Just use Postgres.

Why? Because Postgres has JSONB.

The Hybrid Model

You can have strict relational tables AND flexible documents in the same database.

1
2
3
4
5
6
7

-- The best of both worlds
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- Strict Relation (Safe)
    created_at TIMESTAMP,              -- Strict Time (Sortable)
    payload JSONB                      -- Flexible Document (NoSQL)
);

Why is JSONB amazing?

1. Binary storage: It’s compressed (unlike a text string).
2. Indexable: You can create an index on a field inside the JSON.

   1 2 3

   -- Find all events where payload -> 'status' is 'failed' -- Faster than MongoDB in many benchmarks! CREATE INDEX idx_status ON events ((payload->>'status'));

Final Mental Model

1
2
3
4

SQL (Postgres) -> The Library. Use for structured, critical data (Users, Money).
NoSQL (Mongo)  -> The Warehouse. Use for massive, unstructured logs or rapid prototyping.

Postgres JSONB -> The Library that has a Warehouse in the basement.

Start with SQL. If you don’t know the schema yet, use a JSONB column. Only move to NoSQL if you have PetaBytes of data or specific needs (Graph, TimeSeries) that SQL absolutely cannot handle.