Database Views: Abstraction, Security, and Their Pitfalls

Almost every engineer has encountered a fifty-line SQL query copy-pasted into many different places — one in a monthly report, another in an API endpoint, another in an admin dashboard. When the business definition changes, all those places must be changed one by one, and almost always some get missed. Views exist to solve this problem, but ironically many teams also use them carelessly until views become a new source of confusion — layers of nested views where the original query is no longer recognizable. This article discusses what views are, how the database processes them behind the scenes, and most importantly: when the decision to use a view is right, and when it should actually be avoided.

What Is a View

A view is a virtual table — the result of a SELECT query that’s given a name and stored as an object in the database schema. The key word here is “virtual”: a view doesn’t physically store data. Every time you query a view, the database re-executes the underlying query in real time, then returns the result as if it were a regular table.

Think of a view as a smart alias for a query. You define it once, then use it repeatedly with the same syntax as querying a regular table.

CREATE VIEW v_active_customers AS
SELECT
    c.id,
    c.name,
    c.email,
    COUNT(o.id) AS total_orders,
    MAX(o.created_at) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.id, c.name, c.email;

Once this view is created, you can query it like a regular table:

SELECT * FROM v_active_customers WHERE total_orders > 5;

There’s no physical table named v_active_customers stored on disk. What’s stored is only the query definition. Every execution of SELECT * FROM v_active_customers, the database translates it back into the JOIN and GROUP BY query above, then executes it against the actual customers and orders tables.

flowchart TD
    A[Application: SELECT * FROM v_active_customers] --> B[Database Engine]
    B --> C{Check view definition}
    C --> D[Expand to the original query: JOIN + GROUP BY]
    D --> E[Execute against the customers & orders tables]
    E --> F[Result returned to the application]

Because of its virtual nature, a view always shows up-to-date data — there’s no stale data risk like a cache, unless you deliberately use a materialized view, which will be discussed later.

It’s important to understand that views aren’t a new concept or exclusive to any specific database. Almost all relational databases — PostgreSQL, MySQL, SQL Server, Oracle — have supported views since their earliest versions, because this concept is part of the SQL standard (ANSI SQL) itself. This means the skill of creating and understanding views is portable; once you understand the concept in one database, you can apply it with almost no mental model changes in another database, though the detailed syntax may vary slightly between vendors.

One common misconception is treating a view as a “lighter table” or some kind of performance shortcut. Conceptually, a view is better thought of as a name for a query, not as a table in the data-storage sense. When you drop a base table referenced by a view, the view immediately breaks and fails to execute — clear proof that a view has no data existence independent of its source tables.

How Views Work Behind the Scenes

Understanding the mechanism behind the scenes is important so you don’t have wrong performance expectations. When you create a view, the database does not execute the query at that moment and store the result. What happens is a process called view expansion or query rewriting — the database parser stores the query text as metadata, and every time the view is called, the query planner inserts that definition into the running query before optimizing and executing it.

sequenceDiagram
    participant App as Application
    participant Parser as Query Parser
    participant Optimizer as Query Optimizer
    participant Engine as Storage Engine
    App->>Parser: SELECT * FROM v_active_customers WHERE total_orders > 5
    Parser->>Parser: Detect that v_active_customers is a view
    Parser->>Parser: Substitute with the original query definition
    Parser->>Optimizer: Combined query (view + outer WHERE condition)
    Optimizer->>Optimizer: Plan the optimal execution plan
    Optimizer->>Engine: Execute against the base tables
    Engine-->>App: Query result

The practical implication: the WHERE total_orders > 5 condition in the outer query may be merged by the optimizer into the view’s logic before execution, depending on how smart the query planner is. In modern databases like PostgreSQL or newer MySQL versions, the optimizer is quite good at predicate pushdown — pushing filter conditions into the deepest part of the combined query so irrelevant rows don’t need processing. But this isn’t an absolute guarantee, especially for complex views with many JOINs and layered aggregations.

This also explains why views wrapped inside other views (nested views) can be a performance problem — every nesting layer adds complexity for the optimizer in composing an efficient execution plan.

