SQL views vs materialized views

#Why it matters

The difference between an SQL view and a materialized view is the difference between a saved query and a saved result. A view runs its query every time you read it, so it is always current and costs a full query each time. A materialized view stores the result, so it reads in a millisecond but shows old numbers until someone refreshes it. Every analytics team uses both: views to give analysts one agreed definition of "clean sales", materialized views to make dashboards fast. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "when would you use a materialized view instead of a view?" is a standard interview question.

In this lesson you will build a cleaning view and a monthly revenue materialized view on real data, time them, catch the materialized view showing a £168,000 order that no longer exists, refresh it without blocking readers, and use views to control who sees what.

#The concept

#A view is a stored query

CREATE VIEW clean_sales AS
SELECT ... FROM sales WHERE ...;

SELECT * FROM clean_sales WHERE country = 'France';

The database stores only the query text. When you select from the view, it substitutes the definition into your query and plans the whole thing together, so a filter on the view is usually pushed down into the underlying table. A view costs nothing to store and is always up to date; it costs the full query every time it is read.

What views are good for:

• One definition of a business rule. "Clean sales excludes cancellations, postage and zero prices" lives in one place instead of being copied, slightly differently, into 40 dashboards.

• A stable interface. Rename or split a base table and keep the view's columns the same; reports keep working.

• Security. Grant access to a view that exposes some columns or pre-aggregated rows, and none to the base table.

#A materialized view stores the result

CREATE MATERIALIZED VIEW monthly_revenue_mv AS
SELECT ...;

REFRESH MATERIALIZED VIEW monthly_revenue_mv;               -- rebuild, blocks readers
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv;  -- readers keep reading

It is a table filled by a query. Reads are as fast as reading a small table, and you can index it. The price is staleness: in PostgreSQL it changes only when you run REFRESH, usually from a scheduler. A plain refresh locks out readers while it rebuilds; CONCURRENTLY lets them keep reading the old result, but needs a unique index on the materialized view and takes longer.

#Views vs materialized views vs tables built by a job

