Postgres Materialized Views: Create, Refresh, and Optimize

A Postgres materialized view stores the result of a query as a physical table on disk, so subsequent reads return precomputed rows instead of re-running the SELECT against the underlying tables. For expensive aggregations, multi-table joins, and reporting workloads over large datasets, it turns an operation that scans millions of rows into a single indexed lookup, giving improved query performance to users across the database.

The tradeoff is freshness. Unlike traditional views, a materialized view does not reflect the current state of its source. The cached data is whatever the SELECT returned the last time the materialized view was built or rebuilt — and Postgres does not update it automatically. When the underlying data changes, the materialized view continues to serve a stale snapshot until you tell it to refresh.

This guide covers how to create one, how each refresh mode behaves, how to add indexes for faster access, how to schedule rebuilds without blocking users, and where the built-in implementation runs out of room when downstream readers need real time data under high write throughput.

What Is a Postgres Materialized View?

A Postgres materialized view is a database object that stores the result of a SELECT statement as a physical table on disk. Reads against the view return the cached rows directly without re-running the SELECT, giving improved query performance on expensive joins and aggregations. Unlike a traditional view, it does not update when the underlying data changes — it must be refreshed manually with the `REFRESH MATERIALIZED VIEW` command.

A standard Postgres `VIEW` is a saved query — every time a user selects from it, the database runs that SELECT against the current rows in one or more tables. A `MATERIALIZED VIEW` is a saved result: Postgres executes the SQL statement once, persists the rows on disk in table-like form, and serves subsequent reads from that snapshot.

PostgreSQL materialized views sit between two extremes. On one end, ad-hoc complex queries always return current rows from the source but pay full cost on every call — leading to performance issues and slow access times under load. On the other end, hand-rolled summary tables maintained by application code give fast access but require custom database management logic. A matview is Postgres’s built-in version of a summary table: defined declaratively, rebuilt with one statement, and queryable like any other table.

Common cases where a matview earns its keep — examples spanning reporting and analytical workloads stored on disk for fast read access: - Expensive aggregations that would otherwise scan large fact tables on every dashboard load — for example, daily revenue rollups, monthly active user counts, leaderboard rankings. - Multi-table joins across one or more tables where the join cost dominates query performance for complex queries. - Derived metrics combining multiple sources where live computation is too slow — common in scenarios like customer 360 dashboards or aggregated cohort views. - Reporting workloads that tolerate refresh-interval lag and don’t require fresh data — overnight or hourly is fine.

The cases where it does not fit are the focus of the second half of this post.

Creating a Postgres Materialized View

Creating a materialized view in PostgreSQL is straightforward. You write a SELECT statement, and Postgres stores the result on disk. The basic syntax is the `CREATE MATERIALIZED VIEW` command. As an example, a daily order rollup:

CREATE MATERIALIZED VIEW daily_order_totals AS
SELECT
    DATE_TRUNC('day', created_at) AS order_day,
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY DATE_TRUNC('day', created_at), customer_id
WITH DATA;

Notice two clauses worth understanding upfront when creating a materialized view.

`WITH DATA` vs. `WITH NO DATA`. `WITH DATA` (the default) executes the SELECT immediately and populates the new view at creation time, with rows stored on disk before the statement returns. `WITH NO DATA` creates an empty placeholder — the view exists in the system catalog but contains no rows until you run `REFRESH MATERIALIZED VIEW`. Use `WITH NO DATA` when you want to define the structure now and populate later — for example, during an off-hours maintenance window.

Tablespaces and storage parameters. Because the view is stored on disk as a physical table, you can place it in a specific tablespace and tune storage parameters like `fillfactor` the same way you would when creating any other database table:

CREATE MATERIALIZED VIEW daily_order_totals
TABLESPACE fast_ssd
WITH (fillfactor = 90)
AS SELECT ...;

Once created, the new materialized view appears in `pg_matviews` and behaves like a physical table for `SELECT` purposes. You can join it, filter it, and reference it from other views and queries.

Refreshing a Postgres Materialized View

PostgreSQL does not rebuild materialized views automatically. The base refresh materialized view command is:

REFRESH MATERIALIZED VIEW daily_order_totals;

