How to Create Materialized Views in PostgreSQL

How do I create and refresh a materialized view in PostgreSQL?

CREATE MATERIALIZED VIEW stores the result of a query on disk, letting you query pre-computed data for fast reads.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose a materialized view instead of a table?

Materialized views auto-recompute from a base query, so you avoid manual ETL while still getting read performance similar to a table. Use them for dashboards, aggregates, and vector search snapshots in ParadeDB.

How do I create a materialized view?

Run CREATE MATERIALIZED VIEW with an AS query. Add WITH NO DATA if you want to define it first and populate later with REFRESH.

Syntax breakdown

IF NOT EXISTS prevents errors on re-run. WITH () sets storage parameters such as fillfactor. USING method is optional; ParadeDB users can specify a custom storage method.

E-commerce example

CREATE MATERIALIZED VIEW mv_customer_sales AS SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.id;

This view stores each customer’s lifetime sales so dashboards load instantly.

How do I refresh a materialized view?

Data becomes stale as underlying tables change. Use REFRESH MATERIALIZED VIEW to update it.

Manual refresh

REFRESH MATERIALIZED VIEW mv_customer_sales;

This locks the view during the refresh.

Concurrent refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales;

CONCURRENTLY keeps the old data available but requires a unique index on the view.

When should I use materialized views?

Choose them when query latency matters more than instant freshness: reporting dashboards, autocomplete lookup lists, AI vector indexes in ParadeDB, or cross-table aggregates.

Best practices for ParadeDB users

Store expensive vector search results in a materialized view, then create ParadeDB’s hnsw index on the view’s embedding column for low-latency similarity lookups.

Common mistakes to avoid

1. Forgetting to refresh. Schedule cron or pg_cron jobs.
2. Omitting a unique index. Without it, CONCURRENTLY fails.

FAQ

Do materialized views update automatically?

No. You must call REFRESH manually or via a job scheduler.

Can I index columns in a materialized view?

Yes. Treat it like a table and create B-tree, GIN, or ParadeDB vector indexes.

How do I drop a materialized view?

Use DROP MATERIALIZED VIEW IF EXISTS mv_customer_sales;.

Why How to Create Materialized Views in PostgreSQL is important

How to Create Materialized Views in PostgreSQL Example Usage


-- Snapshot of in-stock products and their latest price
CREATE MATERIALIZED VIEW mv_active_products AS
SELECT id, name, price, stock > 0 AS in_stock
FROM   Products
WHERE  stock > 0;

-- Keep dashboards fresh every hour
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_products;

How to Create Materialized Views in PostgreSQL Syntax


CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
    [ (column_name [, ...] ) ]
    [USING method]
    [WITH ( storage_parameter = value [, ... ] ) ]
    [TABLESPACE tablespace_name]
AS select_query
    [WITH [ NO ] DATA];

REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name;

Common Mistakes

Skipping REFRESH: The view becomes outdated. Fix by scheduling REFRESH MATERIALIZED VIEW via pg_cron or an external job runner.
Missing unique index before CONCURRENTLY: PostgreSQL needs a unique index on the view to support concurrent refreshes. Create one first.