In warehouses the picture shifts: BigQuery and Snowflake materialized views refresh automatically but allow only limited SQL (Snowflake's allow one table and no joins), so most teams build tables with a tool like dbt instead, whose models can be views, tables or incremental tables. Data warehousing covers those options in depth.

#Rules that bite

• SELECT * is expanded when the view is created. Add a column to the table later and the view does not show it. PostgreSQL, MySQL and SQL Server all behave this way.

• Views create dependencies. PostgreSQL refuses to drop or change a column a view uses. CREATE OR REPLACE VIEW can only add columns at the end; to remove or rename one you drop and recreate the view (and everything built on it).

• Simple views are updatable. A view over one table with no aggregates, DISTINCT or GROUP BY accepts INSERT, UPDATE and DELETE. Add WITH CHECK OPTION so rows written through the view must satisfy its WHERE.

• Views run with the owner's privileges by default in PostgreSQL, which is what makes them useful for security. From PostgreSQL 15, WITH (security_invoker = true) makes a view check the reader's privileges instead.

• Stacked views hide cost. A view on a view on a view, each with joins and aggregates, looks like SELECT * FROM report but runs everything underneath on every read. When a dashboard is slow, check how deep its views go.

#Worked example: clean sales and monthly revenue

PostgreSQL 16 on the UCI Online Retail dataset (CC BY 4.0). Every output is from a real run.

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE sales AS
SELECT invoice_no, stock_code, description, quantity, unit_price, customer_id, country,
       to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp AS invoiced_at
FROM raw_retail;

CREATE TABLE
COPY 541909
SELECT 541909

#Step 1: one definition of "clean sales"

Online Retail mixes products with postage (POST), manual adjustments (M), bank charges, Amazon fees and cancellations (invoice numbers starting with C). Every analysis needs the same filter, so it goes in a view:

CREATE VIEW clean_sales AS
SELECT invoice_no,
       stock_code,
       description,
       quantity,
       unit_price,
       quantity * unit_price AS revenue,
       customer_id,
       country,
       invoiced_at
FROM sales
WHERE invoice_no NOT LIKE 'C%'          -- cancellations
  AND stock_code ~ '^[0-9]{5}'          -- real products, not POST, M, BANK CHARGES...
  AND quantity > 0
  AND unit_price > 0;

SELECT (SELECT count(*) FROM sales) AS all_lines,
       (SELECT count(*) FROM clean_sales) AS clean_lines;

CREATE VIEW
 all_lines | clean_lines 
-----------+-------------
    541909 |      527725
(1 row)

14,184 lines are filtered out. Analysts now write FROM clean_sales and get the same rules every time; when the rules change, you change one view.

#Step 2: the same monthly query as a view and as a materialized view

CREATE VIEW monthly_revenue_v AS
SELECT date_trunc('month', invoiced_at)::date AS month,
       count(DISTINCT invoice_no) AS invoices,
       round(sum(revenue), 2) AS revenue
FROM clean_sales
GROUP BY 1;

CREATE MATERIALIZED VIEW monthly_revenue_mv AS
SELECT * FROM monthly_revenue_v;

\timing on
SELECT * FROM monthly_revenue_v WHERE month >= '2011-09-01' ORDER BY month;
SELECT * FROM monthly_revenue_mv WHERE month >= '2011-09-01' ORDER BY month;
\timing off

CREATE VIEW
SELECT 13
Timing is on.
   month    | invoices |  revenue   
------------+----------+------------
 2011-09-01 |     1818 | 1030468.78
 2011-10-01 |     2005 | 1106666.53
 2011-11-01 |     2751 | 1457742.24
 2011-12-01 |      816 |  615489.98
(4 rows)
 
Time: 263.555 ms
   month    | invoices |  revenue   
------------+----------+------------
 2011-09-01 |     1818 | 1030468.78
 2011-10-01 |     2005 | 1106666.53
 2011-11-01 |     2751 | 1457742.24
 2011-12-01 |      816 |  615489.98
(4 rows)
 
Time: 0.519 ms
Timing is off.

Same answer. The view scanned half a million lines (264 ms here; your timings will differ); the materialized view read 13 stored rows (0.5 ms). A dashboard with ten such tiles and fifty viewers feels that difference.

#Step 3: the materialized view goes stale

Look at December 2011: £615,489.98 for nine days of trading. It includes invoice 581483, a single order for 80,995 "PAPER CRAFT , LITTLE BIRDIE" kits worth £168,469.60, cancelled twelve minutes later by invoice C581484. The view's rules drop the cancellation but keep the original order. A correction job now removes the pair from sales:

-- a correction job removes the 80,995-unit order and its cancellation
DELETE FROM sales WHERE invoice_no IN ('581483', 'C581484');

SELECT 1 AS n, 'view' AS source, revenue FROM monthly_revenue_v  WHERE month = '2011-12-01'
UNION ALL
SELECT 2, 'materialized view', revenue FROM monthly_revenue_mv WHERE month = '2011-12-01'
ORDER BY n;

REFRESH MATERIALIZED VIEW monthly_revenue_mv;

SELECT revenue AS mv_after_refresh FROM monthly_revenue_mv WHERE month = '2011-12-01';

DELETE 2
 n |      source       |  revenue  
---+-------------------+-----------
 1 | view              | 447020.38
 2 | materialized view | 615489.98
(2 rows)
 
REFRESH MATERIALIZED VIEW
 mv_after_refresh 
------------------
        447020.38
(1 row)

The view reflected the correction instantly. The materialized view kept showing £168,469.60 of revenue that no longer exists, until the refresh. That is the trade you make: always show "data as of" on a dashboard built on a materialized view, and refresh it right after the jobs that change its sources.

#Step 4: refresh without blocking readers

A plain REFRESH takes an exclusive lock. Here the refresh is held open for a few seconds inside a transaction, standing in for a refresh that takes minutes, while a dashboard query arrives:

BEGIN;
REFRESH MATERIALIZED VIEW monthly_revenue_mv;
SELECT pg_sleep(5);  -- stands in for a refresh that takes minutes
COMMIT;

SELECT pg_sleep(1);
SET lock_timeout = '2s';
SELECT month, revenue FROM monthly_revenue_mv WHERE month = '2011-12-01';

 pg_sleep 
----------
 
(1 row)
 
SET
psql:v6b.sql:3: ERROR:  canceling statement due to lock timeout
LINE 1: SELECT month, revenue FROM monthly_revenue_mv WHERE month = ...
                                   ^

The dashboard waited and gave up. CONCURRENTLY fixes that, but first it needs a unique index:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv;

CREATE UNIQUE INDEX ON monthly_revenue_mv (month);

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv;

psql:v5.sql:1: ERROR:  cannot refresh materialized view "public.monthly_revenue_mv" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
CREATE INDEX
REFRESH MATERIALIZED VIEW

CONCURRENTLY builds the new result on the side and applies only the differences, matching rows by that unique key. The same two sessions again, with CONCURRENTLY in session 1:

BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_mv;
SELECT pg_sleep(5);  -- stands in for a refresh that takes minutes
COMMIT;

SELECT pg_sleep(1);
SET lock_timeout = '2s';
SELECT month, revenue FROM monthly_revenue_mv WHERE month = '2011-12-01';

 pg_sleep 
----------
 
(1 row)
 
SET
   month    |  revenue  
------------+-----------
 2011-12-01 | 447020.38
(1 row)

The dashboard got its answer at once. Two concurrent refreshes of the same view still queue behind each other, so schedule one at a time.

#Step 5: a view as a security boundary

A partner team may see revenue by country and month, never customer ids:

-- a partner team may see revenue by country, never customer ids
CREATE VIEW country_revenue AS
SELECT country,
       date_trunc('month', invoiced_at)::date AS month,
       round(sum(revenue), 2) AS revenue
FROM clean_sales
GROUP BY 1, 2;

CREATE ROLE partner_reader LOGIN;
GRANT SELECT ON country_revenue TO partner_reader;

SET ROLE partner_reader;
SELECT * FROM country_revenue WHERE country = 'Japan' ORDER BY month LIMIT 3;
SELECT customer_id FROM sales LIMIT 1;
SELECT customer_id FROM clean_sales LIMIT 1;
RESET ROLE;

CREATE VIEW
CREATE ROLE
GRANT
SET
 country |   month    | revenue 
---------+------------+---------
 Japan   | 2010-12-01 | 7705.07
 Japan   | 2011-02-01 | 5780.81
 Japan   | 2011-03-01 |   48.90
(3 rows)
 
psql:v7.sql:14: ERROR:  permission denied for table sales
psql:v7.sql:15: ERROR:  permission denied for view clean_sales
RESET

The partner reads the aggregate even though it has no rights on sales or clean_sales, because the view runs with its owner's privileges. That is the feature, and also the risk: anyone who can create views as a powerful owner can expose data through them. For per-row rules (each partner sees only its own country), use row-level security policies rather than one view per partner.

#Step 6: SELECT * freezes, and dependencies protect you

CREATE VIEW sales_star AS SELECT * FROM sales;

ALTER TABLE sales ADD COLUMN channel text DEFAULT 'web';

SELECT count(*) AS columns_in_view
FROM information_schema.columns WHERE table_name = 'sales_star';
SELECT count(*) AS columns_in_table
FROM information_schema.columns WHERE table_name = 'sales';

ALTER TABLE sales DROP COLUMN description;

CREATE VIEW
ALTER TABLE
 columns_in_view 
-----------------
               8
(1 row)
 
 columns_in_table 
------------------
                9
(1 row)
 
psql:v8.sql:10: ERROR:  cannot drop column description of table sales because other objects depend on it
DETAIL:  view clean_sales depends on column description of table sales
view monthly_revenue_v depends on view clean_sales
materialized view monthly_revenue_mv depends on view monthly_revenue_v
view country_revenue depends on view clean_sales
view sales_star depends on column description of table sales
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

sales_star still has 8 columns: the * became a fixed list when the view was created, so the new channel column is invisible through it. List columns explicitly in views. The failed DROP COLUMN is PostgreSQL protecting five objects, and the error message is a free dependency map: it shows the whole stack, including a materialized view built on a view built on a view. Do not reach for CASCADE; it drops all five.

#Step 7: an updatable view with CHECK OPTION

CREATE VIEW uk_sales AS
SELECT invoice_no, stock_code, quantity, unit_price, country, invoiced_at
FROM sales
WHERE country = 'United Kingdom'
WITH CHECK OPTION;

BEGIN;
INSERT INTO uk_sales VALUES ('999999', '85123A', 1, 2.55, 'United Kingdom', '2011-12-09 13:00');
INSERT INTO uk_sales VALUES ('999998', '85123A', 1, 2.55, 'France', '2011-12-09 13:00');
ROLLBACK;

CREATE VIEW
BEGIN
INSERT 0 1
psql:v9.sql:9: ERROR:  new row violates check option for view "uk_sales"
DETAIL:  Failing row contains (999998, 85123A, null, 1, 2.550, null, France, 2011-12-09 13:00:00, web).
ROLLBACK

The view is simple (one table, no aggregates), so PostgreSQL writes through it to sales; columns the view does not have get NULL or their default (web for channel). Without WITH CHECK OPTION the France row would be inserted and then be invisible through the view that inserted it. The check option makes the view's filter a rule for writes too.

#Common mistakes

• Using a materialized view and forgetting to schedule the refresh. It silently shows last week's numbers. Refresh it from the same scheduler that loads its sources, right after them, and show "data as of" on the dashboard.

• Plain REFRESH on a materialized view people are reading. Readers wait for the whole rebuild. Add a unique index and use CONCURRENTLY.

• SELECT * in a view. New columns never appear, and the view breaks the day a column it silently depends on changes. Name the columns.

• Stacking views five deep. Every read runs every layer. Flatten the stack, or materialize the expensive layer.

• DROP ... CASCADE to get past a dependency error. It deletes every dependent view, including ones other teams use. Read the dependency list and recreate deliberately.

• Treating views as a performance tool. A plain view saves typing, not work. If it is slow as a query, it is slow as a view.

• Granting the base table "just for this view". That defeats the security boundary. Grant the view only.

• Writing through a filtered view without WITH CHECK OPTION. Rows disappear from the view that wrote them.

#Interview angle

#Practice task

Build a delivery performance layer on Olist (CC BY-NC-SA 4.0) for a logistics partner.

1. Load olist_orders_dataset.csv into orders and olist_customers_dataset.csv into customers.

2. Create a view delivered_orders: one row per delivered order with the customer's state, purchase month, days to deliver, and whether it arrived after the estimated date.

3. Create a materialized view state_delivery_monthly with orders and the late percentage per state and month, and make it refreshable without blocking readers.

4. Late delivery confirmations arrive: set every RJ order bought in March 2018 that is still shipped to delivered, ten days after its estimated date. Compare the view and the materialized view for RJ in March 2018, then refresh.

5. Create a role for the partner that can read the materialized view but not the order-level view.

CREATE TABLE orders (
  order_id text PRIMARY KEY,
  customer_id text,
  order_status text,
  order_purchase_timestamp timestamp,
  order_approved_at timestamp,
  order_delivered_carrier_date timestamp,
  order_delivered_customer_date timestamp,
  order_estimated_delivery_date timestamp
);
\copy orders FROM 'olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE customers (
  customer_id text PRIMARY KEY,
  customer_unique_id text,
  customer_zip_code_prefix text,
  customer_city text,
  customer_state text
);
\copy customers FROM 'olist_customers_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE
COPY 99441
CREATE TABLE
COPY 99441

