SQL Materialized Views: Caching Expensive Queries
Materialized views store the result of a query on disk. They turn slow aggregations into fast lookups. Here is how I use them and keep them fresh. A materialized view is a query result stored as a table. Unlike a regular view, which is just a saved query definition that runs every time you select from it, a materialized view persists the rows. Selecting from it is as fast as selecting from a table because the data is already computed. I use materialized views for expensive aggregations that do not need real-time accuracy, like daily sales summaries, leaderboards, and reporting dashboards. Regular Views vs Materialized Views A regular view is a saved query. When you select from it, the database runs the underlying query. If the query joins ten tables and aggregates millions of rows, selecting from the view is slow every time. A materialized view stores the result, so selecting from it is fast. The tradeoff is that the stored result can become stale. You must refresh it periodically. . A regular view: slow because it runs the query every time CREATE VIEW daily_sales AS SELECT DATE(created_at) AS sale_date, COUNT(*) AS order_count, SUM(total) AS revenue FROM…