Views and materialized views

In plain English

A view is a saved query that you can use like a table; it does not store data on its own, it just runs the query each time you select from it. You use a view when you want a simpler, reusable way to read data without writing the same joins or filters over and over. A materialized view is different because it actually stores the query result as a snapshot, which is faster to read but can go out of date until you refresh it. Both are effectively read-only, so they are for looking at data rather than editing it.

Step by step

1. 1Write the query you find yourself repeating, then wrap it with CREATE VIEW name AS.
2. 2Use the view name in future queries exactly like a table.
3. 3If reads are slow and freshness is less critical, create a materialized view to store the result.
4. 4Run REFRESH MATERIALIZED VIEW to rebuild the stored result after the data changes.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0
);

CREATE VIEW low_stock AS
SELECT id, name, stock
FROM products
WHERE stock < 5;

CREATE MATERIALIZED VIEW product_count_by_name AS
SELECT name, COUNT(*) AS copies
FROM products
GROUP BY name;

REFRESH MATERIALIZED VIEW product_count_by_name;

Common mistakes

• Expecting a materialized view to update automatically, when it must be refreshed explicitly.
• Trying to insert or update rows through a plain view as if it were a real table.
• Creating a materialized view for data that must always be perfectly current.
• Duplicating a complex query in many places instead of saving it once as a view.