Database: materialized views
This course covers Postgres materialized views and how they compare with an application cache. It starts with the difference between a view, which stores a query, and a materialized view, which stores rows as a snapshot. It explains the locking behavior of REFRESH and REFRESH CONCURRENTLY, the unique index the concurrent form requires, and the choice between scheduled, triggered and lazy refreshes. It looks at Rails: schema.rb limits, structure.sql and the scenic gem. Later lessons cover when a view is worth it and how it fails silently.
What you'll learn
- ✓The difference between a view and a materialized view
- ✓Plain and concurrent refreshes, and the unique index the concurrent one needs
- ✓Choosing a scheduled, triggered or lazy refresh based on acceptable staleness
- ✓When a materialized view fits better than an application cache
- ✓Keeping materialized views in a Rails schema with structure.sql or scenic
- ✓Making silent refresh failures visible with a refreshed_at column
A view versus a materialized view
A view stores a query. A materialized view stores the answer. Everything else about them follows from that one difference.
Read the full lesson free →Curriculum
7 lessonsA view versus a materialized view
Explains how a view stores a query while a materialized view stores rows as a snapshot.
Refreshing is the whole problem
Covers the ACCESS EXCLUSIVE lock of REFRESH, the unique index for CONCURRENTLY, and when to refresh.
The cache you already have
Compares the cached Admin::StatsQuery with a materialized view and explains why result shape decides between them.
Using them from Rails
Shows why schema.rb drops materialized views, the structure.sql and scenic fixes, and refreshing with raw SQL.
When they earn their place
Names three conditions for a materialized view and tests them against per-course statistics and per-user progress.
The failure modes
Describes silent failures: stopped refreshes, refreshes slower than their interval, and view SQL frozen at creation.
Putting it together
Recaps the course and sets a challenge: a needs_refresh check that orders views by how overdue they are.