Views: a saved question, not a saved answer

One definition, used everywhere

Three reports each define “active customer” in their own WHERE clause, and the three definitions have drifted. The fix is not to align the three clauses; it is to have one, in one place, that the three reports read from:

CREATE VIEW active_customers AS
SELECT c.*
FROM customers c
WHERE c.deleted_at IS NULL
  AND EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id
                AND o.placed_at >= current_date - interval '90 days');

SELECT count(*) FROM active_customers now means the same thing in every report, and when the business changes the definition to 60 days, it changes in one line and every report follows.

What a view is, mechanically

A view stores the query, not its result. Every time it is referenced, the engine substitutes the view’s definition into the calling query and plans the whole thing together, which means a view is exactly as fast or slow as the query it wraps, and it always reflects the current data. Filters applied to the view are pushed inside it where the planner can: SELECT * FROM active_customers WHERE city = 'Pune' uses an index on city if one exists, because after substitution it is a plain query over customers.

That last point has a limit. A view containing DISTINCT, an aggregate, or a window function is a barrier that outer filters cannot always be pushed through, and a view over a view over a view can produce a plan nobody can read. Views are for naming, not for stacking.

Views as an interface

A view can hide columns: CREATE VIEW customers_public AS SELECT id, display_name, city FROM customers leaves out phone, email and date of birth, and an analyst role granted access to the view and not the table cannot reach them. It can present a stable shape over tables that change: when orders is split into two tables next year, a view named orders with the old columns keeps every existing report working. And it can rename the awkward: a view over a vendor’s tbl_cust_mstr called customers is a small kindness to everyone who comes after.

Materialised views: when the answer is worth storing

A view whose query takes forty seconds is a report that takes forty seconds every time somebody opens it. A materialised view runs the query once and stores the rows:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', placed_at) AS month, city, sum(amount) AS revenue
FROM orders GROUP BY 1, 2;

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Reads are now instant, and the data is as old as the last refresh. That staleness is a property, not a defect, and it belongs in the report’s title: “as of 06:00 today”. CONCURRENTLY lets reads continue during the refresh and requires a unique index on the materialised view; without it the refresh locks readers out for its duration. Schedule the refresh, and put its timestamp somewhere a query can read, because “the dashboard is wrong” and “the dashboard is six hours old” are different conversations.

DuckDB and SQLite have views and not materialised ones; the equivalent is a table rebuilt by a scheduled CREATE TABLE ... AS. BigQuery has materialised views that refresh themselves within limits.

Writing through a view

A simple view over one table, with no aggregate or join, accepts INSERT, UPDATE and DELETE and passes them to the table. WITH CHECK OPTION refuses a write through the view that would produce a row the view cannot see, so that an insert through active_customers cannot create a deleted customer. Anything more complicated needs a trigger, and at that point a view is the wrong tool.

Dependencies

ALTER TABLE customers DROP COLUMN city fails if a view uses city, with an error naming the view. That is a feature: the database knows what depends on what and will not let a schema change silently break a report. DROP ... CASCADE overrides it and drops the views too, which is the option to read twice before typing.

The habit

When a definition matters to more than one query, make it a view. When a view is slow and its data can be minutes old, make it materialised and print the refresh time. When a view has a view inside it, ask whether one of them should be a table.