CREATE VIEW delivered_orders AS
SELECT o.order_id,
       c.customer_state AS state,
       date_trunc('month', o.order_purchase_timestamp)::date AS month,
       o.order_delivered_customer_date::date - o.order_purchase_timestamp::date AS days_to_deliver,
       o.order_delivered_customer_date::date > o.order_estimated_delivery_date::date AS is_late
FROM orders AS o
JOIN customers AS c USING (customer_id)
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL;

CREATE MATERIALIZED VIEW state_delivery_monthly AS
SELECT state, month,
       count(*) AS orders,
       round(100.0 * avg(is_late::int), 1) AS late_pct
FROM delivered_orders
GROUP BY state, month;

CREATE UNIQUE INDEX ON state_delivery_monthly (state, month);

SELECT * FROM state_delivery_monthly
WHERE month = '2018-03-01' AND state IN ('SP', 'RJ', 'MG')
ORDER BY state;

CREATE VIEW
SELECT 556
CREATE INDEX
 state |   month    | orders | late_pct 
-------+------------+--------+----------
 MG    | 2018-03-01 |    856 |     20.4
 RJ    | 2018-03-01 |    864 |     34.5
 SP    | 2018-03-01 |   2971 |      9.4
(3 rows)

-- late delivery confirmations arrive for RJ orders bought in March 2018
UPDATE orders AS o
SET order_status = 'delivered',
    order_delivered_customer_date = o.order_estimated_delivery_date + interval '10 days'
