Materialized views are obviously useful

Summary

As programmers we spend a lot of time shuttling data back and forth between different systems and transforming it from one format to another. Sometimes it gets pretty miserable! Let’s say you’re making a fancy task tracking app. Tasks belong to projects, and on the projects page, you want to show how many tasks are in each project. Maybe you start with a little SQL that you call from your “get info about project” view model code: async function getTaskCountForProject(projectId) { return await db.query('select count(1) from tasks where project_id = $1', [projectId]); } Wow! So easy. Uh oh, someone is tapping you on the shoulder and saying this is too slow because it has to do a complete index scan of the tasks for the project, every time you load the page. That’s fine, we’ll just put a… Redis cache on it? Putting a cache on it async function getTaskCountForProject(projectId) { const key = `project:${projectId}:task-count`; const redisCount = await redis.get(key); if (redisCount !== null) { return +redisCount; } const count = await db.query( 'select count(1) from tasks where project_id = $1', [projectId], ); await redis.set(key, count, { ex: 3600 }); return count; } Works great. It’s fast enough. (Technically speaking, if 100 people load the same page at the same time and the cache isn’t populated yet, then we’ll end up sending 100 queries to the database which isn’t amazing, but let’s just pretend we didn’t hear that.) Unfortunately our users are complaining that the count is wrong a lot of the time now? Like they add a new task or delete a task and the count doesn’t change and they’re confused. Reasonably so. I guess we could clear the cache entry whenever we create or delete tasks. But really it would be better to not scan the whole list of tasks whenever we need a count. Some projects have many thousands of tasks in them! Incremental updates So let’s do something smarter. It’s not necessary to recompute the count from scratch every time. When we’re creating a task...