Unit 24 · Module 6 · Databases

Normalisation, denormalisation, and the N+1 trap

A clean schema and a fast page are not automatically the same thing.

Unit 24 of the free 49-unit computer-science course, in databases. 4 topics to watch or read, 2 interview questions answered in full and a short self-check.

Watch or read

4 topics make up this unit. Take each one whichever way suits you, then answer the questions below.

The N+1 query problem

You write one query to list 100 products, and the database quietly runs 101. That is the N+1 problem — and the fix is not an index. Here's why a query's real cost is the round-trip, why an index makes ONE query fast but can't change how many you issue, how a single JOIN collapses 101 queries to 1, and why the same shape shows up in REST calls, GraphQL resolvers, and 500 sequential LLM awaits.

Reel Read

Data modeling

Data modeling is designing which facts live where. Learn the three layers (conceptual, logical, physical), normalization vs. denormalization, and why your schema is the contract every pipeline depends on.

Reel Read

S3 vs database blobs

Should raw image and video bytes live inside your database or in object storage like S3? Stuffing files into a bytea/BLOB column keeps everything in one place and works for a weekend project — then backups drag terabytes through your most expensive tier and reads pull huge binaries through the connection pool. Here's why object storage plus a tiny DB reference plus short-lived pre-signed URLs is how the big apps actually store media — and how the same pattern serves model weights and generated media for LLM apps.

Reel Read

View vs materialized view — precomputing a result

You wrapped the slow query in a view and believed it made something faster. It did not. A view stores no data — it stores SQL text, so every SELECT re-runs the query from scratch. A materialized view stores the answer as a real table on disk you can index, which turns 40 seconds into milliseconds and charges you freshness. Here is exactly what each one is, why a materialized view is neither a cache nor an index, what REFRESH really costs (an exclusive lock, or CONCURRENTLY and a unique index), and the one question you must answer before you ship one.

Reel Read

Interview questions this unit unlocks

Asked out loud, answered out loud. Read the answer, then say it in your own words.

What is the N+1 query problem, and why will an index not save you?

You fetch a list with one query, then loop over it issuing one more query per row — a hundred products becomes a hundred and one queries. Each individual query is fast and indexed, which is exactly why it hides: nothing is slow, there is just an enormous number of things. The cost is a network round trip and a query parse per row, and an index makes each one faster without changing the count.

The fixes are a join, or a second query with `WHERE id IN (…)` — one round trip instead of a hundred. In an ORM it is eager loading, and knowing the name of your ORM's flag is worth saying.

When would you denormalise on purpose?

When a read path is hot enough that the join is the bottleneck, and the duplicated data changes rarely enough that keeping copies in step is cheap. Normalisation makes writes safe by giving every fact one home; denormalisation trades that safety for read speed and hands you the job of invalidating every copy. It is a decision to be made with a measurement, not a preference.

A materialised view is often the honest middle: the denormalised shape, with the database owning the refresh.

Self-check — 3 questions

Answer alone, at 2am, with no interviewer in the room.

Part of Everything You Need to Know About Computer Science.