FROM customers AS c
WHERE c.customer_id = o.customer_id
  AND c.customer_state = 'RJ'
  AND o.order_status = 'shipped'
  AND o.order_purchase_timestamp >= '2018-03-01'
  AND o.order_purchase_timestamp <  '2018-04-01';

SELECT 1 AS n, 'view' AS source, count(*) AS orders, round(100.0 * avg(is_late::int), 1) AS late_pct
FROM delivered_orders WHERE state = 'RJ' AND month = '2018-03-01'
UNION ALL
SELECT 2, 'materialized view', orders, late_pct
FROM state_delivery_monthly WHERE state = 'RJ' AND month = '2018-03-01'
ORDER BY n;

REFRESH MATERIALIZED VIEW CONCURRENTLY state_delivery_monthly;

SELECT orders, late_pct FROM state_delivery_monthly
WHERE state = 'RJ' AND month = '2018-03-01';

UPDATE 41
 n |      source       | orders | late_pct 
---+-------------------+--------+----------
 1 | view              |    905 |     37.5
 2 | materialized view |    864 |     34.5
(2 rows)
 
REFRESH MATERIALIZED VIEW
 orders | late_pct 
--------+----------
    905 |     37.5
(1 row)

CREATE ROLE logistics_partner LOGIN;
GRANT SELECT ON state_delivery_monthly TO logistics_partner;

SET ROLE logistics_partner;
SELECT count(*) AS rows_visible FROM state_delivery_monthly;
SELECT count(*) FROM delivered_orders;
RESET ROLE;

CREATE ROLE
GRANT
SET
 rows_visible 
--------------
          556
(1 row)
 
psql:vp3.sql:6: ERROR:  permission denied for view delivered_orders
RESET

#Further reading

• PostgreSQL: CREATE VIEW (updatable views, CHECK OPTION, security_invoker) and CREATE MATERIALIZED VIEW.

• PostgreSQL: REFRESH MATERIALIZED VIEW and row security policies.

• BigQuery materialized views and Snowflake materialized views.

• Earlier lessons: SQL CTE (WITH clause): write readable queries for structuring the queries inside views, and SQL UPSERT and MERGE: insert or update rows for the job-built tables that replace materialized views at scale.

• UCI Online Retail dataset (CC BY 4.0), CSV copy: online-retail-dataset.csv.

• Olist datasets (CC BY-NC-SA 4.0).