Databases

Pradeep P
3 days ago
4 min read

Series: Modern System Design · Layer 1 — The building blocks

Layer 1 · Post 11 of 15

Layer 1 — The building blocks · Post 11 of 88

A database is the system of record. Everything else in the stack is trying to read, write, copy, or hide how hard that job is.

What you'll learn

What "system of record" means in a design
Reads vs writes, and why they stress a database differently
The questions to ask before you pick a brand name

The idea in one minute

A database stores data so you can find it again after a reboot, a deploy, and a bad day.

Caches can vanish. CDN copies can be stale. App memory dies with the process. The database is where you put facts you are not willing to lose: orders, balances, emails, inventory.

Everything else in Layer 1 — load balancers, Redis, CDNs — exists to protect this box or avoid talking to it so often.

Why it matters

Beginners draw "the app" and forget where state lives. Seniors start from data:

What is the unit of data (row, document, event, file)?
How is it looked up (by id, by user, by time range)?
How wrong can a read be?
How hard is a write to undo?

Those answers pick the database more honestly than "everyone uses Postgres" or "we'll use Mongo because JSON."

A database is also usually the hardest thing to scale horizontally. Stateless APIs copy easily. Data has gravity: one fact should not fork into two conflicting facts without a plan.

How it works

At a cartoon level, a database does four jobs:

Accept writes and make them durable (on disk, in a write-ahead log, replicated — details in later posts).
Serve reads using structures that make lookup fast (indexes — two posts from now).
Protect concurrent access so two users editing the same row do not silently clobber each other (transactions, Layer 4).
Recover after a crash so you do not wake up with half a write.

Reads vs writes

Reads scale with copies: replicas, caches, CDNs. Many designs are 90%+ read.

Writes must land on a place that owns the truth. One primary is the simple version. Multiple writers need conflict rules (last-write-wins, CRDTs, transactions). Writes also make caches wrong.

When someone says "the database is slow," ask: slow reads, slow writes, or lock contention? Those are different designs.

You will meet several shapes

You do not need to memorize products. You need shapes:

Relational (SQL): tables, schemas, joins, transactions. Postgres, MySQL, SQL Server.
Document: JSON-like documents, fewer joins. MongoDB, DynamoDB (item model), Couchbase.
Key-value: get/put by key. Redis (if used as DB), DynamoDB, RocksDB-backed stores.
Wide-column: rows with lots of sparse columns, time-series-ish access. Cassandra, Bigtable, HBase.
Search: inverted index, relevance. Elasticsearch, OpenSearch (Layer 6 search post).
Warehouse / OLAP: fat scans, analytics, not 2 ms user-facing reads. Snowflake, BigQuery, Redshift.

OLTP (the app's live data) and OLAP (analytics) are different jobs. Mixing them on one primary is how dashboards take down checkout.

A simple example

A notes app:

Write: user saves a note. That INSERT/UPDATE must not disappear if the API pod restarts.
Read: user opens the note. You might cache it. If cache and DB disagree, the DB wins when you care about the latest save.

A like counter on a viral post:

You might write likes to Redis for speed and flush to the DB in batches. Then Redis is a buffer, not the record. If you only increment Redis, a flush bug loses likes. That is a product decision, not an accident.

Same "database" word, two different contracts.

Common mistakes

One database for every access pattern. User profile by id, "search notes by text," and "likes per day for two years" want different systems. It is normal to have Postgres + Redis + a search index.

No backup / no restore test. Durability is a story you have practiced, not a checkbox on a cloud form.

Connection storms. 200 app pods × 50 connections each will knock over a small Postgres. Pooling (PgBouncer, proxy) is part of the database tier.

Treating the DB as infinitely fast storage. Every query has a cost. Indexes, pagination, and "don't SELECT *" are system design, not style.

How this shows up in real systems

Most startups: Postgres or MySQL as the record, Redis as cache, S3 for files, maybe Elastic for search.
Big consumer apps: a fleet of stores — graph, KV, SQL, warehouse — each for a pattern.
Regulated money: boring SQL with strong transactions, because "we lost a write" is not a blip.

In interviews, name what must be durable, how you look it up, and what you will cache. The logo on the cylinder is secondary.

Recap

The database is the system of record; other layers are helpers.
Reads and writes scale differently. Writes own the truth.
Pick a store from access patterns, then add more stores when one pattern does not fit.