Views and materialized views let you give a name to a query result, but they solve different problems. A regular view stores the query definition and runs it when read. A materialized view stores the query result as rows and must be refreshed when the underlying data changes. In server-side PostgreSQL design, this distinction matters because it affects freshness, permissions, locking, storage, indexing, and the shape of application queries.
The outcome of this lesson is practical: you should be able to create a view for a reusable logical interface, create a materialized view for an expensive derived dataset, choose a refresh method, and diagnose the common cases where the result is stale, slow, unavailable, or unexpectedly writable.
How PostgreSQL Represents Views
A normal view is a relation in the catalog with a stored rewrite rule. When you query the view, PostgreSQL expands the view definition into the outer query and plans the combined statement. The view usually does not create a separate execution boundary. Predicates from the outer query may be pushed down into the underlying tables, joins may be reordered, and indexes on the base tables may still be used.
That means a view is best understood as a named query interface. It can hide columns, centralize join logic, present stable names to application code, and expose a limited subset of rows. It does not automatically make the query faster. If the expanded query is expensive, the view will still be expensive unless the planner can simplify it or use appropriate indexes.
Some views are automatically updatable. A simple view over one table can accept inserts, updates, and deletes if PostgreSQL can map the change back to one base row. Joins, grouping, distinct, set operations, window functions, and many expressions make a view non-updatable unless you add rules or triggers. The WITH CHECK OPTION clause is important for writable filtered views because it prevents writes through the view from creating rows that disappear from that same view.
How Materialized Views Differ
A materialized view also stores a query definition, but it additionally stores a physical snapshot of the query output. Reads access the stored rows, not the base tables. Because the rows are stored, you can create indexes on the materialized view itself. This is useful for aggregates, denormalized lookup data, reporting screens, and search-style projections where recomputing every request would be too costly.
The trade-off is freshness. PostgreSQL does not automatically maintain a materialized view when source tables change. You refresh it explicitly with REFRESH MATERIALIZED VIEW. A normal refresh takes a stronger lock that blocks reads while PostgreSQL replaces the contents. A concurrent refresh keeps the old contents readable while it builds the new result, but it requires at least one unique index that covers all rows and uses only column names. Concurrent refresh also cannot be used the first time if the materialized view was created with no data.
Syntax Anatomy
The basic view syntax is CREATE VIEW name AS query. Add CREATE OR REPLACE VIEW when changing a compatible definition, and add WITH LOCAL CHECK OPTION or WITH CASCADED CHECK OPTION for writable filtered views. Security-sensitive views may use options such as security_barrier to restrict unsafe predicate movement around the view boundary.
The materialized view syntax is CREATE MATERIALIZED VIEW name AS query. You can add WITH NO DATA to register the object without populating it. Refresh uses REFRESH MATERIALIZED VIEW name or REFRESH MATERIALIZED VIEW CONCURRENTLY name. Indexes are created with normal CREATE INDEX statements, but indexes belong to the materialized view storage, not to the source tables.
Example 1: A View as a Query Interface
The first example builds a small order model and exposes current open order totals. The view keeps application queries from repeating the join and aggregation logic.
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
email text NOT NULL UNIQUE
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(customer_id),
status text NOT NULL CHECK (status IN ('open', 'paid', 'cancelled'))
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(order_id),
sku text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, sku)
);
INSERT INTO customers VALUES
(1, 'ada@example.com'),
(2, 'lin@example.com');
INSERT INTO orders VALUES
(10, 1, 'open'),
(11, 1, 'paid'),
(12, 2, 'open');
INSERT INTO order_items VALUES
(10, 'book', 2, 15.00),
(10, 'pen', 3, 2.00),
(11, 'desk', 1, 80.00),
(12, 'book', 1, 15.00);The source data has two open orders. Order 10 totals 36.00 and order 12 totals 15.00. The paid order exists in the base table but should not appear in this view.
CREATE VIEW open_order_totals AS
SELECT
o.order_id,
c.email,
sum(i.quantity * i.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c USING (customer_id)
JOIN order_items AS i USING (order_id)
WHERE o.status = 'open'
GROUP BY o.order_id, c.email;
SELECT order_id, email, order_total
FROM open_order_totals
ORDER BY order_id;The deterministic result is two rows: order 10 for ada@example.com with 36.00, and order 12 for lin@example.com with 15.00. If a new item is inserted into an open order, the next view query sees it because the view is recomputed from the base tables.
Example 2: A Writable Filtered View
A simple filtered view can be writable, but without a check option it can accept changes that immediately make the row disappear from the view. This is surprising in administrative screens where the view is meant to represent the allowed working set.
CREATE VIEW editable_open_orders AS
SELECT order_id, customer_id, status
FROM orders
WHERE status = 'open'
WITH LOCAL CHECK OPTION;
UPDATE editable_open_orders
SET status = 'cancelled'
WHERE order_id = 12;The update is rejected because the changed row would no longer satisfy status = 'open'. The expected behavior is an error similar to a check option violation. The correction is not to remove the check option casually; instead, decide whether cancellation should happen through the base table, a stored procedure, or a separate view that intentionally exposes that transition.
Example 3: A Materialized View for Aggregates
Suppose a dashboard repeatedly needs revenue by customer. Running the full join and aggregation on every page load may be wasteful. A materialized view stores the aggregate result and lets you index it for the dashboard access pattern.
CREATE MATERIALIZED VIEW customer_revenue AS
SELECT
c.customer_id,
c.email,
sum(i.quantity * i.unit_price) AS lifetime_revenue
FROM customers AS c
JOIN orders AS o USING (customer_id)
JOIN order_items AS i USING (order_id)
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.email;
CREATE UNIQUE INDEX customer_revenue_customer_id_idx
ON customer_revenue (customer_id);
SELECT customer_id, email, lifetime_revenue
FROM customer_revenue
ORDER BY customer_id;The expected result is one row for customer 1 with 80.00, because only order 11 is paid. If order 10 changes from open to paid, this materialized view will still show 80.00 until it is refreshed. That stale result is not a bug in PostgreSQL; it is the defining trade-off of materialization.
Example 4: Refresh Strategy
A refresh strategy decides when users can tolerate stale data, when refresh work should run, and whether reads may be blocked. A small back-office report may use a normal refresh during a quiet period. A frequently read dashboard usually prefers concurrent refresh once the required unique index exists.
UPDATE orders
SET status = 'paid'
WHERE order_id = 10;
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_revenue;
SELECT customer_id, lifetime_revenue
FROM customer_revenue
ORDER BY customer_id;After the refresh, customer 1 has 116.00: the original paid order total of 80.00 plus the newly paid order total of 36.00. Reads can continue during the concurrent refresh, but they may see the old snapshot until the refresh completes.
Design Choices and Trade-Offs
Choose a regular view when you need a stable logical interface, central query reuse, column hiding, or a permissions boundary over live data. Choose a materialized view when repeated reads are expensive and slightly stale results are acceptable. If data must be current at statement time, materialization is the wrong default unless you can refresh synchronously inside the same workflow and accept the cost.
Nested views are convenient but can become hard to reason about. PostgreSQL expands them, so a simple outer query may become a large inner query with many joins. Use EXPLAIN on the final application query, not only on the individual view definition. For materialized views, design indexes from the read queries: a unique index may satisfy concurrent refresh, while additional indexes may be needed for filtering or sorting.
Security is subtle. Granting SELECT on a view can avoid granting direct access to base tables, but the view definition, owner, and function volatility still matter. Avoid exposing sensitive columns in helper views that many roles can read. For row-level security designs, test as the actual application role because ownership and security definer functions can change what rows are visible.
Failure Modes and Troubleshooting
Stale dashboard numbers: users report that a paid order is missing from a revenue tile. The likely cause is that the materialized view has not been refreshed since the order changed. Check the source table, query the materialized view, and inspect the scheduler or job log that should run the refresh. Correct it by running the refresh and then fixing the schedule, trigger process, or operational runbook that allowed the refresh to be skipped.
Concurrent refresh fails: the command reports that a unique index is required. The cause is usually that the materialized view has no qualifying unique index, or the index uses an expression or partial predicate. Inspect indexes with \d+ materialized_view_name in psql. Correct it by creating a unique index on plain columns that uniquely identify every row in the materialized view.
Reads block during refresh: application requests hang or time out while a normal refresh runs. Confirm with pg_stat_activity and lock inspection. The cause is the refresh lock mode. Correct it by adding the required unique index and using concurrent refresh, moving refresh to a quiet window, reducing the materialized view query cost, or accepting a separate reporting replica.
A view is slow: the view itself is not cached. Run EXPLAIN (ANALYZE, BUFFERS) on the application query that reads the view. Look for sequential scans, poor join order caused by missing statistics, or filters that cannot use indexes. Correct the base table indexes, rewrite the view to expose filterable columns, or replace the view with a materialized view only if stale data is acceptable.
Hands-On Lab
Prerequisites: a PostgreSQL database where you can create tables, views, materialized views, and indexes. Use a scratch schema or disposable database because the cleanup step drops the objects.
- Create the three base tables and seed data from Example 1.
- Create
open_order_totalsand verify that it returns two rows with totals36.00and15.00. - Create
editable_open_ordersand attempt to update order 12 tocancelled. Verification is the expected check option error. - Create
customer_revenueand its unique index. Verify that it initially returns customer 1 with80.00. - Update order 10 to
paid. Querycustomer_revenuebefore refreshing and confirm that it is stale. - Run
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_revenue. Verify that customer 1 now shows116.00. - Cleanup by dropping the materialized view, views, and tables in dependency order.
DROP MATERIALIZED VIEW IF EXISTS customer_revenue;
DROP VIEW IF EXISTS editable_open_orders;
DROP VIEW IF EXISTS open_order_totals;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;Assessment Exercises
- A product page must always show current inventory, but the category analytics page can lag by ten minutes. Which object type would you use for each query, and why?
- A materialized view refreshed concurrently still makes the database CPU spike. Name two design changes that reduce refresh cost without changing application semantics.
- A role can read a view but not its base table. What must you test before treating the view as a security boundary?
- A developer adds a filter to a query that reads a nested view, but performance does not improve. What should they inspect, and what changes might make the filter usable?
- Why does
WITH CHECK OPTIONmatter on a writable filtered view, and when might you intentionally avoid making such a view writable?
Summary
A PostgreSQL view is a stored query interface over live data. A materialized view is stored query output that trades freshness for faster repeated reads and independent indexing. Refresh strategy is part of the design, not an afterthought: decide how stale the result may be, whether reads can block, what unique index supports concurrent refresh, and how failures are detected. Used carefully, these objects let server-side PostgreSQL designs present stable interfaces and efficient derived data without moving all query logic into application code.
