Denormalisation
Deliberately stepping back from normalisation to read faster: copied column, precomputed counter, materialised view; what each technique costs in writes and consistency, and how copies are kept up to date.
- 25 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
- Understand
A few cards from the deck
Denormalisation: what is it?
Show the answer
The deliberate choice to add redundancy to a normalised schema in order to speed up some reads.
It is not a badly designed schema: it is a schema first normalised, then degraded where a measurement showed that reading cost too much.
Which PostgreSQL command updates a materialised view named ventes_mensuelles?
REFRESH MATERIALIZED VIEW ventes_mensuelles;UPDATE MATERIALIZED VIEW ventes_mensuelles FROM ventes;REFRESH VIEW ventes_mensuelles CONCURRENTLY;ALTER MATERIALIZED VIEW ventes_mensuelles REFRESH;
Show the answer
REFRESH MATERIALIZED VIEW ventes_mensuelles;The syntax has the three words
REFRESH MATERIALIZED VIEW. There is noUPDATEon a materialised view (it is not edited by hand) and noREFRESH VIEW.A … stores the result of its query; an ordinary view re-runs it on every read.
Show the answer
A materialised view stores the result of its query; an ordinary view re-runs it on every read.
The gain comes from reading with no join or aggregate; the price is content that reflects the last refresh, not the current state.
Derived data: what is it?
Show the answer
A value that could be recomputed from other data in the database, but is stored to avoid recomputing it, like a counter or a sum.
It is right as long as it is updated whenever its source changes; otherwise it drifts without the database noticing.
A home page replays a five-table join 2,000 times per second, on data that changes once a day. Which solution fits?
- Drop the foreign keys to speed up reads
- An ordinary view (CREATE VIEW) over the join, read by the page
- Put the database in read-only mode during the day
- A materialised view refreshed every night, read by the page
Show the answer
A materialised view refreshed every night, read by the page
A stable result recomputed on every read: compute it once and re-read it. An ordinary view stores nothing and replays the join on every read. Still, measuring and indexing first remains the opening step.