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.

We should keep the normalized tables. But the application does not have to run the join on every read. 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 and stores its result. Reading course_history then reads that stored result. No joins run for that read. We can even add an index on (student_id, term) to the stored result so the read for one student becomes faster.

The stored result is a copy, and it goes out of date. For example, an administrator may correct a professor’s email address. The change is in professors, but the stored course_history result still has the old email until it is refreshed. A refresh runs the query again and replaces the stored result. How a refresh happens depends on the database and the application. A system might refresh the view every hour. Some databases can refresh it automatically after a change to an underlying table. Some do not support materialized views, so the application has to build the equivalent itself.

The cost is staleness, and whether it is acceptable depends on how soon readers need to see a change. A corrected professor email appearing an hour late may be acceptable. A student who adds an offering expects to see it in their course history immediately. For that write, CourseTracker can refresh the view after adding the history entry, or read that student’s history from the base tables until the next scheduled refresh. The requirements decide which choice is appropriate.

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 rebuilds that copy on refresh. Between refreshes the copy is stale, but each refresh produces one stored result from the source data at that time, 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 when recomputing it on a refresh is cheaper than recomputing it on every read, and when the requirements allow the chosen freshness policy.