The data outlives the code

A house gets repainted, refurnished, even re-roofed over decades — but the foundation poured on day one stays, because replacing it means lifting the whole house.

Languages change, frameworks get swapped, the UI is redrawn ten times. The data and its structure usually stay, because changing them on a live system full of real records is slow and risky. So the database is the first thing to think hard about, and the last thing to rush.

A database answers two questions

Choosing where to keep things asks two things: what shape are the things — books on shelves, or odd tools on pegs? — and how safe must they be — a vault, or a handy drawer?

Every store is defined by two things. First, its data model: how it shapes data — rigid tables, flexible documents, a graph of links. Second, its guarantees: strict correctness, or speed and availability with looser promises. Almost every database choice is a different answer to those two — and the rest of this course is the menu.

Access pattern — pick by how you read and write

A filing cabinet, a card catalogue, and a conveyor belt are all "storage" — but you choose by how you'll fetch things, not by which looks newest.

There is no best database. A store that's perfect for fast key lookups is wrong for deep relationship queries; one built for huge writes is wrong for strict bank transactions. The right question is your access pattern: what you read, what you write, how often, and how it must behave when things go wrong.

The map: 20 concepts in six groups

A metro map: first you see the whole lines, then you ride station by station.

The concepts in this course fall into six groups: the relational core, the NoSQL family, specialised stores, guarantees, scaling techniques, and how to combine it all.

Every concept below gets the same treatment: its English name (the term you'll search the docs for), a picture from life, a diagram, and a card with fields: problem, idea, example, cost, when to use, and related. A one-page cheat sheet waits at the end.

Relational Database — tables, rows, and relations

A spreadsheet done right: each sheet is one kind of thing (customers, orders), each row one record, and orders point back to the customer they belong to.

This is the relational database. It stores data in tables with a fixed schema, and links tables by reference — an order row holds the id of its customer. That reference is called a foreign key. The schema is a promise about shape: every order has the columns it should, checked on the way in. Structure up front, in exchange for sanity later.

SQL — ask for what you want, not how to get it

You tell a librarian "every novel by French authors published after 1950, sorted by year" — and they work out how to find them. You describe the result, not the search.

SQL (Structured Query Language) is a declarative query language: you state the answer you want and the database plans how to fetch it, including joining tables together (JOIN). That plan is built by the query planner. SELECT ... JOIN ... WHERE ... can answer questions you never anticipated at design time — one of the relational model's quiet superpowers.

Transactions — all or nothing

A money transfer is two writes — minus here, plus there — but one intent. Either both happen or neither does; a half-done transfer is a disaster.

This is the transaction: a group of changes that either fully commits or fully rolls back, isolated from others, and survives a crash once confirmed. The set of those promises is called ACID (more in chapter 5). This is why banks, orders, and inventory live in SQL — when correctness matters more than raw speed, you want this promise.

Why it's the right default

A good general-purpose tool: not the fastest at any single job, but the one you reach for until a job specifically demands otherwise.

Structured data, real relationships, ad-hoc queries, strong guarantees — the relational database does all of it well, and modern Postgres also handles JSON, full-text search, and even vectors. Begin every project here; reach for something else only when a concrete pain — scale, shape, or access pattern — pushes you out.

Document Store — a JSON document per thing

A folder of filled-in forms, where each form can have slightly different fields — no central template forcing every one to match.

This is the document store. It keeps records as flexible JSON-like documents, nested and schema-less, so two records in one collection can differ. Great when shapes vary or evolve fast, and a whole thing — a product, a profile — lives as one document you fetch in a single read.

Key-Value Store — a giant hash map

A coat check — you hand over a ticket number, you get back exactly your coat. No searching, no questions, instant.

This is the key-value store — the simplest shape: a key in, a value out, blazingly fast. Perfect for caching, sessions, rate-limit counters, and anything you fetch by a known id.

Wide-Column Store — built for enormous writes

A vast warehouse with endless identical aisles, arranged so a thousand forklifts can stock shelves at once without bumping into each other.

This is the wide-column store. It spreads data across many machines by a partition key for massive write throughput and scale, trading rich queries and strict consistency for the ability to absorb a firehose. Powerful — and more operational work to run well.

Graph Database — when the links are the point

A detective's pinboard — photos connected by string. The value isn't in any one photo; it's in the web of who's tied to whom.

This is the graph database. It models data as nodes and the edges between them, making relationship queries — "friends of friends who like this," "the path between these two accounts" — natural and fast, where SQL would need painful many-way joins. Reach for it when the connections matter more than the records.

Time-Series Database — data stamped by the clock

A heart-rate monitor's tape — an endless strip of readings, each tied to a moment, where you almost always ask "what happened between these two times?"

This is the time-series database. It's tuned for data that arrives in time order and is queried by time — metrics, sensor readings, prices. It ingests huge streams and answers "average per minute over the last week" fast, with built-in expiry of old data (retention).

Search Engine — full-text and relevance

A search box that forgives typos, ranks the best matches first, and finds "running" when you typed "run" — like a librarian who knows what you meant.

This is the search engine. It builds an inverted index — "word → documents" — for fast full-text search with ranking, fuzzy matching, and filters: what a LIKE '%word%' in SQL does slowly and badly. It usually runs alongside your main database, kept in sync, powering the search bar and log exploration.

Vector Database — search by meaning

Instead of matching exact words, you ask "find me things that feel like this one" — and it returns neighbours by similarity, not by keyword.

This is the vector database. It stores embeddings — numeric fingerprints of meaning — and finds the nearest neighbours, so you can search by similarity rather than exact text. This is the engine under semantic search and RAG, where an AI app retrieves the most relevant chunks to answer from.

Object Storage — where the big files go

A self-storage unit — not for index cards, but for the furniture: bulky things you keep by label and haul out whole.

This is object storage. It isn't a database, but it's where the large blobs live — images, video, backups, documents — addressed by a key inside a bucket and served cheaply at scale. The pattern is almost universal: keep the file in object storage, keep a row pointing to it in your database. Records in the DB, payloads in the bucket.

ACID — pessimistic, correct, careful

A bank teller who double-checks every entry and won't move on until the books balance — slower, but never wrong.

ACID is the strict promise relational databases make about transactions: every transaction leaves the data correct and complete, or doesn't happen at all. Four letters: Atomicity (all or nothing), Consistency (the data's rules always hold), Isolation (concurrent transactions don't interfere), Durability (what's confirmed survives a crash). For money, orders, and anything where a wrong number is unacceptable, this is the trade you want.

