In this lesson, you will learn how to trade minor data latency for dramatic read-performance gains using materialized views and the CONCURRENTLY refresh strategy.
The Performance Trade-off
In heavy analytical workloads, your Planner/Optimizer often struggles with complex joins and aggregations computed on the fly. A MATERIALIZED VIEW acts as a physical snapshot, storing the result set of a query on disk. Instead of re-calculating expensive joins or sorting millions of rows, the database simply reads the pre-computed table.
While this gives you near-instant reads, the data becomes "stale" until you explicitly refresh the view.
The Locking Problem
When you run REFRESH MATERIALIZED VIEW my_view, PostgreSQL places an ACCESS EXCLUSIVE lock on the view. This blocks all concurrent read queries—a major problem in high-traffic applications. To solve this, we use CONCURRENTLY.
Refreshing Without Blocking
When you refresh concurrently, PostgreSQL creates a temporary version of the data, compares it to the existing view, and updates only the necessary rows. This requires a unique index on the materialized view to identify row changes.
-- Create the materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT region, SUM(amount) as total
FROM orders
GROUP BY region;
-- You MUST create a unique index for concurrent refreshes
CREATE UNIQUE INDEX idx_mv_region ON monthly_sales_summary (region);
-- Update the data without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;
Why Use This?
If your application queries real-time analytical data, the Planner cost will stay high. By materializing this data, you bypass the Join Algorithms (like Hash or Merge) and Sorting overhead entirely. Using CONCURRENTLY ensures that your background refresh tasks don't cause spikes in latency or connection timeouts for your users. You are effectively shifting the computational cost from the read-time of the user to the background-refresh period.
Key Takeaway: Materialized views turn expensive analytical queries into simple sequential scans, and using the CONCURRENTLY keyword ensures these background updates remain invisible to your users.