Detailed explanation
A view is a named database object defined by a SELECT query that applications can query like a table. An ordinary view stores the query definition, not a separate copy of its result. For example, CREATE VIEW active_orders AS SELECT * FROM orders WHERE status = 'active'; lets callers use SELECT * FROM active_orders instead of repeating the filter. Views simplify reusable queries, provide a stable interface, and can restrict exposed columns or rows when permissions are configured correctly.
When you query an ordinary view, the database generally incorporates its definition into the surrounding query and optimizes access to the underlying tables. Depending on the query, the optimizer may push filters down and use indexes on those tables. A view therefore does not automatically make an expensive query faster. It reflects the base data visible to the querying statement under the transaction's isolation rules, rather than necessarily showing every concurrent change. Some simple views support INSERT, UPDATE, and DELETE, but joins, aggregation, and database-specific rules can limit updatability.
A materialized view stores the query's result physically, trading storage and maintenance work for potentially faster reads. In PostgreSQL, CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', order_date) AS m, SUM(total) AS sales FROM orders GROUP BY 1; computes and stores monthly totals. Later reads can use these stored rows instead of repeatedly scanning and aggregating orders. However, changes to orders do not automatically update this PostgreSQL materialized view; REFRESH MATERIALIZED VIEW monthly_sales; recomputes its contents. Supported refresh approaches differ across databases: some offer incremental maintenance or refresh on commit, while others require explicit or scheduled refreshes.
In an interview, explain the choice through freshness, query cost, and operational overhead. Use an ordinary view when you need query reuse and transaction-consistent access to base data without maintaining a separate result. Consider a materialized view for expensive reporting queries when some staleness is acceptable and refresh costs are manageable. Discuss refresh frequency, concurrency, storage, and indexes rather than claiming materialization always improves performance. Also identify the SQL dialect: DATE_TRUNC and this materialized-view syntax are PostgreSQL examples, not universally portable SQL.
Key points to remember
- An ordinary view stores a SELECT definition; a materialized view physically stores its result.
- Ordinary views expose base data according to the querying transaction's visibility rules.
- Materialized views can accelerate expensive reads but consume storage and require maintenance.
- Refresh capabilities and automatic maintenance differ by database system.
- Choose between them based on freshness requirements, read cost, refresh cost, and concurrency.
Common mistakes candidates make
- Claiming an ordinary view caches results or automatically improves query performance.
- Saying a view always shows the latest committed data without considering transaction isolation.
- Assuming all databases automatically refresh materialized views or support identical refresh syntax.
Likely follow-up questions
Can we create indexes on views?
In PostgreSQL, ordinary views cannot have their own indexes; queries can use indexes on the underlying tables. Materialized views can have indexes because they store rows, while other databases may offer features such as indexed views.
How can a PostgreSQL materialized view be refreshed without blocking readers?
Use REFRESH MATERIALIZED VIEW CONCURRENTLY to allow reads during refresh. The materialized view must already be populated and have a qualifying UNIQUE index using only column names and covering all rows; concurrent refresh can take more resources than a regular refresh.
Can we update data through a view?
Some simple ordinary views are automatically updatable, with changes applied to the underlying table; the exact rules depend on the database. Aggregated views are generally not automatically updatable, and PostgreSQL materialized views are refreshed rather than directly modified with ordinary DML.
Interview tip: Start with the definition-versus-stored-result distinction, then use monthly sales reporting to explain the performance–freshness trade-off.