Views, Materialized Views, and Temporary Tables

---

Views

A view is a logical database object that behaves like a virtual table. It is essentially a named, stored SQL query that references underlying tables. When you query a view, the database substitutes the view’s definition into your query, the optimizer rewrites and optimizes the combined statement, and then executes it as a single unified query. Views do not store physical data (unless materialized); they always derive their results from the base tables at query time.

Views are persistent objects stored in the database catalog, meaning they exist across sessions and can be used by other users, pipelines, or tools to simplify repeated logic.

---

What Views Are

• A view is a virtual table defined by a stored SELECT query.

• It contains no physical data, except in the case of materialized views.

• When queried:

  • the database expands the view by injecting its SQL into the outer query,

  • the optimizer adjusts and rewrites the full query,

  • and executes it as one plan.

• Views can be shared across applications, ETL pipelines, and users to avoid duplicating complex logic.

---

View Dependencies

Views depend on the tables (or other views) referenced in their definition. Because of this:

• Dropping or altering base tables often fails if dependent views exist.

• You may need to drop or recreate the view before modifying those underlying objects.

---

Creating a View

CREATE VIEW customer_info AS
SELECT
    c.firstname,
    c.lastname,
    c.email,
    c.phone,
    a.city,
    a.postal_code,
    co.country
FROM customer c
JOIN address a ON a.id = c.address_id
JOIN city ci   ON ci.id = a.city_id
JOIN country co ON co.id = a.country_id;

This example combines four tables into a single wide, easy-to-query virtual table.

---

Why Views Are Useful

1. Simplifying Complex Queries

Views encapsulate frequently used joins or business logic, letting users work with a cleaner interface.

Without a view:

SELECT firstname, lastname, email, city
FROM customer
JOIN address ON address.id = customer.address_id
JOIN city ON city.id = address.city_id;

With a view:

SELECT firstname, lastname, email, city
FROM customer_info;

Analysts and applications avoid rewriting multi-table joins repeatedly.

2. Security and Access Control

Views can filter rows or expose only specific columns, allowing controlled access to sensitive data. Different views can be created for different roles.

Example: a marketing analyst needs only contact information:

CREATE VIEW marketing_contacts AS
SELECT firstname, lastname, email
FROM customer;

The view hides internal IDs, financial fields, and other restricted attributes.

3. Presenting Clean or Deduplicated Snapshots

In append-only or insert-only systems, a view can apply deduplication logic and show only the latest version of each record, giving consumers an up-to-date representation without manually filtering historical entries.

4. Reusing Logic Across Pipelines

If multiple BI tools, ETL pipelines, or dashboards depend on the same complex logic, a view centralizes that logic in one place. Updating the view updates every downstream consumer automatically.

---

Temporary Tables and Materialized Views

Both temporary tables and materialized views store data, not just query logic. Unlike standard views (which are computed on demand), these objects physically hold the results of a query, enabling better performance and more efficient processing in many workflows.

---

Materialized Views

A materialized view (MV) is a database object that stores the precomputed results of a query. Instead of recalculating the query every time, the database reads directly from the stored results.

Characteristics

• Stores the output of a query.

• Performs computations in advance, reducing query time.

• Caches results on disk; you must refresh the view to update the data.

• Can be refreshed manually, on schedule, or incrementally (depending on the database engine).

• Always introduces some latency between refreshes and underlying table changes.

• Persists until explicitly dropped.

Example

CREATE MATERIALIZED VIEW rental_by_category AS
SELECT
    category.name AS category,
    SUM(payment.amount) AS total_sales
FROM payment
JOIN rental       ON rental.rental_id = payment.rental_id
JOIN inventory    ON inventory.inventory_id = rental.inventory_id
JOIN film         ON film.film_id = inventory.film_id
JOIN film_category ON film_category.film_id = film.film_id
JOIN category     ON category.category_id = film_category.category_id
GROUP BY category.name;

This materialized view pre-aggregates sales by category so analytics queries run instantly without recomputing multi-table joins.

---

Temporary Tables

A temporary table stores query output just like a normal table, but its lifetime is limited to a session or a single transaction.

Characteristics

• Stores the physical result of a query.

• Exists only for:

  • the duration of the session, or

  • the duration of a transaction (if declared ON COMMIT DROP)

• Automatically cleaned up by the database.

• Isolated per user: different sessions can create temp tables with the same name.

• Often faster than regular tables due to lighter logging and caching.

---

Key Advantages of Temporary Tables

1. Performance Benefits

• Usually reduced logging/WAL writes, making operations faster.

• No concurrency overhead because temp tables are isolated to a session.

• Small temp tables often stay memory-resident, giving very fast access.

• No long-term storage footprint.

2. Isolation Benefits

Different sessions can create identically named temp tables without conflict:

-- User 1 session
CREATE TEMP TABLE work_data AS
SELECT * FROM orders WHERE user_id = 1;

-- User 2 session (same name, different data)
CREATE TEMP TABLE work_data AS
SELECT * FROM orders WHERE user_id = 2;

3. Memory Optimization

Useful for small lookup or intermediate tables:

CREATE TEMP TABLE small_lookup (
    id INTEGER,
    value TEXT
);

-- Fast lookups because many engines keep small temp tables in memory

4. ETL and Data Warehousing

Temp tables simplify multi-step transformations:

-- Extract subset for today's operations
CREATE TEMP TABLE daily_extract AS
SELECT *
FROM source_system
WHERE extract_date = CURRENT_DATE;

-- Perform multiple transformation steps...
-- Final stage loads into permanent warehouse tables

5. Application / Transaction Logic

Temp tables can support complex logic inside a transaction:

BEGIN;

CREATE TEMP TABLE order_calculation (
    order_id INT,
    subtotal NUMERIC,
    tax NUMERIC,
    discount NUMERIC
) ON COMMIT DROP;

-- Perform multi-step calculations
-- Use the temp results to update permanent tables atomically

COMMIT;

---

Summary

Feature	Materialized Views	Temporary Tables
Stores data?	✔ Yes	✔ Yes
Lifetime	Permanent (until dropped)	Session or transaction
Refresh needed?	✔ Yes	✖ No
Performance purpose	Precompute expensive queries	Fast, isolated intermediate data
Best for	Dashboards, analytics, pre-aggregations	ETL, staging, multi-step logic, ad-hoc workflows

---