Picture this: your BI dashboard loads in 45 seconds. Your analysts have started leaving the tab open and walking away to get coffee while it loads. Your database server is pegged at 90% CPU every morning when the US East Coast comes online, all because the same handful of complex aggregation queries — spanning hundreds of millions of rows across seven joined tables — run simultaneously for every user who opens the dashboard. You've already added indexes. You've tuned the query planner. You've thrown more RAM at the server. And yet, that 45-second load time stubbornly refuses to move.
This is the scenario where materialized views stop being a nice-to-know feature and become mission-critical infrastructure. A materialized view is, at its core, a precomputed result set stored on disk — not a virtual table that re-executes its defining query every time you touch it, but actual, physical rows that your query planner can read the same way it reads any base table. When designed and maintained correctly, they can turn a 45-second dashboard load into a sub-second one, reduce database CPU load by an order of magnitude, and let your infrastructure scale reads horizontally without touching the underlying transactional tables.
But materialized views come with real complexity. The moment you cache a query result, you've created a consistency problem: how stale is the data, who decides when to refresh it, and what happens to users querying it during a refresh? By the end of this lesson, you'll have genuine command over all of it.
What you'll learn:
You should be comfortable with:
EXPLAIN / EXPLAIN ANALYZE)Before we talk strategy, we need to be precise about the mechanism. When you create a regular view in PostgreSQL or any other RDBMS, the database stores only the view definition — the SQL text. Every time you query the view, the planner expands it inline, as if you had typed the underlying query yourself. There's no caching. There's no stored result. It's a named subquery, nothing more.
A materialized view stores the actual result rows on disk, in its own physical heap. In PostgreSQL, it gets its own pg_class entry, its own storage files, and its own set of indexes. The query planner treats it like a table, because it is a table. The defining query runs exactly once during creation (or during each refresh) and the result is frozen until you explicitly refresh it.
Let's look at the creation syntax to make this concrete:
-- The underlying tables we're working with
-- orders: ~500M rows, partitioned by order_date
-- order_items: ~2B rows
-- products: ~1M rows
-- customers: ~50M rows
CREATE MATERIALIZED VIEW mv_daily_revenue_by_category AS
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
pc.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(oi.quantity) AS units_sold,
SUM(oi.unit_price * oi.quantity) AS gross_revenue,
SUM(oi.unit_price * oi.quantity
- oi.discount_amount) AS net_revenue,
AVG(oi.unit_price * oi.quantity
- oi.discount_amount) AS avg_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN product_categories pc ON pc.category_id = p.category_id
WHERE o.order_date >= '2020-01-01'
GROUP BY
DATE_TRUNC('day', o.order_date),
p.category_id,
pc.category_name
WITH DATA;
The WITH DATA clause tells PostgreSQL to immediately execute the defining query and populate the materialized view. The alternative, WITH NO DATA, creates the view definition but leaves it empty — useful when you want to create the structure during a deployment script and populate it later, or when the underlying data isn't ready yet.
After this runs, you have a physical table on disk. And you can index it:
-- Index for time-range filtering (most common dashboard query pattern)
CREATE INDEX idx_mv_daily_revenue_date
ON mv_daily_revenue_by_category (revenue_date DESC);
-- Composite index for category + date filtering
CREATE INDEX idx_mv_daily_revenue_category_date
ON mv_daily_revenue_by_category (category_id, revenue_date DESC);
-- Partial index for recent data (often the hottest access pattern)
CREATE INDEX idx_mv_daily_revenue_recent
ON mv_daily_revenue_by_category (revenue_date DESC)
WHERE revenue_date >= CURRENT_DATE - INTERVAL '90 days';
This is the first major advantage of materialized views over views: you can index the precomputed result. Your dashboard query that was doing a full join of 500M + 2B rows to compute revenue by category now reads maybe 365 rows from a well-indexed materialized view to return a full year of daily data.
A critical distinction: indexes on materialized views are entirely separate from indexes on the underlying tables. Adding an index to
mv_daily_revenue_by_categorydoes not affect how the defining query (the JOIN + GROUP BY) executes during a refresh. During refresh, PostgreSQL reads the base tables using their indexes. After refresh, your application queries the materialized view using the materialized view's own indexes.
Not every aggregation deserves a materialized view, and some common patterns undermine their benefits. Let's establish a framework for deciding what to materialize and how to structure it.
The best candidates for materialization have two properties: (1) they're expensive to compute — lots of joins, lots of rows, complex window functions — and (2) they're queried frequently with predicates that would be satisfied by a much smaller pre-aggregated result. If a query returns 10 rows from 500M source rows, and that query runs 1,000 times a day, materialization saves you from scanning 500M rows 1,000 times. That's your target.
Poor candidates include: queries that return nearly the full dataset anyway (low aggregation compression), queries that run once per day by one user (low query frequency), and queries where the underlying data changes so rapidly that any cached result is immediately stale.
A common mistake is creating a materialized view that exactly mirrors one specific dashboard query. The right design level is more general — aggregate to the lowest granularity that can serve all your query patterns via filtering and re-aggregation.
Consider this: your analytics team runs three types of queries:
All three queries can be served by mv_daily_revenue_by_category — the daily + category granularity is the finest grain needed. Query 1 filters to 30 days and groups by category. Query 2 filters to category X and 90 days. Query 3 filters to the quarter and groups, orders, limits. The materialized view acts as a pre-aggregated table, and the application queries it the same way it would query any table.
-- Query 1: Served efficiently by the materialized view
SELECT
category_id,
category_name,
SUM(gross_revenue) AS total_gross,
SUM(net_revenue) AS total_net
FROM mv_daily_revenue_by_category
WHERE revenue_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY category_id, category_name
ORDER BY total_net DESC;
-- Query 2: Also served efficiently
SELECT
revenue_date,
gross_revenue,
net_revenue,
order_count
FROM mv_daily_revenue_by_category
WHERE category_id = 42
AND revenue_date >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY revenue_date;
-- Query 3: The quarter filter with LIMIT
SELECT
category_name,
SUM(net_revenue) AS quarterly_revenue
FROM mv_daily_revenue_by_category
WHERE revenue_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY category_name
ORDER BY quarterly_revenue DESC
LIMIT 10;
Window functions are particularly valuable to materialize because they're computationally expensive and their results are deterministic given a fixed dataset. Here's a more complex example that a naive re-run would be very expensive:
CREATE MATERIALIZED VIEW mv_customer_cohort_ltv AS
WITH cohort_assignment AS (
SELECT
customer_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id
),
monthly_revenue AS (
SELECT
c.customer_id,
ca.cohort_month,
DATE_TRUNC('month', o.order_date) AS order_month,
SUM(oi.unit_price * oi.quantity - oi.discount_amount) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN cohort_assignment ca ON ca.customer_id = o.customer_id
-- Alias avoidance: reference the table, not the CTE column
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.customer_id, ca.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
cohort_month,
order_month,
-- Months since cohort acquisition
EXTRACT(YEAR FROM AGE(order_month, cohort_month)) * 12
+ EXTRACT(MONTH FROM AGE(order_month, cohort_month)) AS months_since_acquisition,
COUNT(DISTINCT customer_id) AS active_customers,
SUM(revenue) AS cohort_revenue,
SUM(SUM(revenue)) OVER (
PARTITION BY cohort_month
ORDER BY order_month
) AS cumulative_cohort_revenue,
AVG(SUM(revenue)) OVER (
PARTITION BY cohort_month
ORDER BY order_month
) AS running_avg_revenue
FROM monthly_revenue
GROUP BY cohort_month, order_month
WITH DATA;
This cohort LTV view would take minutes to compute from scratch. As a materialized view indexed on cohort_month and order_month, it returns in milliseconds.
Warning: Materialized views cannot directly reference other materialized views in some older PostgreSQL versions. In PostgreSQL 9.3+, they can, but you need to refresh them in dependency order. We'll cover dependency management in the refresh strategies section.
Refreshing a materialized view is where theory hits production reality. Every refresh strategy involves a set of trade-offs among data freshness, system resource usage, query availability during refresh, and implementation complexity. There's no universally correct answer — the right choice depends on your SLA, your data volume, and your tolerance for complexity.
The simplest refresh: drop all existing rows, re-execute the full defining query, load results into the view.
REFRESH MATERIALIZED VIEW mv_daily_revenue_by_category;
By default in PostgreSQL, this takes an AccessExclusiveLock on the materialized view for the entire duration of the refresh. That means every query against the view blocks until the refresh finishes. For a view that takes 10 minutes to refresh, your dashboard is down for 10 minutes.
The performance characteristics: complete refresh is CPU and I/O intensive during execution, but it's conceptually simple and guaranteed to produce a correct result. It's appropriate when:
PostgreSQL 9.4 introduced CONCURRENTLY, which completely changes the locking story:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue_by_category;
With CONCURRENTLY, PostgreSQL refreshes the view in the background by:
During this entire process, readers can continue querying the view. It takes only a ShareUpdateExclusiveLock, which is compatible with read operations.
The catch: CONCURRENTLY requires a unique index on the materialized view. Without one, PostgreSQL has no efficient way to perform the diff. For our revenue view:
-- Required for CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_daily_revenue_pk
ON mv_daily_revenue_by_category (revenue_date, category_id);
And the performance cost: concurrent refresh is slower than a regular complete refresh — sometimes significantly. It scans both the old and new result sets to produce the diff, so you're doing roughly twice the I/O. For large views, this can matter. Benchmark both approaches and choose based on your availability requirements.
Tip: Run
REFRESH MATERIALIZED VIEW CONCURRENTLYduring off-peak hours even if you don't strictly need it to be live during the refresh. The lock contention from regular refresh can cause cascading queue buildup in connection pools under high load.
PostgreSQL doesn't have native incremental refresh — Oracle and Snowflake do, but PostgreSQL doesn't out of the box. However, you can build an incremental refresh pattern manually using a combination of tracking tables and targeted view redesign.
The approach requires splitting your materialized view strategy into two layers. The first layer materializes all historical data that you know won't change (e.g., all complete days before today). The second layer handles the "hot" data — today's partial data — either with a fast complete refresh of just the current period or with a view (not materialized) that reads live data.
Here's the pattern:
-- Layer 1: Historical materialized view (refreshed daily, covers all complete days)
CREATE MATERIALIZED VIEW mv_daily_revenue_historical AS
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
pc.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(oi.quantity) AS units_sold,
SUM(oi.unit_price * oi.quantity) AS gross_revenue,
SUM(oi.unit_price * oi.quantity
- oi.discount_amount) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN product_categories pc ON pc.category_id = p.category_id
WHERE o.order_date >= '2020-01-01'
AND o.order_date < DATE_TRUNC('day', CURRENT_TIMESTAMP) -- Complete days only
GROUP BY
DATE_TRUNC('day', o.order_date),
p.category_id,
pc.category_name
WITH DATA;
-- Layer 2: Today's data as a regular view (always live)
CREATE VIEW v_daily_revenue_today AS
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
pc.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(oi.quantity) AS units_sold,
SUM(oi.unit_price * oi.quantity) AS gross_revenue,
SUM(oi.unit_price * oi.quantity
- oi.discount_amount) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN product_categories pc ON pc.category_id = p.category_id
WHERE o.order_date >= DATE_TRUNC('day', CURRENT_TIMESTAMP)
GROUP BY
DATE_TRUNC('day', o.order_date),
p.category_id,
pc.category_name;
-- Unified view: applications query this
CREATE VIEW v_daily_revenue_unified AS
SELECT * FROM mv_daily_revenue_historical
UNION ALL
SELECT * FROM v_daily_revenue_today;
This pattern gives you:
The daily refresh only needs to cover all complete days up to yesterday. Because yesterday was in v_daily_revenue_today during the day, and is now complete, the overnight refresh picks it up and moves it into mv_daily_revenue_historical.
You need something driving the refresh. In PostgreSQL, pg_cron is the most common choice for lightweight scheduling:
-- Install pg_cron extension (requires superuser, done once per database)
CREATE EXTENSION pg_cron;
-- Schedule the historical view refresh at 00:30 daily (after data ingestion completes)
SELECT cron.schedule(
'refresh-daily-revenue-historical',
'30 0 * * *', -- 00:30 every day
$$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue_historical$$
);
-- Schedule the cohort LTV view weekly (Sunday at 02:00, expensive to compute)
SELECT cron.schedule(
'refresh-cohort-ltv-weekly',
'0 2 * * 0',
$$REFRESH MATERIALIZED VIEW mv_customer_cohort_ltv$$
);
-- Check scheduled jobs
SELECT * FROM cron.job;
-- Monitor recent job runs
SELECT
jobid,
runid,
job_pid,
database,
username,
command,
status,
return_message,
start_time,
end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;
For production data pipelines, you'll often want a proper orchestrator — Airflow, Prefect, dbt, or Dagster — rather than pg_cron. The advantage of an orchestrator is explicit dependency management: you can ensure the materialized view refresh runs only after your ETL/ELT pipeline has finished loading data, not on a fixed clock schedule that might run before data arrives on a slow night.
Here's how this looks in a dbt context, which has built-in materialized view support:
# models/analytics/daily_revenue_by_category.sql
# dbt model configuration
{{ config(
materialized='materialized_view',
on_configuration_change='apply',
indexes=[
{'columns': ['revenue_date', 'category_id'], 'unique': True},
{'columns': ['revenue_date'], 'type': 'btree'}
]
) }}
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
pc.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.unit_price * oi.quantity
- oi.discount_amount) AS net_revenue
FROM {{ ref('orders') }} o
JOIN {{ ref('order_items') }} oi ON oi.order_id = o.order_id
JOIN {{ ref('products') }} p ON p.product_id = oi.product_id
JOIN {{ ref('product_categories') }} pc ON pc.category_id = p.category_id
GROUP BY 1, 2, 3
dbt handles refresh as part of its dbt run execution, respecting the dependency graph. If orders is loaded before daily_revenue_by_category, dbt ensures the materialized view refreshes after the source data is ready.
In real analytics architectures, materialized views build on each other. A staging materialized view over raw data feeds an intermediate summary, which feeds a reporting-level aggregate. You cannot refresh the top-level view before its dependencies are current, or you'll materialize stale data.
Here's a realistic three-tier dependency:
-- Tier 1: Raw order summary (refreshes hourly)
CREATE MATERIALIZED VIEW mv_order_summary_hourly AS
SELECT
DATE_TRUNC('hour', o.order_date) AS order_hour,
o.store_id,
o.channel_id,
COUNT(*) AS order_count,
SUM(oi.unit_price * oi.quantity) AS gross_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY 1, 2, 3
WITH DATA;
-- Tier 2: Daily rollup from the hourly summary (refreshes daily)
CREATE MATERIALIZED VIEW mv_order_summary_daily AS
SELECT
DATE_TRUNC('day', order_hour) AS order_date,
store_id,
channel_id,
SUM(order_count) AS order_count,
SUM(gross_revenue) AS gross_revenue
FROM mv_order_summary_hourly -- Depends on Tier 1
GROUP BY 1, 2, 3
WITH DATA;
-- Tier 3: Store performance report (refreshes daily, after Tier 2)
CREATE MATERIALIZED VIEW mv_store_performance AS
SELECT
d.order_date,
s.store_name,
s.region,
d.channel_id,
ch.channel_name,
d.order_count,
d.gross_revenue,
d.gross_revenue / NULLIF(d.order_count, 0) AS avg_order_value,
SUM(d.gross_revenue) OVER (
PARTITION BY d.store_id
ORDER BY d.order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_revenue
FROM mv_order_summary_daily d -- Depends on Tier 2
JOIN stores s ON s.store_id = d.store_id
JOIN channels ch ON ch.channel_id = d.channel_id
WITH DATA;
For cascading refreshes, you need to execute them in order. Here's a stored procedure to handle this safely:
CREATE OR REPLACE PROCEDURE refresh_analytics_pipeline()
LANGUAGE plpgsql
AS $$
DECLARE
v_start_time TIMESTAMPTZ;
v_elapsed INTERVAL;
BEGIN
-- Tier 1
v_start_time := clock_timestamp();
RAISE NOTICE 'Refreshing mv_order_summary_hourly at %', v_start_time;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary_hourly;
v_elapsed := clock_timestamp() - v_start_time;
RAISE NOTICE 'mv_order_summary_hourly done in %', v_elapsed;
-- Tier 2 (only after Tier 1 completes)
v_start_time := clock_timestamp();
RAISE NOTICE 'Refreshing mv_order_summary_daily at %', v_start_time;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary_daily;
v_elapsed := clock_timestamp() - v_start_time;
RAISE NOTICE 'mv_order_summary_daily done in %', v_elapsed;
-- Tier 3 (only after Tier 2 completes)
v_start_time := clock_timestamp();
RAISE NOTICE 'Refreshing mv_store_performance at %', v_start_time;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_store_performance;
v_elapsed := clock_timestamp() - v_start_time;
RAISE NOTICE 'mv_store_performance done in %', v_elapsed;
RAISE NOTICE 'Analytics pipeline refresh complete';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Pipeline refresh failed at step: %', SQLERRM;
END;
$$;
Warning: If any tier fails, do not proceed to refresh downstream views. The
EXCEPTIONblock above raises the error and lets the caller handle it. In an orchestrator like Airflow, you'd model each tier as a separate task with explicitupstream_task_idsdependencies, so a failure in Tier 1 automatically prevents Tier 2 and 3 from running.
One of the most powerful features in columnar/analytical databases is automatic query rewrite — where the query planner detects that a query against a base table could be satisfied by a materialized view, and automatically routes to the view instead. PostgreSQL doesn't support this natively, but some databases do.
In Snowflake, materialized views (and the newer Dynamic Tables) support automatic query rewrite. When you run a query against the base table, Snowflake's optimizer checks if a materialized view can satisfy it more efficiently:
-- Snowflake materialized view
CREATE MATERIALIZED VIEW mv_daily_revenue_by_category
CLUSTER BY (revenue_date, category_id)
AS
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
pc.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.unit_price * oi.quantity) AS gross_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN product_categories pc ON pc.category_id = p.category_id
GROUP BY 1, 2, 3;
-- This query against the BASE TABLE may be automatically
-- rewritten to use the materialized view by Snowflake's optimizer
SELECT
DATE_TRUNC('day', o.order_date) AS revenue_date,
p.category_id,
SUM(oi.unit_price * oi.quantity) AS gross_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_date >= '2024-01-01'
GROUP BY 1, 2;
Snowflake's auto-refresh of materialized views is continuous and automatic — you don't schedule it. But this comes at credit cost: Snowflake charges for the background refresh compute. For views with rapidly changing data, auto-refresh costs can be significant.
BigQuery's materialized views also support smart refresh and query rewrite. The "authorized" concept means you can grant the materialized view permission to access source tables without granting direct table access to users:
-- BigQuery materialized view with max_staleness for cost control
CREATE MATERIALIZED VIEW `project.dataset.mv_daily_revenue`
OPTIONS (
enable_refresh = true,
refresh_interval_minutes = 60,
max_staleness = INTERVAL "4" HOUR
)
AS
SELECT
DATE_TRUNC(order_date, DAY) AS revenue_date,
category_id,
COUNT(DISTINCT order_id) AS order_count,
SUM(unit_price * quantity) AS gross_revenue
FROM `project.dataset.orders` o
JOIN `project.dataset.order_items` oi USING (order_id)
JOIN `project.dataset.products` p USING (product_id)
GROUP BY 1, 2;
max_staleness is particularly interesting — it tells BigQuery that it's acceptable to serve results up to 4 hours stale from the materialized view. If the view is fresh enough, BigQuery uses it. If it's too stale, it falls back to computing from the base tables.
In PostgreSQL without automatic rewrite, you handle this at the application layer. A clean pattern is to create a thin view that routes between the materialized view and a live fallback:
-- Function that checks if the materialized view is fresh enough
CREATE OR REPLACE FUNCTION mv_revenue_is_fresh(max_staleness_minutes INTEGER DEFAULT 60)
RETURNS BOOLEAN
LANGUAGE sql STABLE
AS $$
SELECT EXISTS (
SELECT 1
FROM pg_stat_user_tables
WHERE relname = 'mv_daily_revenue_by_category'
AND (last_analyze > NOW() - (max_staleness_minutes || ' minutes')::interval
OR last_autoanalyze > NOW() - (max_staleness_minutes || ' minutes')::interval)
)
$$;
Tip: A more reliable freshness check uses an explicit refresh tracking table that your refresh procedure writes to. Relying on
pg_stat_user_tablesis fragile because autoanalyze timing doesn't directly reflect refresh timing.
-- Better: explicit refresh tracking
CREATE TABLE mv_refresh_log (
view_name TEXT NOT NULL,
refreshed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
duration_ms INTEGER,
row_count BIGINT,
PRIMARY KEY (view_name, refreshed_at)
);
-- In your refresh procedure, log each completion:
INSERT INTO mv_refresh_log (view_name, refreshed_at, duration_ms, row_count)
SELECT
'mv_daily_revenue_by_category',
NOW(),
EXTRACT(EPOCH FROM (NOW() - v_start_time)) * 1000,
(SELECT COUNT(*) FROM mv_daily_revenue_by_category);
You can't manage what you don't measure. When implementing materialized views, establish baselines and track these metrics:
-- Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to capture execution details
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
category_name,
SUM(net_revenue) AS quarterly_revenue
FROM mv_daily_revenue_by_category
WHERE revenue_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY category_name
ORDER BY quarterly_revenue DESC;
Look at:
ANALYZE mv_daily_revenue_by_category after refreshpg_prepared_statementsTrack refresh duration over time. As your underlying data grows, refresh times grow. A refresh that took 30 seconds with 6 months of data might take 8 minutes with 3 years of data. If you're on a fixed schedule (e.g., refresh every hour), a refresh duration that exceeds your refresh interval is a crisis.
-- Monitor refresh duration trends from your refresh log
SELECT
view_name,
DATE_TRUNC('week', refreshed_at) AS week,
AVG(duration_ms) AS avg_duration_ms,
MAX(duration_ms) AS max_duration_ms,
COUNT(*) AS refresh_count
FROM mv_refresh_log
WHERE refreshed_at >= NOW() - INTERVAL '90 days'
GROUP BY view_name, DATE_TRUNC('week', refreshed_at)
ORDER BY view_name, week;
If your refresh is competing with application queries for locks, you'll see it in lock wait metrics:
-- Snapshot of current lock waits (run during a refresh to observe impact)
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query,
blocked_activity.wait_event_type,
blocked_activity.wait_event
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
AND blocking_locks.granted AND NOT blocked_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
AND blocked_locks.relation::regclass::text LIKE 'mv_%';
For very large materialized views, consider partitioning the underlying storage. PostgreSQL doesn't support partitioned materialized views directly, but you can build the pattern:
-- Instead of one large materialized view, create yearly partition-aligned views
CREATE MATERIALIZED VIEW mv_daily_revenue_2022 AS
SELECT * FROM mv_daily_revenue_by_category
WHERE revenue_date BETWEEN '2022-01-01' AND '2022-12-31'
WITH DATA;
CREATE MATERIALIZED VIEW mv_daily_revenue_2023 AS
SELECT * FROM mv_daily_revenue_by_category
WHERE revenue_date BETWEEN '2023-01-01' AND '2023-12-31'
WITH DATA;
-- This is cumbersome; a better approach is to build the union view
-- and only refresh the current year's view frequently
The real-world pattern for very large datasets is to use table inheritance or declarative partitioning on a regular table that you populate from the materialized view data, giving you the benefits of both partitioning and precomputation.
Not all consumers need the same data freshness. Executive dashboards viewing quarterly trends can tolerate 24-hour-old data. An operations dashboard watching today's order throughput needs data that's at most 5 minutes stale. Design your materialized view tier to reflect this:
-- Tier A: Ops dashboard (refreshes every 5 minutes via pg_cron)
CREATE MATERIALIZED VIEW mv_hourly_ops_metrics AS
SELECT
DATE_TRUNC('hour', order_date) AS order_hour,
store_id,
COUNT(*) AS orders_placed,
COUNT(*) FILTER (
WHERE status = 'failed'
) AS orders_failed,
AVG(processing_time_seconds) AS avg_processing_time
FROM orders
WHERE order_date >= CURRENT_TIMESTAMP - INTERVAL '48 hours'
GROUP BY 1, 2
WITH DATA;
-- Refresh every 5 minutes
SELECT cron.schedule(
'refresh-ops-metrics',
'*/5 * * * *',
$$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hourly_ops_metrics$$
);
-- Tier B: Management dashboard (refreshes daily)
CREATE MATERIALIZED VIEW mv_executive_monthly_summary AS
SELECT
DATE_TRUNC('month', order_date) AS order_month,
region,
SUM(gross_revenue) AS gross_revenue,
COUNT(DISTINCT customer_id) AS unique_customers,
COUNT(*) AS total_orders
FROM orders o
JOIN stores s ON s.store_id = o.store_id
GROUP BY 1, 2
WITH DATA;
This exercise builds a complete materialized view analytics layer for a SaaS subscription business. You'll create the views, set up refresh tracking, and validate query performance.
-- Create the base schema
CREATE TABLE subscriptions (
subscription_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL,
plan_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL, -- 'active', 'churned', 'paused'
started_at TIMESTAMPTZ NOT NULL,
ended_at TIMESTAMPTZ,
monthly_amount NUMERIC(10,2) NOT NULL
);
CREATE TABLE plans (
plan_id INTEGER PRIMARY KEY,
plan_name VARCHAR(100) NOT NULL,
plan_tier VARCHAR(20) NOT NULL, -- 'starter', 'growth', 'enterprise'
billing_cycle VARCHAR(20) NOT NULL -- 'monthly', 'annual'
);
CREATE TABLE subscription_events (
event_id BIGSERIAL PRIMARY KEY,
subscription_id BIGINT NOT NULL,
event_type VARCHAR(50) NOT NULL, -- 'created', 'upgraded', 'downgraded', 'churned', 'reactivated'
event_date DATE NOT NULL,
old_plan_id INTEGER,
new_plan_id INTEGER,
old_amount NUMERIC(10,2),
new_amount NUMERIC(10,2)
);
-- Generate realistic test data
INSERT INTO plans VALUES
(1, 'Starter Monthly', 'starter', 'monthly'),
(2, 'Starter Annual', 'starter', 'annual'),
(3, 'Growth Monthly', 'growth', 'monthly'),
(4, 'Growth Annual', 'growth', 'annual'),
(5, 'Enterprise Monthly', 'enterprise', 'monthly'),
(6, 'Enterprise Annual', 'enterprise', 'annual');
-- (Insert your subscription and event data here for testing)
Task 1: Create a materialized view mv_mrr_by_plan_monthly that computes Monthly Recurring Revenue (MRR) broken down by plan tier and billing cycle for each calendar month. MRR for annual subscriptions should normalize the annual amount to a monthly equivalent (annual amount / 12). Include a count of active subscriptions per cohort.
Task 2: Create a second materialized view mv_churn_analysis_monthly that computes the churn rate per month per plan tier. Churn rate = (subscriptions that churned in month) / (subscriptions active at start of month). Reference your MRR view where helpful.
Task 3: Create a refresh log table and write a stored procedure that refreshes both views in dependency order (MRR first, then churn, since churn analysis references the plan tier dimension that MRR also uses), logs duration and row counts, and raises a descriptive error if either refresh fails.
Task 4: Write the query against mv_mrr_by_plan_monthly that produces a 12-month trailing MRR trend by plan tier, with a 3-month moving average using a window function. Verify with EXPLAIN ANALYZE that the query uses the index on the materialized view rather than a sequential scan.
Expected outcome: Your query from Task 4 should execute in under 50ms against the materialized view, compared to several seconds or minutes against the raw subscription tables. Add indexes to mv_mrr_by_plan_monthly and mv_churn_analysis_monthly appropriate to the query patterns you'd expect on a SaaS metrics dashboard.
This seems obvious but causes real production incidents. Someone joins a new data source to a materialized view's underlying table, inserts new data, and wonders why the dashboard doesn't reflect it. There is no trigger, no automatic mechanism, no magic. The view reflects its state at last refresh and nothing else. Document this explicitly for every materialized view your team creates.
Fix: Add a refreshed_at column to your refresh log and surface it in dashboards. Let users see exactly how old the data is.
The error is cryptic: ERROR: cannot refresh materialized view "mv_foo" concurrently and then DETAIL: a unique index is required to refresh it concurrently. The fix is to add the unique index — but choose the unique index columns carefully. They must uniquely identify each row in the result set.
If your GROUP BY doesn't produce a naturally unique combination, add a surrogate with ROW_NUMBER() — but be careful, because that changes the semantics of concurrent refresh's diff algorithm.
A materialized view built directly on raw operational tables will become expensive to refresh as those tables grow. Consider inserting a dbt staging model or a simpler pre-aggregation step that filters and cleans the raw data, then materializing on top of that. The inner step handles data quality; the outer step handles aggregation.
After a complete (non-concurrent) refresh, the query planner's statistics for the materialized view are stale. The planner might choose a sequential scan over an index scan because it doesn't know how many rows are in the view. Run ANALYZE immediately after refresh:
REFRESH MATERIALIZED VIEW mv_daily_revenue_by_category;
ANALYZE mv_daily_revenue_by_category;
Concurrent refresh handles this automatically via the autovacuum process, but for complete refresh you need to do it explicitly.
You cannot create a dependency cycle between materialized views. If View A depends on View B and View B depends on View A, PostgreSQL will reject the creation. This sometimes happens when a team is trying to build a bidirectional comparison and loses track of which view is the source of truth. Draw your dependency graph before building.
A REFRESH MATERIALIZED VIEW CONCURRENTLY is not a single atomic transaction from the user's perspective. Between the moment the refresh starts computing the new result and the moment it finishes applying the diff, users might see slightly inconsistent results if they're querying specific cross-section combinations. This is usually acceptable for analytics, but it's worth understanding. If you need point-in-time consistency, use a complete (non-concurrent) refresh inside a transaction, accepting the downtime.
EXPLAIN ANALYZE on the defining query directly)pg_stat_activity during a refresh to see if it's waiting on locks from other queriesRun ANALYZE mv_your_view_name to update statistics. If the index still isn't used, check that the query predicate matches the index definition exactly. A query on revenue_date::date won't use an index on revenue_date if revenue_date is a TIMESTAMPTZ stored with timezone offset. Ensure types match.
Materialized views are one of the highest-leverage tools in the advanced SQL practitioner's toolkit. The core concepts to take away:
The fundamental trade-off is always freshness vs. performance vs. cost. You're paying storage cost and refresh compute cost to eliminate per-query compute cost. The math works overwhelmingly in your favor when the same expensive computation is run frequently.
Design your materialized views at the right granularity. Don't build one per dashboard query. Build them at the finest grain that serves all related query patterns, and let the application re-aggregate from the precomputed layer. Daily + category granularity serves weekly, monthly, quarterly, and YTD aggregations without separate views.
Choose your refresh strategy based on your availability and freshness requirements:
pg_cron alone cannot provideTrack your refreshes explicitly. A mv_refresh_log table and a monitoring query showing refresh duration trends is non-negotiable in production. Refreshes that grow gradually beyond your refresh window will cause incidents.
For next steps, explore:
is_incremental() macros, which implement the historical/live split pattern in a framework-managed waypg_hint_plan or plan guides (in SQL Server) prevents optimizer regressions from breaking your performance guaranteesLearning Path: Advanced SQL Queries