Materialized View

Medium — good to knowDatabase

ELI5 — The Vibe Check

A materialized view is a saved query result that the database keeps on disk like a cheat sheet. Instead of re-running that expensive 10-second report every time someone asks, it just reads the pre-computed answer. You refresh it whenever you want fresh data. It's caching with a fancy SQL hat.

Real Talk

A materialized view stores the result of a query physically on disk, unlike a regular view which re-executes on every access. It dramatically speeds up expensive aggregations and joins at the cost of storage and staleness. PostgreSQL supports REFRESH MATERIALIZED VIEW with CONCURRENTLY for non-blocking updates.

Show Me The Code

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month,
       SUM(amount) AS total
FROM orders
GROUP BY month;

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

When You'll Hear This

"We refresh the materialized view every hour for the dashboard." / "Materialized views turned our 8-second query into 50ms."

Related Terms

Common Table Expression

A CTE (WITH clause) lets you name a sub-query and use it later, like setting a variable in your SQL.

intermediateDatabase

Covering Index

A covering index includes all the columns your query needs, so the database never has to look at the actual table.

advancedDatabase

OLAP

OLAP is all about analyzing huge amounts of data to answer business questions. 'What were total sales by region last quarter?' That's an OLAP query.

intermediateDatabase

Window Function

Window functions let you do calculations across related rows without collapsing them into one result like GROUP BY does.

intermediateDatabase

Back to Browse Random Term