AuthorVeraDomainDatabases / PostgreSQLDepth2 — practical working knowledgeTypeDeep dive
A regular view is a saved query. Every time you run SELECT * FROM my_view, Postgres executes the underlying query from scratch. That is usually fine.
A materialized view is different. The result set is computed once and stored on disk. Subsequent reads hit that stored result, not the base tables. The query runs fast because it already ran.
The trade-off is explicit: you control when the data refreshes. Postgres does not do it for you.
Creating a materialized view
CREATE MATERIALIZED VIEW homepage_stats AS
SELECT
date_trunc('day', created_at) AS day,
COUNT(*) AS new_projects,
COUNT(*) FILTER (WHERE verified_at IS NOT NULL) AS verified_projects
FROM projects
GROUP BY 1
ORDER BY 1 DESC;
That query runs once, at creation time, and the result is stored. Future reads against homepage_stats are fast table scans against cached rows.
Refreshing
Data goes stale the moment the underlying tables change. To update it:
-- Blocks all reads while refreshing
REFRESH MATERIALIZED VIEW homepage_stats;
-- Non-blocking — runs concurrently with reads
REFRESH MATERIALIZED VIEW CONCURRENTLY homepage_stats;
CONCURRENTLY is almost always what you want in production. It builds a new version of the view and swaps it in without locking readers out. But there is a requirement: the materialized view must have at least one unique index.
CREATE UNIQUE INDEX ON homepage_stats (day);
Without that index, CONCURRENTLY fails. The first refresh after creation also cannot use CONCURRENTLY — the view is empty, so there is no existing data to swap against. Run the first refresh normally, then use CONCURRENTLY for all subsequent ones.
Indexes on materialized views
You can add regular indexes to a materialized view just like a table:
CREATE INDEX ON homepage_stats (new_projects DESC);
This makes range queries and sorts against the materialized view fast. The index is rebuilt on each refresh, so index maintenance cost is included in the refresh cost.
When to use a materialized view
Three patterns where materialized views pay off:
Aggregate reports. Dashboard queries that join many tables and aggregate across millions of rows. Precompute them once; serve the result. Acceptable staleness is usually minutes or hours, not milliseconds.
Search facets. Counts by category, tag, or status for filter UI. These queries are cheap to precompute, expensive to run live under load.
Computed stats on a public-facing page. In Builds, the homepage displays counts that involve scanning the full projects table. We materialized those stats and refresh on a schedule. The homepage does not touch base tables at all.
What materialized views are not
They are not a cache. There is no TTL. There is no automatic invalidation when underlying data changes. There is no partial refresh — you refresh the entire view or nothing. If you need row-level invalidation, you are describing a different problem.
They are not a substitute for proper indexes. If your slow query just needs a better index plan, add the index. Materialized views add operational complexity — refresh scheduling, staleness management, index rebuild cost — that a well-indexed query does not.
They are also not a substitute for denormalized tables in write-heavy workloads. If you need the derived data to be current within milliseconds of writes, maintain a real table with triggers or application-layer updates. Materialized views are for workloads where periodic staleness is acceptable.
The CONCURRENTLY trap on first use
New teams consistently hit this: they create a materialized view, add the required unique index, and then try to refresh with CONCURRENTLY — only to get an error because the view has zero rows. Postgres cannot compute a diff against nothing.
The fix is straightforward:
-- After creation, always do the first refresh non-concurrently
REFRESH MATERIALIZED VIEW homepage_stats;
-- All subsequent refreshes can be concurrent
REFRESH MATERIALIZED VIEW CONCURRENTLY homepage_stats;
Wrap this in whatever job scheduler you use. If you are using pg_cron:
SELECT cron.schedule('*/5 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY homepage_stats');
Comparing with alternatives
Query caching. Application-layer caches (Redis, Memcached) give you TTL-based invalidation and finer control. The tradeoff is cache invalidation complexity and another infrastructure dependency. Materialized views are simpler operationally when staleness is tolerable and the data is already in Postgres.
Denormalized tables. A real table you maintain explicitly gives you row-level updates and triggers. It is more flexible and more work. Use it when you need sub-second freshness or when only parts of the table go stale.
Materialized views sit between them: more structured than raw query caching, less work than full denormalization. They have a specific niche, and that niche is real.
The staleness budget is the decision. If your reporting dashboard can be five minutes behind, materialize it. If it needs to reflect the last committed write, it cannot.
Building in public at builds.anethoth.com — public build dossiers for software projects in progress.