Another thing to understand is that a view does not freeze the data structure at creation time. If you add a new column to the base table after the view is created, that new column doesn’t automatically appear in the view unless you defined the view with SELECT * — and even with SELECT *, some databases (like PostgreSQL) still lock the column list at the time the view definition is created, so new columns in the base table still won’t show up in the view until you re-run CREATE OR REPLACE VIEW. This behavior often surprises developers who assume views are always perfectly in sync with the source table structure.

Additionally, because a view is executed as part of the query that calls it, all security constraints like row-level security or additional WHERE conditions from the caller get merged into the final execution plan. This means indexes used to optimize queries against the base table remain effective even when accessed through a view — as long as the query planner can do predicate pushdown well.

View vs Running the Query Directly

A frequent question: if a view just “stores” a query and still gets re-executed every time, what’s the benefit compared to running that query directly whenever needed?

The performance point is important to clarify up front: a standard view doesn’t make queries faster. Because behind the scenes it still runs the exact same query. If you expect a view to be the solution for slow queries, that’s a wrong expectation — what you might need is better indexing, or a materialized view if the data can tolerate slight staleness.

The real benefit of views is on the human side, not the machine side: maintainability, security, and business logic consistency.

The analogy is similar to why you write functions in application code instead of copying the same logic repeatedly across many files. Functions don’t make the CPU work faster — the instructions ultimately executed remain the same. But functions make code easier to understand, test, and change in one place. Views play exactly that role, just at the database layer instead of the application layer. When you think “should I create a view for this?”, the more accurate question is actually the same one you ask when deciding to create a new function: will this logic be used more than once, and will writing it in one place reduce the risk of future inconsistency?

Main Purposes of Views

Abstraction of Query Complexity

A query with five JOINs, several CASE WHENs, and nested subqueries is hard for new developers to understand. By wrapping it into a clearly named view like v_active_customers, query consumers only need to understand the intent from the name, without reading the implementation details.

Access Control Without Data Duplication

You can create a view that only exposes some columns, then grant SELECT permission to that view without giving access to the original table.

-- A view hiding sensitive columns
CREATE VIEW v_public_employees AS
SELECT id, name, department, position
FROM employees;

-- Grant only to the view, not to the original employees table
GRANT SELECT ON v_public_employees TO role_junior_hr;

This also applies to row-level security in multi-tenant applications. For example you want each tenant to only see its own data:

CREATE VIEW v_orders_tenant_a AS
SELECT * FROM orders WHERE tenant_id = 'tenant_a';

This pattern is often combined with session or connection role mechanisms in the application, so each tenant is automatically directed to the appropriate view without manually adding a WHERE tenant_id = ? filter in every application query — although in modern databases, native row-level security (RLS) (like in PostgreSQL) is often a more robust alternative for this case.

Single Source of Truth for Business Logic

Definitions like “active customer”, “valid transaction”, or “best-selling product” often involve several conditions that are easily interpreted differently between teams. If that logic is defined in a view, everyone — data teams, backend, and BI alike — uses the identical definition.

-- ANTI-PATTERN: the "active customer" definition repeated in many places
-- File monthly-report.sql
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.created_at >= NOW() - INTERVAL '30 days'
);

-- File api-endpoint.sql (slightly different condition, a bug gap!)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.created_at >= NOW() - INTERVAL '31 days'
);

-- CORRECT: one definition in a view, used everywhere
CREATE VIEW v_active_customers_flag AS
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.created_at >= NOW() - INTERVAL '30 days'
);

Interface Stability During Schema Refactors

If you need to split the customers table into customers and customer_contacts for normalization reasons, applications already querying the old table directly will break. If those applications query through a view, you just update the view definition to keep producing the same output structure, while outside consumers don’t need to know anything about the changes behind it.

Simplifying Reporting and BI

BI tools like internal dashboards usually query pre-joined, pre-aggregated views, so analysts don’t need to understand the complex database schema to build reports. This also reduces the risk of analysts making join mistakes that produce duplicated data (fan-out) — a classic problem when someone unfamiliar with the database schema joins tables with one-to-many relationships without proper aggregation, causing report numbers to multiply unnoticed.

