Views & Materialized ViewsBeginner
1Question 1 of 1
sqlWhat is a Materialized View in PostgreSQL and how do you refresh it concurrently?
Explanation & Concept
A Materialized View executes an expensive query and persists the result set as a physical table on disk, which can have its own indexes for blazing-fast reads.
However, data becomes stale until refreshed. Using `REFRESH MATERIALIZED VIEW CONCURRENTLY` updates the view without acquiring exclusive read locks, allowing users to continue querying old data while the refresh completes.
Code Example
sql
-- Define materialized view for monthly billing metrics
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
date_trunc('month', created_at) AS month,
COUNT(id) AS total_invoices,
SUM(amount) AS gross_revenue
FROM Invoices
WHERE status = 'PAID'
GROUP BY 1;
-- Unique index is REQUIRED for CONCURRENT refresh
CREATE UNIQUE INDEX idx_mv_revenue_month ON mv_monthly_revenue (month);
-- Refresh in background without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;How It Works & Key Takeaways
A unique index on the materialized view is mandatory for `CONCURRENTLY`. Postgres creates a temporary copy, computes the delta, and updates the rows in place without locking out readers.
Mock Test · 1 Questions · Optional
Ready for Triggers & Functions?
Take a quick test to evaluate yourself, or move straight to the next module.
Intermediate1 questions