Types PostgreSQL Views

As I move more into PostgreSQL from MS SQL Server, I am going through different aspects of PostgreSQL in relative to SQL Server. I’m looking at the different types of views.

Let’s assume we have customer and order tables below for the base example.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    order_amount DECIMAL(10, 2)
);

Standard Views

These are the most common views. They represent a virtual table based on the result-set of a SELECT statement. They hide the complexity of data and can be used like regular tables.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Customer/Order Standard View

CREATE VIEW customer_orders AS
SELECT 
    o.order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    o.order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id;

Querying the Standard View:

SELECT * FROM customer_orders;

Materialized Views

Unlike standard views, materialized views store the query result at the time of creation and can be refreshed periodically. They are useful when you want to cache heavy queries and update the results periodically rather than recomputing them with every access.

CREATE MATERIALIZED VIEW mv_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Customer/Order Materialized View

We want to aggregate the total amount spent by each customer. A materialized view can help with this, especially if the dataset is large and we want to avoid performing this aggregation every time we query:

CREATE MATERIALIZED VIEW total_spent_by_customer AS
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    SUM(o.order_amount) AS total_spent
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.first_name, c.last_name;

With this, the aggregated data gets stored in the materialized view. Remember, materialized views store their result data, unlike standard views, and therefore can become stale.

SELECT * FROM total_spent_by_customer;

Refreshing the Materialized View:

Since the data in a materialized view can become stale as the underlying tables change, you’ll occasionally need to refresh the view:

REFRESH MATERIALIZED VIEW total_spent_by_customer;

Using materialized views can help to improve performance for complex queries, especially when the underlying data doesn’t change frequently, but the computation is expensive.