With views designed and verified by a team that deeply understands the database schema, BI analysts can focus on the report’s business logic instead of error-prone technical join details. This divides responsibility clearly: the data/engineering team maintains the correctness of query structures at the view level, while the BI team maintains the correctness of business interpretation at the report level.

Some modern databases like PostgreSQL also support updatable views — you can INSERT, UPDATE, or DELETE directly into a view as long as the view meets certain requirements (usually: based on a single table, no aggregation, no DISTINCT). Views with JOIN or GROUP BY are generally read-only unless you explicitly define an INSTEAD OF trigger.

Creating and Using Views

The basic view syntax is relatively uniform across relational databases, though there are slight dialect variations.

-- Creating a new view
CREATE VIEW v_best_selling_products AS
SELECT
    pr.id,
    pr.name,
    SUM(oi.qty) AS total_sold
FROM products pr
JOIN order_items oi ON oi.product_id = pr.id
GROUP BY pr.id, pr.name
ORDER BY total_sold DESC;

-- Changing an existing view's definition without dropping first
CREATE OR REPLACE VIEW v_best_selling_products AS
SELECT
    pr.id,
    pr.name,
    pr.category,
    SUM(oi.qty) AS total_sold
FROM products pr
JOIN order_items oi ON oi.product_id = pr.id
GROUP BY pr.id, pr.name, pr.category
ORDER BY total_sold DESC;

-- Dropping a view
DROP VIEW v_best_selling_products;

CREATE OR REPLACE VIEW is very useful in deployment workflows because you don’t need to worry about broken dependencies from a DROP followed by re-CREATE — permissions and dependencies on the view are preserved as long as the output column structure is compatible.

Updatable View vs Read-Only View

Not all views can be INSERTed or UPDATEd directly. The rules vary between databases, but generally:

-- Updatable: based on a single table, no aggregation
CREATE VIEW v_active_employees AS
SELECT id, name, department, status
FROM employees
WHERE status = 'active';

-- This is valid in most databases
UPDATE v_active_employees SET department = 'Engineering' WHERE id = 42;

-- ANTI-PATTERN: expecting a view with JOIN + GROUP BY to be directly UPDATE-able
-- This view is read-only in most databases without an additional trigger
CREATE VIEW v_customers_with_order_total AS
SELECT c.id, c.name, COUNT(o.id) AS total_orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- CORRECT: if you really need updates through a complex view, define an INSTEAD OF trigger
CREATE TRIGGER trg_update_customer
INSTEAD OF UPDATE ON v_customers_with_order_total
FOR EACH ROW
EXECUTE FUNCTION fn_handle_customer_update();

View vs CTE — When to Use Which

Many developers confuse views and CTEs (WITH ... AS) because both wrap a query for better readability. The fundamental difference is in lifecycle and scope.

This confusion is understandable because syntactically, both produce query results that can be referenced by name as if they were tables. But their usage contexts are very different. A CTE is defined at the start of one SELECT statement and only lives as long as that statement runs — once the query finishes executing, the CTE definition disappears without leaving any trace in the database schema. A view, by contrast, is a permanent object in the schema, persisting until you explicitly run DROP VIEW, and can be called from any query, anytime, by anyone with permission.

-- CTE suits one-time queries with intermediate steps
WITH customers_with_total AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, t.total_spent
FROM customers c
JOIN customers_with_total t ON t.customer_id = c.id
WHERE t.total_spent > 1000000;

The rule of thumb: if the logic is only relevant to one specific query and won’t be used elsewhere, use a CTE. If the logic is a business definition that will be used repeatedly across various queries and different applications, it’s a strong candidate for a view.

Materialized Views: When a Regular View Isn’t Enough

A standard view is always re-executed every time it’s called, which means for heavy queries — aggregating millions of rows for example — a standard view doesn’t help performance at all. This is where materialized views come in: the query result is actually physically stored on disk like a regular table, so queries against it become much faster.

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    category,
    SUM(total) AS total_sales