This re-runs the SELECT and replaces the contents with the new result set. The process acquires an `ACCESS EXCLUSIVE` lock — concurrent reads and writes against the view block until the operation completes. For small views or off-hours rebuilds, this is fine. For views queried by an application that cannot tolerate read blocks, performance issues during rebuild become a real problem.

Indexing a Postgres Materialized View

Because the view is stored as a physical table, it can be indexed like one. Creating indexes is typically the highest-leverage tuning step after the view exists — without them, every read scans the entire table, eroding the speed advantage the materialization was supposed to deliver.

CREATE INDEX idx_daily_totals_customer
ON daily_order_totals (customer_id);

CREATE INDEX idx_daily_totals_amount
ON daily_order_totals (total_amount DESC)
WHERE total_amount > 1000;

Three guidelines: 1. Match the query patterns. If users filter by `customer_id`, that column needs an index. Match indexes to actual data access patterns, not hypothetical ones. 2. Use partial indexes for skewed distributions. A partial index on `total_amount > 1000` is small and fast for the queries that need it, without slowing down rebuild or wasting storage on cold rows. 3. Remember the primary-key-style index for concurrent rebuild. Non-negotiable when concurrent mode is required.

Indexes are rebuilt during a plain `REFRESH MATERIALIZED VIEW` (PostgreSQL drops and recreates the heap, then rebuilds them). Concurrent mode updates them incrementally, preserving access times throughout the process.

Choosing a Refresh Strategy

How you trigger rebuilds matters as much as which statement you run. Three patterns cover most workloads:

Scheduled refresh. Run `REFRESH MATERIALIZED VIEW` on a fixed cadence — hourly, daily, every five minutes — using `pg_cron`, an external scheduler, or a simple cron job. Best fit: views where the source changes predictably and downstream users tolerate a known lag.

SELECT cron.schedule(
    'refresh-daily-totals',
    '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_totals'
);

Triggered refresh. Rebuild in response to a specific event — a batch load completing, a downstream report being requested. Best fit: freshness tied to a specific upstream signal rather than wall-clock time.

Manual refresh. Rebuild manually on demand from an application or operator. Best fit: ad-hoc views, development environments, or cases where the view only needs to be current when a specific human is asking. Many teams run rebuilds manually during data corrections or backfills.

What none of these patterns gives you is continuous freshness. The view is always at least one refresh interval behind the source. If your scheduled rebuild runs every fifteen minutes, downstream reads can be up to fifteen minutes stale. Whether that matters depends entirely on the consumer — a daily executive dashboard does not care; a real-time fraud check or credit decision operating on real time data does.

Materialized View vs. View vs. Table

A side-by-side comparison clarifies when each PostgreSQL object earns its keep — and when each delivers the database performance the workload actually needs:

The decision rule: use a traditional view when the underlying query is cheap or always needs to reflect actual data. Use a materialized view when the query is expensive and users tolerate refresh-interval staleness. Use `CREATE TABLE` when you own the rows and need to write to them directly.

When the Built-in Approach Hits Its Limit

The built-in implementation is a good fit for one specific pattern: an expensive query that returns results changing slowly relative to read frequency, feeding users that tolerate known refresh-interval lag.

Three workloads break that pattern:

Source data changes constantly. If `orders` is taking thousands of inserts per second, a view aggregating over `orders` is stale the instant rebuild completes. Schedule it every minute and the view is up to a minute behind. Schedule it every second and rebuild itself never finishes — PostgreSQL recomputes the full SELECT over every source row every time.

Downstream consumers need current state. Dashboards tolerate minutes of lag. Automated decisions — fraud checks, credit approvals, eligibility gates, AI agent actions — do not. When the consumer acts within a tight validity window, derived context two minutes old can produce an outcome that costs real money. These are real time data scenarios, and refresh-on-a-cron cannot serve them.

Concurrent reads need data consistency. A single view rebuilt in isolation is one thing. But real systems have many derived views — a velocity counter, an exposure total, a behavioral score — across one or more PostgreSQL databases. If each is rebuilt on its own cadence, concurrent reads see different versions of reality. The fraud counter says 3 transactions in the last hour; the spend total reflects 2. Each is internally correct; together they describe a customer state that never existed.

This is the context gap — the structural lag between source events and derived state that downstream automated decisions read. Vanilla matviews are firmly on the wrong side of it.

Incremental Refresh Approaches

