Types, Constraints & Database Objects
Views and Materialized Views
A view stores a query interface evaluated against current base data, while a materialized view stores a result snapshot that must be refreshed; both can provide abstraction, but security, freshness, updateability, and refresh concurrency are separate contracts.
- A regular view stores a query definition.
- A materialized view stores rows from a query.
- Refresh semantics are vendor-specific.
- A view is not automatically a security boundary.
- Updateability depends on mapping writes unambiguously.
- Schema abstraction has limits.
CREATE VIEW customer_public AS
SELECT customer_id, name
FROM customers;
CREATE MATERIALIZED VIEW customer_lifetime_value AS
SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS paid_total
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'PAID' GROUP BY o.customer_id;
CREATE UNIQUE INDEX uq_customer_ltv
ON customer_lifetime_value(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;| Property | View | Materialized view |
|---|---|---|
| Storage | Definition; result not inherently stored | Result snapshot plus indexes |
| Freshness | Current under query visibility | As of last successful refresh/maintenance |
| Read cost | Underlying query cost | Snapshot scan/index cost |
| Write cost | Normally base-table cost | Refresh or incremental maintenance |
| Security | Possible interface, engine-specific execution rules | Separate stored object still requiring grants |
| Failure mode | Slow or invalid dependency | Stale, blocked, or failed refresh |
A refresh is a data pipeline: define its timestamp, acceptable staleness, transaction snapshot, failure alert, and reader behavior. PostgreSQL CONCURRENTLY reduces reader blocking but does not mean “free,” instantaneous, or incremental.