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) );
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;
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.
Querying the Materialized View:
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.