Creating Materialized Views in PostgreSQL for Faster Analytics

When you're dealing with complex or frequently accessed queries, materialized views in PostgreSQL can drastically improve performance. Unlike regular views, materialized views store the result set, allowing for faster reads at the cost of manual refreshes.

What Is a Materialized View?

A materialized view is a snapshot of a query result stored on disk. It's useful for analytics, reports, or dashboard metrics that don't need to update in real time.

Step 1: Create a Base Table

Let’s start with a sample sales table:

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product TEXT,
  amount NUMERIC,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the Materialized View

Let’s say we want to calculate daily revenue:

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT 
  DATE(created_at) AS date,
  SUM(amount) AS total
FROM sales
GROUP BY DATE(created_at)
ORDER BY date;

Step 3: Query the View

Querying the materialized view is just like querying a regular table, and it's fast because the data is precomputed:

SELECT * FROM daily_revenue ORDER BY date DESC;

Step 4: Refreshing the View

Since the materialized view doesn't auto-update, you’ll need to refresh it manually (or via a scheduled job):

REFRESH MATERIALIZED VIEW daily_revenue;

For faster refreshes, consider adding the CONCURRENTLY keyword (note: this requires a unique index):

CREATE UNIQUE INDEX daily_revenue_date_idx ON daily_revenue(date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Use Cases

• Dashboard metrics that don’t require real-time updates
• Slow aggregation queries
• Offline reporting

Conclusion

Materialized views can significantly reduce query times for analytics in PostgreSQL. Use them strategically to cache expensive computations and improve app performance where real-time data isn't critical.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift