Materialized views and denormalization
Store the answer to an expensive query, or keep a joined copy of the data, so reads stop doing the same work again and again.
A materialized view saves the result of a query, and denormalization stores data in the shape it will be read.
Use it when#
A report or dashboard query scans large tables and runs again and again.
Reads need several joins, and the same joins run on every request.
The reader can accept data as old as the last refresh.
How it works#
A materialized view is a table filled by a query, such as daily revenue by region. Reads hit the small result, and a refresh job recomputes it every few minutes or hours. PostgreSQL has REFRESH MATERIALIZED VIEW for this. A report that scanned 50 million rows now reads a few hundred.
Denormalization copies data into the table that needs it, such as storing a customer's name on each order, so a read skips the join. Reads get cheaper, and every write must now update each copy.
What it costs#
Both put old data on the page. A view is as stale as its refresh, and a denormalized copy is wrong between a write and the update of its copies. Denormalization also makes writes costlier and lets copies drift apart if one update fails. Use a view for reads that tolerate delay, and keep a normalized source of truth.
In Quorum#
A Database has a Materialized view switch after Materialized views, which makes reports read a stored result and shows up as slightly stale answers. Denormalization has no switch: it makes reads cheaper and writes dearer, and the Design lists it as a research it needs.
Drag to pan. Hold Ctrl or Cmd and scroll to zoom.
| Where | Setting | Value | Why |
|---|---|---|---|
| App Server | Instance size | small | Small gives the app two vCPUs, enough to run code while the data tier does its own work. |
| Database | Materialized view | On | A report reads a stored result instead of scanning the tables, for about half the CPU. Its figures are as old as the last refresh. |
| Database | Instance size | small | Small gives the Database two vCPUs. |
Research this Design needs: Checkout, Hosted models, Materialized views, Denormalization.
The Report kills the database incident shows the cost of scanning on the primary, and a Data Warehouse is the larger fix. The glossary defines materialized view and denormalization.