BASE — optimistic, available, eventually right

A busy shop that lets everyone keep buying even while the price tags are being updated — the numbers catch up in a moment, and that's fine.

Many NoSQL systems take the opposite stance: BASE (Basically Available, Soft state, Eventually consistent). They stay fast and available and accept that copies of the data may briefly disagree before converging. This is called eventual consistency.

CAP Theorem — choose when the network breaks

A team split by a downed phone line: each half can keep working alone (but they'll diverge), or down tools until reconnected (but you stop). You can't have both.

The CAP theorem says that when a network partition splits your database's machines, you must choose Consistency (refuse writes, stay correct) or Availability (keep serving, risk disagreement) — you can't have both during the split. Every distributed database makes this call. Knowing which your store picks tells you how it behaves on its worst day.

Index — the book's index, for data

To find a word in a 900-page book, you don't read every page — you flip to the index at the back and jump straight there.

An index is a side structure that turns a full table scan into a direct lookup — the single biggest lever on read speed. The catch: each index costs storage and slows writes (every insert has to update it too).

Replication — keep copies, survive failure

A document kept in three safes in three buildings — if one burns down, the others still hold it, and three people can read at once.

Replication keeps copies of the data on several machines. All writes go to the leader, and the followers (replicas) receive a copy and serve reads. If the leader dies, a follower takes over (failover). The catch is replication lag: a follower can be a moment behind, so a value you just wrote might not show up on a replica yet.

Sharding — split when one box isn't enough

One overflowing filing cabinet becomes A–M in one cabinet and N–Z in another. More room — but now you must know which cabinet a name lives in.

When the data or write load outgrows a single machine, sharding splits the data across many — users A–M here, N–Z there. Each part is a shard, and the field you split by is the shard key. It unlocks near-unlimited scale, but the shard key is a hard, near-permanent choice: pick badly and you get hotspots, or queries that have to hit every shard.

Normalization / Denormalization — one fact in one place, or copies for speed

One master address list everyone refers to (no contradictions) versus stapling a copy of the address onto every order (faster to read, but now there are copies to keep honest).

Normalization keeps each fact in exactly one place — clean, no contradictions, the relational default. Denormalization deliberately duplicates data so reads are faster and need fewer joins. Both are valid; the rule is to do it consciously, and to always know which copy is the source of truth and which is just a fast duplicate. (Same lesson as a cache.)

Polyglot Persistence — several stores, on purpose

A kitchen has a fridge, a freezer, and a pantry — not one box forced to do all three. Each keeps what suits it.

This is the polyglot persistence pattern. Mature systems usually use several stores: Postgres as the source of truth, Redis for caching and sessions, a search index for the search bar, maybe a vector store for AI features and object storage for files. Each store does what it's good at. The discipline is keeping clear which one holds the truth, with the others as fast copies kept in sync.

Postgres First — start with Postgres, grow out of it deliberately

A Swiss Army knife handles a startling amount before you ever need the full toolbox.

This is the Postgres first practice. Modern Postgres is astonishingly capable — relational core, JSON documents, full-text search, vectors, even a workable job queue. Most products should start with just Postgres and add a specialised store only when a real, measured pain demands it. Every extra database is another thing to run, back up, and keep consistent. The cheapest store is the one you didn't add.

How it all connects

A neighbourhood map after a long walk: now you see which streets lead where.

The concepts in this course don't live alone. The relational database is the source of truth with ACID guarantees; an index speeds up its reads; the search engine and the vector database are copies fed from it; object storage holds the files its rows point to. When one machine isn't enough, replication and sharding arrive — and with them the CAP choice. The NoSQL family often picks BASE — the opposite of ACID.