Act 1 — The Monolith

Chapter 02

Separate the Database

Your app and database are roommates sharing one bedroom. Under pressure, they fight over space. The first architectural decision you'll ever make is giving each of them their own room.

The roommate problem

In Chapter 1, everything lived on one server — your application code and your database running side by side on the same machine, sharing the same RAM and CPU.

That is fine when things are quiet. But under load, they start getting in each other's way. Here is exactly why.

Every database needs to keep frequently accessed data in RAM so it does not have to read from disk every time. Think of it as the database's working memory. On a 4GB server, the database ideally wants about 1GB of that for itself.

Meanwhile, your application also needs RAM — to hold the state of every active user connection, every request being processed, every response being built. Under load, it wants that same 1GB.

Shared Server Host (4 GB total memory limit)

💻

App Code

Wants 2.5 GB

⚡ Fight ⚡

🗄️

Database

Wants 2.0 GB

⚠️ Starvation: 2.5G + 2.0G = 4.5 GB requested. System runs out of memory, thrashes disk, and crashes.

⚠ What happens when they compete

The database starts getting less memory than it needs. It can no longer hold its working data in RAM, so it has to go to disk for data it used to serve instantly. Disk is roughly 100× slower than RAM. Queries that took 2ms now take 200ms. Meanwhile your app is running low on memory too, so the operating system starts aggressively cleaning up — which causes your application to stutter and slow down. Both sides lose.

The fix: dedicated servers

Move the database to a dedicated server. Now the database gets 100% of that server's RAM for its own working memory, and your application gets 100% of its server's RAM for connection handling. Both run at full capacity.

APP SERVER (HOST 1)

💻

100% App RAM

Connection handling, APIs

DATABASE SERVER (HOST 2)

🗄️

100% DB Buffer RAM

Caching hot rows in memory

See the difference — click Split the Database

The network penalty

Before the split, when your app asked the database for data, it was like asking a question to someone sitting next to you. Instant.

After the split, the app server and database server are on the same internal network, but now each request travels over a wire. That adds about 0.3–0.5 milliseconds per query.

App Server── Serialise SQL (0.1ms) ⟶ [ Ethernet Wire (~0.4ms) ] ⟶DB Server

Tradeoff: Local Call (0.01ms) ⚡ vs. Network Call (0.5ms) 🌐. You trade micro-latency for macro-scale capability.

Is that a problem? If a query used to take 5ms, it now takes 5.5ms — a 10% increase. Completely acceptable given what you gain: two machines that can be tuned, scaled, and replaced independently.

✨

Key Insight

This is your first encounter with a theme you will see everywhere in system design: every architectural decision is a tradeoff. You are trading a tiny bit of latency for a massive gain in flexibility and capacity. When the tradeoff is this lopsided, it is an easy yes.

Five database types

Here is something nobody tells you when you start learning system design: there is not just one kind of database. There are five fundamentally different types, each designed for a completely different problem. Picking the wrong one for your access patterns means paying for it for years.

Interviewers will absolutely ask "which database would you use?" The right answer starts with questions — How is this data read? How is it written? Does it need to connect to other data? — before picking anything. Here are all five, with their internals, real examples, and honest gotchas:

PostgreSQL · MySQL~1–10ms typical query latency

Relational — how it works

users

idnameemail

1Alexalex@co.com

2Priyapriya@co.com

3Samsam@co.com

orders

iduser_iditemamt

1011Burger$9

1022Pizza$12

1032Pasta$14

1043Sushi$18

A relational database stores data in tables — think of a spreadsheet where every row is one record and every column is a field. What makes it "relational" is that tables can reference each other using IDs. Your users table has an id column. Your orders table has a user_id column that points back to a user. The database can combine ("join") these two tables on the fly to answer questions like "give me all orders placed by Priya."

What makes this powerful is that you only store Priya's name and email once — in the users table. Ten thousand orders all just store her ID. If Priya changes her email, you update one row, and every order immediately reflects that. No duplicates. This property is called normalisation.

✓ Use this when

✓Your data has relationships that need to stay consistent

Users place orders. Orders contain products. Products belong to categories. In a relational DB, each entity is stored once and linked by ID — change a product's price in one row and every order immediately reflects it. Without this, you'd store the price inside each order record and have to update thousands of rows every time it changes.

💬 Say in interview: "Our data has clear relationships between users, orders, and products — relational gives us referential integrity for free."

✓Money, inventory, or anything that can't be half-done

When a user buys something, two things must happen: money leaves their account AND the order gets created. If the server crashes between the two, you cannot have one without the other. Relational databases give you transactions — either both operations succeed together, or neither does. This guarantee is called atomicity, and it is non-negotiable for financial systems.

💬 Say in interview: "We're handling payments, so we need ACID transactions — PostgreSQL guarantees atomicity out of the box."

✓You need to ask questions you haven't thought of yet

"All orders over $50 placed by users who signed up this month, grouped by product category." A relational database handles this in a single query. With most other DB types you would have to fetch raw data and write your own joining and filtering logic in application code — much slower and error-prone.

💬 Say in interview: "Our reporting requirements are complex and evolving, so we need the flexibility of SQL rather than locking into fixed access patterns."

⚠ Watch out

✗Schema changes at scale are expensive, painful operations

Adding a new column to a table with 500 million rows doesn't just insert a field — PostgreSQL rewrites every single row on disk. This can take hours and block all writes to that table while it runs. At scale, even 'trivial' schema changes require dedicated migration tools and careful scheduling. If your schema changes every sprint AND you expect hundreds of millions of rows, plan for this from day one.

💬 Say in interview: "We'll need an online schema migration strategy — tools like pg_repack or pt-online-schema-change so we can alter tables without downtime."

✗Joins slow down when individual tables hit hundreds of millions of rows

Joining a 100M-row users table with a 1B-row events table means the database has to match billions of row combinations. Without careful indexing this query can take minutes. This isn't a reason to avoid relational DBs — it's a signal that at extreme scale you need to denormalise, pre-aggregate, or move that specific workload to a different store.

Using multiple databases

Large products do not pick one database for everything. Twitter uses MySQL for core entities, Redis for prebuilt timelines, and Elasticsearch for search. Not because it is elegant — managing multiple databases is extra operational work. But each type is 10–100× better at its specific job than any general-purpose alternative.

⚠️

Warning

A common trap: choosing DynamoDB because it "sounds scalable," then discovering you can only look up data by primary key. Every other query becomes an expensive full scan. The database choice must match your access patterns — how you read the data matters more than how much of it you have.

Architecture after this chapter — how you'd draw it on the whiteboard

The database now runs on its own server. App and database can be sized, tuned, and scaled independently of each other.

💡 In the Interview

When asked "which database would you choose?" — respond with questions first. "What are the read/write patterns? Does the data have relationships? Do we need transactions? Will the schema change frequently?" Pick based on the answers, then explain the tradeoff you're accepting. The reasoning is what the interviewer is listening for.