We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
A virtual table defined by a SQL query. Simplifies complex joins, enforces access control, and presents data in a specific shape without duplicating storage.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Database View across a run of lessons, not one page. These 4 alone are about 143 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
A view and the query it is defined as produced identical plans, character for character. A materialised view answered in 0.017 ms what took 94.929 ms to compute, and is a cache with every problem that implies.
A plain REFRESH made a 7 ms read take 1,231 ms, because it holds an exclusive lock for its whole duration. The concurrent form cost 3 percent more and blocked nobody, and refuses to run without a unique index.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
How SQL databases scale across multiple nodes, sharding, query routing, distributed joins, and the coordination challenges.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Viewspay once,
yours for life
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.
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.
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.
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 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.
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.
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.
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.
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.
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.
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.
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).
This page explains the idea. The "Database Views" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Views lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Database View in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