FROM orders o
JOIN products pr ON pr.id = o.product_id
GROUP BY DATE_TRUNC('month', created_at), category;

-- Data doesn't auto-update -- needs manual or scheduled refresh
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- Refresh without locking the table for reads (requires a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

The consequence: data in a materialized view can be stale — not reflecting the latest changes until the refresh process runs. This trade-off must be well understood: you’re trading real-time-ness for read speed.

flowchart LR
    A[Base Table Changes] -.not automatic.-> B[Materialized View]
    C[Scheduler / Cron] -->|Scheduled REFRESH| B
    D[Application Queries] -->|fast read| B

MySQL doesn’t natively support materialized views like PostgreSQL or Oracle. The common solution is creating a regular table populated via a scheduled job or trigger, then treating it like a materialized view manually. Make sure you know your database platform’s limitations before designing a materialized-view-based architecture.

When to use a materialized view versus a regular view:

USE a materialized view if:
  ✓ the base query is heavy (large aggregations, many complex JOINs)
  ✓ the data can tolerate slight staleness (minutes, hours, even daily)
  ✓ it's used repeatedly at high frequency (dashboards, reports)

USE a regular view if:
  ✗ the data must always be real-time
  ✗ the base query is already fast enough without materialization
  ✗ data changes happen so often that refresh becomes expensive

Performance: View vs Materialized View

After understanding how both work, it’s important to dissect the performance characteristics of each in more detail, because this is where misconceptions most often happen in the field.

A Standard View Neither Adds Nor Reduces Performance

This point was touched on earlier, but deserves more emphasis: because a view is just an abstraction layer over its original query, the execution time of a query through a view is essentially identical to executing the same query written directly — with a small additional overhead from the parsing and view expansion process, which is usually negligible (microseconds) compared to the query’s execution time itself.

What actually has a big impact on performance is the index on the base tables. A view has no indexes of its own — it only inherits the indexes that already exist on the tables it references. If a view’s base query is slow because of missing indexes, wrapping it in a view won’t make it any faster. Conversely, if you add the right index to the base table, all views depending on that table automatically get faster too, without needing to change the view definitions themselves.

-- This view is slow not because of the "view", but because created_at isn't indexed
CREATE VIEW v_orders_this_month AS
SELECT * FROM orders WHERE created_at >= DATE_TRUNC('month', NOW());

-- The solution isn't changing the view structure, but adding an index on the base table
CREATE INDEX idx_orders_created_at ON orders (created_at);

Query Planner Risks on Complex Views

For simple views based on a single table with a WHERE filter, modern optimizers can almost always do predicate pushdown perfectly, so performance is identical to a manual query. But for views involving many JOINs, nested subqueries, or window function aggregations, the optimizer sometimes struggles to compose the optimal execution plan — especially when such views are called with additional WHERE conditions from outside that should be pushed deep inside, but aren’t always fully pushed depending on the database dialect and version.

Nested views worsen this risk. Each nesting layer adds work for the optimizer to “unwrap” the entire definition chain before it can compose a single efficient execution plan. In some extreme cases, three-to-four-layer nested views can turn a query that should run in milliseconds into one taking seconds, because the optimizer fails to recognize that many filter conditions in the outer layers could actually be simplified long before the big join happens.

Materialized Views: Read Speed vs Data Freshness Trade-off

Materialized views solve the performance problem in a completely different way: instead of relying on the optimizer to speed up re-execution, they eliminate the need for re-execution itself by physically storing the result. As a consequence, queries against a materialized view run at the same speed as queries against a regular table — because physically, that’s what they are.

But this speed comes with two hidden costs often overlooked:

Storage cost. A materialized view physically duplicates data, adding disk requirements. For large aggregations from multi-million-row tables, the materialized view itself can be quite large depending on the cardinality of the aggregation result.

Refresh cost. The REFRESH MATERIALIZED VIEW process essentially re-runs the full base query (unless you use incremental refresh, supported by some databases). For heavy queries, the refresh process itself can take significant time and burden the database, especially if scheduled too often or run during high traffic.

Indexing on Materialized Views

One thing often missed: because a materialized view physically stores data, you can — and should — create indexes on it, exactly like a regular table.

-- Add an index on the materialized view to speed up further queries
CREATE INDEX idx_mv_monthly_sales_category
ON mv_monthly_sales (category);

-- A unique index is also required so REFRESH CONCURRENTLY can be used
CREATE UNIQUE INDEX idx_mv_monthly_sales_unique
ON mv_monthly_sales (month, category);

Without these additional indexes, queries against a large materialized view can still be slow even though the data is already “frozen” — because the database still has to do a sequential scan if no index supports the filter conditions being used.

When You Should Use Views

USE views if:
  ✓ query logic is reused across many places (reports, APIs, dashboards)
  ✓ you need to restrict column/row access without duplicating data
  ✓ business definitions (e.g. "active customer") need consistency across teams
  ✓ you want a stable interface when the base table schema changes
  ✓ queries are complex enough that readability is a real problem

When You Shouldn’t Use Views

AVOID views if:
  ✗ the query is only used once and won't be reused
  ✗ you expect a view to automatically speed up heavy queries (use a materialized view)
  ✗ it will create layers of nested views that are hard to debug
  ✗ the team lacks naming/documentation conventions, making views a "black box"

Nested views — a view calling another view, which calls yet another view — are the most common performance trap. Each nesting layer adds burden for the query optimizer to compose an efficient execution plan, and in some databases the optimizer can fail to do predicate pushdown well across many layers. Additionally, debugging becomes a nightmare because you must trace through layers of definitions just to know which base tables are actually being accessed.

Common Anti-Patterns

-- ✗ Layers of nested views, hard to trace and heavy for the optimizer
CREATE VIEW v_layer1 AS SELECT * FROM orders WHERE status = 'paid';
CREATE VIEW v_layer2 AS SELECT * FROM v_layer1 WHERE created_at >= NOW() - INTERVAL '90 days';
CREATE VIEW v_layer3 AS SELECT customer_id, SUM(total) FROM v_layer2 GROUP BY customer_id;

-- ✓ One definition directly against the base table, easier to optimize and read
CREATE VIEW v_90day_total_spend AS
SELECT customer_id, SUM(total) AS total_spend
FROM orders
WHERE status = 'paid'
  AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY customer_id;

-- ✗ A view hiding an expensive query without the caller realizing it
CREATE VIEW v_all_orders_with_details AS
SELECT o.*, c.name, pr.name AS product_name, k.name AS category
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products pr ON pr.id = oi.product_id
JOIN categories k ON k.id = pr.category_id;
-- Called without a WHERE by a new developer who doesn't know this is a JOIN-layered full scan

-- ✓ Give names implying data scale, or add documentation/comments
-- This view MUST be called with an order_id or customer_id filter
COMMENT ON VIEW v_all_orders_with_details IS
    'Heavy -- always filter by order_id or customer_id, don't query without a WHERE';

View and Materialized View Best Practices

After understanding the concepts, purposes, and performance of views, here’s a collection of practices that help you use views healthily long-term — not just functional today, but still easy to maintain six months or a year from now.

1. Use a Consistent Naming Convention

Prefixes like v_ for views and mv_ for materialized views let anyone immediately know what kind of object they’re using without checking database metadata. This consistency also makes searching easier in database client tools and automated documentation.

-- ✗ Ambiguous name, unclear whether this is a view or a regular table
CREATE VIEW active_customers AS ...;

-- ✓ Clear prefix indicating the object type
CREATE VIEW v_active_customers AS ...;

CREATE MATERIALIZED VIEW mv_monthly_sales AS ...;

2. Document Definitions and Usage Constraints

Views that are computationally expensive or have specific constraints (for example must be filtered by a certain column) should be documented directly at the database level, not just in an internal wiki that easily goes stale.

COMMENT ON VIEW v_active_customers IS
    'Customers with at least one order in the last 30 days. Used by the monthly report and the /api/customers/active endpoint.';

3. Avoid Nested Views Deeper Than Two Layers

Limit the nesting depth to a maximum of two layers. If you find yourself creating a third view that calls a second view that calls a first view, that’s a strong signal to refactor into a single definition directly against the base table, as discussed in the anti-patterns section.

4. Always Test the Execution Plan Before Deploying

Before pushing a new view to production, run EXPLAIN ANALYZE against the queries calling it to make sure the optimizer is actually doing predicate pushdown as expected, especially for views with many JOINs.

EXPLAIN ANALYZE
SELECT * FROM v_active_customers WHERE total_orders > 5;

If the EXPLAIN output shows a large sequential scan on the base table when you expect an index scan, that’s a sign the view needs revision or the base table needs an additional index.

5. Set a Clear Refresh Strategy for Materialized Views

Don’t let materialized view refresh schedules be determined ad-hoc. Set an interval matching the business staleness tolerance — an executive dashboard might only need hourly refreshes, while a daily report is fine with one refresh overnight.

-- Example scheduled refresh with pg_cron in PostgreSQL
SELECT cron.schedule(
    'refresh-monthly-sales',
    '0 * * * *',  -- every hour
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales'
);

6. Always Create a Unique Index Before Using Concurrent Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY allows refreshing without locking the table for reads, but requires a unique index. Without it, the refresh will lock the materialized view completely during the process, which can impact applications reading it.

7. Audit View Usage Periodically

Over time, some views become unused because the features using them were removed, but the views themselves remain in the schema. Views piling up without audit make the database schema hard for new developers to understand and increase the risk of someone accidentally depending on a view that should have been deprecated.

-- PostgreSQL: check a view's dependencies before dropping it
SELECT dependent_ns.nspname AS dependent_schema,
       dependent_view.relname AS dependent_view
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace AS dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
WHERE source_table.relname = 'v_active_customers';

8. Limit Exposed Columns, Don’t Always SELECT *

For views serving as an access control layer, avoid SELECT * because sensitive columns added to the base table in the future could accidentally get exposed if the view is carelessly redefined. List columns explicitly.

-- ✗ Prone to future sensitive column leaks
CREATE VIEW v_public_employees AS SELECT * FROM employees;

-- ✓ Explicit columns, safe from new sensitive column additions
CREATE VIEW v_public_employees AS
SELECT id, name, department, position FROM employees;

A short checklist that can be used as a quick reference:

BEFORE CREATING A NEW VIEW:
  □ Name uses a consistent prefix (v_ / mv_)
  □ Definition and usage constraints documented
  □ Columns listed explicitly, not casually SELECT *
  □ EXPLAIN ANALYZE checked for views with many JOINs
  □ Not adding more than two layers of nested views

SPECIFIC TO MATERIALIZED VIEWS:
  □ Refresh strategy (interval, trigger, or manual) determined
  □ Unique index created so REFRESH CONCURRENTLY can be used
  □ Additional indexes created matching frequently used query patterns
  □ Teams depending on this data understand its staleness tolerance

PERIODIC MAINTENANCE:
  □ Audit views that are no longer used
  □ Re-review execution plans after significant data volume growth

Summary

• A view is a virtual table from a SELECT query stored as a definition, not physical data — every call re-executes against the base tables.
• A standard view’s performance is the same as its original query — views aren’t a solution for speeding up slow queries.
• The main benefits of views are on the maintainability, security, and business logic consistency side — not performance.
• Use views for complexity abstraction, granular access control, a single source of truth for business definitions, and keeping interfaces stable when schemas change.
• CTEs suit one-off logic within a single query; views suit logic reused across queries and applications.
• Materialized views physically store results so queries become fast, but data can be stale until refreshed — good for heavy queries tolerant of data delay.
• Avoid layers of nested views because they hinder the query optimizer and make debugging difficult.
• Don’t use views for queries used only once, and always document views wrapping heavy queries so they aren’t called carelessly without filters.
• Apply consistent naming conventions (v_ / mv_), list columns explicitly instead of SELECT *, and periodically audit unused views.
• For materialized views, make sure the refresh strategy and unique index are determined up front so REFRESH CONCURRENTLY can be used without locking reads.