Creating and Using Materialized Views
1. Creating Materialized View
CREATE MATERIALIZED VIEW customer_stats AS
SELECT customer_id, count(*) AS orders, sum(total_cents) AS revenue
FROM orders GROUP BY customer_id
WITH DATA;2. Creating with Data
CREATE MATERIALIZED VIEW v WITH (fillfactor = 100) AS SELECT ... WITH DATA;3. Creating without Data
CREATE MATERIALIZED VIEW v AS SELECT ... WITH NO DATA;
REFRESH MATERIALIZED VIEW v;| Mode | Behavior |
|---|---|
| WITH DATA | Populate immediately |
| WITH NO DATA | Empty (unqueryable until refresh) |
4. Querying Materialized Views
SELECT * FROM customer_stats WHERE orders > 10;Note: Reads are normal table reads—use indexes on materialized views for speed.
5. Refreshing Materialized View
REFRESH MATERIALIZED VIEW customer_stats;| Lock | Effect |
|---|---|
| Default | ACCESS EXCLUSIVE — blocks reads |
| CONCURRENTLY | Allows reads but requires unique index |
6. Refreshing Concurrently
CREATE UNIQUE INDEX customer_stats_pk ON customer_stats(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_stats;7. Creating Indexes on Materialized Views
CREATE INDEX customer_stats_rev ON customer_stats(revenue DESC);8. Listing Materialized Views
\dm
SELECT matviewname, ispopulated, definition
FROM pg_matviews;9. Dropping Materialized View
DROP MATERIALIZED VIEW IF EXISTS customer_stats;10. Understanding Materialized View Use Cases
| Use Case | Why |
|---|---|
| Heavy reporting queries | Avoid recompute on every dashboard hit |
| Pre-joined dimension tables | Avoid expensive JOINs |
| Caching FDW results | Snapshot remote data locally |
| Stale-OK aggregates | Refresh nightly via cron |
Warning: Data is stale until next refresh; never use for transactional reads. Use logical replication or triggers for near-real-time needs.