Database View
A virtual table defined by a SQL query. Simplifies complex joins, enforces access control, and presents data in a specific shape without duplicating storage.
What is Database View?
In short
A database view is a named, virtual table defined by a stored SQL query. It holds no data of its own; every time you read from it, the database runs the underlying query and returns the result, which lets you reshape, join, or restrict tables without copying any data.
What a view actually is
When you write CREATE VIEW active_customers AS SELECT id, name, email FROM customers WHERE status = 'active', you are not creating a new table. You are saving a query under a name. From then on, SELECT * FROM active_customers behaves like reading a table, but the rows are computed on the fly from the customers table each time.
Because a view stores only the query text, it takes up almost no space and never goes stale. If a row in customers changes, the next read from active_customers reflects that change immediately. There is no second copy of the data to keep in sync.
Views can be built on top of other views, joins across many tables, aggregations, or filtered subsets. The application sees a clean, stable shape, while the real tables underneath are free to be normalized, renamed, or restructured.
How the database runs a view
A plain view is expanded at query time. When you run SELECT name FROM active_customers WHERE email LIKE '%@gmail.com', the planner substitutes the view's definition into your query and optimizes the whole thing as one statement. So the WHERE on status and your filter on email are merged into a single execution plan. There is no separate step that materializes the view first.
This means a view is usually exactly as fast as writing the equivalent query by hand. The cost lives in the underlying tables and indexes, not in the view itself. A view over an unindexed 50 million row table will be slow for the same reason the raw query would be.
Some views are updatable: if a view maps cleanly to a single table with no aggregation or DISTINCT, an INSERT or UPDATE against the view can pass through to the base table. Once you add joins, GROUP BY, or computed columns, the view becomes read only, because the database can no longer tell which base row your write should touch. Postgres lets you restore write behavior with an INSTEAD OF trigger or a rule.
When to use one, and the trade-offs
Reach for a view when several queries repeat the same join or filter. Defining it once gives every caller the same logic, so a fix to the business rule happens in one place instead of scattered across the codebase. Views are also a clean access control boundary: grant a reporting user SELECT on a view that exposes only non sensitive columns, and revoke their access to the raw table that holds salaries or password hashes.
The main trade-off is performance on heavy queries. A plain view re-runs its full query on every read, so a view that joins six tables and aggregates millions of rows will repeat that work each call. When the result is expensive and does not need to be fresh to the second, a materialized view is the answer: it stores the computed result physically and you refresh it on a schedule, trading freshness for speed.
Other caveats: deeply nested views (a view on a view on a view) can produce query plans that are hard to read and tune, and an updatable view with surprising write rules can confuse new developers. Keep view definitions shallow and document which ones are read only.
A concrete example
Say an e-commerce app has orders, order_items, and products tables. The finance team constantly needs revenue per product. You create a view: CREATE VIEW product_revenue AS SELECT p.id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.id, p.name.
Now every dashboard, report, and analyst query reads from product_revenue instead of re-writing that three-table join. If finance later decides discounts should be subtracted, you edit one view definition and every consumer gets the corrected number.
If that view turns out to scan tens of millions of order_items on every dashboard load, you convert it to a materialized view and refresh it every 15 minutes. The dashboards now read a precomputed table in milliseconds, and the freshness lag of a few minutes is acceptable for revenue reporting.
Where it is used in production
PostgreSQL
Supports both plain views and materialized views, with INSTEAD OF triggers to make complex views writable and REFRESH MATERIALIZED VIEW CONCURRENTLY for non-blocking refreshes.
Snowflake
Heavily uses views and secure views to expose curated, governed datasets to analysts while hiding the underlying raw tables and sensitive columns.
Amazon Redshift
Data teams build materialized views over large fact tables so BI dashboards read precomputed aggregates instead of rescanning billions of rows.
MySQL
Offers updatable views for single-table mappings and the WITH CHECK OPTION clause to stop writes through a view from violating its own filter.
Frequently asked questions
- What is the difference between a view and a table?
- A table physically stores rows on disk. A view stores only a saved SQL query and computes its rows on demand every time you read it. A view takes almost no space and is always consistent with the base tables, but it does no caching of its own unless it is a materialized view.
- Does a view make queries faster?
- Not by itself. A plain view is expanded into your query and optimized together, so it runs about as fast as the equivalent hand-written query. To gain speed on expensive aggregations, use a materialized view, which stores the result physically and is refreshed on a schedule.
- Can you insert or update through a view?
- Sometimes. If the view maps cleanly to one base table with no joins, aggregation, or DISTINCT, the database can pass writes through. Once the view has joins, GROUP BY, or computed columns it is read only by default, though Postgres lets you add an INSTEAD OF trigger to handle writes manually.
- What is a materialized view?
- A materialized view stores the query result physically, like a cached table, rather than recomputing it on every read. It is much faster to query but the data can be stale until you run a refresh, so it suits reporting and analytics where a few minutes of lag is acceptable.
- How does a view help with security?
- You can grant a user read access to a view that exposes only safe columns and rows, then revoke their access to the underlying table. The user queries the view normally but can never see the hidden columns, such as salaries or password hashes, that the base table contains.
Learn Database View hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
Related lessons
Lessons that touch on Database View as part of a larger topic.
See also
Related glossary terms you might want to look up next.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Normalization
Organizing database tables to reduce redundancy by splitting data into related tables connected by foreign keys. Follows normal forms (1NF, 2NF, 3NF).
Materialized View
A precomputed query result stored as a physical table and refreshed periodically. Trades storage for read performance on expensive aggregations.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).