Using PostgreSQL Materialized Views to Speed Up Repeated Aggregations

The problem: costly repeated scans on a fact table

Imagine a dashboard that shows daily sales totals. The underlying sales fact table holds tens of millions of rows, and the query that powers the widget is:

SELECT sale_date, SUM(amount) AS total_sales
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Running this query every few minutes forces PostgreSQL to scan the entire table, consuming CPU and I/O and adding latency for users.

Takeaway: materialized views turn the scan into a cheap read

A materialized view stores the result of that aggregation as a physical table. Querying the view reads only the pre‑computed rows, which can be served with an index‑only scan. Refreshing the view updates the stored data, and you can choose how often to refresh based on your freshness requirements.

When to consider a materialized view

• The base query is executed frequently with little or no change in its WHERE clause.
• The underlying data changes relatively slowly compared to the query frequency.
• You can tolerate a brief period of staleness between refreshes.

Creating the view

You need CREATE privilege on the schema and SELECT on the source table. Run this as a role that has those rights:

CREATE MATERIALIZED VIEW sales_daily AS
SELECT sale_date,
       SUM(amount) AS total_sales
FROM sales
GROUP BY sale_date;

-- Enable fast look‑ups by date
CREATE UNIQUE INDEX sales_daily_sale_date_idx ON sales_daily (sale_date);

The unique index is required if you later want to refresh concurrently.

Querying the view

Now the dashboard can query the view instead of the base table:

SELECT * FROM sales_daily WHERE sale_date = CURRENT_DATE;

With the index in place, PostgreSQL will typically show an Index Only Scan on sales_daily_sale_date_idx in EXPLAIN ANALYZE output, meaning it reads only the index pages.

Refresh strategies

Two refresh modes are available:

1. Blocking refresh – simple and fast for small views:

REFRESH MATERIALIZED VIEW sales_daily;

While refreshing, the view is locked; concurrent SELECT statements wait until the refresh finishes.

1. Concurrent refresh – allows reads to continue (requires PostgreSQL 9.4+ and a unique index):

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;

This builds a new version of the view in parallel and swaps it in, but it does extra work (roughly 20‑30 % more CPU) and can fail if the unique index is missing or if there are duplicate keys.

Verifying the benefit

Follow these steps to confirm the improvement in your environment:

1. Baseline – run the original aggregation with EXPLAIN ANALYZE and note the execution time and rows scanned.
2. Create the view as shown above.
3. Query the view with the same EXPLAIN ANALYZE; you should see a dramatically lower execution time and an index‑only scan.
4. Measure refresh cost – time a REFRESH MATERIALIZED VIEW (or CONCURRENTLY) and ensure it fits inside your maintenance window.
5. Check freshness – after a refresh, query pg_stat_user_tables for the view’s last_vacuum and last_analyze timestamps, or compare MAX(sale_date) in the view to the base table to see lag.

Trade‑offs and limitations

• Storage overhead – the view duplicates the aggregated data; size is roughly the number of distinct groups times the width of the aggregated columns.
• Staleness risk – if source data changes every few seconds, frequent refreshes may erase the performance gain.
• Concurrent refresh overhead – requires a unique index and extra CPU; monitor pg_stat_progress_create_index if you suspect long‑running refreshes.
• Version note – REFRESH ... CONCURRENTLY is available from PostgreSQL 9.4; older versions only support the blocking form.

Actionable closing

Start by capturing the current cost of your repetitive aggregation with EXPLAIN ANALYZE. If the query scans more than a few hundred thousand rows each run, create a materialized view as shown, add a unique index on the grouping columns, and test both blocking and concurrent refreshes. Choose the refresh interval that keeps the view’s lag within your service‑level agreement while keeping refresh CPU usage below a threshold you can afford (e.g., < 5 % of total CPU during off‑peak hours). Periodically re‑run the baseline check to ensure the view still provides a net benefit as data volumes evolve.