Views
Named queries. Virtual by default; materialized when you cache the result.
Level: beginner
What is it?
A view is a named query that presents a virtual relation to callers. An ordinary view stores the query definition rather than a separate copy of its results; a materialized view stores results and must be refreshed or maintained.
Why does it matter?
Views can simplify repeated query logic, provide a stable logical interface, and restrict which columns/rows a client sees. They do not automatically make an expensive query fast or enforce every form of security.
Mental model
A regular view behaves like a reusable query expression. A materialized view behaves more like a cached result, trading storage and freshness/refresh complexity for faster reads.
How it works
- A regular view generally reflects current underlying data according to the transaction's visibility rules when queried.
- A materialized view may be stale until refreshed or incrementally maintained.
- Views can provide abstraction when underlying table structure changes, subject to compatibility and dependency constraints.
- Security depends on the DBMS's view/definer/invoker and privilege semantics; a view alone is not universally a complete authorization boundary.
- Complex views may be expanded into larger queries and optimized along with the caller's statement.
Steps
- Define a view around a useful projection/filter/join over underlying tables.
- Grant the relevant caller access to the view and choose underlying privilege semantics carefully.
- Inspect the query plan if the view is slow; its logic may expand into joins, filters, or sorts.
- For materialized results, define refresh timing and acceptable staleness.
Real-world usage
- Providing a stable schema for reports and application queries.
- Exposing a limited column set for a specific consumer.
- Caching expensive aggregates through materialized views.
Common mistakes
- Assuming an ordinary view stores data or improves performance automatically.
- Ignoring materialized-view staleness and refresh failures.
- Treating the existence of a view as proof that sensitive underlying data are inaccessible.
Interview questions
How does a materialized view differ from a regular view?
A regular view stores a query definition and computes results when used; a materialized view stores query results and needs refresh/maintenance.
Can views help with security?
They can expose only selected data with appropriate privilege configuration, but the exact security boundary depends on database semantics and grants.