Materialized Views

Joins have a cost. Every join is work the database does on each read: find the matching rows in the second table and pair them with the rows from the first. Take the list of published posts with the author’s name. The join to users is one lookup per post in the list, and the list is built on every reader visit. With only 20 posts per list and hundreds of readers, that is thousands of lookups.

One way to remove the join is to denormalize posts: add an author_name column next to author_id and write the name into it when the post is created. The list query then reads one table and you avoid the join. This has its own cost, which we discussed earlier in the section on denormalization.

Whether that trade is worth making is hard to know in advance. The right way to decide is to profile the application in use and decide from what you measure. I would build the application with the join, watch how readers actually use HopPress, measure the query, and then decide whether to denormalize or keep the join. If you decide before the application launches, you are guessing, and the guess rests on experience with similar applications.

There are cases where denormalizing is not a reasonable option at all. Consider CourseTracker from Chapter 2. One of its requirements says: “When a student requests their course history, CourseTracker must list the offerings they recorded, grouped by term. For each recorded offering, CourseTracker must show the course number, title, credits, subject area, professor’s name and email address, meeting time, and room.”

Those values live in five tables. The query joins all of them:

SELECT o.term, c.course_number, c.title, c.credits, s.name AS subject_area,
       p.name AS professor, p.email, o.meeting_time, o.room
FROM history_entries h
JOIN offerings o ON h.offering_id = o.offering_id
JOIN courses c ON o.course_number = c.course_number
JOIN subject_areas s ON c.subject_area_id = s.subject_area_id
JOIN professors p ON o.professor_id = p.professor_id
WHERE h.student_id = 1
ORDER BY o.term;

The degree requirements for each course are one more join, left out here to keep the query readable.

To denormalize this, we would copy the course title, credits, subject area, professor’s name and email, meeting time, and room into history_entries. That is effectively the spreadsheet we split apart to get the normalized tables. All the problems of the original spreadsheet are back: data duplication, inconsistency, and the need to update multiple rows when a single value changes.

So the join has to stay. Every student who opens CourseTracker runs it. There is a way to make the read cheaper: materialized views. A view is a query saved under a name:

CREATE VIEW course_history AS
SELECT h.student_id, o.term, c.course_number, c.title, c.credits,
       s.name AS subject_area, p.name AS professor, p.email,
       o.meeting_time, o.room
FROM history_entries h
JOIN offerings o ON h.offering_id = o.offering_id
JOIN courses c ON o.course_number = c.course_number
JOIN subject_areas s ON c.subject_area_id = s.subject_area_id
JOIN professors p ON o.professor_id = p.professor_id;

After this, the application can query the view as if it were a table:

SELECT * FROM course_history WHERE student_id = 1 ORDER BY term;

The database runs the saved query and applies the condition. The join is written once, in the view, and every place in the application that needs a course history reads from course_history. The gain is that the application code is simpler and easier to maintain. The join is still run on every read, and the read is still expensive.

A materialized view is a view whose result is stored:

CREATE MATERIALIZED VIEW course_history AS
SELECT ...;

The difference is that the database runs the query once and writes the result to disk as a table. Reading course_history now reads that stored table. No joins run. We can even add an index on student_id to the stored table so the read for one student becomes faster.

The stored result is a copy, and it goes out of date. For example, when an administrator changes an offering’s room, the change is in the offerings table, but not in course_history. The copy shows the old room until it is refreshed, which means the database runs the query again and replaces the stored result. How a refresh is triggered depends on the database. Some refresh on a schedule the administrator sets. Some can refresh whenever one of the underlying tables changes. Some do not support materialized views at all, and the application has to build the equivalent by hand.

The cost is staleness, and whether it is acceptable depends on how soon readers need to see a change. Catalog data changes rarely, on an administrator’s schedule. A room change appearing an hour late is acceptable for a course-history view. It would not be acceptable for a seat count in a registration system.

Two ways to store a derived result

Denormalization stores a derived value, the post’s current state, as a column on posts. The application maintains that copy, in a transaction, on every write. If the application gets it wrong, the copy no longer matches the source rows.

A materialized view stores a derived result, the joined course history, as a table. The database maintains that copy, on refresh. Between refreshes the copy is stale, but it is never inconsistent with itself, and the application does not write to it.

Both are the same trade: store the answer to a frequent read so that the read does not compute it. The difference is who keeps the stored answer current, and what goes wrong when they do not. Denormalization fits a value that changes on the application’s own writes and must be current on the next read, like a post’s state. A materialized view fits a result assembled from many tables that changes on someone else’s schedule and can lag, like a course history.