Materialized views in PostgreSQL: how they work and when to use them

← Back to the section

Sometimes one query against the database runs in a second, another takes five, and a third takes twenty. This is almost always analytics: COUNT, SUM, GROUP BY, several JOINs. You can't fix such a query with an index — it is heavy by nature.

A materialized view is a way to store the result of such a query on disk and serve it quickly, without recomputing it every time.

What a materialized view is

A regular VIEW is just stored SQL. Every time you query it, PostgreSQL runs the query again from scratch.

A MATERIALIZED VIEW works differently: PostgreSQL runs the query once, stores the result as a table on disk, and reads from it afterwards. The data "goes stale" — but in exchange it is read instantly.

Here is the heavy query itself — how many orders each customer placed and for how much:

live example

SELECT customer_id,
       count(*)          AS orders_count,
       sum(total_amount) AS total_spent,
       max(created_at)   AS last_order_at
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

On a hundred thousand orders it reads the whole table and recomputes the aggregates every time. A materialized view runs it once and puts the result on disk:

CREATE MATERIALIZED VIEW order_stats_mv AS
SELECT customer_id,
       count(*)          AS orders_count,
       sum(total_amount) AS total_spent,
       max(created_at)   AS last_order_at
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
WITH DATA;

After that, a query looks like an ordinary SELECT from a table:

SELECT * FROM order_stats_mv WHERE customer_id = 42;

WITH NO DATA creates an empty materialized view without computing it immediately — handy during migrations, when you want to create the structure first and populate it later. You cannot read such a view: until the first REFRESH, any SELECT from it fails with an error.

When a materialized view helps

A materialized view is a good fit in several situations:

• Heavy aggregations that are read often, where a small refresh delay is acceptable: reports, dashboards.
• Complex JOINs across several tables whose result changes rarely.
• Precomputed search indexes — pre-processed tsvector vectors for full-text search.

A materialized view is not a good fit when:

• the data changes constantly and you need real-time freshness;
• the query is simple — an ordinary index is enough;
• the cost of refreshing the materialized view is higher than the benefit of caching.

Indexes on a materialized view

A materialized view is a table, and you can create indexes on it just like on a regular one:

CREATE INDEX ix_order_stats_customer ON order_stats_mv (customer_id);
CREATE UNIQUE INDEX uk_order_stats_customer ON order_stats_mv (customer_id);

A regular index speeds up lookups. A unique index is needed for one more reason — it is required for REFRESH CONCURRENTLY, which we cover below. And not just any unique index will do: plain column names only, no WHERE clause and no expressions.

How to refresh a materialized view

The data in a materialized view does not update itself. You have to call REFRESH explicitly.

Plain REFRESH — maintenance window only

REFRESH MATERIALIZED VIEW order_stats_mv;

It recomputes everything from scratch and, for that whole time, blocks any SELECT from the view: on a large view the lock lasts minutes. Suitable for small views, or for a period with no active users.

REFRESH CONCURRENTLY — the standard choice for production

REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;

This variant does not block reads. While the refresh is running, queries against the materialized view keep working — they see the old data, but they don't wait.

How it works: PostgreSQL computes the new result in a temporary structure, then compares it with the current data and applies only the difference. Hence three conditions and one price:

• a unique index is required — without it PostgreSQL doesn't know how to match rows;
• the view must already be populated: CONCURRENTLY and WITH NO DATA cannot be used together;
• two refreshes of the same view never run at once — the second one waits for the first;
• a plain REFRESH uses fewer resources and finishes faster, because it simply writes the new result over the old one. CONCURRENTLY also builds a temporary copy and compares it with the old data, and it wins where few rows have changed.

For production, always use CONCURRENTLY.

Incremental refresh

PostgreSQL cannot refresh a materialized view partially out of the box. It's all or nothing.

If you need an incremental refresh, there are a few options:

• pg_ivm — a PostgreSQL extension that adds incremental refresh of materialized views;
• triggers on the source tables with manual updates of the affected rows;
• TimescaleDB continuous aggregates — if TimescaleDB is already in your stack.

How often to refresh

Periodically, on a schedule — for analytics

The simplest and most reliable option: refresh every few minutes regardless of changes in the data. Suitable for dashboards and reports, where a small delay is not critical.

@Component
class OrderStatsRefreshJob {

    private final JdbcTemplate jdbc;

    OrderStatsRefreshJob(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Scheduled(fixedDelay = 300_000)
    public void refreshOrderStats() {
        jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
    }
}

A trigger on every change — too expensive

You can put a trigger on the source table that runs REFRESH after every INSERT, UPDATE, or DELETE. The problem is that under active writes a heavy REFRESH fires on every row — that kills performance. It is justified only if changes are very rare and the view is small.

Debouncing via a dirty flag — the sweet spot

The best option when the data changes regularly but not constantly: a change sets a "needs refresh" flag, and a periodic job looks at the flag once a minute and runs REFRESH only because of it.

You can see the mechanics without a database. The orders list here is the source table, snapshot is what the materialized view holds, and tick() is that periodic job.

live example

import java.util.ArrayList;
import java.util.List;

public class RefreshDemo {
    static final List<Integer> orders = new ArrayList<>(List.of(500, 400, 600));
    static long snapshot = total();
    static boolean dirty = false;

    public static void main(String[] args) {
        for (int minute = 1; minute <= 4; minute++) {
            if (minute == 1 || minute == 3) {
                orders.add(600);
                dirty = true;
            }
            System.out.println("minute " + minute + ": table " + total() + ", snapshot " + snapshot);
            tick();
        }
    }

    static void tick() {
        if (!dirty) {
            System.out.println("  REFRESH skipped: nothing changed");
            return;
        }
        snapshot = total();
        dirty = false;
        System.out.println("  REFRESH CONCURRENTLY: snapshot is now " + snapshot);
    }

    static long total() {
        return orders.stream().mapToLong(Integer::intValue).sum();
    }
}

Across four runs REFRESH happened twice — where the data really changed; in between, readers saw the old total. The flag can live in Redis or in the application's memory; what matters is not recomputing without a reason.

Materialized view or a separate projection table

Sometimes a materialized view is compared to the read model approach from CQRS — a separate table that is updated by an event handler.

The rule is simple: complex aggregation, frequent reads and an acceptable one-minute delay — a materialized view; minimal delay and per-row updates — a separate table with an event handler.

Common mistakes

No unique index — no CONCURRENTLY. REFRESH CONCURRENTLY will fail with an error if there is no unique index. Create it right when you create the materialized view.

An AFTER trigger on every INSERT. Under heavy write load this destroys performance. Use debouncing or a periodic schedule.

REFRESH from a database migration. A migration is not the place for a REFRESH. Refresh through a scheduled job or manually after deploy.

In short

• A materialized view stores the result of a heavy query on disk: reads are instant, but you see the data as of the last REFRESH.
• A plain REFRESH blocks SELECT for the whole recomputation — maintenance window only.
• REFRESH CONCURRENTLY does not block reads, but it needs a unique index on plain columns and an already populated view.
• A schedule every few minutes is the simplest working option for reports and dashboards.
• A dirty flag saves recomputations when changes come in bursts; a trigger per row is only for rare changes and small views.
• Real-time data and simple queries without aggregations are not a job for a materialized view — use an index or a projection table.

What to read next

• EXPLAIN ANALYZE — how to confirm the query really is heavy and the view is read by index.
• Composite indexes and the leftmost prefix — which index to add besides the mandatory unique one.
• Triggers — when NOT to use them — why a REFRESH from a per-row trigger kills writes.
• Full-text search in PostgreSQL — a common case for a materialized view: precomputed tsvector.