Data modelling and storage engines

your queries touch a handful of rows by key, thousands of times a second, and each one has to come back in milliseconds.

OLTP

your query reads three columns across two billion rows and nobody minds if it takes forty seconds.

OLAP

you store the customer's address in exactly one table so that changing it cannot leave four stale copies behind.

Normalisation

you copy the customer's name into the orders table so the list page stops doing a join on every render.

Denormalisation

you add an index and the query that was scanning ten million rows starts answering in a millisecond by walking a sorted tree.

B-tree index

you index the boolean column, the planner ignores it, and the reason is that half the table matches any value you look up.

Cardinality

you pick the primary key and quietly decide the physical order the rows will be stored in on disk forever.

Clustered index

you add the extra column to the index so the query is answered from the index alone and never touches the table.

Covering index

you run EXPLAIN and find out the database chose a sequential scan over the index you added last week.

Query planner

you fetch a list of fifty orders and then the ORM quietly issues fifty more queries, one per order, to load the customer.

N+1 query

you precompute the expensive aggregate into a real table and refresh it on a schedule instead of recomputing it per request.

Materialised view

you never update a record in place; you write the change to the end of a file and let readers work out the current state.

Append-only log

writes go to an in-memory table and an append-only log, and a background process keeps merging the files on disk behind you.

LSM tree

you check a small probabilistic structure first so you can skip opening a file that definitely does not contain the key.

Bloom filter

you wrote one kilobyte and the disk did fifteen kilobytes of work, because the row is in three indexes and compaction will rewrite it twice.

Write amplification

one point lookup turns into six file reads because the key could be in any of the levels the compactor has not merged yet.

Read amplification

you split the giant table into monthly chunks so the query touches one of them and dropping old data is a DDL statement instead of a delete.

Partitioning

one database stopped being enough, so you split the rows across several of them and now every query has to know which one to ask.

Sharding

you pick the column that decides which database a row lives on, and every query that does not filter on it now hits all of them.

Shard key

you have to change the shape of a table that is being read and written right now, without stopping either.

Schema migration

you added a column and now have to populate it for eighty million existing rows without locking the table or saturating the disk.

Backfill

you set a deleted flag instead of removing the row, and now every query in the codebase has to remember to filter on it.

Soft delete

you stop storing the current balance and instead store every deposit and withdrawal, deriving the balance by replaying them.

Event sourcing

you stop trying to serve reads and writes from the same model and build a separate read model shaped for the queries.

CQRS