Quick Start (5 min): Cache Your First Heavy Query
Scanning millions of rows for analytical dashboards routinely crushes database performance. When an internal portal recalculates total order volumes, customer lifetime value, and gross margins on every refresh, database CPU quickly hits 100%. End users are left waiting on blank screens.
Standard SQL views do not solve this bottleneck. A standard view acts merely as a saved query alias; PostgreSQL re-evaluates the entire query tree on every single call. In contrast, a materialized view executes the query once and writes the raw results to disk as physical heap pages. You read the pre-computed dataset just like a standard table.
Let’s run a test on a standard transactional table:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id INT NOT NULL,
amount NUMERIC(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert one million test transactions
INSERT INTO orders (customer_id, amount, status, created_at)
SELECT
(random() * 5000)::INT,
(random() * 500 + 10)::NUMERIC(10, 2),
(ARRAY['completed', 'pending', 'cancelled'])[floor(random() * 3 + 1)],
NOW() - (random() * interval '90 days')
FROM generate_series(1, 1000000);Running aggregations against all 1,000,000 rows typically takes 400ms to 900ms depending on disk speed and buffer cache warmth. We can eliminate that overhead by storing the pre-computed totals in a materialized view:
CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
customer_id,
COUNT(id) AS total_orders,
SUM(amount) AS total_spent,
MAX(created_at) AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;Querying the materialized view is now straightforward:
SELECT * FROM mv_customer_sales_summary WHERE total_spent > 5000;Query latency drops immediately from 680ms down to 1.8ms. PostgreSQL avoids scanning the 65MB orders table and reads straight from the pre-aggregated 200KB snapshot.
Deep Dive: Why Materialized Views Outperform Application Caching
Database administrators often debate whether to cache aggregate metrics in Redis or precalculate them directly in PostgreSQL. While Redis provides microsecond key-value lookups, it shifts considerable complexity to your application layer.
The Problem: Reporting Tools Exhaust Database Connections
Picture a management dashboard loaded simultaneously by 40 operations leads during morning standup. Each visit triggers multi-table joins across orders, shipping logs, and refund tables. Under raw SQL views, PostgreSQL spins up parallel query workers for each request. Memory fills with hash tables, query queues back up, and client connection pools quickly hit exhaustion limits.
The Trade-off: External Key-Value Caching vs. In-Engine Persistence
External caching layers like Redis handle point lookups well, but filtering aggregated data creates architectural friction. What happens when an analyst wants to filter high-value accounts by acquisition date and sort by lifetime spend? With Redis, you must either fetch massive JSON blobs into application memory or maintain complicated secondary indexes.
Materialized views bridge this gap neatly. They retain relational query flexibility while eliminating repetitive aggregation work.
Native Indexing on Aggregated Data
Because a materialized view stores concrete rows on disk, you can index it like any standard PostgreSQL table. Regular views do not allow indexing:
CREATE INDEX idx_mv_customer_spent
ON mv_customer_sales_summary(total_spent DESC);Once indexed, retrieving the top 100 spenders requires an Index Scan rather than a full sequential scan. On a dataset with 500,000 aggregated customers, this cuts query time from 45ms to 0.4ms.
Zero-Downtime Updates with REFRESH CONCURRENTLY
Materialized views are static snapshots. When new rows land in the orders table, the view falls behind. Refreshing the view is straightforward:
REFRESH MATERIALIZED VIEW mv_customer_sales_summary;However, running this default command in active production environments will cause serious problems.
The Locking Penalty of Default Refreshes
A basic REFRESH MATERIALIZED VIEW command grabs an ACCESS EXCLUSIVE table lock. This lock blocks every incoming SELECT query on the view until recalculation finishes. If your query takes 45 seconds to scan base tables, all user requests queuing behind it will stall or hit HTTP 504 gateway timeouts.
The Zero-Downtime Solution: Concurrent Refreshes
PostgreSQL provides a non-blocking alternative: REFRESH MATERIALIZED VIEW CONCURRENTLY. Instead of locking the view, PostgreSQL generates the fresh dataset into a temporary table. It then calculates a differential diff and applies changes directly to the existing view using an EXCLUSIVE lock. This allows active SELECT queries to proceed uninterrupted.
To use this mode, PostgreSQL requires a UNIQUE index with no WHERE clause (it cannot be a partial index) spanning columns with non-null values:
-- Step 1: Create a non-partial unique index
CREATE UNIQUE INDEX idx_mv_customer_id_unique
ON mv_customer_sales_summary(customer_id);
-- Step 2: Refresh the view without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;Incoming traffic reads the older snapshot until the refresh finishes. Once the diff commits, new queries instantly see the updated records without dropped transactions or stalled worker threads.
Automating Periodic Refreshes
PostgreSQL does not ship with an automated scheduler out of the box. Teams typically handle periodic updates through external job workers, crontabs, or the pg_cron extension. Here is a production-ready cron configuration:
# Refresh customer summary every 15 minutes without blocking reads
*/15 * * * * psql -U dbuser -d production_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;" >> /var/log/pg_refresh.log 2>&1If you prefer scheduling inside the database engine, install pg_cron and schedule the refresh using native SQL:
SELECT cron.schedule('refresh_customer_sales',
'*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary'
);Practical Tips for Production Workloads
- Plan for Temporary Disk Surges: During a concurrent refresh, PostgreSQL builds the entire updated view in the background before applying diffs. Ensure your disk has enough headroom to store roughly 2.5x the view’s normal disk footprint during the operation.
- Weigh Speed Against Locking Needs: Concurrent refreshes take roughly 1.5x to 2x longer to complete and produce more write I/O than standard refreshes. If you are refreshing historical data inside an off-peak maintenance window with no read traffic, run a standard refresh for faster completion.
- Watch Out for WITH NO DATA Pitfalls: You can append
WITH NO DATAduring schema migrations to avoid long deployment locks. However, PostgreSQL marks an unpopulated view as unreadable. Furthermore, you cannot run a concurrent refresh on an unpopulated view; your first run must be a standard refresh:
-- Instant deployment in migration script (does not populate rows)
CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
customer_id,
COUNT(id) AS total_orders,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
WITH NO DATA;
-- Required initial populate (standard refresh, not CONCURRENTLY):
REFRESH MATERIALIZED VIEW mv_customer_sales_summary;
-- Subsequent updates can now safely use CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_customer_id_unique ON mv_customer_sales_summary(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;- Audit Execution Plans with EXPLAIN (ANALYZE, BUFFERS): Always profile baseline and optimized queries. Check the
Buffers: shared hitcount to confirm that PostgreSQL is serving data directly from shared buffers instead of running sequential heap scans across millions of source rows.
