How to Create and Use Materialized Views

Imagine yourself as the administrator of a users’ database for an e-commerce website. Within this database, there is a table known as “orders” which stores the details of every order that has been made. Now suppose that you want to continuously have a view with the number of orders made by each customer.

If you create a normal Postgres view, you will be running a large table scan and aggregation every time you view this data, which is a bit inefficient. Instead, you can use materialized views.

What are Materialized Views?

Materialized views are very similar to normal Postgres views, with the key difference that their results can be stored in disk as they are computed. By physically storing the data, they avoid the need to recompute the results each time a query runs.

Some key benefits of materialized views include:

• Improved query performance for complex calculations
• Reduced server load by pre-computing expensive operations
• Ability to create indexes on the materialized view
• Perfect for data warehousing and business intelligence applications

(To learn more about normal views in Postgres, take a look at our guide.)

Creating and Using Materialized Views

Let’s walk through the steps to create and effectively use materialized views:

Step 1 - Open your terminal and connect it to your desired Postgres database. Check out our comprehensive guide to establish a connection from your terminal to Postgres database.

Step 2 - Create a materialized view using the CREATE MATERIALIZED VIEW statement. Here are two practical examples:

Example 1 - Customer Order Analytics:

CREATE MATERIALIZED VIEW customer_order_totals AS
SELECT
    customer_id,
    COUNT(*) as total_orders,
    SUM(order_amount) AS total_amount,
    AVG(order_amount) AS avg_order_value,
    MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;

Example 2 - Product Performance Metrics:

CREATE MATERIALIZED VIEW product_performance AS
SELECT
    p.product_id,
    p.product_name,
    COUNT(o.order_id) as times_ordered,
    SUM(o.quantity) as total_units_sold,
    SUM(o.quantity * p.price) as total_revenue
FROM products p
LEFT JOIN order_items o ON p.product_id = o.product_id
GROUP BY p.product_id, p.product_name;

Step 3 - After creating the materialized view, you need to populate it with data. There are several approaches:

Manual Refresh

Use the REFRESH MATERIALIZED VIEW statement to populate and refresh the materialized view:

REFRESH MATERIALIZED VIEW customer_order_totals;

Concurrent Refresh

If you need to keep the view accessible during refresh, use the CONCURRENT option:

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_totals;

Note: To use CONCURRENT refresh, your materialized view must have a UNIQUE index on one or more columns. For example:

CREATE UNIQUE INDEX customer_order_totals_customer_id ON customer_order_totals(customer_id);

Automatic Refresh

You can set up automatic refresh using a function and a scheduled job. Here’s an example using pg_cron:

-- First, enable the pg_cron extension
CREATE EXTENSION pg_cron;

-- Create a refresh function
CREATE OR REPLACE FUNCTION refresh_mat_views()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_totals;
    REFRESH MATERIALIZED VIEW CONCURRENTLY product_performance;
END;
$$ LANGUAGE plpgsql;

-- Schedule the refresh to run every hour
SELECT cron.schedule('0 * * * *', 'SELECT refresh_mat_views()');

Step 4 - Query the materialized view like any regular table:

-- Get top 10 customers by total spend
SELECT * FROM customer_order_totals
ORDER BY total_amount DESC
LIMIT 10;

-- Find products with no sales
SELECT * FROM product_performance
WHERE times_ordered = 0;

Best Practices and Considerations

When working with materialized views, keep these important points in mind:

1. Refresh Strategy: Choose your refresh strategy carefully based on your use case:

   • For data that changes infrequently, manual refresh might be sufficient
   • For real-time analytics, consider using triggers or scheduled refreshes
   • Use CONCURRENT refresh when you need to maintain view accessibility

2. Storage Requirements: Remember that materialized views store data physically, so monitor their size:

   SELECT schemaname, matviewname, matviewowner, tablespace,
          pg_size_pretty(pg_total_relation_size(schemaname||'.'||matviewname)) as size
   FROM pg_matviews;
   

3. Performance Optimization: Create appropriate indexes on your materialized views:

   -- Example: Index for faster customer lookups
   CREATE INDEX idx_customer_order_totals_amount
   ON customer_order_totals(total_amount DESC);
   

4. Monitoring: Keep track of when your materialized views were last refreshed:

   SELECT schemaname, matviewname,
          pg_size_pretty(pg_relation_size(schemaname||'.'||matviewname)) as size,
          last_refresh
   FROM (
       SELECT schemaname, matviewname,
              CASE WHEN c.reltuples < 0 THEN NULL
                   ELSE c.reltuples::bigint
              END as row_count,
              pg_stat_get_last_analyze_time(c.oid) as last_refresh
       FROM pg_matviews m
       JOIN pg_class c ON m.matviewname = c.relname
   ) s;
   

Want a deeper dive? Check out the official documentation on Materialized Views.

Conclusion

Materialized views are a powerful feature in PostgreSQL that can significantly improve query performance for complex calculations and aggregations. They’re especially useful for data warehousing, business intelligence applications, and scenarios where you need to frequently access computed results from complex queries.

Remember to:

• Choose the right refresh strategy for your use case
• Monitor storage usage and performance
• Create appropriate indexes on your materialized views
• Consider the trade-off between storage space and query performance

Still have questions about PostgreSQL? We’re here to help - have a look at our guides for more.