LLM Best Practices

Materialized view

2 min read

View markdown source

Overview

This page is the atomic definition. Query optimization lives at postgres and postgres-explain.

Definition

A materialized view is a database object that stores the result of a SELECT query physically on disk, like a regular table. Unlike a regular (logical) view, which re-runs its query on every access, a materialized view is computed once and stored. Queries against it are as fast as queries against a normal indexed table. The data goes stale after the underlying tables change; REFRESH MATERIALIZED VIEW recomputes it. PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY, which updates the view without blocking readers by computing the new snapshot in the background and swapping atomically (requires a unique index on the view). Incremental Materialized Views (supported in some databases like Snowflake and DuckDB) update only the changed rows rather than recomputing the full result. Materialized views are best for expensive aggregations, denormalized reporting tables, and precomputed join results that are read far more often than they are refreshed.

When it applies

Use a materialized view when a query takes more than a few hundred milliseconds and the data does not need to be real-time. Pair with a background job or trigger to refresh on a schedule or after significant data changes.

Example

CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1;
 
CREATE UNIQUE INDEX monthly_revenue_month_idx ON monthly_revenue (month);
 
-- Refresh without blocking reads:
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
  • postgres - CREATE MATERIALIZED VIEW syntax and refresh strategies.
  • secondary-index - materialized views require a unique index for concurrent refresh.
  • read-replica - both materialize reads; replicas handle live queries, views handle precomputed aggregations.
  • postgres-explain - use EXPLAIN to confirm the planner is hitting the materialized view.

Citing this term

See Materialized view (llmbestpractices.com/glossary/materialized-view).

Graph View

Backlinks