A materialized view that recomputed only the changes to the underlying tables — instead of re-running the full SELECT — would close most of the gap. Several approaches exist around or beyond core PostgreSQL.

`pg_ivm` extension. pg_ivm adds incremental view maintenance for a subset of query shapes. When a row is inserted into one of the underlying tables, the extension propagates the delta to dependent views — no full recomputation, no scan of the entire table. Coverage is limited (some complex views are not supported), but for the workloads it covers, the latency improvement is dramatic and database performance under rebuild load drops substantially.

Trigger-based maintenance. For specific high-value derived tables, a trigger on the source can update a summary table directly on each insert. Most flexible — you can express any logic — but most operationally expensive: every trigger adds write-path overhead, and base schema changes require rewriting the maintenance logic. Scales poorly past a handful of derived views.

External CDC + stream processor. Capture changes from PostgreSQL via logical decoding and push them through a stream processor (Flink, Materialize, RisingWave) that maintains the view incrementally. Decouples maintenance from the source database’s write path and lets the system cache results outside PostgreSQL, but introduces a separate system to operate.

Streaming databases and context lakes. A class of newer systems treats incremental view maintenance as a first-class capability — derived state is maintained continuously as events arrive, and reads always reflect the most recent ingested events under one consistent snapshot. PostgreSQL materialized views become the analytical fallback rather than the serving layer. Tacnode is one example; the incremental materialized views post covers this pattern in depth, with examples of how PostgreSQL materialized views complement rather than compete with continuous incremental maintenance.

The right approach depends on workload shape. For a single high-value view with simple aggregation logic, `pg_ivm` or triggers may be enough. For systems where many derived views must stay fresh together — and data consistency across them matters — a separate substrate purpose-built for incremental maintenance is usually the right answer.

When Matviews Aren’t Enough

For reporting and dashboards, matviews are excellent. One statement creates them, one rebuilds them, and one B-tree makes them fast for the queries users actually run — all without leaving the database your operational rows already live in. For teams running expensive query workloads against frequently accessed data with minutes-to-hours freshness tolerance, vanilla matviews deliver the speed and operational simplicity that drove their adoption.

For automated decisions on derived data — fraud, credit, eligibility, agent actions — they fall short on three structural axes: 1. Freshness. The view is always at least one refresh interval behind. Decisions acting within a validity window shorter than the refresh interval read stale state by definition. 2. Refresh cost. Without incremental computation, rebuild recomputes the entire SELECT every time. As source rows grow, rebuild time grows linearly — past some point, frequency cannot keep up with the freshness requirement. 3. Consistency across views. Multiple matviews refreshed independently produce inconsistent snapshots when read concurrently. The fraud counter, the exposure total, and the behavioral score reflect three different points in time.

For workloads where these axes matter — high-velocity scenarios where complex queries must serve consistent state from a small validity window — a context lake maintains derived state incrementally as CDC events arrive, with all retrieval patterns served from one internally coherent snapshot. Postgres remains the source of truth for transactional rows; the context lake serves the derived state that decisions read.

This is not a replacement for matviews. It is the layer above them, for workloads they were never designed to handle.

Frequently Asked Questions

Key Takeaways

• A Postgres materialized view caches a SELECT result as a physical table for improved query performance on expensive joins and aggregations. Reads are fast; rows are stale until refreshed. - Use `CREATE MATERIALIZED VIEW ... WITH DATA` to populate at creation, `WITH NO DATA` to defer. - `REFRESH MATERIALIZED VIEW` rebuilds the view but takes an `ACCESS EXCLUSIVE` lock. Concurrent rebuild allows reads to continue but needs a primary-key-style index. - Neither rebuild mode is incremental — PostgreSQL recomputes the entire underlying query every time. - Index on the columns your queries filter and aggregate by; partial indexes work well for skewed distributions and faster access. - Use `pg_cron`, an external scheduler, or event triggers to drive scheduled rebuilds — or refresh manually for ad-hoc cases. - Matviews fit reporting and dashboard workloads. They do not fit automated decisions on derived data under high write throughput — the freshness, rebuild cost, and data consistency limits across views are structural, not tuning problems. - For workloads where derived state must stay current under concurrency, layer an incremental maintenance approach on top of PostgreSQL or move derived state into a substrate that maintains views incrementally as events arrive.