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.

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 order_doc
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.

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.
• Denormalization for reads: instead of recomputing statistics every time, keep them ready-made.
• Precomputed search indexes — for example, when you need to store 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.

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;

This variant recomputes everything from scratch and, while it runs, blocks any SELECT from that view. On a large materialized view the lock can last minutes. It's only suitable for small views or during 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. Because of this:

• a unique index is required — without it PostgreSQL doesn't know how to match rows;
• it runs a bit slower than a plain REFRESH because of this comparison.

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: under active writes the trigger will run a heavy REFRESH on every row. This kills performance.

This variant is justified only if changes to the source table are very rare and the materialized view is small.

Debouncing via a dirty flag — the sweet spot

The best option for situations where the data changes regularly but not constantly: refresh the materialized view only when the data has changed, and no more than once a minute.

The idea is simple: when the data changes, we set a "needs refresh" flag. A periodic job checks the flag and runs REFRESH only if the flag is set.

@Component
class OrderStatsDebouncedRefresh {

    private final StringRedisTemplate redis;
    private final JdbcTemplate jdbc;

    OrderStatsDebouncedRefresh(StringRedisTemplate redis, JdbcTemplate jdbc) {
        this.redis = redis;
        this.jdbc = jdbc;
    }

    @EventListener
    public void onOrderChanged(OrderChangedEvent e) {
        redis.opsForValue().set("mv:order_stats:dirty", "1");
    }

    @Scheduled(fixedDelay = 60_000)
    public void refreshIfDirty() {
        Boolean deleted = redis.delete("mv:order_stats:dirty");
        if (Boolean.TRUE.equals(deleted)) {
            jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
        }
    }
}

The flag can live in Redis, in the application's memory, or in any other fast store. The main thing is not to run REFRESH without a reason and no more often than needed.

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 for choosing is simple: if you need a complex SELECT ... GROUP BY with several JOINs, the data is read often, and a one-minute delay is acceptable — use a materialized view. If you need minimal delay and per-row updates — use a separate table with an event handler.

Common mistakes

Using a plain REFRESH in production. It blocks reads. Always use CONCURRENTLY.

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.

A materialized view for real-time data. If the data is updated every second and you need freshness — this is not a job for a materialized view.

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 the data may be slightly stale.
• For production, always use REFRESH MATERIALIZED VIEW CONCURRENTLY. It does not block reads.
• CONCURRENTLY requires a unique index — create it right away.
• A plain REFRESH blocks SELECT for the entire duration — maintenance window only.
• A periodic schedule (every 5 minutes) is the simplest option for analytics.
• Debouncing via a dirty flag is the best option when you need to refresh only on real changes.
• A trigger on every change — only for rare changes and small views.
• A materialized view is not a good fit for real-time data or for simple queries without aggregations.

What to read next

• Indexes in PostgreSQL — how to index a materialized view.
• Full-text search in PostgreSQL — materialized views for search indexes.
• Partitioning — an alternative for archival data.